DB2 UDB CLI functions

 

These DB2^® UDB call level interface APIs are available for database access on the i5/OS^® operating system. Each of the DB2 UDB CLI function descriptions is presented in a consistent format.

See Categories of DB2 UDB CLIs for a categorical listing of the functions.

How the CLI functions are described

The following table shows the type of information that is described in each section of the function description.

Type	Description
Purpose	This section gives a brief overview of what the function does. It also indicates if any functions should be called before and after calling the function being described.
Syntax	This section contains the C language prototype for the i5/OS environment.
Arguments	This section lists each function argument, along with its data type, a description and whether it is an input or output argument. Each DB2 UDB CLI argument is either an input or output argument. With the exception of SQLGetInfo(), DB2 UDB CLI only modifies arguments that are indicated as output. Some functions contain input or output arguments which are known as deferred or bound arguments. These arguments are pointers to buffers allocated by the application. These arguments are associated with (or bound to) either a parameter in an SQL statement, or a column in a result set. The data areas specified by the function are accessed by DB2 UDB CLI at a later time. It is important that these deferred data areas are still valid at the time DB2 UDB CLI accesses them.
Usage	This section provides information about how to use the function, and any special considerations. Possible error conditions are not discussed here, but are listed in the diagnostics section instead.
Return codes	This section lists all the possible function return codes. When SQL_ERROR or SQL_SUCCESS_WITH_INFO is returned, error information can be obtained by calling SQLError(). Refer to Diagnostics in a DB2 UDB CLI application for more information about return codes.
Diagnostics	This section contains a table that lists the SQLSTATEs explicitly returned by DB2 UDB CLI (SQLSTATEs generated by the Database Management System (DBMS) might also be returned) and indicates the cause of the error. These values are obtained by calling SQLError() after the function returns SQL_ERROR or SQL_SUCCESS_WITH_INFO. An * in the first column indicates that the SQLSTATE is returned only by DB2 UDB CLI, and is not returned by other ODBC drivers. Refer to Diagnostics in a DB2 UDB CLI application for more information about diagnostics.
Restrictions	This section indicates any differences or limitations between DB2 UDB CLI and ODBC that might affect an application.
Example	This section is a code fragment demonstrating the use of the function. The complete source used for all code fragments is listed in Examples: DB2 UDB CLI applications.
References	This section lists related DB2 UDB CLI functions.

• Categories of DB2 UDB CLIs
  The list shows the DB2 UDB call level interface (CLI) functions by category.

• SQLAllocConnect - Allocate connection handle
  SQLAllocConnect() allocates a connection handle and associated resources within the environment that is identified by the input environment handle. Call SQLGetInfo() with fInfoType set to SQL_ACTIVE_CONNECTIONS to query the number of connections that can be allocated at any one time.

• SQLAllocEnv - Allocate environment handle
  SQLAllocEnv() allocates an environment handle and associated resources.

• SQLAllocHandle - Allocate handle
  SQLAllocHandle() allocates any type of handle.

• SQLAllocStmt - Allocate a statement handle
  SQLAllocStmt() allocates a new statement handle and associates it with the connection specified by the connection handle. There is no defined limit to the number of statement handles that can be allocated at any one time.

• SQLBindCol - Bind a column to an application variable
  SQLBindCol() is used to associate (bind) columns in a result set to application variables (storage buffers) for all data types. Data is transferred from the Database Management System (DBMS) to the application when SQLFetch() is called.

• SQLBindFileToCol - Bind LOB file reference to LOB column
  SQLBindFileToCol() is used to associate (bind) a LOB column in a result set to a file reference or an array of file references. In this way, data in the LOB column can be transferred directly into a file when each row is fetched for the statement handle.

• SQLBindFileToParam - Bind LOB file reference to LOB parameter
  SQLBindFileToParam() is used to associate (bind) a parameter marker in an SQL statement to a file reference or an array of file references. In this way, data from the file can be transferred directly into a LOB column when that statement is subsequently processed.

• SQLBindParam - Bind a buffer to a parameter marker
  SQLBindParam() has been deprecated and replaced by SQLBindParameter(). Although this version of DB2 UDB CLI continues to support SQLBindParam(), it is recommended that you begin using SQLBindParameter() in your DB2 UDB CLI programs so that they conform to the latest standards.

• SQLBindParameter - Bind a parameter marker to a buffer
  SQLBindParameter() is used to associate (bind) parameter markers in an SQL statement to application variables. Data is transferred from the application to the Database Management System (DBMS) when SQLExecute() or SQLExecDirect() is called. Data conversion might occur when the data is transferred.

