DB2 for i CLI functions
These DB2® for i call level interface APIs are available for database access on the IBM® i operating system. Each of the DB2 for i CLI function descriptions is presented in a consistent format.
See Categories of DB2 for i 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 for i CLI argument is either an input or output argument. With the exception of SQLGetInfo(), DB2 for i 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 for i CLI at a later time. It is important that these deferred data areas are still valid at the time DB2 for i 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 for i CLI application for more information about return codes. |
| Diagnostics | This section contains a table that lists the SQLSTATEs explicitly returned by DB2 for i 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 for i CLI, and is not returned by other ODBC drivers. Refer to Diagnostics in a DB2 for i CLI application for more information about diagnostics. |
| Restrictions | This section indicates any differences or limitations between DB2 for i 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 for i CLI applications. |
| References | This section lists related DB2 for i CLI functions. |
- Categories of DB2 for i CLI functions
The list shows the DB2 for i 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 for i CLI continues to support SQLBindParam(), it is recommended that you begin using SQLBindParameter() in your DB2 for i 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. - SQLColAttribute - Return a column attribute
SQLColAttribute() obtains an attribute for a column of the result set, and is also used to determine the number of columns. SQLColAttribute() is a more extensible alternative to the SQLDescribeCol() function. - SQLColAttributes - Obtain column attributes
SQLColAttributes() has been deprecated and replaced by SQLColAttribute(). - 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 query 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 - 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 for i 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 for i CLI resources associated with the connection handle are freed. - SQLFreeEnv - Free environment handle
SQLFreeEnv() invalidates and frees the environment handle. All DB2 for i 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 for i CLI continues to support SQLGetConnectOption(), it is recommended that you begin using SQLGetConnectAttr() in your DB2 for i 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 internally 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 for i 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 for i 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 for i CLI continues to support SQLGetStmtOption(), it is recommended that you begin using SQLGetStmtAttr() in your DB2 for i 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 for i 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 for i CLI interprets vendor escape clauses. If the original SQL string that is passed by the application contains vendor escape clause sequences, DB2 for i 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 run INSERT, UPDATE, DELETE, and MERGE statements providing multiple sets of arguments 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 for i 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, MERGE, SELECT from 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 for i CLI continues to support SQLSetConnectOption(), it is recommended that you begin using SQLSetConnectAttr() in your DB2 for i 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 for i 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 SQLSetDescField() 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 for i CLI continues to support SQLSetParam(), it is recommended that you begin using SQLBindParameter() in your DB2 for i 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 for i CLI continues to support SQLSetStmtOption(), it is recommended that you begin using SQLSetStmtAttr() in your DB2 for i 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 a transaction
SQLTransact() commits or rolls back the current transaction in the connection.
Parent topic: SQL call level interface