SQLExtendedProcedureColumns function (CLI) - Get input/output parameter information for a procedure

Returns a list of input and output parameters associated with a stored procedure. The information is returned in an SQL result set, which you can retrieve by using the same functions that you use to process a result set generated by a query.

Specification:

  • CLI 9.7
Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLExtendedProcedureColumnsW(). For information about ANSI to Unicode function mappings, see Unicode functions (CLI).

Modules are an extension to the concept of schemas. You can use applications that connect to Db2® version 9.7 or later data servers to create modules inside the schema and procedures inside the modules. The fully qualified name of a procedure in a module is <SCHEMA NAME>.<MODULE NAME>.<PROCEDURE NAME>. The SQLExtendedProcedures() and SQLExtendedProcedureColumns() functions provide information about modules. These functions are not part of the current ODBC specification. See Modules in SQL Procedural Languages: Application Enablement and Support for more information.

Syntax

SQLRETURN SQLExtendedProcedureColumns(
             SQLHSTMT          StatementHandle,   /* hstmt */
             SQLCHAR           *CatalogName,      /* szProcCatalog */
             SQLSMALLINT       NameLength1,       /* cbProcCatalog */
             SQLCHAR           *SchemaName,       /* szProcSchema */
             SQLSMALLINT       NameLength2,       /* cbProcSchema */
             SQLCHAR           *ProcName,         /* szProcName */
             SQLSMALLINT       NameLength3,       /* cbProcName */
             SQLCHAR           *ColumnName,       /* szColumnName */
             SQLSMALLINT       NameLength4),      /* cbColumnName */
             SQLCHAR           *ProcModule,       /* szProcModule */
             SQLSMALLINT       NameLength5;       /* cbProcModule */

Function arguments

Table 1. SQLExtendedProcedureColumns arguments
Data type Argument Use Description
SQLHSTMT StatementHandle Input A statement handle.
SQLCHAR * CatalogName Input A catalog qualifier of a 3-part table name. If the target DBMS does not support 3-part naming, and CatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that support 3-part naming.
SQLSMALLINT NameLength1 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR * SchemaName Input A buffer that can contain a pattern value to qualify the result set by schema name.

For Db2, SchemaName can contain a valid pattern value. For more information about valid search patterns, see the catalog functions input arguments.
SQLSMALLINT NameLength2 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR * ProcName Input A buffer that can contain a pattern value to qualify the result set by procedure name.
SQLSMALLINT NameLength3 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ProcName, or SQL_NTS if ProcName is null-terminated.
SQLCHAR * ColumnName Input A buffer that can contain a pattern value to qualify the result set by parameter name. Use this argument to further qualify the result set that is already restricted by specifying a non-empty value for ProcName, SchemaName, or both.
SQLSMALLINT NameLength4 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ColumnName, or SQL_NTS if ColumnName is null-terminated.
SQLCHAR * ProcModule Input A buffer that can contain a pattern value to qualify the result set by parameter name. Use this argument to further qualify the result set that is already restricted by specifying a non-empty value for ProcName, SchemaName, or ColumnName.
SQLSMALLINT NameLength5 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ProcModule, or SQL_NTS if ProcModule is null-terminated.

Usage

The SQLExtendedProcedureColumns() function returns the information in a result set, which is ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, COLUMN_TYPE and PROCEDURE_MODULE. Columns returned by SQLExtendedProcedureColumns lists the columns in the result set. Columns that are beyond the last column might be defined in future releases.

In many cases, calls to the SQLExtendedProcedureColumns() function map to a complex and thus expensive query against the system catalog, therefore you should use these calls sparingly, and save the results rather than repeating calls.

Call the SQLGetInfo() function with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine the actual lengths of the TABLE_CAT, TABLE_SCHEM, and COLUMN_NAME columns that are supported by the connected DBMS.

If the SQL_ATTR_LONGDATA_COMPAT connection attribute is set, LOB column types are reported as LONG VARCHAR, LONG VARBINARY or LONG VARGRAPHIC types.

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

For versions of other Db2 servers that do not provide facilities for a stored procedure catalog, an empty result set is returned.