• SQLCancel - Cancel statement
  SQLCancel() is used to end the processing of an SQL statement operation that is running synchronously. To cancel the function, the application calls SQLCancel() with the same statement handle that is used by the target function, but on a different thread. How the function is canceled depends on the operating system.

• SQLCloseCursor - Close cursor statement
  SQLCloseCursor() closes the open cursor on a statement handle.

• SQLColAttributes - Obtain column attributes
  SQLColAttributes() obtains an attribute for a column of the result set, and is also used to determine the number of columns. SQLColAttributes() is a more extensible alternative to the SQLDescribeCol() function.

• SQLColumnPrivileges - Get privileges associated with the columns of a table
  SQLColumnPrivileges() returns a list of columns and associated privileges for the specified table. The information is returned in an SQL result set, which can be retrieved with the same functions that are used to process a result set generated from a query.

• SQLColumns - Get column information for a table
  SQLColumns() returns a list of columns in the specified tables. The information is returned in an SQL result set, which can be retrieved with the same functions that are used to fetch a result set generated by a SELECT statement.

• SQLConnect - Connect to a data source
  SQLConnect() establishes a connection to the target database. The application can optionally supply a target SQL database, an authorization name, and an authentication string.

• SQLCopyDesc - Copy description statement
  SQLCopyDesc() copies the fields of the data structure associated with the source handle to the data structure associated with the target handle.

• SQLDataSources - Get list of data sources
  SQLDataSources() returns a list of target databases available, one at a time. A database must be cataloged to be available.

• SQLDescribeCol - Describe column attributes
  SQLDescribeCol() returns the result descriptor information (column name, type, precision) for the indicated column in the result set generated by a SELECT statement.

• SQLDescribeParam - Return description of a parameter marker
  SQLDescribeParam() returns the description of a parameter marker associated with a prepared SQL statement. This information is also available in the fields of the implementation parameter descriptor.

• SQLDisconnect - Disconnect from a data source
  SQLDisconnect() ends the connection associated with the database connection handle.

• SQLDriverConnect - (Expanded) Connect to a data source
  SQLDriverConnect() is an alternative to SQLConnect(). Both functions establish a connection to the target database, but SQLDriverConnect() uses a connection string to determine the data source name, user ID, and password. The functions are the same; both are supported for compatibility purposes.

• SQLEndTran - Commit or roll back a transaction
  SQLEndTran() commits or rolls back the current transaction in the connection.

• SQLError - Retrieve error information
  SQLError() returns the diagnostic information associated with the most recently called DB2 UDB CLI function for a particular statement, connection, or environment handle.

• SQLExecDirect - Execute a statement directly
  SQLExecDirect() directly runs the specified SQL statement. The statement can only be processed once. Also, the connected database server must be able to prepare the statement.

• SQLExecute - Execute a statement
  SQLExecute() runs a statement that was successfully prepared using SQLPrepare() once or multiple times. The statement is processed with the current values of any application variables that were bound to parameter markers by SQLBindParam().

• SQLExtendedFetch - Fetch array of rows
  SQLExtendedFetch() extends the function of SQLFetch() by returning a block of data that contains multiple rows (called a rowset) in the form of an array, for each bound column. The size of the rowset is determined by the SQL_ROWSET_SIZE attribute on an SQLSetStmtAttr() call.

• SQLFetch - Fetch next row
  SQLFetch() advances the cursor to the next row of the result set, and retrieves any bound columns.

• SQLFetchScroll - Fetch from a scrollable cursor
  SQLFetchScroll() positions the cursor based on the requested orientation and then retrieves any bound columns.

• SQLForeignKeys - Get the list of foreign key columns
  SQLForeignKeys() returns information about foreign keys for the specified table. The information is returned in an SQL result set, which can be processed with the same functions that are used to retrieve a result that is generated by a query.

• SQLFreeConnect - Free connection handle
  SQLFreeConnect() invalidates and frees the connection handle. All DB2 UDB CLI resources associated with the connection handle are freed.

• SQLFreeEnv - Free environment handle
  SQLFreeEnv() invalidates and frees the environment handle. All DB2 UDB CLI resources associated with the environment handle are freed.

• SQLFreeHandle - Free a handle
  SQLFreeHandle() invalidates and frees a handle.

• SQLFreeStmt - Free (or reset) a statement handle
  SQLFreeStmt() ends processing on the statement that is referenced by the statement handle.

• SQLGetCol - Retrieve one column of a row of the result set
  SQLGetCol() retrieves data for a single column in the current row of the result set. This is an alternative to SQLBindCol(), which transfers data directly into application variables on a call to SQLFetch(). SQLGetCol() is also used to retrieve large character-based data in pieces.

• SQLGetConnectAttr - Get the value of a connection attribute
  SQLGetConnectAttr() returns the current settings for the specified connection option.

