Call Level Interface Guide and Reference

SQLProcedureColumns - Get Input/Output Parameter Information for A Procedure

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

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 generated by a query.

Syntax

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

Function Arguments

Table 140. SQLProcedureColumns Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle.
SQLCHAR * CatalogName input Catalog qualifier of a 3 part procedure name.
This must be a NULL pointer or a zero length string.
SQLSMALLINT NameLength1 input Length of CatalogName. This must be set to 0.
SQLCHAR * SchemaName input Buffer that may contain a pattern-value to qualify the result set by schema name.
For DB2 for MVS/ESA V 4.1, all the stored procedures are in one schema; the only acceptable value for the SchemaName argument is a null pointer. For DB2 Universal Database, SchemaName can contain a valid pattern value. For more information about valid search patterns, refer to Querying System Catalog Information.
SQLSMALLINT NameLength2 input Length of SchemaName
SQLCHAR * ProcName input Buffer that may contain a pattern-value to qualify the result set by procedure name.
SQLSMALLINT NameLength3 input Length of ProcName
SQLCHAR * ColumnName input Buffer that may contain a pattern-value to qualify the result set by parameter name. This argument is to be used to further qualify the result set already restricted by specifying a non-empty value for ProcName and/or SchemaName.
SQLSMALLINT NameLength4 input Length of ColumnName

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	input	Statement handle.
SQLCHAR *	CatalogName	input	Catalog qualifier of a 3 part procedure name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	input	Length of `CatalogName`. This must be set to 0.
SQLCHAR *	SchemaName	input	Buffer that may contain a `pattern-value` to qualify the result set by schema name. For DB2 for MVS/ESA V 4.1, all the stored procedures are in one schema; the only acceptable value for the `SchemaName` argument is a null pointer. For DB2 Universal Database, `SchemaName` can contain a valid pattern value. For more information about valid search patterns, refer to Querying System Catalog Information.
SQLSMALLINT	NameLength2	input	Length of `SchemaName`
SQLCHAR *	ProcName	input	Buffer that may contain a `pattern-value` to qualify the result set by procedure name.
SQLSMALLINT	NameLength3	input	Length of `ProcName`
SQLCHAR *	ColumnName	input	Buffer that may contain a `pattern-value` to qualify the result set by parameter name. This argument is to be used to further qualify the result set already restricted by specifying a non-empty value for ProcName and/or SchemaName.
SQLSMALLINT	NameLength4	input	Length of `ColumnName`

Usage

DB2 Universal Database version 5 introduced two system catalog views used to store information about all stored procedures on the server (SYSCAT.PROCEDURES and SYSCAT.PROCPARMS). See Appendix G, Catalog Views for Stored Procedures for information on these views.

Before version 5, DB2 CLI used the pseudo catalog table for stored procedure registration. By default, DB2 CLI will use the new system catalog views. If the application expects to use the pseudo catalog table then the CLI/ODBC configuration keyword PATCH1 should be set to 262144. See Replacement of the Pseudo Catalog Table for Stored Procedures for more information.

If the stored procedure is at a DB2 for MVS/ESA V 4.1 server or later, the name of the stored procedures must be registered in the server's SYSIBM.SYSPROCEDURES catalog table.

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

DB2 CLI will return information on the input, input/output, and output parameters associated with the stored procedure, but cannot return information on the descriptor information for any result sets returned.

SQLProcedureColumns() returns the information in a result set, ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, and COLUMN_TYPE. Columns Returned By SQLProcedureColumns lists the columns in the result set. Applications should be aware that columns beyond the last column may be defined in future releases.

Since calls to SQLProcedureColumns() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results saved rather than repeating calls.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

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

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change. There were changes to these columns between version 2 and version 5. See Changes to SQLProcedureColumns() Return Values for more information if you are running a version 2 DB2 CLI application that uses SQLProcedureColumns(), against a version 5 or later server.

Columns Returned By SQLProcedureColumns

Column 1 PROCEDURE_CAT (VARCHAR(128))

This is always null.

Column 2 PROCEDURE_SCHEM (VARCHAR(128))

