Overview of ADO Programming
gra ming using ADO with KB_SQL is just as it is for using ADO with any other ata Source.
A p
C uses a syntactical . The escape clause consists of a pair of curly braces {} which surround the standard SQL syntax form and a token which specifies the type of clau . Dat T d form of DATE types is: 10:30 AM 0:30 AM
p endixes
A - Escape Sequences
Extensions SQL ExtensionsKB_SQL API supports several ODBC SQL extensions:
• the representation of date and time literals
• the syntax for scalar functions
• LIKE predicate escape characters
• stored procedures
These extensions provide essential functionality that is shared by most DBMSs. tax required by the DBMSs varies, ODB
However, because the syn notation called the escape clause
se
,
e ime, and TimeStamp
Date, Time, and Timestamp Data
KB_SQL API supports the DATE, TIME, and TIMESTAMP (a combine hese data and TIME) data types. The shorthand escape clause syntax for t
{d 'value'} {d '1999-04-28'} April 28, 1999 {t 'value'} {t '10:30:00'}
{ts 'value'} {ts '1999-04-28 10:30:00'} April 28, 1999 at 1
KBSODBCDriver
For a underscore is
a litera LA _
Using the escape character prevents the engine from interpreting the underscore as a LE,
Sca r
Scalar ( pute
absolut e or time, or return a substring. The on-line query
SQ F ctions
and the
Functi Description
SQ F
SQL_F
SQL_FN_CEILING(numeric_exp) Returns smallest integer
ASCII code ng SQL rules
ate_exp) Returns day name
QL_FN_DAYOFMONTH(date_exp) Returns day of month based on date value
date_exp) Returns day of year
ENCE(string_exp1, string_exp2) Difference in Soundex codes
Returns largest integer less than or equal to value Returns hour for time value
<value>) Formats an
SQL_IDENTIFIER value
alue) Returns NULL or value
APS(Value) Format value using initial
capitals
NSERT(string_exp1, start, length, string_exp2) Overwrite characters in ex mple, to obtain all columns for the LAB_PATIENT table where the
l in the SQL_IDENTIFIER, you send the table name parameter as: B\ PATIENT
wildcard, thus avoiding retrieving columns for tables that could be named LABaTAB LABbTABLE, and so forth.
Scalar functions
la Functions
or row) functions operate on a single value. They include functions that com e value or length, format a dat
L_ UNCTION_LIST provides a complete list of the KB_SQL API scalar fun ir syntax.
on
L_ N_ABS(numeric_exp) Returns absolute value
N_ASCII(string_exp) Returns the ASCII code
value of the leftmost character
greater or equal to value
SQL_FN_CHAR(code) Returns the character of the
SQL_FN_CONCAT(string_exp1, string_exp2) Concatenates two values usi
SQL_FN_DAYNAME(d S
SQL_FN_DAYOFWEEK(date_exp) Returns day of week
(1=Sunday) SQL_FN_DAYOFYEAR( SQL_FN_DIFFER SQL_FN_FLOOR(numeric_exp) SQL_FN_HOUR(time_exp) SQL_FN_IDENTIFIER( SQL_FN_IFNULL(exp,v SQL_FN_INITIAL_C SQL_FN_I 72
string
tring_exp) Convert to lowercase
) Returns the leftmost count
of characters in string Number of characters in
ks starting position of exp1 in exp2
Returns minute of hour
p2) Returns remainder
(modulus) of integer_exp1 divided by inte2
_MONTH(date_exp) Returns month based on
date value
Returns name of month IRST(<DateValue>) Returns the date value of
next first of month Returns the date value of the next month end date DateValue>) Returns the date value of
the next weekday
_NEXT_WEEKEND_DAY(<DateValue>) Returns date value of next weekend day
(numeric_exp,integer_exp) Returns value raised to power
p) Returns quarter (1-4) for
date value
Returns random number using value as seed L_FN_REPEAT(string_exp, count) Repeat string n-times SQL_FN_REPLACE(string_exp1,string_exp2,string_exp3) Replace all occurrences of
s2 in s1 with s3 SQL_FN_LCASE(s
SQL_FN_LEFT(string_exp, count SQL_FN_LENGTH(strin
string less trailing blan g_exp)
SQL_FN_LOCATE(exp1,exp2[,exp3]) Returns
SQL_FN_LTRIM(string_exp) Strip leading blanks
SQL_FN_MINUTE(time_exp) SQL_FN_MOD(integer_exp1,integer_ex SQL_FN SQL_FN_MONTHNAME(date_exp) SQL_FN_NEXT_MONTH_F SQL_FN_NEXT_MONTH_LAST SQL_FN_NEXT_WEEKDAY(< SQL_FN SQL_FN_POWER SQL_FN_QUARTER(date_ex SQL_FN_RAND(integer_exp) SQ
KBSODBCDriver valu SQL_FN_SIG e N(num ero), SQL_FN_SOUNDE SQL_FN_SPACE(c
SQL_FN_SQRT(flo Returns the square root of
SQL_FN_SUBSTR string value TA interval, integer_exp,timesta SQL_FN_TIMESTAMPDIFF( timestamp_exp1,tim
Returns integer number of
A
CA
SE(s d
SQL_FN_YEAR(date_exp) Returns year of date value
icate
f the LIKE predicate escape clause allows for searches to include metacharacters. In a LIKE predicate, the underscore character (_) is used for single-
haracter replacement and the percent sign (%) is used for multiple-character
replacement. The percent and underscore characters can be used as literals in a LIKE ODBC queries looks like:
mple ape ‘\’} mple s aab aad aazz 23 aa_2 abc ade
eric_exp) Returns -1 (neg), 0 (z
or 1 (positive)
X(string_exp) Returns site-determined
value of string
ount) Returns string of n-spaces
at_exp)
value
ING(string_exp,start,length) Returns sub-component of SQL_FN_TIMES MPADD(
mp_exp)
Add interval to timestamp
interval,
estamp_exp2)
intervals between m1 and m2
MPPART(<Part>,<TimeStamp>) Returns a
SQL_FN_TIMEST specified part of a
timestamp SQL_FN_TRUN
SQL_FN_UCA
TE(numeric_exp,integer_exp) Truncates numeric value
tring_exp) Converts all lowercase
characters to uppercase
SQL_FN_WEEK( ate_exp) Returns week of year based
on date value
LIKE Pred
LIKE Predicate Escape Characters
The support o c
predicate by preceding them with an escape character. The escape clause for
{escape ‘escape-character’} Exa {esc Exa Item aac aa_1 45 74
SQL without escape character code item_m h esc characte de m_m r ode l ‘aa\_%’ he d lt LIKE ).
the escape clause syntax: {escape '\'}.
he S Editor su s not use the {} syntax.
xam de m_m r ode l ‘aa\_%’ itor mple de m_m r
ode l ‘aa\_%’ escape ‘\’
re C
stored procedures as a collection of
pi L erver.
• An application can call a procedure in place of an SQL statement.
B_SQL rts manent stored procedures.
select rom
f aster
where code like ‘aa_%’
Returns aab aac aad aazz aa_123 aa_245 SQL wit ape r select co
from ite aste
where c ike Returns aa_123 aa_245 Notes • T
• The escape character can be specified using efau escape character is '\' (backslash
• T QL pports the feature, but doe
ODBC E ple
select co
from ite aste
where c ike {escape ‘\’}
SQL Ed Exa select co from ite where c aste ike Procedu alls
• The KB_SQL Server API supports the use of precom led SQ statements residing on the s
KBSODBCDriver
Ou
The KB_SQL Outer Join syntax is proprietary to KBS. For generic applications, use of
this ould pro lication. To avoid this limitation,
generic applications use the ODBC escape clause to encapsulate the outer join syntax,
allowing the specific driv necessary. This is
exactly what KB_SQL does.
ses e seque ns. The syntax of this escape sequence is as
r-joi
uter- is: eren EFT | R ch-co ion
t any terms in {br erms would appear in a
or e ple, an o aracterized as one of LEFT, RIGHT, or
o that the outer-jo cification.
h the ing of o not
sup this cap
onn tion O
BC i ace allow which a client application can
connection to a ice of option is sometimes
left tion such Office products. Other situations give
the developer the choice of connection strategy. These strategies are basically trading off l co rations,
ontr er appe e bu g a custo d to custome ng S riverCo
het r not yo e to define the parameters
f the nection.
e bu g a custo ou might want total control over the
on p eters. Y ing the creation of the data
ou c lso cont ion parameter value information
plica code an re-defined data source. See File Data -less Co
ter Joins
syntax w com mise the portability of the app
er to translate into a native syntax where ODBC u scape nces for outer joi
follows:
{oj oute n}
where o join
table-ref ce {L IGHT | FULL} OUTER JOIN {table-reference | outer-join} ON sear ndit
Note tha aces} indicate that only one of the t phrase. F xam uter join would be ch
FULL.
Note als in term can be included as part of the outer-join spe _SQL does Althoug nest uter joins might be a desired feature, KB
currently port ability as of 10/18/2001.
B - C
ec
ptions
Connection options
The OD nterf s some flexibility in the ways in server-based data source. The cho achieve a
to the applica as the case of Microsoft of severa nside including:
• C ol ov arance of dialogs in your application.
If you ar ildin m application, you will want to control the dialogs that are presente
NOT usi
your QLD
rs. At a minimum, his translates to using SQLConnect and nnect.
• W her o u require a pre-configured data sourc
o con
If you ar ildin m application, y
connecti aram ou can accomplish this by controll source. Y an a rol this by putting the connect in the ap tion d connecting without a p Sources and DSN nnections.
• When g a com n, your options are limited to the choices ade he devel
es this means tha te a certain type of connection and
onne n strateg hen evaluating a new ODBC-enabled
ion, i aluable options that are supported.
nnec
nnec he simpl quires a data source name and
an op al user ID ell for applications that hard-code a
rce n and do n ord. It also works well for
ions t ant to c l,” or that have no user interface.
plications can bui SQLDataSources; prompt the
data ce, user n call SQLConnect.
Con function a driver and a data source. The
on h e referen out the connection to the data
nclu status, t ation.
es th lowing: val nnection val B_SQL val B_SQL ting a C pr ect t ta sourc LCo t(hdbc, le AR Sales", S ourceName AR DBA", S ername
AR SHARK is the password
tion a M pr C^S S("M" he D ourceN is the rname K is passwo is the nection
usin mercial applicatio
m by t opers.
Sometim t you are forced to crea e. W certain c ctio ies are not availabl applicat
SQLCo
t is v
t
to review the connection
SQLCo t is t est connection function. It re
accepts tion and password. It works w data sou ame ot require a user ID or passw applicat hat w ontrol their own “look and fee
using Such ap ld a list of data sources user for
The SQL
sour nect
ID, and password; and the establishes connections to ces storage of all informati
connecti andl on ab
ransaction state, and error inform source, i ding
It requir e fol
• A id co handle (hdbc).
• A id K username.
• A id K password.
Connec from ogram.
/* Conn rc = SQ
o da nnec
e */
// hdbc is the connection hand QL_NTS, // Sales is the DataS (SQLCH *) "
(SQLCH *) " QL_NTS, // DBA is the us
(SQLCH *) " ", SQL_NTS); // SHARK
Connec from ogram.
S rc=$$ QLO , "DBA", "SHARK", hdbc)
// M is t ataS ame
// DBA use
// SHAR the rd
KBSODBCDriver
• To le applicat mation.
o re t that the user for connection information.
• To connect without specifying a data source.
n alternative to SQLConnect. It supports data sources that information than the three arguments in SQLConnect, dialog
information, and data sources that are not ystem information.
provides the following connection attributes:
string that contains the data source
he tion to a data source that is not defined in the system
rmation. If the application supplies a partial connection the driver can prompt the user for connection information. See DSN-Less Connections.
ce using a connection string constructed from See File Data Sources.
DriverConnect returns the leted connection r subsequent connection requests. For more documentation.
ct function is not available in the M-based interface. For
s, use the SQLConnect function.
without DSN, or a file DSN, to connect to ction properties supp
er t
#include <sqlext.h>
t the ion use driver-specific connection infor
• T ques driver prompt the
SQLDriverConnect is a
require more connection
boxes to prompt the user for all connection defined in the s
SQLDriverConnect
• Establish a connection using a connection
name, one or more user IDs, one or more passwords, and other information required by the data source.
• Establish a connection using a partial connection string or no additional information; in this case, the Driver Manager and the driver can each prompt t user for connection information.
• Establish a connec
info string,
• Establish a connection to a data sour the information in a .dsn file.
After a connection is established, SQL comp
string. The application can use this string fo information, see the Microsoft ODBC Note that the SQLDriverConne connections from M program
DSN-less Connections
If you are using ODBC 3.0 and a connection
your server, you can set the additional conne orted by the driver.
Connection Parameters
UID=UserName PWD=PassWord
Driver=KB_SQL ODBC 32-bit Driv Host=ServerAddress TCP_PORT=ServerListeningPor Connection Example // DsnLessConnect.cpp #include "stdafx.h" #include <windows.h> int main() { 78
SQLHENV hEnv; SQLHDBC hDbc; SQLRETURN rc;
SQLCHAR szConnStrOut[256];
SQLCHAR szConnStrIn[] = "DRIVER=
K;TCP_PORT=6500;HOST=192.9.90.36;"; StrOut, cbConnStrOutLen n = SQL_DRIVER_COMPLETE_REQUIRED; ConnStrOutLen, sg[128]; = 128, pcbErrorMsg; rrorMsg); KB_SQL ODBC 32-bit Driver;UID=DBA;PWD=SHAR
SQLSMALLINT cbConnStrIn, cbConn SQLHWND hWnd = NULL; ; SQLSMALLINT fDriverCompletio cbConnStrIn = cbConnStrOut = 256; rc = SQLAllocEnv(&hEnv); rc = SQLAllocConnect(hEnv, &hDbc); rc = SQLDriverConnect( hDbc, hWnd, szConnStrIn, SQL_NTS,
szConnStrOut, cbConnStrOut, &cb fDriverCompletion);
if (rc != SQL_SUCCESS) {
SQLCHAR szSQLState[10], szErrorM SQLSMALLINT cbErrorMsgMax SQLINTEGER pfNativeError; rc = SQLError(NULL, hDbc, NULL, szSQLState, &pfNativeError,
szErrorMsg, cbErrorMsgMax, &pcbE } rc = SQLDisconnect(hDbc); rc = SQLFreeConnect(hDbc); rc = SQLFreeEnv(hEnv); return 0; }
C - Error Messages
KBSODBCDriver
[module_name][version_number]API_function; error_message
Description
tandard naming sch r errors cess Group (SAG). eturned native
ppropriate SQLSTATE code. If the oes not have a correspondi
the KB_SQL ODBC Driver returns SQLSTA ss violation).
Native
SQLSTATE SQLSTATE codes are a s
the SQL Ac eme fo r developed by error code is The mapped to the a
native error code d ng SQLSTATE code,
TE 37000 (Syntax error or acce
_error_code The native error code number will be included for any error
rated by the KB_SQL Server API. It corr _SQL specific error message.
of the provider of the
the error. In the example below, it is KBS, the provider of the Server icrosoft, if the module he ODBC Driver is is the module responsible for the error. I
is the KB_SQL Server API. Errors can also be tagged with KB_SQL Driver for errors generated by the driver.
Version_number This is the version number of the module that reported the error. In
below, it is version 3.6
API_function This is the name of the API function that was last invoked. This ill prefix the first error generated for a par
function call.
he native error text that corresponds to the native error code. In the example below, the test corresponds to the error code for
ased Systems, Inc.][Server API][V3.6] SQLConnect: User
Note: For a complete list of all the possible errors and their descriptions, see one of the
TATE or SQL_ERROR_LIST
SQLCODE
owing values for SQLCODE. See also, the
Description
Function completed Function failed. _HANDLE Network failure.
WITH_INFO Function completed successf nonfatal error.
The application needs to send parameter
gene esponds to a KB
Vendor_name This is the vendor name module that reported
API. It could also be M Manager.
was t
Module_name Th n the example below, it
ODBC the example
name w ticular API
Error_message This is t #530.
Example
28000 (530): [Knowledge B name / password failure
following queries: SQL_API_SQLS .
Error processing typically handles the foll SQLError function.
SQLCODE ODBC Value
0 SQL_SUCCESS successfully.
-1 SQL_ERROR
-2 SQL_INVALID
1. SQL_SUCCESS_ ully with a
99 SQL_NEED_DATA
data values.
100 SQL_NO_DATA_FOUND No data found (or End-of-Data)
odes that can be generated with diagnostic or function.
tion
overflow. 22012 -1 Division by zero.
String data, length mismatch. y constraint violation.
te. nsaction state.
ent name.
Triggered data change violation. ification.
ber.
syntax error or access violation. ollback.
40001 -1 Serialization failure. n.
mation
SQLSTATE Mappings
The following table shows the SQLSTATE c o, the SQLErr messages from KB_SQL. See als
SQLSTATE SQLCODE Descrip
21000 -1 Cardinality violation. 22000 -1 Data exception.
22001 -1 String data right truncation. 22003 -1 Numeric value out of range. 22005 -1 Error in assignment.
22008 -1 Datetime field 22026 -1
23000 -1 Integrit
24000 -1 Invalid cursor sta 25000 -1 Invalid tra
26000 -1 Invalid statem 27000 -1
28000 -1 Invalid authorization spec 33000 -1 Invalid SQLDA name.
Invalid cursor name. 34000 -1
35000 -1 Invalid condition num
37000 -1 Dynamic SQL
40000 -1 R
42000 -1 Syntax error or access violation. 44000 -1 Check option violatio
70100 -1 Operation aborted.
00000 0 Success.
01000 1. Success with infor 01002 1. Disconnect error.
KBSODBCDriver
1. S02 1. Option value changed. leted.
or deleted. ta found
or.
of parameters.
type attribute violation.
ect to data source. n in use.
t open.
cted establishment of connection. during transaction.
k failure. not supported.
21S01 -1 Insert value list does not match column list. rived table does not match 2. A000 -1 Direct SQL syntax or access error. 2B000 -1 Dependent privileges exist.
e. n termination.
e. hema name.
Remote Database Access condition. IM001 -1 Driver does not support this function.
urce name not found and no defa IM003 -1 Driver specified by data source name co
ed. iver's SQLAllocConnect failed.
ver's SQLSetConnectOption failed. ata source specified; dialog prohibi IM009 -1 Unable to load translation DLL.
ng. 1. S03 1. No rows updated or de 1. S04 1. More than one row updated
02000 100 No da 07000 -1 Dynamic SQL err 07001 -1 Wrong number 07006 -1 Restricted data 08000 -1 Connection error. 08001 -1 Unable to conn 08002 -1 Connectio 08003 -1 Connection no 08004 -1 Data source reje 08007 -1 Connection failure 08S01 -1 Communication lin
0A000 -1 Feature
21S02 -1 Degree of de column list.
2C000 -1 Invalid character set nam 2D000 -1 Invalid transactio
3. C000 -1 Duplicate cursor name. 3D000 -1 Invalid catalog nam 3F000 -1 Invalid sc
HZ000 -1
IM002 -1 Data so ult driver specified.
uld not be loaded. IM004 -1 Driver's SQLAllocEnv fail
IM005 -1 Dr
IM006 -1 Dri
IM007 -1 No d
IM008 -1 Dialog failed.
ted.
IM010 -1 Data source name too lo ong. IM011 -1 Driver name too l
yword syntax error.
IM012 -1 DRIVER ke
IM013 -1 Trace file error.
S0001 -1 Base table or view already exists. le not found.
dex already exists. d.
ult for column.
failure. lid column number.
ram type out of range. ata type out of range. S1008 -1 Operation canceled.
S1009 -1 Invalid argument value. S1010 -1 Function sequence error.
id at this time.
S1012 -1 Invalid transaction operation code specified. sor name available.
ffer length. S1091 -1 Descriptor type out of range.
ion type out of range. S1093 -1 Invalid parameter number.
value.
e out of range. range. S1097 -1 Column type out of range.
queness option type out of range. S1101 -1 Accuracy option type out of range. S1102 -1 Table type out of range.
cision value.
S0002 -1 Base tab
S0011 -1 In
S0012 -1 Index not foun
S0021 -1 Column already exists. not found. S0022 -1 Column S0023 -1 No defa S1000 -1 General error. S1001 -1 Memory allocation S1002 -1 Inva S1003 -1 Prog S1004 -1 SQL d S1011 -1 Operation inval S1015 -1 No cur S1090 -1 Invalid string or bu S1092 -1 Opt S1094 -1 Invalid scale S1095 -1 Function typ
S1096 -1 Information type out of S1098 -1 Scope type out of range. S1099 -1 Nullable type out of range.
S1100 -1 Uni
S1103 -1 Direction option out of range. S1104 -1 Invalid pre
KBSODBCDriver
S1DE0 -1 No data at execution values pending.
SQLGetInfo
ion types used by ODBC along with the responses items marked with an asterisk
defined names or values that could be different in your installation reproduced using the GetInfoAll option of the ODBC Test utility Microsoft ODBC SDK. InfoType SQL_ACCESSIBLE_PROCEDURES=20 SQL_ACCESSIBLE_TABLES=19 IONS=0 SQL_ACTIVE_ENVIRONMENTS=116 SQL_ACTIVE_STATEMENTS=1 SQL_ALTER_TABLE=86 DD_COLUM S1T00 -1 Timeout expired
D - Info Types
SQLGetInfo Return Values
The following table lists the informat
from the KB_SQL Server. Note that (*) are based on site- . This list can be that comes with the
Value "Y" "Y" SQL_ACTIVE_CONNECT 254 254 65534 SQL_ALTER_DOMAIN=117 0x00000000 0x00000020 = SQL_AT_A N SQL_ASYNC_MODE=10021 _NONE = 0 SQL_BATCH_SUPPORT=121 SQL_BOOKMARK_PERSISTENCE=82 SQL_CATALOG_LOCATION=114 SQL_CATALOG_NAME_SEPARATOR=41 SQL_CATALOG_NAME=10003 SQL_CATALOG_USAGE=92 SQL_COLLATION_SEQ=10004 SQL_COLUMN_ALIAS=87 SQL_CONCAT_NULL_BEHAVIOR=22 SQL_CONVERT_BIGINT=53 54 SQL_AM SQL_BATCH_ROW_COUNT=120 0x00000002 = SQL_BRC_EXPLICIT