• SQLGetConnectOption - Return current setting of a connect option
  SQLGetConnectOption() has been deprecated and replaced with SQLGetConnectAttr(). Although this version of DB2 UDB CLI continues to support SQLGetConnectOption(), it is recommended that you begin using SQLGetConnectAttr() in your DB2 UDB CLI programs so that they conform to the latest standards.

• SQLGetCursorName - Get cursor name
  SQLGetCursorName() returns the cursor name associated with the input statement handle. If a cursor name is explicitly set by calling SQLSetCursorName(), this name is returned; otherwise, an implicitly generated name is returned.

• SQLGetData - Get data from a column
  SQLGetData() retrieves data for a single column in the current row of the result set. This is an alternative to SQLBindCol(), which transfers data directly into application variables on a call to SQLFetch(). SQLGetData() can also be used to retrieve large character-based data in pieces.

• SQLGetDescField - Get descriptor field
  SQLGetDescField() obtains a value from a descriptor. SQLGetDescField() is a more extensible alternative to the SQLGetDescRec() function.

• SQLGetDescRec - Get descriptor record
  SQLGetDescRec() obtains an entire record from a descriptor. SQLGetDescRec() is a more concise alternative to the SQLGetDescField() function.

• SQLGetDiagField - Return diagnostic information (extensible)
  SQLGetDiagField() returns the diagnostic information associated with the most recently called DB2 UDB CLI function for a particular statement, connection, or environment handle.

• SQLGetDiagRec - Return diagnostic information (concise)
  SQLGetDiagRec() returns the diagnostic information associated with the most recently called DB2 UDB CLI function for a particular statement, connection, or environment handle.

• SQLGetEnvAttr - Return current setting of an environment attribute
  SQLGetEnvAttr() returns the current settings for the specified environment attribute.

• SQLGetFunctions - Get functions
  SQLGetFunctions() queries whether a specific function is supported. This allows applications to adapt to varying levels of support when using different drivers.

• SQLGetInfo - Get general information
  SQLGetInfo() returns general information (including supported data conversions) about the Database Management System (DBMS) that the application is currently connected to.

• SQLGetLength - Retrieve length of a string value
  SQLGetLength() is used to retrieve the length of a large object value referenced by a large object locator. The large object locator has been returned from the data source (as a result of a fetch or an SQLGetSubString() call) during the current transaction.

• SQLGetPosition - Return starting position of string
  SQLGetPosition() is used to return the starting position of one string within a LOB value (the source). The source value must be a LOB locator; the search string can be a LOB locator or a literal string.

• SQLGetStmtAttr - Get the value of a statement attribute
  SQLGetStmtAttr() returns the current settings of the specified statement attribute.

• SQLGetStmtOption - Return current setting of a statement option
  SQLGetStmtOption() has been deprecated and replaced with SQLGetStmtAttr(). Although this version of DB2 UDB CLI continues to support SQLGetStmtOption(), it is recommended that you begin using SQLGetStmtAttr() in your DB2 UDB CLI programs so that they conform to the latest standards.

• SQLGetSubString - Retrieve portion of a string value
  SQLGetSubString() is used to retrieve a portion of a large object value referenced by a large object locator. The large object locator has been returned from the data source (returned by a fetch or a previous SQLGetSubString() call) during the current transaction.

• SQLGetTypeInfo - Get data type information
  SQLGetTypeInfo() returns information about the data types that are supported by the Database Management Systems (DBMSs) associated with DB2 UDB CLI. The information is returned in an SQL result set. The columns can be received using the same functions that are used to process a query.

• SQLLanguages - Get SQL dialect or conformance information
  SQLLanguages() returns SQL dialect or conformance information. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to fetch a result set generated by a SELECT statement.

• SQLMoreResults - Determine whether there are more result sets
  SQLMoreResults() determines whether there is more information available on the statement handle that has been associated with a stored procedure that is returning result sets.

• SQLNativeSql - Get native SQL text
  SQLNativeSql() is used to show how DB2 UDB CLI interprets vendor escape clauses. If the original SQL string that is passed by the application contains vendor escape clause sequences, DB2 UDB CLI returns the transformed SQL string that is seen by the data source (with vendor escape clauses either converted or discarded as appropriate).

• SQLNextResult - Process the next result set
  SQLNextResult() determines whether there is more information available on the statement handle that has been associated with a stored procedure that is returning result sets.

• SQLNumParams - Get number of parameters in an SQL statement
  SQLNumParams() returns the number of parameter markers in an SQL statement.

• SQLNumResultCols - Get number of result columns
  SQLNumResultCols() returns the number of columns in the result set associated with the input statement handle.

• SQLParamData - Get next parameter for which a data value is needed
  SQLParamData() is used with SQLPutData() to send long data in pieces. It can also be used to send fixed-length data.