CLI returns information about the input, input/output, and output parameters that are associated with the stored procedure, but cannot return descriptor information for any result sets that the stored procedure might return.

You can specify *ALL as a value in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. Alternatively, you can set the SchemaFilter IBM® Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL.

Columns returned by SQLExtendedProcedureColumns
Column 1PROCEDURE_CAT (VARCHAR(128))
The name of the procedure catalog. The value is NULL if this procedure does not have catalogs.
Column 2PROCEDURE_SCHEM (VARCHAR(128))
The name of the schema containing PROCEDURE_NAME.
Column 3PROCEDURE_NAME (VARCHAR(128))
The name of the procedure.
Column 4COLUMN_NAME (VARCHAR(128))
The name of the parameter.
Column 5COLUMN_TYPE (SMALLINT not NULL)
Identifies the type of information that is associated with this row. The values can be:
  • SQL_PARAM_TYPE_UNKNOWN : The parameter type is unknown.
    Note: This is not returned.
  • SQL_PARAM_INPUT: This parameter is an input parameter.
  • SQL_PARAM_INPUT_OUTPUT: This parameter is an input / output parameter.
  • SQL_PARAM_OUTPUT: This parameter is an output parameter.
  • SQL_RETURN_VALUE: The procedure column is the return value of the procedure.
    Note: This is not returned.
  • SQL_RESULT_COL: This parameter is actually a column in the result set.
    Note: This is not returned.
Column 6DATA_TYPE (SMALLINT not NULL)
An SQL data type.
Column 7TYPE_NAME (VARCHAR(128) not NULL)
A character string that represents the name of the data type that corresponds to DATA_TYPE.
Column 8COLUMN_SIZE (INTEGER)
For XML arguments in SQL routines, zero is returned (as XML arguments have no length). For cataloged external routines, however, XML parameters are declared as XML AS CLOB(n), in which case COLUMN_SIZE is the cataloged length, n.

If the DATA_TYPE column value denotes a character or binary string, this column contains the maximum length in SQLCHAR or SQLWCHAR elements. If the DATA_TYPE column value is a graphic (DBCS) string, COLUMN_SIZE is the number of double byte SQLCHAR or SQLWCHAR elements for the parameter.

For date, time, and timestamp data types, COLUMN_SIZE is the total number of SQLCHAR or SQLWCHAR elements that are required to display the value when converted to character data type.

For the columns that are defined with the CODEUNITS32 unit, the number of code units for the column is returned.

For numeric data types, this is either the total number of digits, or the total number of bits that are allowed in the column, depending on the value in the NUM_PREC_RADIX column in the result set.

See the table of data type precision.

Column 9BUFFER_LENGTH (INTEGER)
The maximum number of bytes for the associated C buffer to store data from this parameter if SQL_C_DEFAULT is specified on the SQLBindCol(), SQLGetData() and SQLBindParameter() calls. This length excludes any null-terminator. For exact numeric data types, the length accounts for the decimal and the sign.

For XML arguments in SQL routines, zero is returned (as XML arguments have no length). For cataloged external routines, however, XML parameters are declared as XML AS CLOB(n), in which case BUFFER_LENGTH is the cataloged length, n.

See the table of data type length.

Column 10DECIMAL_DIGITS (SMALLINT)
The scale of the parameter. NULL is returned for data types where scale is not applicable.

See the table of data type scale.

Column 11NUM_PREC_RADIX (SMALLINT)
Either 10, 2, or NULL. If DATA_TYPE is an approximate numeric data type, this column contains the value 2, and the COLUMN_SIZE column contains the number of bits that are allowed in the parameter.

If DATA_TYPE is an exact numeric data type, this column contains the value 10, and the COLUMN_SIZE and DECIMAL_DIGITS columns contain the number of decimal digits that are allowed for the parameter.

For numeric data types, the DBMS can return a NUM_PREC_RADIX of either 10 or 2.

NULL is returned for data types where radix is not applicable.

Column 12NULLABLE (SMALLINT not NULL)
SQL_NO_NULLS if the parameter does not accept NULL values.

SQL_NULLABLE if the parameter accepts NULL values.

