Figure 2. Conceptual View of Initialization and Termination Tasks
|
Figure 2 shows the function call sequences for both the initialization and termination tasks. The transaction processing task in the middle of the diagram is shown in Figure 3.
The initialization task consists of the allocation and initialization of environment and connection handles (which are later freed in the termination task). An application then passes the appropriate handle when it calls other DB2 CLI functions. A handle is a variable that refers to a data object controlled by DB2 CLI. Using handles relieves the application from having to allocate and manage global variables or data structures, such as the SQLDA or SQLCA, used in IBM's embedded SQL interfaces.
The SQLAllocHandle() function is called with a handle type and parent handle arguments to create environment, connection, statement, or descriptor handles. The function SQLFreeHandle() is used to free the resources allocated to a handle.
There are four types of handles:
An application can be connected to several servers at the same time, and can establish several distinct connections to the same server. An application requires a connection handle for each concurrent connection to a database server. For information on multiple connections, refer to Connecting to One or More Data Sources.
Call SQLGetInfo() to determine if a user imposed limit on the number of connector handles has been set.
Descriptors and descriptor handles are discussed in the section Using Descriptors.
In order to connect concurrently to one or more data sources (or multiple concurrent connections to the same data source), an application calls SQLAllocHandle(), with a HandleType of SQL_HANDLE_DBC, once for each connection. The subsequent connection handle is used with SQLConnect() to request a database connection and with SQLAllocHandle(), with a HandleType of SQL_HANDLE_STMT, to allocate statement handles for use within that connection. There is also an extended connect function, SQLDriverConnect(), which allows for additional connect options, and the ability to directly open a connection dialog box in environments that support a Graphical User Interface. The function SQLBrowseConnect() can be used to discover all of the attributes and attribute values required to connect to a data source.
The use of connection handles ensures that multi-threaded applications that utilize one connection per thread are thread-safe since separate data structures are allocated and maintained by DB2 CLI for each connection.
Unlike the distributed unit of work connections described in Multisite Updates (Two Phase Commit), there is no coordination between the statements that are executed on different connections.
/* From CLI sample basiccon.c */
/* ... */
#include <stdio.h>
#include <stdlib.h>
#include <sqlcli1.h>
/* ... */
SQLRETURN
prompted_connect( SQLHANDLE henv,
SQLHANDLE * hdbc);
#define MAX_UID_LENGTH 18
#define MAX_PWD_LENGTH 30
#define MAX_CONNECTIONS 2
#define MAX_CONNECTIONS 2
/* extern SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
extern SQLCHAR uid[MAX_UID_LENGTH + 1] ;
extern SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
*/
int main( ) {
SQLHANDLE henv;
SQLHANDLE hdbc[MAX_CONNECTIONS] ;
/* ... */
/* allocate an environment handle */
SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
/* Connect to first data source */
prompted_connect( henv, &hdbc[0] ) ;
/* Connect to second data source */
prompted_connect( henv, &hdbc[1] ) ;
/********* Start Processing Step *************************/
/* allocate statement handle, execute statement, etc. */
/********* End Processing Step ***************************/
printf( "\nDisconnecting .....\n" ) ;
SQLDisconnect( hdbc[0] ) ; /* disconnect first connection */
SQLDisconnect( hdbc[1] ) ; /* disconnect second connection */
/* free first connection handle */
SQLFreeHandle( SQL_HANDLE_DBC, hdbc[0] ) ;
/* free second connection handle */
SQLFreeHandle( SQL_HANDLE_DBC, hdbc[1] ) ;
/* free environment handle */
SQLFreeHandle( SQL_HANDLE_ENV, henv ) ;
return ( SQL_SUCCESS ) ;
}
/* prompted_connect - prompt for connect options and connect */
SQLRETURN prompted_connect( SQLHANDLE henv,
SQLHANDLE * hdbc
) {
SQLCHAR server[SQL_MAX_DSN_LENGTH + 1] ;
SQLCHAR uid[MAX_UID_LENGTH + 1] ;
SQLCHAR pwd[MAX_PWD_LENGTH + 1] ;
/* allocate a connection handle */
if ( SQLAllocHandle( SQL_HANDLE_DBC,
henv,
hdbc
) != SQL_SUCCESS ) {
printf( ">---ERROR while allocating a connection handle-----\n" ) ;
return( SQL_ERROR ) ;
}
/* Set AUTOCOMMIT OFF */
if ( SQLSetConnectAttr( * hdbc,
SQL_ATTR_AUTOCOMMIT,
( void * ) SQL_AUTOCOMMIT_OFF, SQL_NTS
) != SQL_SUCCESS ) {
printf( ">---ERROR while setting AUTOCOMMIT OFF ------------\n" ) ;
return( SQL_ERROR ) ;
}
printf( ">Enter Server Name:\n" ) ;
gets( ( char * ) server ) ;
printf( ">Enter User Name:\n" ) ;
gets( ( char * ) uid ) ;
printf( ">Enter Password:\n" ) ;
gets( ( char * ) pwd ) ;
if ( SQLConnect( * hdbc,
server, SQL_NTS,
uid, SQL_NTS,
pwd, SQL_NTS
) != SQL_SUCCESS ) {
printf( ">--- ERROR while connecting to %s -------------\n",
server
) ;
SQLDisconnect( * hdbc ) ;
SQLFreeHandle( SQL_HANDLE_DBC, * hdbc ) ;
return( SQL_ERROR ) ;
}
else /* Print Connection Information */
printf( "Successful Connect to %s\n", server ) ;
return( SQL_SUCCESS ) ;
}
The following figure shows the typical order of function calls in a DB2 CLI application. Not all functions or possible paths are shown.
Figure 3. Transaction Processing
 |
