|
   
6.
Error Handling and Catalog Functions
6.1
Retrieving Error Information
6.2
Catalog Functions
6.3
Getting System Information
6. Error Handling and Catalog Functions
After reading
the previous chapters, you can construct an ODBC program. But what do
you do when problems occur while calling ODBC functions? In this chapter
you will learn how to get error information when an error occurs. This
chapter also introduces some ODBC catalog functions which allow you
to get information from the system catalog (system tables). Some other
ODBC functions are also covered here, including functions which are
used to get system information about the data source, such as supported
data types, supported built-in functions, and supported ODBC functions.
In this chapter you will learn how to:
get detailed error information when a call to an ODBC function fails
by using the SQLError function.
retrieve catalog information such as table schemas and statistics information
by using catalog functions such as SQLTables, SQLColumns,
SQLStatistics and SQLSpecialColumns.
obtain system information about the data source by using the SQLGetTypeInfo,
SQLGetInfo and SQLGetFunctions functions.
Note:
You retrieve error information differently using DBMaker 3.5 (ODBC 3.0)
from what is described in this chapter. Refer to Appendix E, What's
New in DBMaker 3.5 for more information.
6.1 Retrieving Error Information
When an
application executes an ODBC function and an error code is returned,
it needs detailed error information to determine what caused the error.
This section explains how to use the SQLError function to retrieve
error information.
Common Error Codes Defined in ODBC
After calling
an ODBC function, you may get one of the following return codes:
SQL_SUCCESS - the ODBC function executed successfully.
SQL_SUCCESS_WITH_INFO - the ODBC function executed successfully,
but some warning information is being returned.
SQL_NO_DATA_FOUND - no more data can be fetched.
SQL_ERROR - some error has occurred and the function failed.
SQL_INVALID_HANDLE - an invalid handle was detected and the function
failed.
SQL_NEED_DATA - the driver found the application must send parameter
data values.
If an application
calls any ODBC function (except SQLError itself) and the return
code is SQL_ERROR or SQL_SUCCESS_WITH_INFO, it can call SQLError
to get additional error information.
How to Use SQLError
SQLError
is used to get the error information in the input handle, including
the error message, error state and the driver's native error code. The
driver's native error code is the error code is defined by each driver
itself. This may be different for different drivers. (For the native
DBMaker error codes, see Appendix C.)
Generally
speaking, an application calls SQLError when the error code returned
by the previous ODBC function is SQL_ERROR or SQL_SUCCESS_WITH_INFO.
The prototype
for SQLError is:
RETCODE SQLError(
HENV henv,
HDBC hdbc,
HSTMT hstmt,
UCHAR FAR *szSqlState,
SDWORD FAR *pfNativeError,
UCHAR FAR *szErrorMsg,
SWORD cbErrorMsgMax,
SWORD FAR *pcbErrorMsg
);
|
Note that
the three handles in the argument list of SQLError are not all
necessarily passed to SQLError. The ODBC driver will find the
associated return code from the rightmost non-null handle, for example,
if you pass handles like this:
SQLError(henv, hdbc, hstmt, ....)
|
then the
returned error information is in hstmt. In the following case,
SQLError(SQL_NULL_ENV, hdbc, SQL_NULL_STMT, ...)
|
the driver
will return the error information associated with hdbc. You should ensure
the applications pass the proper handles to SQLError so that
the error information they need can be retrieved successfully.
If there
is no error information to be retrieved, SQLError will return
SQL_NO_DATA_FOUND. Each time after SQLError is called and error
information is returned, the error information in that handle will be
cleared. This means the error information for one ODBC function call
can be retrieved only once.
The SQLSTATE
values returned from SQLError are defined by the SQL Access Group
SQL CAE specification (1992) and X/Open. The values are 5-character
strings with a two character class value followed by a three character
subclass value. For example, the class value 01 is a warning and the
corresponding return code is SQL_SUCCESS_WITH_INFO. To get more detailed
information about SQLSTATE values defined in ODBC, refer to the Microsoft
ODBC Programmer's Reference .
Here is
an example to illustrate the usage of SQLError:
#define MSG_LEN 256 /* error message buffer length */
HENV henv; /* environment handle */
HDBC hdbc; /* connection handle */
HSTMT hstmt; /* statement handle */
SDWORD retcode, retcode1; /* return code */
UCHAR sqlState[6]; /* buffer to store SQLSTATE */
SDWORD nativeErr; /* native error code */
UCHAR errMsg[MSG_LEN]; /* buffer to store error message */
SWORD realMsgLen; /* real length of error message */
retcode = SQLAllocEnv(&henv);
retcode = SQLAllocConnect(&hdbc);
/* Use specified DB_NAME(data source name), uid (user id), */
/* pwd (password) to connect to a data source. If any warnings or */
/* errors are detected, call SQLError and pass hdbc to retrieve */
/* error information from the connection handle with other handles */
/* set to NULL. Then print the error information and return. */
retcode = SQLConnect(hdbc, DB_NAME, SQL_NTS, uid, SQL_NTS, pwd,
SQL_NTS);
if (retcode != SQL_SUCCESS) /* warning or error returned */
{
retcode1 = SQLError(SQL_NULL_HDBC, hdbc, SQL_NULL_HSTMT, sqlState,
&nativeErr, errMsg, MSG_LEN, &realMsgLen);
print_err(sqlState, nativeErr, errMsg, realMsgLen);
return;
}
/* Get SQL command string and execute it. If any warnings or errors */
/* are detected, call SQLError and pass hstmt to retrieve error */
/* information from the statement handle, then print the error */
/* information and return. */
retcode = execute_cmd(hstmt); /* execute a SQL command */
if (retcode != SQL_SUCCESS) /* warning or error returned */
{
retcode1 = SQLError(SQL_NULL_HDBC, SQL_NULL_HDBC, hstmt, sqlState,
&nativeErr, errMsg, MSG_LEN, &realMsgLen);
print_err(sqlState, nativeErr, errMsg, realMsgLen);
return;
}
|
Example
6-1: Getting error information with SQLError
Error Queues
ODBC allows
multiple error codes to be stored in an error queue and associated with
one handle. SQLError can be called multiple times to retrieve
the error codes one by one. Currently DBMaker will only return multiple
errors for DataBase Consistency Checking (DBCC) operations. These
errors are stored in the error queue.
An application
can call SQLError many times until all the errors in error queue
are fetched. Once all errors have been fetched SQLError will
return SQL_NO_DATA_FOUND. For example, if an application wants to check
the consistency of the account table, the code may look like this:
#define MSG_LEN 256 /* error message buffer length */
UCHAR sqlState[6]; /* buffer to store SQLSTATE */
SDWORD nativeErr; /* native error code */
UCHAR errMsg[MSG_LEN]; /* buffer to store error message */
SWORD realMsgLen; /* real length of error message */
SWORD count;
SWORD retcode;
retcode = SQLExecute(hstmt, "check table account", SQL_NTS);
do
{
retcode = SQLError(SQL_NULL_HDBC, hdbc, SQL_NULL_HSTMT, sqlState,
&nativeErr, errMsg, MSG_LEN, &realMsgLen);
if (retcode == SQL_NO_DATA_FOUND)
{
printf("check error queue finish \n\n");
break;
}
count++;
printf("-->Error %d :\n", count);
printf(" SQLSTATE = %s \n", sqlState);
printf(" native error = %ld \n", nativeErr);
printf(" error message = %s \n", errMsg);
printf(" error message length = %d \n", realMsgLen);
}
while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO));
|
Example
6-2: Retrieving error codes from an error queue
6.2 Catalog Functions
There are
several system tables in a relational database which record information
about tables, columns, privileges on tables, etc. We call these system
tables catalogs. When you want to know the schema of tables and
indexes in a database, you can look at the catalogs to get the information.
ODBC provides
many functions to retrieve catalog information. These functions are
called catalog functions. In this section, four commonly used catalog
functions: SQLTables, SQLColumns, SQLStatistics,
and SQLSpecialColumns will be introduced.
SQLTables - gets a list of table or view names in a database.
SQLColumns - gets column information about specified tables.
SQLStatistics - gets statistics information about tables and the
indexes associated with those tables.
SQLSpecialColumns - gets the optimal set of columns that uniquely
identifies a row in a table.
All of
the catalog functions work in the same manner. You specify what information
you want in parameters when calling the catalog functions, and a result
set will be returned. Then you can fetch data from the result set.
Search Patterns
Some arguments
of the catalog functions accepts search patterns to select the desired
object. The simplest search pattern is a character string which is used
to match exactly the item you are looking for. In addition you can use
wild card metacharacters in a search pattern to do more powerful searches.
DBMaker supports the following metacharacters: underscore(_), percent
(%) and the escape character (\).
The underscore character (_) is used to match any one character.
The percent character (%) is used to match zero or more characters.
The escape character (\) permits the metacharacters % or _ to be used
as literal characters in search patterns. To use the escape character
\ as a literal character in search pattern, just include it twice (\\).
For example,
if the search pattern for a table name is %A%, the function will return
all tables with names that contain the character A. If the search pattern
for a table name is _A_, the function will return all tables with names
that are three character long with A as the middle character. If the
search pattern for a table name is %, the function will return all tables.
If you
want to retrieve the information for a table named TAB_TEST,
and you use TAB_TEST as the search pattern, then you will also
get the information for the tables TAB1TEST, TAB2TEST,
etc. in the result set. This is not what you want. To solve this problem
put an escape character in front of the metacharacter: TAB\_TEST.
Note:
When passing this string through a C compiler, you must specify TAB\\_TEST
instead of TAB\_TEST. This is because the C compiler also treats "\"
as an escape character. See the following example for SQLTables.
SQLTables: Retrieving Information About Tables or Views
When you
connect to a database and want to determine information about all or
a particular set of tables, calling SQLTables with the specified
criteria will get the answer.
The prototype
for SQLTables is:
RETCODE SQLTables (
HSTMT hstmt,
UCHAR *szTableQualifier,
SWORD cbTableQualifier,
UCHAR *szTableOwner,
SWORD cbTableOwner,
UCHAR *szTableName,
SWORD cbTableName,
UCHAR *szTableType,
SWORD cbTableType
);
|
The arguments
for SQLTables are:
hstmt - a valid statement handle for retrieved results.
szTableQualifier - not supported by DBMaker, and should be NULL
or an empty string.
cbTableQualifier - the length of szTableQualifier, and should
be zero.
szTableOwner - points to the owner name. The owner is the person
who created the table or view. It can be a string to be used as a search
pattern or a NULL value. Use a NULL value to indicate all owners.
cbTableOwner - the length of szTableOwner or SQL_NTS.
szTableName - points to the name of tables or views. It can be a
string to be used as a search pattern or a NULL value. Use a NULL value
to indicate all names.
cbTableName - the length of szTableName or SQL_NTS.
szTableType - the list of table types (TABLE or VIEW or both)
cbTableType - the length of szTableType.
Note:
A string to be used as a search pattern can exist in szTableQualifier,
szTableOwner and szTableName. These three arguments and their corresponding
string length arguments, cbTableQualifier, cbTableOwner and cbTableName
also appear in the other three catalog functions: SQLColumns, SQLStatistics
and SQLSpecialColumns. (These three functions will be introduced later).
SQLTables
returns a result set consisting of the following columns:
|
Column
No.
|
Column
Name
|
Data
Type
|
|
1
|
TABLE_QUALIFIER
|
VARCHAR(128)
|
|
2
|
TABLE_OWNER
|
VARCHAR(128)
|
|
3
|
TABLE_NAME
|
VARCHAR(128)
|
|
4
|
TABLE_TYPE
|
VARCHAR(128)
|
|
5
|
REMARKS
|
VARCHAR(254)
|
Table
6-1: Columns returned in SQLTables result set
SQLTables
returns a result set according to the user's criteria. For example,
when szTableName is %A% and szTableOwner is _A_, the result
set will contain all tables whose names contain the character A and
that have owners whose names are three characters long with A as the
middle character. If you want to find the names of all tables in the
database, you only need to set szTableQualifier, szTableOwner
and szTableName to NULL.
In fact,
you can regard SQLTables as a form of executing a query by SQLExecDirect.
This means you need to use SQLFetch to get the result set. Of
course before using SQLFetch, you should use SQLBindCol
to bind the columns in the result set.
The following
code gives an example for SQLTables. Suppose there are two tables
named TAB_TEST1 and TAB_TEST2. After calling SQLTables,
you will get information for TAB_TEST1 and TAB_TEST2,
ordered by TABLE_TYPE, TABLE_QUALIFIER, TABLE_OWNER
and TABLE_NAME:
HDBC hdbc;
HSTMT hstmt;
UCHAR tabQualifier[255], tabOwner[255], tabName[255],
UCHAR tabType[255], remarks[255];
SDWORD lenTabQualifier, lenTabOwner, lenTableName;
SDWORD lenTableType, lenRemarks;
SDWORD retcode;
...
retcode = SQLAllocStmt(hdbc,&hstmt);
retcode = SQLTables(hstmt,
(UCHAR FAR *)NULL, 0, /* tabQualifier */
(UCHAR FAR *)NULL, 0, /* tabOwners */
(UCHAR FAR *)"DB\\_%", SQL_NTS, /* table name */
(UCHAR FAR *)"TABLE", SQL_NTS); /* table type */
/* Bind columns in result set to storage locations */
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, tabQualifier, 255,
&lenTabQualifier);
retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, tabOwner, 255, &lenTabOwner);
retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, tabName, 255, &lenTableName);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, tabType, 255, &lenTableType);
retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, remarks, 255, &lenremarks);
/* print out the records in the result set */
while ((retcode = SQLFetch(hstmt)) == SQL_SUCCESS)
{
printf("column 1 : table qualifier = %s\n", tabQualifier);
printf("column 2 : table owner = %s\n", tabOwner);
printf("column 3 : table name = %s\n", tabName);
printf("column 4 : table type = %s\n", tabType);
printf("column 5 : remarks = %s\n", remarks);
}
...
|
Example
6-3: Retrieving table/view information using SQLTables
Note:
When a function returns a result set, the user should use SQLBindCol
and SQLFetch to get the rows in the result set. SQLTables, SQLColumns,
SQLStatistics and SQLSpecialColumns are all such functions.
SQLColumns: Retrieving Information About Columns in Specified Tables
You can
use SQLTables to get the names of tables that are in a database.
Similarly, you can use the SQLColumns function to get information
on the columns found in a particular table.
The prototype
for SQLColumns is:
RETCODE SQLColumns (
HSTMT hstmt,
UCHAR *szTableQualifier,
SWORD cbTableQualifier,
UCHAR *szTableOwner,
SWORD cbTableOwner,
UCHAR *szTableName,
SWORD cbTableName,
UCHAR *szColumnName,
SWORD cbColumnName
);
|
where szTableQualifier,
cbTableQualifier, szTableOwner, cbTableOwner, szTableName
and cbTableName are defined the same as for SQLTables.
szColumnName points to the search pattern string of the column
name. The cbColumnName is the length of szColumnName.
Like the
SQLTables function, a result set that matches the criteria in
the arguments given above is returned containing the column information.
The following table lists the columns of the result set:
|
Column
No.
|
Column
Name
|
Data
Type
|
Comments
|
|
1
|
TABLE_QUALIFIER
|
VARCHAR(128)
|
|
|
2
|
TABLE_OWNER
|
VARCHAR(128)
|
|
|
3
|
TABLE_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
4
|
COLUMN_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
5
|
DATA_TYPE
|
SMALLINT
|
NOT
NULL
|
|
6
|
TYPE_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
7
|
PRECISION
|
INTEGER
|
|
|
8
|
LENGTH
|
INTEGER
|
|
|
9
|
SCALE
|
SMALLINT
|
|
|
10
|
RADIX
|
SMALLINT
|
|
|
11
|
NULLABLE
|
SMALLINT
|
NOT
NULL
|
|
12
|
REMARKS
|
VARCHAR(254)
|
|
Table
6-2: Columns returned in SQLColumns result set
The result
set is ordered by TABLE_QUALIFIER, TABLE_OWNER and TABLE_NAME.
You should use SQLBindCol to bind the columns in the result set
and then use SQLFetch to fetch the results.
SQLStatistics
SQLStatistics
retrieves a list of statistics about specified table(s) and the indexes
associated with those table(s).
The prototype
for SQLStatistics is:
RETCODE SQLStatistics (
HSTMT hstmt,
UCHAR *szTableQualifier,
SWORD cbTableQualifier,
UCHAR *szTableOwner,
SWORD cbTableOwner,
UCHAR *szTableName,
SWORD cbTableName,
UWORD fUnique,
UWORD fAccuracy
);
|
where szTableQualifier,
cbTableQualifier, szTableOwner, cbTableOwner, szTableName
and cbTableName are defined the same as for SQLTables
and SQLColumns. fUnique is used to specify the type of
index to be returned and fAccuracy is used to specify the importance
of the CARDINALITY and PAGES columns in the result set.
Note:
fUnique has two options: SQL_INDEX_UNIQUE or SQL_INDEX_ALL. fAccuracy
also has two options: SQL_ENSURE or SQL_QUICK.
The following
table lists the columns in the result set:
|
Column
No.
|
Column
Name
|
Data
Type
|
Comments
|
|
1
|
TABLE_QUALIFIER
|
VARCHAR(128)
|
|
|
2
|
TABLE_OWNER
|
VARCHAR(128)
|
|
|
3
|
TABLE_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
4
|
NON_UNIQUE
|
SMALLINT
|
|
|
5
|
INDEX_QUALIFIER
|
VARCHAR(128)
|
|
|
6
|
INDEX_NAME
|
VARCHAR(128)
|
|
|
7
|
TYPE
|
SMALLINT
|
NOT
NULL
|
|
8
|
SEQ_IN_INDEX
|
SMALLINT
|
|
|
9
|
COLUMN_NAME
|
VARCHAR(128)
|
|
|
10
|
COLLATION
|
CHAR(1)
|
|
|
11
|
CARDINALITY
|
INTEGER
|
|
|
12
|
PAGES
|
INTEGER
|
|
|
13
|
FILTER_CONDITION
|
VARCHAR(128)
|
|
Table
6-3: Columns returned in SQLStatistics result set
The TYPE
column either has the value SQL_TABLE_STAT or SQL_INDEX_OTHER. SQL_TABLE_STAT
indicates the row contains statistics for a table and the NON_UNIQUE,
INDEX_QUALIFIER, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME,
COLLATION and FILTER_CONDITION columns (used for indexes)
will be NULL. On the other hand, SQL_INDEX_OTHER indicates the row contains
statistics for an index.
Similar
to SQLTables and SQLColumns, you need SQLBindCol
and SQLFetch to retrieve the data in the result set. The order
of the columns in the result set is NON_UNIQUE, TYPE,
INDEX_QUALIFIER, INDEX_NAME and SEQ_IN_INDEX. For
a code example of a similar function, please refer to the SQLTables
code example.
SQLSpecialColumns
As the
function name implies, SQLSpecialColumns returns the special
columns which could uniquely specify rows in a table.
The prototype
for SQLSpecialColumns is:
RETCODE SQLSpecialColumns (
HSTMT hstmt,
UWORD fColType,
UCHAR *szTableQualifier,
SWORD cbTableQualifier,
UCHAR *szTableOwner,
SWORD cbTableOwner,
UCHAR *szTableName,
SWORD cbTableName,
UWORD fScope,
UWORD fNullable
);
|
hstmt
is a valid statement handle, szTableQualifier, cbTableQualifier,
szTableOwner, cbTableOwner, szTableName, cbTableName
are all defined the same as for SQLTables. fColType specifies
the type of column to return. fScope is the minimum required
scope of the special column. fNullable determines whether to
return special columns that can have a NULL value.
Note:
fColType has two options: SQL_BEST_ROWID and SQL_ROWVER. fScope has
three options: SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION and SQL_SCOPE_SESSION.
fNullable has two options: SQL_NO_NULLS and SQL_NULLABLE.
The following
table lists the columns in the result set:
|
Column
No.
|
Column
Name
|
Data
Type
|
Comments
|
|
1
|
SCOPE
|
SMALLINT
|
|
|
2
|
COLLUMN_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
3
|
DATA_TYPE
|
SMALLINT
|
NOT
NULL
|
|
4
|
TYPE_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
5
|
PRECISION
|
INTEGER
|
|
|
6
|
LENGTH
|
INTEGER
|
|
|
7
|
SCALE
|
SMALLINT
|
|
|
8
|
PSEUDO_COLUMN
|
SMALLINT
|
|
Table
6-4: Columns returned SQLSpecialColumns result set
DBMaker
provides a specific row identifier, OID, which is similar
to ROWID in Oracle or TID in Ingres. OID is treated
as a pseudo-column in a table because a query like SELECT * FROM ACCOUNT
will not return such a column name, but you can still use OID
in a select list or WHERE clause to fetch the records you want by explicitly
specifying it.
Once you
specify SQL_BEST_ROWID in fColType, the result set returned
by SQLSpecialColumns simply contains a row whose column name
is OID. You can use this special column to re-select that row
within the defined scope in fScope. And the result of the SELECT
statement is guaranteed to have either no rows or one row. For a code
example of a similar function, please reference SQLTables.
Note:
If the fColType, fScope, or fNullable arguments specify characteristics
that are not supported by DBMaker, SQLSpecialColumns returns a rowset
with no rows. A subsequent call to SQLFetch or SQLExtendedFetch on the
hstmt will return SQL_NO_DATA_FOUND.
6.3 Getting System Information
You can
use SQLGetTypeInfo, SQLGetInfo, SQLGetFunctions
to get system information about the data source. These ODBC functions
are illustrated by examples in the following sections.
SQLGetTypeInfo
You can
use SQLGetTypeInfo to get information about data types supported
by the data source. The prototype for SQLGetTypeInfo is:
RETCODE SQLGetTypeInfo (
HSTMT hstmt,
SWORD fSqlType
);
|
When given
a value for fSqlType, SQLGetTypeInfo returns the related
type information in the result set. You can use SQLBindCol to
bind output storage for the result set and use SQLFetch to fetch
the results into the output storage. fSqlType can be any SQL
data type - SQL_CHAR, SQL_DECIMAL, SQL_INTEGER, etc.
The result
set is shown below:
|
Column
No.
|
Column
Name
|
Data
Type
|
Comments
|
|
1
|
TYPE_NAME
|
VARCHAR(128)
|
NOT
NULL
|
|
2
|
DATA_TYPE
|
SMALLINT
|
NOT
NULL
|
|
3
|
PRECISION
|
INTEGER
|
|
|
4
|
LITERAL_PREFIX
|
VARCHAR(128)
|
|
|
5
|
LITERAL_SUFFIX
|
VARCHAR(128)
|
|
|
6
|
CREATE_PARAMS
|
VARCHAR(128)
|
|
|
7
|
NULLABLE
|
SMALLINT
|
NOT
NULL
|
|
8
|
CASE_SENSITIVE
|
SMALLINT
|
NOT
NULL
|
|
9
|
SEARCHABLE
|
SMALLINT
|
NOT
NULL
|
|
10
|
UNSIGNED_ATTRIBUTE
|
SMALLINT
|
|
|
11
|
MONEY
|
SMALLINT
|
NOT
NULL
|
|
12
|
AUTO_INCREMENT
|
SMALLINT
|
|
|
13
|
LOCAL_TYPE_NAME
|
VARCHAR(128)
|
|
|
14
|
MINIMUM_SCALE
|
SMALLINT
|
|
|
15
|
MAXIMUM_SCALE
|
SMALLINT
|
|
Table
6-5: Columns returned in SQLGetTypeInfo result set
This example
uses SQLGetTypeInfo with SQL_ALL_TYPES as the value of fSqlType
to fetch all data types supported by the data source.
UCHAR name[30, prefix[30], suffix[30], params[30], local_name[30];
SWORD type, nullable, case_sen, searchable, unsign, money, auto_inc;
SWORD min_scale, max_scale;
UDWORD prec;
SDWORD len[15], retcode;
/* bind all columns */
retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, name, 30, &len[1]);
retcode = SQLBindCol(hstmt, 2, SQL_C_SHORT, &type, 0, &len[2]);
retcode = SQLBindCol(hstmt, 3, SQL_C_LONG, &prec, 0, &len[3]);
retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, prefix, 30, &len[4]);
retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, suffix, 30, &len[5]);
retcode = SQLBindCol(hstmt, 6, SQL_C_CHAR, params, 30, &len[6]);
retcode = SQLBindCol(hstmt, 7, SQL_C_SHORT, &nullable, 0, &len[7]);
retcode = SQLBindCol(hstmt, 8, SQL_C_SHORT, &case_sen, 0, &len[8]);
retcode = SQLBindCol(hstmt, 9, SQL_C_SHORT, &searchable, 0, &len[9]);
retcode = SQLBindCol(hstmt, 10, SQL_C_SHORT, &unsign, 0, &len[10]);
retcode = SQLBindCol(hstmt, 11, SQL_C_SHORT, &money, 0, &len[11]);
retcode = SQLBindCol(hstmt, 12, SQL_C_SHORT, &auto_inc, 0, &len[12]);
retcode = SQLBindCol(hstmt, 13, SQL_C_CHAR, local_name, 30, &len[13]);
retcode = SQLBindCol(hstmt, 14, SQL_C_SHORT, &min_scale, 0, &len[14]);
retcode = SQLBindCol(hstmt, 15, SQL_C_SHORT, &max_scale, 0, &len[15]);
/* tell odbc driver to get all type information */
printf("tell odbc driver to get all SQL type information \n");
SQLGetTypeInfo(hstmt,SQL_ALL_TYPES);
/ fetch all type information */
do
{
retcode = SQLFetch(hstmt);
switch (retcode)
{
case SQL_SUCCESS_WITH_INFO:
case SQL_SUCCESS:
print_type_info(); /* print type info such as name,type, */
break; /* prec, prefix, ... */
case SQL_NO_DATA_FOUND:
break;
default:
print error
}
}
while (retcode != SQL_NO_DATA_FOUND);
|
Example
6-4: Determining supported data types using SQLGetTypeInfo
SQLGetInfo
You can
use SQLGetInfo to get general information about the data source.
The prototype
for SQLGetInfo is:
RETCODE SQLGetInfo (
HDBC hdbc,
UWORD fInfoType,
PTR rgbInfoValue,
SWORD cbInfoValueMax,
SWORD FAR *pcbInfoValue
);
|
Given a
value in fInfoType representing the type of information you want
to know, and given the output storage rgbInfoValue and its storage
size cbInfoValueMax, SQLGetInfo will return the fetched
information in rgbInfoValue and will return the size of the fetched
information in pcbInfoValue.
Example
You can
use the following codes to decide whether the data source supports the
string function CONCAT.
UDWORD bitmask;
SDWORD retcode;
retcode = SQLGetInfo(hdbc, SQL_STRING_FUNCTIONS, (PTR) &bitmask,
sizeof(bitmask), NULL);
if (bitmask & SQL_FN_STR_CONCAT)
printf ("the data source supports CONCAT\n");
else
printf ("the data source does not support CONCAT\n");
|
Example
If you
want to know the maximum number of columns allowed in a table, you can
try the following code:
UWORD maxNCol;
SDWORD retcode;
retcode = SQLGetInfo(hdbc, SQL_MAX_COLUMNS_IN_TABLE, (PTR) &maxNCol,
sizeof(maxNCol));
printf ("In this data source, a table can have %d columns at most\n",
(int) maxNCol );
|
SQLGetFunctions
You can
use SQLGetFunctions to check what ODBC functions the data source
supports. The prototype for SQLGetFunctions is:
RETCODE SQLGetFunctions (
HDBC hdbc,
UWORD fFunction,
UWORD FAR *pfExists
);
|
The input
argument fFunction specifies the kind of ODBC function. The value
of fFunction can be SQL_API_SQLCANCEL, SQL_API_SQLFETCH, SQL_PUTDATA,
etc. - SQLCancel, SQLFetch, SQLPutData are all
ODBC functions.
For example,
you can give SQL_API_SQLCANCEL in fFunction to check whether
the data source supports SQLCancel.
After submitting
SQLGetFunctions, in order to determine the existence of the ODBC
function you can check the Boolean value(s) in pfExists, which
is a pointer to a single Boolean value or a list of Boolean values.
Example
You can
use the following code to check whether the data source supports SQLExecDirect.
UWORD fExecDirect;
SDWORD retcode;
retcode = SQLGetFunctions (hdbc, SQL_API_SQLEXECDIRECT, &fExecDirect);
if (fExecDirect)
printf ("the data source supports SQLExecDirect\n");
else
printf ("the data source does not support SQLExecDirect\n");
|
Example
You can
use the following code to check whether the data source supports SQLTables.
UWORD fExecDirect;
SDWORD retcode;
retcode = SQLGetFunctions (hdbc, SQL_API_SQLTABLES, &fExecDirect);
if (fExecDirect)
printf ("the data source supports SQLExecDirect\n");
else
printf ("the data source does not support SQLExecDirect\n");
|
|
