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.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLGetInfoW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN SQLGetInfo (SQLHDBC        hdbc,
                      SQLSMALLINT    fInfoType,
                      SQLPOINTER     rgbInfoValue,
                      SQLSMALLINT    cbInfoValueMax,
                      SQLSMALLINT    *pcbInfoValue);

Function arguments

Table 1. SQLGetInfo arguments
Data type	Argument	Use	Description
SQLHDBC	hdbc	Input	Database connection handle.
SQLSMALLINT	fInfoType	Input	Type of the required information.
SQLPOINTER	rgbInfoValue	Output (also input)	Pointer to buffer where this function stores the required information. Depending on the type of information being retrieved, four types of information can be returned: 16-bit integer value 32-bit integer value 32-bit binary value Null-terminated character string
SQLSMALLINT	cbInfoValueMax	Input	The maximum length of the buffer pointed by rgbInfoValue pointer.
SQLSMALLINT *	pcbInfoValue	Output	Pointer to location where this function returns the total number of bytes available to return the required information. If the value in the location pointed to by pcbInfoValue is greater than the size of the rgbInfoValue buffer as specified in cbInfoValueMax, then the string output information is truncated to cbInfoValueMax - 1 bytes and the function returns with SQL_SUCCESS_WITH_INFO.

Usage

Table 2 lists the possible values of fInfoType and a description of the information that SQLGetInfo() returns for that value.

