ODBC 3.x API notes

The following table lists IBM® i Access ODBC 3.x APIs by their associated task and identifies considerations for each API.

Notes:

The IBM i Access ODBC Driver is a Unicode driver; however, ANSI applications will still continue to work with it. The ODBC Driver Manager will handle converting an ANSI ODBC API call to the wide version before calling the IBM i Access ODBC Driver. To write a Unicode application, you must call the wide version for some of these APIs. When writing an application to the wide ODBC interface, you need to know whether the length for each API is defined as character, in bytes, or if the length is not applicable. Refer to the 'Type' column in the following table for this information.
For more details on how these APIs work, search for ODBC at the Microsoft Web site.

Type	API	Description	Other considerations
Connecting to a data source Note: For information on how the connection APIs prompt signon dialogs see Signon dialog behavior. .Also see connection pooling for more information.
N/A	SQLAllocHandle	Obtains an environment and connection handle. One environment handle is used for one or more connections. May also allocate a statement or a descriptor handle.
Char	SQLConnect	Connects to a specific data source name with a specific user ID and password.	There is an option to control whether this API prompts a signon dialog when the user ID and password are not specified. This option can be set from the Connection options dialog on the General tab of the DSN.
Char	SQLDriverConnect	Connects to a specific driver by connection string or requests that the Driver Manager and driver display connection dialogs for the user.	Uses all keywords. Only DSN is required. Other values are optional. Refer to Connection string keywords for more information.
Char	SQLBrowseConnect	Returns successive levels of connection attributes and valid attribute values. When a value has been specified for each connection attribute, connects to the data source.	To make a connection attempt the SYSTEM keyword and either the DSN or DRIVER keywords must be specified. All the other keywords are optional. Note, the PWD keyword is not returned in the output string for security purposes. Refer to Connection string keywords for more implementation issues.
Get information regarding a driver or data source
Byte	SQLGetInfo	Returns information about a specific driver and data source.	Special attributes returned differently based on attributes and keywords.The information that is returned by SQLGetInfo can vary depending on which keywords and attributes are in use. The InfoType options that are affected are: SQL_CATALOG_NAME_SEPARATOR – By default a period is returned. If the connection string keyword NAM is set to 1, a comma is returned. SQL_CURSOR_COMMIT_BEHAVIOR, SQL_CURSOR_ROLLBACK_BEHAVIOR – By default SQL_CB_PRESERVE is returned. If the connection attribute, 1204, is set SQL_CB_DELETE is returned. SQL_DATA_SOURCE_READ_ONLY – By default N is returned. If the connection string keyword CONNTYPE is set to 0 then Y is returned. SQL_IDENTIFIER_QUOTE_CHAR – By default a double-quote mark is returned. If the application in use is MS QUERY (MSQRY32) then a single blank is returned. SQL_IDENTIFIER_CASE – By default SQL_IC_UPPER is returned. If the connection string keyword DEBUG has the option 2 set then SQL_IC_MIXED is rteurned. SQL_MAX_QUALIFIER_NAME_LEN – By default 18 is returned. If the connection string keyword DEBUG has the 8 bit set then 0 is returned. SQLDriverVer - Returns the version of the driver in the format of VV.RR.SSST, where, VV represents the version of the IBM i Access for Windows product. RR is the release identifier of the IBM i Access for Windows product. SSS is the number of the service pack that has been applied to the IBM i Access for Windows product, and T is the version of the test fix that has been applied for an ODBC driver problem, otherwise, it is 0.
N/A	SQLGetTypeInfo	Returns information about supported data types.	Different result data types can be seen when running to different IBM i versions. For example, the DECFLOAT data type is only in the result set when running to V6R1 or later servers. The "LONG VARCHAR" data type is not returned in the result set. This is due to problems that were seen with some applications expecting to specify a length with this type. "LONG VARCHAR FOR BIT DATA" and "LONG VARGRAPHIC" are also not returned for similar reasons. In the TYPE_NAME column, when a data type requires a value to be in parentheses, the parentheses are included in the data type name. However the parentheses are omitted when the parentheses would end up at the end of the data type string. In the following string example, the "CHAR" data type is followed by parenthesis while the "DATA" data type is not followed by parentheses: "CHAR( ) FOR BIT DATA". The setting for the connection string keyword GRAPHIC affects whether the driver returns graphic (DBCS) data types as supported types or not. See ODBC data types and how they correspond to DB2 for i database types for more information.
Set and retrieve driver attributes Note: Refer to Connection and statement attributes for details on driver-specific connection and statement attributes applicable to the following APIs.
Byte	SQLSetConnectAttr	Sets a connection option.
Byte	SQLGetConnectAttr	Returns the value of a connection option.
N/A	SQLSetEnvAttr	Sets an environment option.
N/A	SQLGetEnvAttr	Returns the value of an environment option.
Byte	SQLSetStmtAttr	Sets a statement option.	The SQL_ATTR_PARAMSET_SIZE, SQL_ATTR_ROW_ARRAY_SIZE, SQL_DESC_ARRAY_SIZE, and SQL_ROWSET_SIZE attributes support up to 32767 rows. If working with LOB locator fields the driver restricts these values to 1 row at a time. LOB fields are handled as locators if the MAXFIELDLEN connection string value is less than the LOB field size. SELECT statements that contain the FOR FETCH ONLY or FOR UPDATE clause override the current setting of SQL_ATTR_CONCURRENCY attribute. An error is not returned during the SQLExecute or SQLExecDirect if the SQL_ATTR_CONCURRENCY setting conflicts with the clause in the SQL statement. The following are not supported: SQL_ATTR_ASYNC_ENABLE SQL_ATTR_RETRIEVE_DATA SQL_ATTR_SIMULATE_CURSOR SQL_ATTR_USE_BOOKMARKS SQL_ATTR_FETCH_BOOKMARK_PTR SQL_ATTR_KEYSET_SIZE Setting SQL_ATTR_MAX_ROWS is supported, however, it only impacts performance for static cursors. The full result set is still built with other cursor types even if this option is set. Using the FETCH FIRST x ROWS ONLY clause in your SQL query may work better since it reduces the amount of work the server does.This API has been extended to also contain the cursor row count for the following two result set types: stored procedure array result sets static cursor result sets
Byte	SQLGetStmtAttr	Returns the value of a statement option.	The following are not supported: SQL_ATTR_ASYNC_ENABLE SQL_ATTR_RETRIEVE_DATA SQL_ATTR_SIMULATE_CURSOR SQL_ATTR_USE_BOOKMARKS SQL_ATTR_FETCH_BOOKMARK_PTR
Set and retrieve descriptor fields
Byte	SQLGetDescField	Returns a piece of information from a descriptor.
Char	SQLGetDescRec	Returns several pieces of information from a descriptor.
Byte	SQLSetDescField	Sets a descriptor field.	Can not set descriptor fields for an IRD other than SQL_DESC_ARRAY_STATUS_PTR and SQL_DESC_ROWS_PROCESSED_PTR. Does not support named parameters.
Char	SQLSetDescRec	Sets several options for a descriptor.
N/A	SQLCopyDesc	Copies information from one descriptor to another descriptor.	SQLCopyDesc does not support named parameters.
Prepare SQL requests
Char	SQLPrepare	Prepares an SQL statement for later processing.	Packages are created the first time a SQL statement is prepared for that Connection. This results in the first prepare taking slightly longer to complete than it would normally take. If there are any problems with a pre-existing package the first prepare may return an error depending on the setting for the package as specified in the DSN setup GUI. On the Package tab of the DSN setup GUI are default package settings. These settings are used when package settings have not already been customized for that application. Note, these are not global settings By default, the driver sends SQL statement text to the host in the EBCDIC CCSID associated with your job. Set the UNICODESQL keyword equal 1 or equal 2, to enable the driver to send SQL statement text to the host in Unicode. Note that when sending Unicode SQL statements the driver generates a different package name to avoid collisions with existing packages that contain EBCDIC SQL statements. Setting the connection string keyword UNICODESQL allows an application to specify Unicode data for literals in the SQL statement. See SQL Statement Considerations for several SQL statements that are not recommended to be prepared and executed. For information on which escape sequences and scalar functions the driver supports see SQLPrepare and SQLNativeSQL escape sequences and scalar functions.
Byte	SQLBindParameter	Assigns storage for a parameter in an SQL statement. See Parameter markers for additional information.	Data conversions are made directly from the C type that is specified to the actual host parameter (column) data type. The SQL data type and column size that are specified are ignored. Conversions that involve character data convert directly from the client codepage to the column CCSID. On V6R1 and later hosts: SQL_DEFAULT_PARAM and SQL_UNASSIGNED are only valid in binding to an INSERT or UPDATE Statement. Use on other statements will return an error. If Strlen_or_IndPtr = SQL_DEFAULT_PARAM, the driver uses the column default value. If SQL_UNASSIGNED or Strlen_or_IndPtr = -7, and the statement is an UPDATE statement, the corresponding column value in the table being updated is unchanged. On an INSERT statement, the default value is used. On pre-V6R1 hosts: Default and unassigned parameters are not supported. If SQL_DEFAULT_PARAM or SQL_UNASSIGNED is specified for the Strlen_or_IndPtr parameter, the driver returns an error during the execution of the SQL statement. For binding statements using SQL_DEFAULT_PARAM or SQL_UNASSIGNED, the driver returns SQLSTATE error of 07S01.
Char	SQLGetCursorName	Returns the cursor name associated with a statement handle.	The driver will upper case all cursor names without double-quotes around the name.
Char	SQLSetCursorName	Specifies a cursor name.	The cursor name is converted to capital letters if it is not entered in quotes. Cursor names that are entered in quotes are not converted. For example, myCursorName becomes MYCURSORNAME while "myCursorName" is treated as myCursorName, with a length of 14 since the quotes are included in the length. The driver supports only these characters in cursor names: "",a-z, A-Z, 0-9, or _. No error will be returned by SQLSetCursorName if an invalid name is entered, however, an error will be returned later when trying to use an invalid name. The maximum cursor name is 128 characters, including the leading and trailing double quotes if they exist, and must be in characters that can be translated from UNICODE to ANSI. If an application wishes to use a DRDA connection through ODBC then they will have the following restrictions: Cursor name changes are not allowed during the DRDA connection. Cursor names will be changed by the driver and should be checked via SQLGetCursorName after the cursor is open. (after SQLExecute or SQLExecDirect).
Submit requests
N/A	SQLExecute	Runs a prepared statement.	SQLExecute is affected by the settings of several of the connection string keywords such as PREFETCH, CONNTYPE, CMT, and LAZYCLOSE. Refer to Connection string keywords for descriptions of these keywords.
Char	SQLExecDirect	Runs a statement.	See SQLPrepare and SQLExecute.
Char	SQLNativeSQL	Returns the text of an SQL statement as translated by the driver.
Char	SQLDescribeParam	Returns the description for a specific parameter in a statement.
N/A	SQLNumParams	Returns the number of parameters in a statement.
N/A	SQLParamData	Returns the storage value assigned to a parameter for which data will be sent at run time (useful for long data values).
Byte	SQLPutData	Send part or all of a data value for a parameter (useful for long data values).
Retrieve results and related information
N/A	SQLRowCount	Returns the number of rows that are affected by an insert, update, or delete request.	This API has been extended to also contain the cursor row count for a result set using a static cursor or an array result set.
N/A	SQLNumResultCols	Returns the number of columns in the result set.
Char	SQLDescribeCol	Describes a column in the result set.
Byte	SQLColAttribute	Describes attributes of a column in the result set.
Byte	SQLBindCol	Assigns storage for a result column and specifies the data type.
N/A	SQLExtendedFetch	Returns rows in the result set. This is a supported 2.x ODBC API. However, new applications should use SQLFetchScroll API instead.	Uses the value of the statement attribute SQL_ROWSET_SIZE instead of SQL_ATTR_ROW_ARRAY_SIZE for the rowset size. You can only use SQLExtendedFetch in combination with SQLSetPos and SQLGetData if the row size is 1. SQL_FETCH_BOOKMARK is not supported. The result set for catalog APIs (such as SQLTables and SQLColumns) is forward only and read only. When SQLExtendedFetch is used with result sets generated by catalog APIs, no scrolling is allowed.
N/A	SQLFetch	Returns rows in the result set.	Can only be used with SQL_FETCH_FIRST and SQL_FETCH_NEXT since the cursor is forward only.
N/A	SQLFetchScroll	Returns rows in the result set. Can be used with scrollable cursors.	Does not support the fetch orientation of SQL_FETCH_BOOKMARK because the driver does not support bookmarks.
Byte	SQLGetData	Returns part or all of one column of one row of a result set (useful for long data values). See SQLFetch and SQLGetData for additional information.	SQLGetData can only be used with single row fetches. Errors are reported by SQLGetData if the row array size is larger than one.
N/A	SQLSetPos	Positions a cursor within a fetched block of data.	SQL_UPDATE, SQL_DELETE, SQL_ADD are unsupported options for Operations parameter. SQL_LOCK_EXCLUSIVE, SQL_LOCK_UNLOCK are unsupported options for the LockType parameter.
N/A	SQLBulkOperations	Performs bulk insertions and bulk bookmark operations, including update, delete, and fetch by bookmark.	The driver does not support SQLBulkOperations.
N/A	SQLMoreResults	Determines whether there are more result sets available and if so, initializes processing for the next result set.
Byte	SQLGetDiagField	Returns a piece of diagnostic information.	The SQL_DIAG_CURSOR_ROW_COUNT option is only accurate for static cursors when running to V5R1 or later server versions.
Char	SQLGetDiagRec	Returns additional error or status information.
Get data source system table information
Char	SQLColumnPrivileges	Returns a list of columns and associated privileges for one or more tables.
Char	SQLColumns	Returns a list of information on columns in one or more tables.
Char	SQLForeignKeys	Returns a list of column names that comprise foreign keys, if they exist for a specified table.
Char	SQLProcedureColumns	Returns the list of input and output parameters for the specified procedures.	The driver does not return information about columns that make up result sets generated by procedures. The driver only returns information about the parameters to the procedures.
Char	SQLProcedures	Returns the list of procedure names stored in a specific data source.
Char	SQLSpecialColumns	Retrieves information about the optimal set of columns that uniquely identifies a row in a specified table. It also retrieves information about the columns that are automatically updated when any value in the row is updated by a transaction.	If called with the SQL_BEST_ROWID option, returns all indexed columns of that table.
Char	SQLStatistics	Retrieves statistics about a single table and the list of indexes that are associated with the table.	Beginning with V6R1, you can define a derived key index. When SQLStatistics is used to retrieve information about the index, the COLUMN_NAME result set column returns the expression that represents the derived key index. If the WHERE clause was used when creating the index, the Where expression is returned in FILTER_CONDITION result set column.
Char	SQLTables	Returns a list of schemas, tables, or table types in the data source.	See SQLTables Description
Char	SQLTablePrivileges	Returns a list of tables and the privileges that are associated with each table.
Char	SQLPrimaryKeys	Returns the list of column name or names that comprise the primary key for a table.
Clean up a statement
N/A	SQLFreeStmt	Ends statement processing and closes the associated cursor, and discards pending results.
N/A	SQLCloseCursor	Closes a cursor that is open on the statement handle.
N/A	SQLCancel	Cancels an SQL statement.	Not all queries can be cancelled. This is recommended only for long running queries. For more information, see Handle long-running queries.
N/A	SQLEndTran	Commits or rolls back a transaction.	For information regarding commitment control, see Commitment control considerations.
Terminate a connection
N/A	SQLDisconnect	Closes the connection.
N/A	SQLFreeHandle	Releases resources associated with handles.