Column 13REMARKS (VARCHAR(254))
Might contain descriptive information about the parameter.
Column 14COLUMN_DEF (VARCHAR)
The default value of the column.

If NULL is specified as the default value, this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, this column contains TRUNCATED, with no enclosing single quotation marks. If no default value is specified, this column is NULL.

You can use the value of COLUMN_DEF to generate a new column definition, except when it contains the value TRUNCATED.

Column 15SQL_DATA_TYPE (SMALLINT not NULL)
The value of the SQL data type as it is displayed in the SQL_DESC_TYPE field of the descriptor. This column is the same as the DATA_TYPE column except for datetime data types (CLI does not support interval data types).

For datetime data types, the SQL_DATA_TYPE field in the result set is SQL_DATETIME, and the SQL_DATETIME_SUB field returns the subcode for the specific datetime data type (SQL_CODE_DATE, SQL_CODE_TIME or SQL_CODE_TIMESTAMP).

Column 16SQL_DATETIME_SUB (SMALLINT)
The subtype code for datetime data types. For all other data types this column returns a NULL (including interval data types, which CLI does not support).
Column 17CHAR_OCTET_LENGTH (INTEGER)
The maximum length in bytes of a character data type column. For the columns that are defined with the CODEUNITS32 unit, the number of bytes for the column is returned. For all other data types, this column returns a NULL.
Column 18ORDINAL_POSITION (INTEGER NOT NULL)
Contains the ordinal position of the parameter that is given by COLUMN_NAME in this result set. The ORDINAL_POSITION is the ordinal position of the argument to be provided on the CALL statement. The leftmost argument has an ordinal position of 1.
Column 19IS_NULLABLE (Varchar)
  • NO if the column does not include NULLs.
  • YES if the column can include NULLs.
  • Zero-length string if the nullability is unknown.
ISO rules are followed to determine nullability.

An ISO SQL-compliant DBMS cannot return an empty string.

The value that is returned for this column is different than the value that is returned for the NULLABLE column. See the description of the NULLABLE column.

Column 20SPECIFIC_NAME (VARCHAR(128))
The unique specific name of PROCEDURE_NAME.
Column 21PROCEDURE_MODULE (VARCHAR(128))
The name of the module containing PROCEDURE_NAME within the schema.
Note:
  • The column names that are used by CLI follow the X/Open CLI CAE specification style. The column types, contents, and order are identical to those defined for the SQLExtendedProcedureColumns() result set in ODBC.
  • If two modules contain procedures that share the same name, the SQLExtendedProcedureColumns() function returns details about both procedures.

Return codes

  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_STILL_EXECUTING
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

Table 2. SQLExtendedProcedureColumns SQLSTATEs
SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor is already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
42601 PARMLIST syntax error. The PARMLIST value that is in the stored procedures catalog table contains a syntax error.
HY001 Memory allocation failure. Db2 CLI is unable to allocate memory required to support execution or completion of the function. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008 Operation was Canceled. Asynchronous processing was enabled for StatementHandle. The function was called, and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
HY010 Function sequence error.

The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.

An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.

The function was called before a statement was prepared on the statement handle.
HY014 No more handles. Db2 CLI was unable to allocate a handle due to resource limitations.
HY090 Invalid string or buffer length. The value of one of the name-length arguments was less than 0, but not equal to SQL_NTS.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. You can set the timeout period by using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

Restrictions

SQLExtendedProcedureColumns() does not return information about the attributes of result sets that might be returned from stored procedures.

If an application is connected to a Db2 server that does not provide support for a stored procedure catalog, or does not provide support for stored procedures, SQLExtendedProcedureColumns() returns an empty result set.

SQLExtendedProcedureColumns() is currently only supported with Db2 Version 9.7 or later.

Example

The following code example gets parameter information for a procedure.
/* get input/output parameter information for a procedure including 
extended information */
cliRC = SQLExtendedProcedureColumns(hstmt, 
	"CatalogName", 
	SQL_NTS, 
	"SchemaName", 
	SQL_NTS, 
	"ProcName",
	SQL_NTS,
	"ColumnName",
	SQL_NTS,
	"ModuleName",
	SQL_NTS );