Table 2. Information returned by SQLGetInfo
fInfoType	Format	Description and notes
SQL_ACTIVE_CONNECTIONS	Short int	The maximum number of active connections supported per application. Zero is returned, indicating that the limit is dependent on system resources.
SQL_ACTIVE_STATEMENTS	Short int	The maximum number of active statements per connection. Zero is returned, indicating that the limit is dependent on system resources.
SQL_AGGREGATE_FUNCTIONS	32-bit mask	A bit mask enumerating support for aggregation functions: SQL_AF_ALL SQL_AF_AVG SQL_AF_COUNT SQL_AF_DISTINCT SQL_AF_MAX SQL_AF_MIN SQL_AF_SUM
SQL_CATALOG_NAME	String	A character string of Y indicates that the data source supports catalog names. N indicates that catalog names are not supported.
SQL_COLUMN_ALIAS	String	Whether the connection supports column aliases. The value Y is returned if the connection supports the concept of a column alias.
SQL_CONNECTION_JOB_NAME	String	When in server mode, this is a character string that contains the complete job name associated with the connection. When not in server mode, a function sequence error is returned.
SQL_CONVERT_BIGINT SQL_CONVERT_BINARY SQL_CONVERT_BLOB SQL_CONVERT_CHAR SQL_CONVERT_CLOB SQL_CONVERT_DATE SQL_CONVERT_DBCLOB SQL_CONVERT_DECIMAL SQL_CONVERT_DOUBLE SQL_CONVERT_FLOAT SQL_CONVERT_INTEGER SQL_CONVERT_LONGVARBINARY SQL_CONVERT_LONGVARCHAR SQL_CONVERT_NUMERIC SQL_CONVERT_REAL SQL_CONVERT_SMALLINT SQL_CONVERT_TIME SQL_CONVERT_TIMESTAMP SQL_CONVERT_VARBINARY SQL_CONVERT_VARCHAR SQL_CONVERT_WCHAR SQL_CONVERT_WLONGVARCHAR SQL_CONVERT_WVARCHAR	32-bit mask	This indicates the conversions supported by the data source with the CONVERT scalar function for data of the type named in the infoType. If the bit mask equals zero, the data source does not support any conversions for the data of the named type, including conversions to the same data type. For example, to find out if a data source supports the conversion of SQL_INTEGER data to the SQL_DECIMAL data type, an application calls SQLGetInfo() with finfoType of SQL_CONVERT_INTEGER. The application then ANDs the returned bit mask with SQL_CVT_DECIMAL. If the resulting value is nonzero, then the conversion is supported. The following bit masks are used to determine which conversions are supported: SQL_CONVERT_BLOB SQL_CONVERT_CLOB SQL_CONVERT_DBCLOB SQL_CONVERT_SMALLINT SQL_CONVERT_TIME SQL_CONVERT_TIMESTAMP SQL_CONVERT_VARBINARY SQL_CONVERT_VARCHAR SQL_CONVERT_WCHAR SQL_CONVERT_WLONGVARCHAR SQL_CONVERT_WVARCHAR SQL_CVT_BIGINT SQL_CVT_BINARY SQL_CVT_CHAR SQL_CVT_DATE SQL_CVT_DECIMAL SQL_CVT_DOUBLE SQL_CVT_FLOAT SQL_CVT_INTEGER SQL_CVT_LONGVARBINARY SQL_CVT_LONGVARCHAR SQL_CVT_NUMERIC SQL_CVT_REAL
SQL_CONVERT_FUNCTIONS	32 bit mask	This indicates the scalar conversion functions supported by the driver and associated data source: SQL_FN_CVT_CONVERT is used to determine which conversion functions are supported. SQL_FN_CVT_CAST is used to determine which cast functions are supported.
SQL_CORRELATION_NAME	Short int	This indicates the degree of correlation name support by the system: SQL_CN_ANY – Correlation name is supported and can be any valid user-defined name. SQL_CN_NONE – Correlation name is not supported. SQL_CN_DIFFERENT – Correlation name is supported but it must be different from the name of the table that it represents.
SQL_CURSOR_COMMIT_BEHAVIOR	16-bit integer	This indicates how a COMMIT operation affects cursors: SQL_CB_DELETE destroys cursors and drops access plans for dynamic SQL statements. SQL_CB_CLOSE destroys cursors, but retains access plans for dynamic SQL statements (including nonquery statements). SQL_CB_PRESERVE retains cursors and access plans for dynamic statements (including nonquery statements). Applications can continue to fetch data, or close the cursor and reprocess the query without preparing the statement again. Note: After the COMMIT operation, a FETCH must be issued to reposition the cursor before actions such as positioned updates or deletes can be taken.
SQL_CURSOR_ROLLBACK_BEHAVIOR	16-bit integer	This indicates how a ROLLBACK operation affects cursors: SQL_CB_DELETE destroys cursors and drops access plans for dynamic SQL statements. SQL_CB_CLOSE destroys cursors, but retains access plans for dynamic SQL statements (including nonquery statements) SQL_CB_PRESERVE retains cursors and access plans for dynamic statements (including nonquery statements). Applications can continue to fetch data, or close the cursor and run the query again without preparing the statement again. Note: DB2 servers do not have the SQL_CB_PRESERVE property.
SQL_DATA_SOURCE_NAME	String	Name of the connected data source for the connection handle.
SQL_DATA_SOURCE_READ_ONLY	String	A character string of Y indicates that the database is set to READ ONLY mode; an N indicates that it is not set to READ ONLY mode.
SQL_DATABASE_NAME	String	Name of the current database in use. This string is the same as that returned by the SELECT CURRENT SERVER SQL statement.
SQL_DBMS_NAME	String	Name of the Distributed Relational Database Architecture (DRDA) Service Name being accessed. For example: AS for DB2 for i DB2/xxx for DB2 for Linux, UNIX, and Windows DB2 for DB2 for z/OS®
SQL_DBMS_VER	String	Version of the DBMS product accessed.
SQL_DEFAULT_TXN_ISOLATION	32-bit mask	The default transaction-isolation level supported. One of the following masks are returned: SQL_TXN_READ_UNCOMMITTED – Changes are immediately perceived by all transactions (dirty read, non-repeatable read, and phantoms are possible). This is equivalent to UR level. SQL_TXN_READ_COMMITTED – Row read by transaction 1 can be altered and committed by transaction 2 (non-repeatable read and phantoms are possible). This is equivalent to CS level. SQL_TXN_REPEATABLE_READ – A transaction can add or remove rows matching the search condition or a pending transaction (repeatable read, but phantoms are possible). This is equivalent to RS level. SQL_TXN_SERIALIZABLE – Data affected by pending transaction is not available to other transactions (repeatable read, phantoms are not possible). This is equivalent to RR level. SQL_TXN_VERSIONING – Not applicable to IBM® DBMSs. SQL_TXN_NOCOMMIT – Any changes are effectively committed at the end of a successful operation; no explicit commit or rollback operation is allowed. This is a DB2 isolation level. In IBM terminology, SQL_TXN_READ_UNCOMMITTED is uncommitted read. SQL_TXN_READ_COMMITTED is cursor stability. SQL_TXN_REPEATABLE_READ is read stability. SQL_TXN_SERIALIZABLE is repeatable read.
SQL_DESCRIBE_PARAMETER	String	Y if parameters can be described; N if not.
SQL_DRIVER_NAME	String	File name of the driver used to access the data source.
SQL_DRIVER_ODBC_VER	String	The version number of ODBC that the driver supports. DB2 ODBC returns `2.1`.
SQL_GROUP_BY	16-bit integer	This indicates the degree of support for the GROUP BY clause by the data source: SQL_GB_NO_RELATION means there is no relationship between the columns in the GROUP BY and in the SELECT list. SQL_GB_NOT_SUPPORTED – GROUP BY is not supported. SQL_GB_GROUP_BY_EQUALS_SELECT – GROUP BY must include all nonaggregated columns in the select list. SQL_GB_GROUP_BY_CONTAINS_SELECT – GROUP BY clause must contain all nonaggregated columns in the SELECT list.
SQL_IDENTIFIER_CASE	16-bit integer	This indicates case sensitivity of object names (such as table-name). SQL_IC_UPPER – Identifier names are stored in uppercase in the system catalog. SQL_IC_LOWER – Identifier names are stored in lowercase in the system catalog. SQL_IC_SENSITIVE – Identifier names are case sensitive, and are stored in mixed case in the system catalog. SQL_IC_MIXED – Identifier names are not case sensitive, and are stored in mixed case in the system catalog. Note: Identifier names in IBM DBMSs are not case sensitive.
SQL_IDENTIFIER_QUOTE_CHAR	String	Character used as the delimiter of a quoted string.
SQL_KEYWORDS	String	A character string containing a comma-separated list of all data source-specific keywords. This is a list of all reserved keywords. Interoperable applications should not use these keywords in object names. This list does not contain keywords specific to ODBC or keywords used by both the data source and ODBC.
SQL_LIKE_ESCAPE_CLAUSE	String	A character string that indicates whether an escape character is supported for the metacharacters percent and underscore in a LIKE predicate.
SQL_MAX_CATALOG_NAME_LEN	16-bit integer	The maximum length of a catalog qualifier name; first part of a three-part table name (in bytes).
SQL_MAX_COLUMN_NAME_LEN	Short int	The maximum length of a column name.
SQL_MAX_COLUMNS_IN_GROUP_BY	Short int	The maximum number of columns in a GROUP BY clause.
SQL_MAX_COLUMNS_IN_INDEX	Short int	The maximum number of columns in an SQL index.
SQL_MAX_COLUMNS_IN_ORDER_BY	Short int	Maximum number of columns in an ORDER BY clause.
SQL_MAX_COLUMNS_IN_SELECT	Short int	The maximum number of columns in a SELECT statement.
SQL_MAX_COLUMNS_IN_TABLE	Short int	The maximum number of columns in an SQL table.
SQL_MAX_CURSOR_NAME_LEN	Short int	The maximum length of a cursor name.
SQL_MAX_OWNER_NAME_LEN	Short int	The maximum length of an owner name.
SQL_MAX_ROW_SIZE	32–bit unsigned integer	The maximum length in bytes that the data source supports in a single row of a base table. It is zero if there is no limit.
SQL_MAX_SCHEMA_NAME_LEN	Int	The maximum length of a schema name.
SQL_MAX_STATEMENT_LEN	32–bit unsigned integer	This indicates the maximum length of an SQL statement string in bytes, including the number of white spaces in the statement.
SQL_MAX_TABLE_NAME	Short int	The maximum length of a table name.
SQL_MAX_TABLES_IN_SELECT	Short int	The maximum number of tables in a SELECT statement.
SQL_MULTIPLE_ACTIVE_TXN	String	The character string Y indicates that active transactions on multiple connections are allowed. N indicates that only one connection at a time can have an active transaction.
SQL_NON_NULLABLE_COLUMNS	16-bit integer	This indicates whether non-nullable columns are supported: SQL_NNC_NON_NULL – columns can be defined as NOT NULL. SQL_NNC_NULL – columns cannot be defined as NOT NULL.
SQL_NUMERIC_FUNCTIONS	32-bit mask	The scalar numeric functions supported. The following bit masks are used to determine which numeric functions are supported: SQL_FN_NUM_ABS SQL_FN_NUM_ACOS SQL_FN_NUM_ASIN SQL_FN_NUM_ATAN SQL_FN_NUM_ATAN2 SQL_FN_NUM_CEILING SQL_FN_NUM_COS SQL_FN_NUM_COT SQL_FN_NUM_DEGREES SQL_FN_NUM_EXP SQL_FN_NUM_FLOOR SQL_FN_NUM_LOG SQL_FN_NUM_LOG10 SQL_FN_NUM_MOD SQL_FN_NUM_PI SQL_FN_NUM_POWER SQL_FN_NUM_RADIANS SQL_FN_NUM_RAND SQL_FN_NUM_ROUND SQL_FN_NUM_SIGN SQL_FN_NUM_SIN SQL_FN_NUM_SQRT SQL_FN_NUM_TAN SQL_FN_NUM_TRUNCATE
SQL_ODBC_API_CONFORMANCE	16-bit integer	The level of ODBC conformance: SQL_OAC_NONE SQL_OAC_LEVEL1 SQL_OAC_LEVEL2
SQL_ODBC_SQL_CONFORMANCE	16-bit integer	A value of: SQL_OSC_MINIMUM means minimum ODBC SQL grammar supported SQL_OSC_CORE means core ODBC SQL grammar supported SQL_OSC_EXTENDED means extended ODBC SQL grammar supported For the definition of the previous types of ODBC SQL grammar, see Microsoft ODBC 3.0 Software Development Kit and Programmer's Reference.
SQL_ORDER_BY_COLUMNS_IN_SELECT	String	Set to Y if columns in the ORDER BY clauses must be in the select list; otherwise set to N.
SQL_OUTER_JOINS	String	The character string: Y indicates that outer joins are supported, and DB2 ODBC supports the ODBC outer join request syntax. N indicated that outer joins are not supported.
SQL_OWNER_TERM or SQL_SCHEMA_TERM	String	The database vendor terminology for a schema (owner).
SQL_OWNER_USAGE or SQL_SCHEMA_USAGE	32-bit mask	This indicates the type of SQL statements that have schema (owners) associated with them when these statements are processed. Schema qualifiers (owners) are as follows: SQL_OU_DML_STATEMENTS is supported in all DML statements. SQL_OU_PROCEDURE_INVOCATION is supported in the procedure invocation statement. SQL_OU_TABLE_DEFINITION is supported in all table definition statements. SQL_OU_INDEX_DEFINITION is supported in all index definition statements. SQL_OU_PRIVILEGE_DEFINITION is supported in all privilege definition statements (that is, grant and revoke statements).
SQL_POSITIONED_STATEMENTS	32-bit mask	This indicates the degree of support for positioned UPDATE and positioned DELETE statements: SQL_PS_POSITIONED_DELETE SQL_PS_POSITIONED_UPDATE SQL_PS_SELECT_FOR_UPDATE SQL_PS_SELECT_FOR_UPDATE indicates whether the data source requires the FOR UPDATE clause to be specified on a <query expression> for a column to be updated with the cursor.
SQL_PROCEDURE_TERM	String	Data source name for a procedure.
SQL_PROCEDURES	String	Whether the current server supports SQL procedures. The value Y is returned if the connection supports SQL procedures.
SQL_QUALIFIER_LOCATION or SQL_CATALOG_LOCATION	16-bit integer	A 16-bit integer value indicated the position of the qualifier in a qualified table name. Zero indicates that qualified names are not supported.
SQL_QUALIFIER_NAME_SEPARATOR or SQL_CATALOG_NAME_SEPARATOR	String	The characters used as a separator between a catalog name and the qualified name element that follows it.
SQL_QUALIFIER_TERM or SQL_CATALOG_TERM	String	The database vendor terminology for a qualifier. This is the name that the vendor uses for the high-order part of a 3-part name. Because DB2 ODBC does not support 3-part names, a zero-length string is returned. For non-ODBC applications, the SQL_CATALOG_TERM symbolic name should be used instead of SQL_QUALIFIER_NAME.
SQL_QUALIFIER_USAGE or SQL_CATALOG_USAGE	32-bit mask	This is similar to SQL_OWNER_USAGE except that this is used for catalog.
SQL_QUOTED_IDENTIFIER_CASE	16-bit integer	SQL_IC_UPPER – Quoted identifiers in SQL are case insensitive and stored in uppercase in the system catalog. SQL_IC_LOWER – Quoted identifiers in SQL are case insensitive and are stored in lowercase in the system catalog. SQL_IC_SENSITIVE – Quoted identifiers (delimited identifiers) in SQL are case sensitive and are stored in mixed case in the system catalog. SQL_IC_MIXED – Quoted identifiers in SQL are case insensitive and are stored in mixed case in the system catalog. This should be contrasted with the SQL_IDENTIFIER_CASE fInfoType, which is used to determine how (unquoted) identifiers are stored in the system catalog.
SQL_SEARCH_PATTERN_ESCAPE	String	Used to specify what the driver supports as an escape character for catalog functions, such as `SQLTables()` and `SQLColumns()`.
SQL_SQL92_PREDICATES	32-bit mask	This indicates the predicates supported in a SELECT statement that SQL-92 defines. SQL_SP_BETWEEN SQL_SP_COMPARISON SQL_SP_EXISTS SQL_SP_IN SQL_SP_ISNOTNULL SQL_SP_ISNULL SQL_SP_LIKE SQL_SP_MATCH_FULL SQL_SP_MATCH_PARTIAL SQL_SP_MATCH_UNIQUE_FULL SQL_SP_MATCH_UNIQUE_PARTIAL SQL_SP_OVERLAPS SQL_SP_QUANTIFIED_COMPARISON SQL_SP_UNIQUE
SQL_SQL92_VALUE_EXPRESSIONS	32-bit mask	This indicates the value expressions supported that SQL-92 defines. SQL_SVE_CASE SQL_SVE_CAST SQL_SVE_COALESCE SQL_SVE_NULLIF
SQL_STRING_FUNCTIONS	32-bit bit mask	This indicates which string functions are supported. The following bit masks are used to determine which string functions are supported: SQL_FN_STR_ASCII SQL_FN_STR_CHAR SQL_FN_STR_CONCAT SQL_FN_STR_DIFFERENCE SQL_FN_STR_INSERT SQL_FN_STR_LCASE SQL_FN_STR_LEFT SQL_FN_STR_LENGTH SQL_FN_STR_LOCATE SQL_FN_STR_LOCATE_2 SQL_FN_STR_LTRIM SQL_FN_STR_REPEAT SQL_FN_STR_REPLACE SQL_FN_STR_RIGHT SQL_FN_STR_RTRIM SQL_FN_STR_SOUNDEX SQL_FN_STR_SPACE SQL_FN_STR_SUBSTRING SQL_FN_STR_UCASE If an application can call the LOCATE scalar function with the string1, string2, and start arguments, the SQL_FN_STR_LOCATE bit mask is returned. If an application can only call the LOCATE scalar function with the string1 and string2, the SQL_FN_STR_LOCATE_2 bit mask is returned. If the LOCATE scalar function is fully supported, both bit masks are returned.
SQL_TIMEDATE_FUNCTIONS	32-bit mask	This indicates which time and date functions are supported. The following bit masks are used to determine which date functions are supported: SQL_FN_TD_CURDATE SQL_FN_TD_CURTIME SQL_FN_TD_DAYNAME SQL_FN_TD_DAYOFMONTH SQL_FN_TD_DAYOFWEEK SQL_FN_TD_DAYOFYEAR SQL_FN_TD_HOUR SQL_FN_TD_JULIAN_DAY SQL_FN_TD_MINUTE SQL_FN_TD_MONTH SQL_FN_TD_MONTHNAME SQL_FN_TD_NOW SQL_FN_TD_QUARTER SQL_FN_TD_SECOND SQL_FN_TD_SECONDS_SINCE_MIDNIGHT SQL_FN_TD_TIMESTAMPADD SQL_FN_TD_TIMESTAMPDIFF SQL_FN_TD_WEEK SQL_FN_TD_YEAR
SQL_TXN_CAPABLE	Short int	This indicates whether transactions can contain DDL or DML or both: SQL_TC_NONE – Transactions are not supported. SQL_TC_DML – Transactions can only contain DML statements (SELECT, INSERT, UPDATE, DELETE, and so on). DDL statements (CREATE TABLE, DROP INDEX, and so on) encountered in a transaction cause an error. SQL_TC_DDL_COMMIT – Transactions can only contain DML statements. DDL statements encountered in a transaction cause the transaction to be committed. SQL_TC_DDL_IGNORE – Transactions can only contain DML statements. DDL statements encountered in a transaction are ignored. SQL_TC_ALL – Transactions can contain DDL and DML statements in any order.
SQL_USER_NAME	String	User name used in a particular database.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLGetInfo SQLSTATEs
SQLSTATE	Description	Explanation
01004	Data truncated	The requested information is returned as a null-terminated string and its length exceeded the length of the application buffer as specified in cbInfoValueMax. The argument pcbInfoValue contains the actual (not truncated) length of the requested information.
08003	Connection not open	The type of information requested in fInfoType requires an open connection. Only SQL_ODBC_VER does not require an open connection.
40003 *	Statement completion unknown	The communication link between the CLI and the data source fails before the function completes processing.
58004	System error	Unrecoverable system error.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument value that is not valid	The argument rgbInfoValue is a null pointer An fInfoType that is not valid is specified.
HY013 *	Memory management problem	The driver is unable to access memory required to support the processing or completion of the function.