• SQLParamOptions - Specify an input array for a parameter
  SQLParamOptions() provides the ability to set multiple values for each parameter set by SQLBindParameter(). This allows the application to insert multiple rows into a table on a single call to SQLExecute() or SQLExecDirect().

• SQLPrepare - Prepare a statement
  SQLPrepare() associates an SQL statement with the input statement handle and sends the statement to the DBMS to be prepared. The application can reference this prepared statement by passing the statement handle to other functions.

• SQLPrimaryKeys - Get primary key columns of a table
  SQLPrimaryKeys() returns a list of column names that comprise the primary key for a table. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set that is generated by a query.

• SQLProcedureColumns - Get input/output parameter information for a procedure
  SQLProcedureColumns() returns a list of input and output parameters associated with a procedure. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set that is generated by a query.

• SQLProcedures - Get list of procedure names
  SQLProcedures() returns a list of procedure names that have been registered on the system and match the specified search pattern.

• SQLPutData - Pass data value for a parameter
  SQLPutData() is called following an SQLParamData() call returning SQL_NEED_DATA to supply parameter data values. This function can be used to send large parameter values in pieces.

• SQLReleaseEnv - Release all environment resources
  SQLReleaseEnv() invalidates and frees the environment handle. All DB2 UDB CLI resources associated with the environment handle are freed.

• SQLRowCount - Get row count
  SQLRowCount() returns the number of rows in a table affected by an UPDATE, INSERT, or DELETE statement processed against the table, or a view based on the table.

• SQLSetConnectAttr - Set a connection attribute
  SQLSetConnectAttr() sets connection attributes for a particular connection.

• SQLSetConnectOption - Set connection option
  SQLSetConnectOption() has been deprecated and replaced with SQLSetConnectAttr(). Although this version of DB2 UDB CLI continues to support SQLSetConnectOption(), it is recommended that you begin using SQLSetConnectAttr() in your DB2 UDB CLI programs so that they conform to the latest standards.

• SQLSetCursorName - Set cursor name
  SQLSetCursorName() associates a cursor name with the statement handle. This function is optional because DB2 UDB CLI implicitly generates a cursor name when needed.

• SQLSetDescField - Set a descriptor field
  SQLSetDescField() sets a field in a descriptor. SQLSetDescField() is a more extensible alternative to the SQLSetDescRec() function.

• SQLSetDescRec - Set a descriptor record
  SQLSetDescRec() sets all the attributes for a descriptor record. SQLSetDescRec() is a more concise alternative to the SQLDescField() function.

• SQLSetEnvAttr - Set environment attribute
  SQLSetEnvAttr() sets an environment attribute for the current environment.

• SQLSetParam - Set parameter
  SQLSetParam() has been deprecated and replaced by SQLBindParameter(). Although this version of DB2 UDB CLI continues to support SQLSetParam(), it is recommended that you begin using SQLBindParameter() in your DB2 UDB CLI programs so that they conform to the latest standards.

• SQLSetStmtAttr - Set a statement attribute
  SQLSetStmtAttr() sets an attribute of a specific statement handle. To set an option for all statement handles associated with a connection handle, the application can call SQLSetConnectOption().

• SQLSetStmtOption - Set statement option
  SQLSetStmtOption() has been deprecated and replaced with SQLSetStmtAttr(). Although this version of DB2 UDB CLI continues to support SQLSetStmtOption(), it is recommended that you begin using SQLSetStmtAttr() in your DB2 UDB CLI programs so that they conform to the latest standards.

• SQLSpecialColumns - Get special (row identifier) columns
  SQLSpecialColumns() returns unique row identifier information (primary key or unique index) for a table. For example, unique index or primary key information. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to fetch a result set generated by a SELECT statement.

• SQLStatistics - Get index and statistics information for a base table
  SQLStatistics() retrieves index information for a given table. It also returns the cardinality and the number of pages associated with the table and the indexes on the table. The information is returned in a result set, which can be retrieved using the same functions that are used to fetch a result set generated by a SELECT statement.

• SQLTablePrivileges - Get privileges associated with a table
  SQLTablePrivileges() returns a list of tables and associated privileges for each table. The information is returned in an SQL result set, which can be retrieved using the same functions that are used to process a result set generated by a query.

• SQLTables - Get table information
  SQLTables() returns a list of table names and associated information stored in the system catalogs of the connected data source. The list of table names is returned as a result set, which can be retrieved using the same functions that are used to retrieve a result set generated by a SELECT statement.

• SQLTransact - Commit or roll back transaction
  SQLTransact() commits or rolls back the current transaction in the connection.

 

Parent topic:

SQL call level interface