Figure 3 shows the steps and the DB2 CLI functions in the transaction processing task. This task contains five steps:
SQLAllocHandle() is called with a HandleType of SQL_HANDLE_STMT to allocate a statement handle. A statement handle refers to the data object that is used to track the execution of a single SQL statement. This includes information such as statement attributes, SQL statement text, dynamic parameters, cursor information, bindings for dynamic arguments and columns, result values and status information (these are discussed later). Each statement handle is associated with a connection handle.
A statement handle must be allocated before a statement can be executed.
The maximum number of statement handles that may be allocated at any one time is limited by overall system resources (usually stack size). The maximum number of statement handles that may actually be used, however, is defined by DB2 CLI: 5500. An HY014 SQLSTATE will be returned on the call to SQLPrepare() or SQLExecDirect() if the application exceeds these limits.
Once a statement handle has been allocated, there are two methods of specifying and executing SQL statements:
The first method splits the preparation of the statement from the execution. This method is used when:
The second method combines the prepare step and the execute step into one. This method is used when:
| Note: | SQLGetTypeInfo() and the schema (catalog) functions discussed in Chapter 3, Using Advanced Features, execute their own query statements, and generate a result set. Calling a schema function is equivalent to executing a query statement, the result set is then processed as if a query statement had been executed. |
DB2 Universal Database version 5 or later has a global dynamic statement cache stored on the server. This cache is used to store the most popular access plans for prepared SQL statements. Before each statement is prepared, the server searches this cache to see if an access plan has already been created for this exact SQL statement (by this application or any other application or client). If so, the server does not need to generate a new access plan, but will use the one in the cache instead. There is now no need for the application to cache connections at the client unless connecting to a server that does not have a global dynamic statement cache (such as DB2 Common Server v2). For information on caching connections at the client see Caching Statement Handles on the Client in the Migration section.
Binding Parameters in SQL Statements:
Both of the execution methods described above allow the use of parameter markers in place of an expression (or host variable in embedded SQL) in an SQL statement.
Parameter markers are represented by the '?' character and indicate the position in the SQL statement where the contents of application variables are to be substituted when the statement is executed. The parameter markers are referenced sequentially, from left to right, starting at 1. SQLNumParams() can be used to determine the number of parameters in a statement.
When an application variable is associated with a parameter marker it is bound to the parameter marker. The application must bind an application variable to each parameter marker in the SQL statement before it executes that statement. Binding is carried out by calling the SQLBindParameter() function with a number of arguments to indicate, the numerical position of the parameter, the SQL type of the parameter, the data type of the variable, a pointer to the application variable, and length of the variable.
The bound application variable and its associated length are called deferred input arguments since only the pointers are passed when the parameter is bound; no data is read from the variable until the statement is executed. Deferred arguments allow the application to modify the contents of the bound parameter variables, and repeat the execution of the statement with the new values.
Information for each parameter remains in effect until overridden, or until the application unbinds the parameter or drops the statement handle. If the application executes the SQL statement repeatedly without changing the parameter binding, then DB2 CLI uses the same pointers to locate the data on each execution. The application can also change the parameter binding to a different set of deferred variables. The application must not de-allocate or discard variables used for deferred input fields between the time it binds the fields to parameter markers and the time DB2 CLI accesses them at execution time.
It is possible to bind the parameters to a variable of a different type from that required by the SQL statement. The application must indicate the C data type of the source, and the SQL type of the parameter marker, and DB2 CLI will convert the contents of the variable to match the SQL data type specified. For example, the SQL statement may require an integer value, but your application has a string representation of an integer. The string can be bound to the parameter, and DB2 CLI will convert the string to the corresponding integer value when you execute the statement.
By default, DB2 CLI does not verify the type of the parameter marker. If the application indicates an incorrect type for the parameter marker, it could cause either an extra conversion by the DBMS, or an error. Refer to Data Types and Data Conversion for more information about data conversion.
Information about the parameter markers can be accessed using descriptors. If you enable automatic population of the implementation parameter descriptor (IPD) then information about the parameter markers will be collected. The statement attribute SQL_ATTR_ENABLE_AUTO_IPD must be set to SQL_TRUE for this to work. See Using Descriptors for more information.
If the parameter marker is part of a predicate on a query and is associated with a User Defined Type, then the parameter marker must be cast to the built-in type in the predicate portion of the statement; otherwise, an error will occur. For an example, refer to User Defined Types in Predicates.
The global dynamic statement cache was introduced in an earlier section. The access plan will only be shared between statements if they are exactly the same. For SQL statements with parameter markers, the specific values that are bound to the parameters do not have to be the same, only the SQL statement itself.
For information on more advanced methods for binding application storage to parameter markers, refer to:
The next step after the statement has been executed depends on the type of SQL statement.
Processing Query (SELECT, VALUES) Statements:
If the statement is a query statement, the following steps are generally needed in order to retrieve each row of the result set:
If the application does not bind any columns, as in the case when it needs to retrieve columns of long data in pieces, it can use SQLGetData(). Both the SQLBindCol() and SQLGetData() techniques can be combined if some columns are bound and some are unbound. The application must not de-allocate or discard variables used for deferred output fields between the time it binds them to columns of the result set and the time DB2 CLI writes the data to these fields.
If data conversion was indicated by the data types specified on the call to SQLBindCol(), the conversion will occur when SQLFetch() is called. Refer to Data Types and Data Conversion for an explanation.
Data conversion can also be indicated here, as in SQLBindCol(), by specifying the desired target C data type of the application variable. Refer to Data Types and Data Conversion for more information.
To unbind a particular column of the result set, use SQLBindCol() with a null pointer for the application variable argument (TargetValuePtr). To unbind all of the columns with one function call, use SQLFreeStmt() with an Option of SQL_UNBIND.
Applications will perform better if columns are bound, rather than having them retrieved as unbound columns using SQLGetData(). However, an application may be constrained in the amount of long data that it can retrieve and handle at one time. If this is a concern, then SQLGetData() may be the better choice. See Using Large Objects for additional techniques to handle long data.
For information on more advanced methods for binding application storage to result set columns, refer to:
Processing UPDATE, DELETE and INSERT Statements: If the statement is modifying data (UPDATE, DELETE or INSERT), no action is required, other than the normal check for diagnostic messages. In this case, SQLRowCount() can be used to obtain the number of rows affected by the SQL statement.
If the SQL statement is a Positioned UPDATE or DELETE, it will be necessary to use a cursor. A cursor is a moveable pointer to a row in the result table of an active query statement. (This query statement must contain the FOR UPDATE OF clause to ensure that the query is not opened as readonly.) In embedded SQL, cursors names are used to retrieve, update or delete rows. In DB2 CLI, a cursor name is needed only for Positioned UPDATE or DELETE SQL statements as they reference the cursor by name. Furthermore, a cursor name is automatically generated when SQLAllocHandle() is called with a HandleType of SQL_HANDLE_STMT.
To update a row that has been fetched, the application uses two statement handles, one for the fetch and one for the update. The application calls SQLGetCursorName() to obtain the cursor name. The application generates the text of a Positioned UPDATE or DELETE, including this cursor name, and executes that SQL statement using a second statement handle. The application cannot reuse the fetch statement handle to execute a Positioned UPDATE or DELETE as it is still in use. You can also define your own cursor name using SQLSetCursorName(), but it is best to use the generated name, since all error messages will reference the generated name, and not the one defined by SQLSetCursorName().
Processing Other Statements: If the statement neither queries nor modifies the data, then there is no further action other than the normal check for diagnostic messages.
A transaction is a recoverable unit of work, or a group of SQL statements that can be treated as one atomic operation. This means that all the operations within the group are guaranteed to be completed (committed) or undone (rolled back), as if they were a single operation. A transaction can also be referred to as a Unit of Work or a Logical Unit of Work. When the transaction spans multiple connections, it is referred to as a Distributed Unit of Work.
DB2 CLI supports two commit modes:
The default commit mode is auto-commit (except when participating in a coordinated transaction, see Multisite Updates (Two Phase Commit)). An application can switch between manual-commit and auto-commit modes by calling SQLSetConnectAttr(). Typically, a query-only application may wish to stay in auto-commit mode. Applications that need to perform updates to the database should turn off auto-commit as soon as the database connection has been established.
When multiple connections exist to the same or different databases, each connection has its own transaction. Special care must be taken to call SQLEndTran() with the correct connection handle to ensure that only the intended connection and related transaction is affected. It is also possible to rollback or commit all the connections by specifying a valid environment handle, and a NULL connection handle on the SQLEndTran() call. Unlike distributed unit of work connections (described in Multisite Updates (Two Phase Commit)), there is no coordination between the transactions on each connection.
If the application is in auto-commit mode, it never needs to call SQLEndTran(). A commit is issued implicitly at the end of each statement execution.
In manual-commit mode, SQLEndTran() must be called before calling SQLDisconnect(). If Distributed Unit of Work is involved, additional rules may apply, refer to Multisite Updates (Two Phase Commit) for details.
It is recommended that an application that performs updates should not wait until the disconnect before committing or rolling back the transaction. The other extreme is to operate in auto-commit mode, which is also not recommended as this adds extra processing. Refer to the Environment, Connection, and Statement Attributes and SQLSetConnectAttr - Set Connection Attributes for information about switching between auto-commit and manual-commit.
Consider the following when deciding where in the application to end a transaction:
Effects of calling SQLEndTran():
When a transaction ends:
For more information and an example refer to SQLEndTran - End Transactions of a Connection.
Call SQLFreeStmt() to end processing for a particular statement handle. This function can be used to do one or more of the following:
The SQL_DESC_COUNT field of the application row descriptor (ARD) will also be set to zero in this case. See Using Descriptors for more information on using descriptors.
The SQL_DESC_COUNT field of the application parameter descriptor (APD) will also be set to zero in this case. See Using Descriptors for more information on using descriptors.
Call SQLFreeHandle() with a HandleType of SQL_HANDLE_STMT to:
The columns and parameters should always be unbound before using the handle to process a statement with a different number or type of parameters or a different result set; otherwise application programming errors may occur.
Diagnostics refers to dealing with warning or error conditions generated within an application. There are two levels of diagnostics returned when calling DB2 CLI functions :
Each CLI function returns the function return code as a basic diagnostic. Both SQLGetDiagRec() and SQLGetDiagField() functions provide more detailed diagnostic information. The SQLGetSQLCA() function provides access to the SQLCA, if the diagnostic is reported by the data source. This arrangement lets applications handle the basic flow control based on Return Codes, and the SQLSTATES allow determination of the specific causes of failure and specific error handling.
Both SQLGetDiagRec() and SQLGetDiagField() return three pieces of information:
For the detailed function information and example usage, refer to SQLGetDiagRec - Get Multiple Fields Settings of Diagnostic Record and SQLGetDiagField - Get a Field of Diagnostic Data.
SQLGetSQLCA() returns the SQLCA for access to specific fields, but should never be used as a substitute for SQLGetDiagRec() or SQLGetDiagField().
The following table lists all possible return codes for DB2 CLI
functions. Each function description in Chapter 5, DB2 CLI Functions lists the possible codes returned for each
function.
Table 1. DB2 CLI Function Return Codes
| Return Code | Explanation |
|---|---|
| SQL_SUCCESS | The function completed successfully, no additional SQLSTATE information is available. |
| SQL_SUCCESS_WITH_INFO | The function completed successfully, with a warning or other information. Call SQLGetDiagRec() to receive the SQLSTATE and any other informational messages or warnings. The SQLSTATE will have a class of '01', see SQLState Cross Reference table. |
| SQL_STILL_EXECUTING | The function is running asynchronously and has not yet completed. The DB2 CLI driver has returned control to the application after calling the function, but the function has not yet finished executing. See Asynchronous Execution of CLI for complete details. |
| SQL_NO_DATA_FOUND | The function returned successfully, but no relevant data was found. When this is returned after the execution of an SQL statement, additional information may be available and can be obtained by calling SQLGetDiagRec(). |
| SQL_NEED_DATA | The application tried to execute an SQL statement but DB2 CLI lacks parameter data that the application had indicated would be passed at execute time. For more information, refer to Sending/Retrieving Long Data in Pieces. |
| SQL_ERROR | The function failed. Call SQLGetDiagRec() to receive the SQLSTATE and any other error information. |
| SQL_INVALID_HANDLE | The function failed due to an invalid input handle (environment, connection or statement handle). This is a programming error. No further information is available. |
SQLSTATEs are alphanumeric strings of 5 characters (bytes) with a format of ccsss, where cc indicates class and sss indicates subclass. Any SQLSTATE that has a class of:
| Note: | Previous versions of DB2 CLI returned SQLSTATEs with a class of 'S1' rather than 'HY'. To force the CLI driver to return 'S1' SQLSTATEs, the application should set the environment attribute SQL_ATTR_ODBC_VERSION to the value SQL_OV_ODBC2. See SQLSetEnvAttr - Set Environment Attribute and Appendix B, Migrating Applications for more information. |
DB2 CLI SQLSTATEs include both additional IBM defined SQLSTATEs that are returned by the database server, and DB2 CLI defined SQLSTATEs for conditions that are not defined in the ODBC v3 and ISO SQL/CLI specification. This allows for the maximum amount of diagnostic information to be returned. When running applications in a ODBC environment, it is also possible to receive ODBC defined SQLSTATEs.
Follow these guidelines for using SQLSTATEs within your application:
| Note: | It may be useful to build dependencies on the class (the first 2 characters) of the SQLSTATEs. |
Refer to SQLState Cross Reference table for a listing and description of the SQLSTATEs explicitly returned by DB2 CLI.
You can use the CLI/ODBC trace facility to gain a better understanding of how your application calls DB2, including any errors that may occur. Refer to Appendix K, Using the DB2 CLI/ODBC Trace Facility and the CLI/ODBC configuration keyword TRACE.
Embedded applications rely on the SQLCA for all diagnostic information. Although DB2 CLI applications can retrieve much of the same information by using SQLGetDiagRec(), there may still be a need for the application to access the SQLCA related to the processing of a statement. (For example, after preparing a statement, the SQLCA will contain the relative cost of executing the statement.) The SQLCA only contains meaningful information if there was an interaction with the data source on the previous request (for example: connect, prepare, execute, fetch, disconnect).
The SQLGetSQLCA() function is used to retrieve this structure. Refer to SQLGetSQLCA - Get SQLCA Data Structure for more information.
SQLGetSQLCA() should never be used as a substitute for SQLGetDiagRec() or SQLGetDiagField().
When writing a DB2 CLI application it is necessary to work with both SQL data types and C data types. This is unavoidable since the DBMS uses SQL data types, and the application must use C data types. This means the application must match C data types to SQL data types when transferring data between the DBMS and the application (when calling DB2 CLI functions).
To help address this, DB2 CLI provides symbolic names for the various data types, and manages the transfer of data between the DBMS and the application. It will also perform data conversion (from a C character string to an SQL INTEGER type, for example) if required. To accomplish this, DB2 CLI needs to know both the source and target data type. This requires the application to identify both data types using symbolic names.
Table 2 list each of the SQL data types, with its corresponding symbolic name, and the default C symbolic name.
Table 2. SQL Symbolic and Default Data Types
| SQL Data Type | Symbolic SQL Data Type | Default Symbolic C Data Type | ||
|---|---|---|---|---|
| BIGINT | SQL_BIGINT | SQL_C_BIGINT | ||
| BLOB | SQL_BLOB | SQL_C_BINARY | ||
| BLOB LOCATOR a | SQL_BLOB_LOCATOR | SQL_C_BLOB_LOCATOR | ||
| CHAR | SQL_CHAR | SQL_C_CHAR | ||
| CHAR FOR BIT DATA b | SQL_BINARY | SQL_C_BINARY | ||
| CLOB | SQL_CLOB | SQL_C_CHAR | ||
| CLOB LOCATOR a | SQL_CLOB_LOCATOR | SQL_C_CLOB_LOCATOR | ||
| DATE | SQL_TYPE_DATE d | SQL_C__TYPE_DATE d | ||
| DBCLOB | SQL_DBCLOB | SQL_C_DBCHAR | ||
| DBCLOB LOCATOR a | SQL_DBCLOB_LOCATOR | SQL_C_DBCLOB_LOCATOR | ||
| DECIMAL | SQL_DECIMAL | SQL_C_CHAR | ||
| DOUBLE | SQL_DOUBLE | SQL_C_DOUBLE | ||
| FLOAT | SQL_FLOAT | SQL_C_DOUBLE | ||
| GRAPHIC | SQL_GRAPHIC | SQL_C_DBCHAR | ||
| INTEGER | SQL_INTEGER | SQL_C_LONG | ||
| LONG VARCHAR b | SQL_LONGVARCHAR | SQL_C_CHAR | ||
| LONG VARCHAR FOR BIT DATA b | SQL_LONGVARBINARY | SQL_C_BINARY | ||
| LONG VARGRAPHIC b | SQL_LONGVARGRAPHIC | SQL_C_DBCHAR | ||
| NUMERIC c | SQL_NUMERIC c | SQL_C_CHAR | ||
| REAL | SQL_REAL | SQL_C_FLOAT | ||
| SMALLINT | SQL_SMALLINT | SQL_C_SHORT | ||
| TIME | SQL_TYPE_TIME d | SQL_C_TYPE_TIME d | ||
| TIMESTAMP | SQL_TYPE_TIMESTAMP d | SQL_C_TYPE_TIMESTAMP d | ||
| VARCHAR | SQL_VARCHAR | SQL_C_CHAR | ||
| VARCHAR FOR BIT DATA b | SQL_VARBINARY | SQL_C_BINARY | ||
| VARGRAPHIC | SQL_VARGRAPHIC | SQL_C_DBCHAR | ||
|
| ||||
Table 3 shows the generic type definitions for each symbolic C type.
| C Symbolic Data Type | C Type | Base C type | ||
|---|---|---|---|---|
| SQL_C_CHAR | SQLCHAR | unsigned char | ||
| SQL_C_BIT | SQLCHAR | unsigned char or char (Value 1 or 0) | ||
| SQL_C_TINYINT | SQLSCHAR | signed char (Range -128 to 127) | ||
| SQL_C_SHORT | SQLSMALLINT | short int | ||
| SQL_C_LONG | SQLINTEGER | long int | ||
| SQL_C_DOUBLE | SQLDOUBLE | double | ||
| SQL_C_FLOAT | SQLREAL | float | ||
| SQL_C_SBIGINT | SQLBIGINT | _int64 | ||
| SQL_C_UBIGINT | SQLBIGINT | unsigned _int64 | ||
| SQL_C_NUMERIC c | SQL_NUMERIC_STRUCT | see Table 4 | ||
| SQL_C_TYPE_DATE b | DATE_STRUCT | see Table 4 | ||
| SQL_C_TYPE_TIME b | TIME_STRUCT | see Table 4 | ||
| SQL_C_TYPE_TIMESTAMP b | TIMESTAMP_STRUCT | see Table 4 | ||
| SQL_C_CLOB_LOCATOR a | SQLINTEGER | long int | ||
| SQL_C_BINARY | SQLCHAR | unsigned char | ||
| SQL_C_BLOB_LOCATOR a | SQLINTEGER | long int | ||
| SQL_C_DBCHAR | SQLDBCHAR | wchar_t | ||
| SQL_C_DBCLOB_LOCATOR | SQLINTEGER | long int | ||
| ||||
| C Type | Generic Structure | Windows Structure |
|---|---|---|
| DATE_STRUCT |
typedef struct DATE_STRUCT
{
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
} DATE_STRUCT;
|
typedef struct tagDATE_STRUCT
{
SWORD year;
UWORD month;
UWORD day;
} DATE_STRUCT;
|
| TIME_STRUCT |
typedef struct TIME_STRUCT
{
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT;
|
typedef struct tagTIME_STRUCT
{
UWORD hour;
UWORD minutes;
UWORD second;
} TIME_STRUCT;
|
| TIMESTAMP_STRUCT |
typedef struct TIMESTAMP_STRUCT
{
SQLUSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
SQLINTEGER fraction;
} TIMESTAMP_STRUCT;
|
typedef struct tagTIMESTAMP_STRUCT
{
SWORD year;
UWORD month;
UWORD day;
UWORD hour;
UWORD minute;
UWORD second;
UDWORD fraction;
} TIMESTAMP_STRUCT;
|
| SQL_NUMERIC_STRUCT | (None. Windows 32-bit only) |
typedef struct tagSQL_NUMERIC_STRUCT
{
SQLCHAR precision;
SQLCHAR scale;
SQLCHAR sign; a
SQLCHAR
val[SQL_MAX_NUMERIC_LEN];b c
} SQL_NUMERIC_STRUCT;
|
|
Refer to Table 5 for more information on the SQLUSMALLINT C data type.
| ||
As well as the data types that map to SQL data types, there are also C
symbolic types used for other function arguments, such as pointers and
handles. Both the generic and ODBC data types are shown below.
Table 5. C Data Types and Base C Data Types
| Defined C Type | Base C Type | Typical Usage |
|---|---|---|
| SQLPOINTER | void * | Pointers to storage for data and parameters. |
| SQLHANDLE | long int | Handle used to reference all 4 types of handle information. |
| SQLHENV | long int | Handle referencing environment information. |
| SQLHDBC | long int | Handle referencing database connection information. |
| SQLHSTMT | long int | Handle referencing statement information. |
| SQLUSMALLINT | unsigned short int | Function input argument for unsigned short integer values. |
| SQLUINTEGER | unsigned long int | Function input argument for unsigned long integer values. |
| SQLRETURN | short int | Return code from DB2 CLI functions. |
|
Versions of DB2 CLI prior to Version 2.1:
| ||
As mentioned previously, DB2 CLI manages the transfer and any required conversion of data between the application and the DBMS. Before the data transfer actually takes place, the source, target or both data types are indicated when calling SQLBindParameter(), SQLBindCol() or SQLGetData(). These functions use the symbolic type names shown in Table 2, to identify the data types involved.
For example, to bind a parameter marker that corresponds to an SQL data type of DECIMAL(5,3), to an application's C buffer type of double, the appropriate SQLBindParameter() call would look like:
SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
SQL_DECIMAL, 5, 3, double_ptr, 0, NULL);
Table 2 shows only the default data conversions. The functions mentioned in the previous paragraph can be used to convert data to other types, but not all data conversions are supported or make sense. Table 6 shows all the conversions supported by DB2 CLI.
The first column in Table 6 contains the data type of the SQL data type. The remaining columns represent the C data types. If the C data type columns contains:
As an example, the table indicates that a CHAR (or a C character string as indicated in Table 6) can be converted into a SQL_C_LONG (a signed long). In contrast, a LONGVARCHAR cannot be converted to a SQL_C_LONG.
Refer to Appendix F, Data Conversion for information about the required formats and the results of converting between data types.
There are rules specified in the SQL Reference for limits on precision and scale, as well as truncation and rounding rules
for type conversions. These rules apply in DB2 CLI, with the following
exception: truncation of values to the right of the decimal point for
numeric values may return a truncation warning, whereas truncation to the left
of the decimal point returns an error. In cases of error, the
application should call SQLGetDiagRec() to obtain the SQLSTATE and
additional information on the failure. When moving and converting
floating point data values between the application and DB2 CLI, no
correspondence is guaranteed to be exact as the values may change in precision
and scale.
Table 6. Supported Data Conversions
|
SQL Data Type |
S Q L _ C _ C H A R |
S Q L _ C _ L O N G |
S Q L _ C _ S H O R T |
S Q L _ C _ T I N Y I N T |
|
S Q L _ C _ D O U B L E |
S Q L _ C _ T Y P E _ D A T E |
S Q L _ C _ T Y P E _ T I M E |
S Q L _ C _ T Y P E _ T I M E S T A M P |
S Q L _ C _ B I N A R Y |
S Q L _ C _ B I T |
S Q L _ C _ D B C H A R |
S Q L _ C _ C L O B _ L O C A T O R |
S Q L _ C _ B L O B _ L O C A T O R |
S Q L _ C _ D B C L O B _ L O C A T O R |
S Q L _ C _ B I G I N T |
S Q L _ C _ N U M E R I C | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
BLOB |
X |
|
|
|
|
|
|
|
|
D |
|
|
|
X |
|
|
| ||
|
CHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
|
X |
X | ||
|
CLOB |
D |
|
|
|
|
|
|
|
|
X |
|
|
X |
|
|
|
| ||
|
DATE |
X |
|
|
|
|
|
D |
|
X |
|
|
|
|
|
|
|
| ||
|
DBCLOB |
|
|
|
|
|
|
|
|
|
X |
|
D |
|
|
X |
|
| ||
|
DECIMAL |
D |
X |
X |
X |
X |
X |
|
|
|
X |
X |
|
|
|
|
X |
X | ||
|
DOUBLE |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
|
X |
X | ||
|
FLOAT |
X |
X |
X |
X |
X |
D |
|
|
|
|
X |
|
|
|
|
X |
X | ||
|
GRAPHIC |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
|
|
|
| ||
|
INTEGER |
X |
D |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
|
X |
X | ||
|
LONG VARCHAR |
D |
|
|
|
|
|
|
|
|
X |
|
|
|
|
|
|
| ||
|
LONG VARGRAPHIC |
X |
|
|
|
|
|
|
|
|
X |
|
D |
|
|
|
|
| ||
|
NUMERIC |
D |
X |
X |
X |
X |
X |
|
|
|
|
X |
|
|
|
|
|
X | ||
|
REAL |
X |
X |
X |
X |
D |
X |
|
|
|
|
X |
|
|
|
|
|
X | ||
|
SMALLINT |
X |
X |
D |
X |
X |
X |
|
|
|
|
X |
|
|
|
|
X |
X | ||
|
BIGINT |
X |
X |
X |
X |
X |
X |
|
|
|
X |
X |
|
|
|
|
D |
X | ||
|
TIME |
X |
|
|
|
|
|
|
D |
X |
|
|
|
|
|
|
|
| ||
|
TIMESTAMP |
X |
|
|
|
|
|
X |
X |
D |
|
|
|
|
|
|
|
| ||
|
VARCHAR |
D |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
|
|
|
X |
X | ||
|
VARGRAPHIC |
X |
|
|
|
|
|
|
|
|
|
|
D |
|
|
|
|
| ||
| |||||||||||||||||||
The following conventions deal with the various aspects of working with string arguments in DB2 CLI functions.
Input string arguments have an associated length argument. This argument indicates either the exact length of the argument (not including the null terminator), the special value SQL_NTS to indicate a null-terminated string, or SQL_NULL_DATA to pass a NULL value. If the length is set to SQL_NTS, DB2 CLI will determine the length of the string by locating the null terminator.
Output string arguments have two associated length arguments: an input length argument to specify the length of the allocated output buffer, and an output length argument to return the actual length of the string returned by DB2 CLI. The returned length value is the total length of the string available for return, regardless of whether it fits in the buffer or not.
For SQL column data, if the output is a null value, SQL_NULL_DATA is returned in the length argument and the output buffer is untouched. The descriptor field SQL_DESC_INDICATOR_PTR is set to SQL_NULL_DATA if the column value is a null value. For more information, including which other fields are set, see SQL_DESC_INDICATOR_PTR in SQLSetDescField().
If a function is called with a null pointer for an output length argument, DB2 CLI will not return a length, and assumes that the data buffer is large enough to hold the data. When the output data is a NULL value, DB2 CLI can not indicate that the value is NULL. If it is possible that a column in a result set can contain a NULL value, a valid pointer to the output length argument must always be provided. It is highly recommended that a valid output length argument always be used.
By default, every character string that DB2 CLI returns is terminated with a null terminator (hex 00), except for strings returned from graphic and DBCLOB data types into SQL_C_CHAR application variables. Graphic and DBCLOB data types that are retrieved into SQL_C_DBCHAR application variables are null terminated with a double byte null terminator. This requires that all buffers allocate enough space for the maximum number of bytes expected, plus the null-terminator.
It is also possible to use SQLSetEnvAttr() and set an environment attribute to disable null termination of variable length output (character string) data. In this case, the application allocates a buffer exactly as long as the longest string it expects. The application must provide a valid pointer to storage for the output length argument so that DB2 CLI can indicate the actual length of data returned; otherwise, the application will not have any means to determine this. The DB2 CLI default is to always write the null terminator.
It is possible, using the PATCH1 CLI/ODBC configuration keyword, to force DB2 CLI to null terminate graphic and DBCLOB strings. This keyword can be set from the CLI/ODBC Settings notebook accessible from the Client Configuration Assistant (CCA). Refer to the Platform Specific Details for CLI/ODBC Access. The Configure the CLI/ODBC Driver section for your platform will provide the steps required to set the keywords. The description of PATCH1 in Configuration Keywords includes how to find the setting required to force the null termination of graphic and DBCLOB strings.
If an output string does not fit into a buffer, DB2 CLI will truncate the string to the size of the buffer, and write the null terminator. If truncation occurs, the function will return SQL_SUCCESS_WITH_INFO and an SQLSTATE of 01004 indicating truncation. The application can then compare the buffer length to the output length to determine which string was truncated.
For example, if SQLFetch() returns SQL_SUCCESS_WITH_INFO, and an SQLSTATE of 01004, at least one of the buffers bound to a column is too small to hold the data. For each buffer that is bound to a column, the application can compare the buffer length with the output length and determine which column was truncated.
Normally, DB2 CLI interprets string arguments in a case-sensitive manner and does not trim any spaces from the values. The one exception is the cursor name input argument on the SQLSetCursorName() function, where if the cursor name is not delimited (enclosed by double quotes) the leading and trailing blanks are removed and case is ignored.
There are many situations where an application requires information about the characteristics and capabilities of the current DB2 CLI driver or the data source that it is connected to. DB2 CLI provides a number of functions that return this information.
There are two common situations where the application requires this information:
The following DB2 CLI functions provide data source specific information:
The getinfo.c sample, shown in Example generates the following output when connected to DB2.
Server Name: SAMPLE
Database Name: SAMPLE
Instance Name: db2inst1
DBMS Name: DB2/6000
DBMS Version: 05.00.0000
CLI Driver Name: libdb2.a
CLI Driver Version: 05.00.0000
ODBC SQL Conformance Level: Extended Grammar