The name of the schema containing PROCEDURE_NAME. (This is also NULL for DB2 for MVS/ESA V 4.1 SQLProcedureColumns() result sets.)

Column 3 PROCEDURE_NAME (VARCHAR(128))

Name of the procedure.

Column 4 COLUMN_NAME (VARCHAR(128))

Name of the parameter.

Column 5 COLUMN_TYPE (SMALLINT not NULL)

Identifies the type information 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 6 DATA_TYPE (SMALLINT not NULL)

SQL data type.

Column 7 TYPE_NAME (VARCHAR(128) not NULL)

Character string representing the name of the data type corresponding to DATA_TYPE.

Column 8 COLUMN_SIZE (INTEGER)

If the DATA_TYPE column value denotes a character or binary string, then this column contains the maximum length in bytes; if it is a graphic (DBCS) string, this is the number of double byte characters for the parameter.

For date, time, timestamp data types, this is the total number of bytes required to display the value when converted to character.

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

See also Table 194.

Column 9 BUFFER_LENGTH (INTEGER)

The maximum number of bytes for the associated C buffer to store data from this parameter if SQL_C_DEFAULT were 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.

See Table 196.

Column 10 DECIMAL_DIGITS (SMALLINT)

The scale of the parameter. NULL is returned for data types where scale is not applicable.

See Table 195.

Column 11 NUM_PREC_RADIX (SMALLINT)

Either 10 or 2 or NULL. If DATA_TYPE is an approximate numeric data type, this column contains the value 2, then the COLUMN_SIZE column contains the number of bits 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 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 12 NULLABLE (SMALLINT not NULL)

SQL_NO_NULLS if the parameter does not accept NULL values.

SQL_NULLABLE if the parameter accepts NULL values.

Column 13 REMARKS (VARCHAR(254))

May contain descriptive information about the parameter.

Column 14 COLUMN_DEF (VARCHAR)

The default value of the column.

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

The value of COLUMN_DEF can be used in generating a new column definition, except when it contains the value TRUNCATED.

Column 15 SQL_DATA_TYPE (SMALLINT not NULL)

The value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the DATA_TYPE column except for datetime data types (DB2 CLI does not support interval data types).

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

Column 16 SQL_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 DB2 CLI does not support).

Column 17 CHAR_OCTET_LENGTH (INTEGER)

The maximum length in bytes of a character data type column. For all other data types, this column returns a NULL.

Column 18 ORDINAL_POSITION (INTEGER NOT NULL)

Contains the ordinal position of the parameter given by COLUMN_NAME in this result set. This is the ordinal position of the argument to be provided on the CALL statement. The leftmost argument has an ordinal position of 1.

Column 19 IS_NULLABLE (Varchar)

"NO" if the column does not include NULLs.
"YES" if the column can include NULLs.
zero-length string if nullability is unknown.

ISO rules are followed to determine nullability.

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

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

Note: The column names used by DB2 CLI follow the X/Open CLI CAE specification style. The column types, contents and order are identical to those defined for the SQLProcedureColumns() result set in ODBC.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 141. SQLProcedureColumns SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was 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 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.
HY008 Operation canceled
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
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.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid String or Buffer Length The value of one of the name length arguments was less than 0, but not equal SQL_NTS.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for procedure name.
The connected server does not support schema as a qualifier for procedure name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was 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 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.
HY008	Operation canceled	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
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.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid String or Buffer Length	The value of one of the name length arguments was less than 0, but not equal SQL_NTS.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for procedure name. The connected server does not support `schema` as a qualifier for procedure name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

SQLProcedureColumns() does not return information about the attributes of result sets that may 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, SQLProcedureColumns() will return an empty result set.

CLI Sample stpcli.c

(The complete sample stpcli.c is also available here .)

 
/* From the CLI sample STPCLI.C */
/* ... */
 
    rc = SQLProcedureColumns(hstmt, NULL, 0,
                             stpSchemaPattern, SQL_NTS, 
                             stpNamePattern, SQL_NTS,
                             colNamePattern, SQL_NTS);

References

SQLProcedures - Get List of Procedure Names

[ Top of Page | Previous Page | Next Page ]