Many ODBC functions are processed by the Microsoft ODBC driver manager before being routed to the target ODBC driver. We do not describe the driver manager functionality here. The user is again referred to the ODBC documentation.
The ODBC interface is defined for Windows, and is implemented as a Microsoft Windows DLL. It is possible to use the CQL++ ODBC interface on client platforms other than Microsoft Windows (for example, UNIX or OS/2). Technically, this is not a complete implementation of ODBC, because the driver manager functions provided by the Microsoft ODBC driver manager are not available except on platforms supported by Microsoft (currently Windows and MacIntosh). The primary task of the driver manager is to manage client connections to multiple databases. Thus, unless the user provides an equivalent of the driver manager, only one database connection would be possible on clients not supported by Microsoft.
CQL++ implements the ODBC functionality in the SQL engine (the server for client/server versions). The Windows DLL does little more than pass requests to the SQL engine and pass responses to the client. Certain ODBC functions, such as those which return information about system capabilities, are implemented in the Windows DLL. All functions related to processing of SQL statements are implemented in the SQL engine.
Error processing in ODBC involves the function SQLError. For most functions, SQL_ERROR is returned when an error occurs. The application then calls SQLError to obtain the value of SQLSTATE, which describes the error. The mechanism is analagous to the errno variable in C. Unlike errno, the SQLSTATE is a string of five characters. Where appropriate, a string description of the error may be provided using the szErrorMsg argument of SQLError.
There is a special SQLSTATE value, S1000, which is used for errors where no specific ODBC SQLSTATE value is defined for the error that occurs. In this case, the error is described by szErrorMsg. In the function descriptions which follow, only those SQLSTATE codes which may be returned by CQL++ are described. The complete list of SQLSTATE values defined by ODBC can be found in the ODBC SDK documentation.
To access CQL++ through ODBC, the following functions are called:
If the SQL statement includes parameters, these parameters must be provided to the driver. There are two classes of ODBC parameters. For the first class, the application supplies values for the parameters. Pointers to these values must be provided to the driver using SQLSetParam before the call to SQLExecDirect or SQLExecute. The application needs to ensure that the data for these pointers is valid at execute time.
The second parameter class has the attribute SQL_DATA_AT_EXEC. In this case, instead of a pointer, the pointer parameter in SQLSetParam is treated as an identifier. This identifier may or may not be a pointer; ODBC does not care what it is. When SQLExecute or SQLExecDirect is called for a statement with SQL_DATA_AT_EXEC parameters, the return value is SQL_NEED_DATA. The application must then supply data for the SQL_DATA_AT_EXEC parameters. For each such parameter, the application makes a call to SQLParamData, which returns the identifier for the parameter that was specified on the SQLSetParam call. The application uses the identifier value to identify the parameter being requested, and provides a value for that parameter using SQLPutData. The return value of SQLParamData is either SQL_NEED_DATA or SQL_SUCCESS (assuming no error occured). When SQLParamData returns SQL_SUCCESS, all parameter data has been provided.
The SQLPrepare function is analogous to the ANSI DECLARE CURSOR statement. The SQLExecute function is analogous to the ANSI OPEN cursor statement. The SQLExecDirect function is essentially equivalent to SQLPrepare followed by SQLExecute. However, statements prepared using SQLPrepare can be executed multiple times, while SQLExecDirect can only execute a statement once.
Data is fetched using function SQLFetch or SQLGetData. To use SQLFetch, the application must first bind each result column to a C variable, using function SQLBindCol. The bind operation specifies a pointer, length, and type for result columns. When SQLFetch is called, data is written into these locations.
An alternate method for fetching data involves the SQLGetData function. This function is called after SQLFetch, and returns data for one column in the most recently fetched row. SQLGetData can return data for large columns in more than one operation, if a sufficiently large enough buffer is not available.
Generally, data is fetched until SQLFetch returns SQL_NO_DATA. The statement is then cleaned up by calling SQLFreeStmt. If any updates occured, the application needs to call SQLTransact to commit the transaction (or roll it back).
Whenever a string parameter (UCHAR FAR *) is provided to an ODBC function, it is followed by a length parameter (SWORD). For example, when a table name is specified, there are two parameters:
UCHAR FAR *szTableName, SWORD cbTableName, ...Here, cbTableName specifies the number of characters referenced by szTableName. The ODBC parameter names follow the Hungarian notation convention, where the first two characters of the parameter specify the type. A special value, SQL_NTS, is defined to specify that the sz parameter points to a null terminated string.
When an ODBC function returns data, it is either returned to a 32 bit integer, or to an area of storage referenced by a void pointer. In the first case, the output result size is fixed, and there is no length parameter. In the second case, there are three parameters: The user buffer pointer, the length of the user's buffer, and a pointer to an integer to hold the length of the data available for return by the function. If the user's buffer is too small for the data, the ODBC function returns SQL_SUCCESS_WITH_INFO, and the application can use the returned length value to allocate a bigger buffer. Here is an example of a set of three parameters describing a user buffer:
... PTR rgbDesc, SWORD cbDescMax, SWORD FAR *pcbDesc ...When specifying a table, ODBC function have three specifiers, a table qualifier, a table owner, and a table name. CQL++ does not currently use the table qualifier.
The following list describes parameter names and types used in may ODBC functions.
RETCODE SQLAllocConnect( HENV henv, HDBC FAR *phdbc )
RETCODE SQLAllocEnv( HENV FAR * phenv )
There are actually two SQLAllocEnv functions, one in the Microsoft driver manager and one in the CQL++ ODBC driver. Refer to the ODBC documentation for information about the behavior of the Microsoft driver manager.
RETCODE SQLAllocStmt( HDBC hdbc, HSTMT FAR *phstmt )
RETCODE SQLBindCol( HSTMT hstmt, // statement handle UWORD icol, // column number SWORD fCType, // C variable type PTR rgbValue, // pointer to address of C variable SDWORD cbValueMax, // length of area pointer to by rgbValue SDWORD FAR *pcbValue // Null indicator/length of result )
RETCODE SQLCancel( HSTMT hstmt )
RETCODE SQLColAttributes( HSTMT hstmt, // statement handle UWORD icol, // column number UWORD fDescType, // descriptor type PTR rgbDesc, // data area for descriptor information SWORD cbDescMax, // length of descriptor area SWORD FAR * pcbDesc, // return data length SDWORD FAR *pfDesc // Pointer to integer results )
RETCODE SQLColumns( HSTMT hstmt, UCHAR FAR *szTableQualifier, SWORD cbTableQualifier, UCHAR FAR *szTableOwner, SWORD cbTableOwner, UCHAR FAR *szTableName, SWORD cbTableName, UCHAR FAR *szColumnName, SWORD cbColumnName )
SQLColumns performs the equivalent of an SQLExecDirect for this imaginary table. The application uses one of the ODBC fetch functions to retrieve the data.
RETCODE SQLConnect( HDBC hdbc, // database handle UCHAR FAR *szDSN, // data source name SWORD cbDSN, UCHAR FAR *szUID, // user identifier SWORD cbUID, UCHAR FAR *szAuthStr, // password SWORD cbAuthStr )
RETCODE SQLDescribeCol( HSTMT hstmt, // statement handle UWORD icol, // column number UCHAR FAR *szColName, // column name (output) SWORD cbColNameMax, // space for column name SWORD FAR *pcbColName, // actual column name length SWORD FAR *pfSqlType, // ODBC SQL data type (output) UDWORD FAR *pcbColDef, // Column precision (output) SWORD FAR *pibScale, // Column scale (output) SWORD FAR *pfNullable )
RETCODE SQLDisconnect( HDBC hdbc )
RETCODE SQLDriverConnect( HDBC hdbc; // connection handle HWND hwnd; // window handle UCHAR FAR *szConnStrIn, // connection string SWORD cbConnStrIn, UCHAR FAR *szConnStrOut, // output connection string SWORD cbConnStrOutMax, SWORD FAR *pcbConnStrOut, UWORD fDriverCompletion // protocol control )
Parameter fDriverCompletion controls the behavior of the driver manager. Refer to the Microsoft ODBC SDK documentation for details.
The user can specify that the CQL++ driver use szConnStrIn for connection
information. The syntax of the string is:
"DSN=value;UID=value;PWD=value"
If a value is missing a dialog box is used to prompt the user for the
value. CQL++ does not currently use the password value.
RETCODE SQLError( HENV henv, // environment handle HDBC hdbc, // connection handle HSTMT hstmt, // statement handle UCHAR FAR *szSqlState, // value of SQLSTATE SDWORD FAR *pfNativeError, // CQL position code UCHAR FAR *szErrorMsg, // error message text SWORD cbErrorMsgMax, SWORD FAR *pcbErrorMessage )
Error information is associated with the handle used on a particular ODBC call that returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO. The application then calls SQLError with the appropriate handle arguments to retrieve the state. The CQL++ position code is returned in pNativeError. In some cases, a CQL++ error description is returned in szErrorMsg.
Some errors are processed entirely by the Microsoft driver manager. Refer to the Microsoft ODBC SDK documentation for more information.
RETCODE SQLExecDirect( HSTMT hstmt, // statement handle UCHAR FAR *szSqlStr, // SQL statement SDWORD cbSqlStr )
RETCODE SQLExecute( HSTMT hstmt )
RETCODE SQLFetch( HSTMT hstmt )
When a column value is NULL, SQLFetch stores SQL_NULL_DATA in the location pointed to by pcbValue.
RETCODE SQLForeignKeys( HSTMT hstmt, // statement handle UCHAR FAR *szPkTableQualifier, // always empty for CQL++ SWORD cbPkTableQualifier, UCHAR FAR *szPkTableOwner, // primary key table owner SWORD cbPkTableOwner, UCHAR FAR *szPkTableName, // primary key table name SWORD cbPkTableName, UCHAR FAR *szFkTableQualifier, // always empty for CQL++ SWORD cbFkTableQualifier, UCHAR FAR *szFkTableOwner, // foreign key table owner SWORD cbFkTableOwner, UCHAR FAR *szFkTableName, // foreign key table name SWORD cbFkTableName )
For case 1, where only the primary key table is identified, SQLForeignKeys returns a result set consisting of the table and column names for all tables defining a foreign key corresponding to the primary key of the identified table.
For case 2, where only the foreign key table is identified, SQLForeignKeys returns a result set consisting of the table and column names for which the identified table has created foreign keys.
For case 3, where both the foreign key and the primary key are identified, SQLForeignKeys returns a result set consisting of the description of the keys in the foreign key table that are related to the primary key of the primary key table. In this case, the result set has either 0 or 1 row.
The result set returned by SQLForeignKeys has the following structure:
RETCODE SQLFreeConnect( HDBC hdbc )
RETCODE SQLFreeEnv( HENV henv )
SQLFreeStmt( HSTMT hstmt, UWORD fOption )
RETCODE SQLGetConnectOption( HDBC hdbc, // connection handle UWORD fOption, // option selector PTR pvParam // result data )
RETCODE SQLGetData( HSTMT hstmt, UWORD icol, SWORD fCType, PTR rgbValue, SDWORD cbValueMax, SDWORD FAR *pcbValue )
SQLGetData can be used to retrieve SQL_LONGVARBINARY and SQL_LONGVARCHAR values which may exceed the size of the buffer which is used to retrieve SQL data. If the buffer is not large enough, the driver writes as many characters as possible into the buffer and returns SQL_SUCCESS_WITH_INFO. The application can then make additional calls to SQLGetData using the same icol value. All data has been fetched when SQLGetData returns SQL_SUCCESS.
If there are bound columns associated with hstmt, the ODBC specification requires that SQLGetData fail for columns whose icol value is less than the icol value for the highest bound column. Also, the ODBC specification requires that, once SQLGetData is called for a value of icol, no subsequent call to SQLGetData may occur with a lower value of icol. CQL++ does not enforce these restrictions. However, if you are concerned about keeping your application 100% ODBC compliant, you should follow these restrictions.
The allowable values for fCType are listed in ODBC Parameters(2-ODBC).
RETCODE SQLGetFunctions( HDBC hdbc, UWORD fFunction, UWORD FAR *pfExists )
RETCODE SQLGetInfo( HDBC hdbc, UWORD fInfoType, PTR rgbInfoValue, SWORD cbInfoValueMax, SWORD FAR *pcbInfoValue )
The information returned can be a 16 bit integer, a 32 bit integer, a bitmask (32 bit unsigned integer), or a character string. The data is written to rgbInfoValue, and the data length is written to pcbInfoValue.
For some values of fInfoType, parameter rgbInfoValue is used as an input parameter.
In the following list, each value of fInfoType is described. In cases where the information does not vary for CQL++, the fixed value is described. Complete information about all possible return values can be found in the Microsoft ODBC SDK documentation.
RETCODE SQLGetStmtOption( HSTMT hstmt, UWORD fOption, PTR pvParam )
RETCODE SQLGetTypeInfo( HSTMT hstmt, SWORD fSqlType )
RETCODE SQLNumResultCols( HSTMT hstmt, SWORD FAR *pccol )
RETCODE SQLParamData( HSTMT hstmt, PTR FAR * prgbValue )
RETCODE SQLPrepare( HSTMT hstmt, UCHAR FAR *szSqlStr, SDWORD cbSqlStr )
RETCODE SQLPutData( HSTMT hstmt, PTR rgbValue, SDWORD cbValue )
RETCODE SQLRowCount( HSTMT hstmt, SDWORD FAR *pcrow )
RETCODE SQLSetConnectOption( HDBC hdbc, UWORD fOption, UDWORD vParam )
The following list shows the allowable values for fOption:
RETCODE SQLSetCursorName( HSTMT hstmt, UCHAR FAR *szCursor, SWORD cbCursor )
RETCODE SQLSetParam( HSTMT hstmt, // statement handle UWORD ipar, // parameter number SWORD fCType, // parameter type SWORD fSqlType, // ODBC SQL type UDWORD cbColDef, // precision of parameter in SQL statement SWORD ibScale, // the scale of the parameter in SQL statement PTR rgbValue, // storage location (see description) SDWORD FAR *pcbValue // length of data pointed to by rgbValue )
If pcbValue points to a value greater than zero, then a value is being supplied in rgbValue. If pcbValue points to SQL_NULL_DATA, a null value is supplied for the parameter. If pcbValue points to SQL_DATA_AT_EXEC, the specification of the parameter value is being delayed until execution time. If pcbValue points to SQL_DATA_AT_EXEC, then rgbValue is defined by the ODBC specification as an identifier to be supplied to the application on a call to SQLParamData. Typically this is the address of a variable, but it can be any 32 bit value.
Allowable values for fCType and fSqlType are listed in the ODBC Parameters section.
RETCODE SQLSetStmtOption( HSTMT hstmt, UWORD fOption, UDWORD vParam )
RETCODE SQLSpecialColumns( HSTMT hstmt, // statement handle UWORD fColType, // specifies type of information desired UCHAR FAR *szTableQualifier, // qualifier not used by CQL++ SWORD cbTableQualifier, UCHAR FAR *szTableOwner, // table owner SWORD cbTableOwner, UCHAR FAR *szTableName, // table name SWORD cbTableName, UWORD fScope, // required scope (see description) UWORD fNullable // controls selection of nullable columns )
Parameter fScope specifies the period for which the information is being requested. That is, for how long can the application assume that the data returned by SQLSpecialColumns is valid? There are three allowable values for fScope: SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, and SQL_SCOPE_SESSION. The information returned by the CQL++ driver is always valid for the transaction; the same information is returned for fScope values SQL_SCOPE_CURROW and SQL_SCOPE_TRANSACTION. If the application specifies SQL_SCOPE_SESSION, an error is returned.
Parameter fNullable is used to specify whether or not the columns returned can allow null values. If columns which allow null values are to be rejected, fNullable is set to SQL_NO_NULLS. If columns which allow null values are to be accepted, fNullable is set to SQL_NULLABLE.
SQLSpecialColumns returns a result set with the following structure:
RETCODE SQLStatistics( HSTMT hstmt, UCHAR FAR *szTableQualifier, // qualifier not used by CQL++ SWORD cbTableQualifier, UCHAR FAR *szTableOwner, // table owner SWORD cbTableOwner, UCHAR FAR *szTableName, // table name SWORD cbTableName, UWORD fUnique, UWORD fAccuracy )
The result set also includes a row which describes the table itself rather than one of its indices. The result set column TYPE specifies the type of row being returned.
The result set has the following structure:
RETCODE SQLTables( HSTMT hstmt, UCHAR FAR *szTableQualifier, // qualifier not used by CQL++ SWORD cbTableQualifier, UCHAR FAR *szTableOwner, // table owner SWORD cbTableOwner, UCHAR FAR *szTableName, // table name SWORD cbTableName, UCHAR FAR *szTableType, // table type SWORD cbTableType )
For CQL++ only TABLE, VIEW, and SYSTEM TABLE types exist in the SQL engine. The szTableType string requires single quotes around the elements of the list. For example: " 'TABLE', 'VIEW' "
The result set has the following structure:
RETCODE SQLTransact( HENV henv, // environment handle HDBC hdbc, // connection handle UWORD fType // operation code )