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 CWB_ATTR_PRESERVE_CURSORS 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 option 8 set, then 0 is returned.
  • SQL_DRIVER_VER - Returns the version of the driver in the format of VV.RR.SSST, where,
    • VV represents the version of the IBM i Access product.
    • RR is the release identifier of the IBM i Access product.
    • SSS is the number of the service pack that has been applied to the IBM i Access 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.

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 to 1 or 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. 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.

Although IBM i supports default parameters for procedures (IBM i 7.1 and up) and functions (IBM i 7.2 and up), SQL_DEFAULT_PARAM is only supported for using default column values in INSERT and UPDATE statements.

A driver-specific value, CWB_UNASSIGNED, can be specified for the indicator value on an INSERT or UPDATE statement to cause database to treat the statement as if there was no parameter marker. In this case, the column is not updated if the statement is an UPDATE and the default value is used if the statement is an INSERT. This enables applications to code a generic INSERT or UPDATE statement, but selectively choose which columns are affected by the call of that statement.
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.  
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). 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.  
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.  
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. When SQLStatistics is used to retrieve information about a derived key 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.