SQLRETURN SQLProcedureColumns(
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 */
Data type | Argument | Use | Description |
---|---|---|---|
SQLHSTMT | StatementHandle | Input | The 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 supports 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® for z/OS®, the stored procedures are in one schema; the only acceptable value for the SchemaName argument is a null pointer. If a value is specified, an empty result set and SQL_SUCCESS are returned. For DB2 for Linux, UNIX, and Windows, 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 the 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 the 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. |
The SQLProcedureColumns() function 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.
In many cases, calls to the SQLProcedureColumns() function map to a complex and thus expensive query against the system catalog, so you should use the calls sparingly, and save the results rather than repeating calls.
Call SQLGetInfo() 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.
If the stored procedure is at a DB2 for z/OS server, the name of the stored procedure must be registered in the SYSIBM.SYSPROCEDURES catalog table of the server. For V8 and later servers, the stored procedures must be registered in the SYSIBM.SYSROUTINES and SYSIBM.SYSPARAMS catalog tables of the server.
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.
If the DATA_TYPE column value denotes a character or binary string, this column contains the maximum length in SQLCHAR or SQLWCHAR elements. If DATA_TYPE column value is a graphic (DBCS) string, the COLUMN_SIZE is the number of double byte SQLCHAR or SQLWCHAR elements for the parameter.
For date, time, and timestamp data types, this 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, COLUMN_SIZE value 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.
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.
See the table of data type scale.
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.
SQL_NULLABLE if the parameter accepts NULL values.
If NULL was 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, not enclosed in 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 COLUMN_DEF contains the value TRUNCATED.
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).
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.)
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. 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 in 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(). |
The SQLProcedureColumns() function 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, the SQLProcedureColumns() function returns an empty result set.
/* get input/output parameter information for a procedure */
sqlrc = SQLProcedureColumns(hstmt,
NULL,
0, /* catalog name not used */
(unsigned char *)colSchemaNamePattern,
SQL_NTS, /* schema name not currently used */
(unsigned char *)procname,
SQL_NTS,
colNamePattern,
SQL_NTS); /* all columns */