SQLProcedureColumns - Get input/output parameter information for a procedure

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

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

Syntax

SQLRETURN SQLProcedureColumns(SQLHSTMT          StatementHandle,
                              SQLCHAR           *CatalogName,
                              SQLSMALLINT       NameLength1,
                              SQLCHAR           *SchemaName,
                              SQLSMALLINT       NameLength2,
                              SQLCHAR           *ProcName,
                              SQLSMALLINT       NameLength3,
                              SQLCHAR           *ColumnName,
                              SQLSMALLINT       NameLength4);

Function arguments

Table 1. 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 might contain a pattern-value to qualify the result set by schema name. For DB2 for z/OS® and OS/390® 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, SchemaName can contain a valid pattern value.
SQLSMALLINT	NameLength2	Input	Length of SchemaName.
SQLCHAR *	ProcName	Input	Buffer that might contain a pattern-value to qualify the result set by procedure name.
SQLSMALLINT	NameLength3	Input	Length of ProcName.
SQLCHAR *	ColumnName	Input	Buffer that might 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 or SchemaName.
SQLSMALLINT	NameLength4	Input	Length of ColumnName.

Usage

DB2 for i CLI returns information about the input, input and output, and output parameters associated with the stored procedure, but cannot return information about the descriptor for any result sets returned.

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

Because 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.

Special support was added to handle a keyword "*LIBL" in the SchemaName argument. Specifying this keyword will tell SQLStatistics to use the schema's on the library list to qualify the search criteria for retrieving index information for tables. The highest library on the library list hierarchy that matches the search criteria will be used. Also, system naming must be in effect for this support to be honored. The following behavior will occur when different connections are used:

When SQL Server Mode is used, the SQLProcedureColumns() API will use the library list of the initial thread within the associated QSQSRVR job when processing the '*LIBL' request.
When SQL Server Mode is not used, the SQLProcedureColumns() API will use the library list of the current thread when processing the '*LIBL' request.

Table 2. Columns returned by SQLProcedureColumns
Column number/name	Data type	Description
1 PROCEDURE_CAT	VARCHAR(128)	The current server.
2 PROCEDURE_SCHEM	VARCHAR(128)	The name of the schema containing PROCEDURE_NAME.
3 PROCEDURE_NAME	VARCHAR(128)	Name of the procedure.
4 COLUMN_NAME	VARCHAR(128)	Name of the parameter.
5 COLUMN_TYPE	SMALLINT not NULL	This 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.
6 DATA_TYPE	SMALLINT not NULL	SQL data type.
7 TYPE_NAME	VARCHAR(128) not NULL	Character string representing the name of the data type corresponding to DATA_TYPE.
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.
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.
10 DECIMAL_DIGITS	SMALLINT	The scale of the parameter. NULL is returned for data types where scale is not applicable.
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 Database Management System (DBMS) can return a NUM_PREC_RADIX of either 10 or 2. NULL is returned for data types where radix is not applicable.
12 NULLABLE	SMALLINT not NULL	'SQL_NO_NULLS' if the parameter does not accept NULL values. 'SQL_NULLABLE' if the parameter accepts NULL values.
13 REMARKS	VARCHAR(254)	Might contain descriptive information about the parameter.
14 COLUMN_DEF	VARCHAR	The default value of the column. If NULL is 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 is 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.
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 for i 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).
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 for i CLI does not support).
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.
18 ORDINAL_POSITION	INTEGER not NULL	This 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.
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.)

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Error conditions

Table 3. SQLProcedureColumns SQLSTATEs
SQLSTATE	Description	Explanation
24000	Cursor state that is not valid	A cursor is already opened on the statement handle.
40003 08S01	Communication link failure	The communication link between the application and data source fails before the function is completed.
42601	PARMLIST syntax error	The PARMLIST value in the stored procedures catalog table contains a syntax error.
HY001	Memory allocation failure	DB2 for i CLI is unable to allocate memory required to support the processing or completion of the function.
HY008	Operation canceled
HY010	Function sequence error
HY014	No more handles	DB2 for i CLI is unable to allocate a handle due to internal resources.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HY090	String or buffer length that is not valid	The value of one of the name length arguments is less than 0, but not equal SQL_NTS.
HYC00	Driver not capable	DB2 for i CLI does not support catalog as a qualifier for procedure name. The connected data source does not support schema as a qualifier for a procedure name.
HYT00	Timeout expired

Restrictions

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

Example

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

/* From CLI sample proccols.c */
/* ... */
 
    printf("Enter Procedure Schema Name Search Pattern:\n");
    gets((char *)proc_schem.s);
 
    printf("Enter Procedure Name Search Pattern:\n");
    gets((char *)proc_name.s);
 
    rc = SQLProcedureColumns(hstmt, NULL, 0, proc_schem.s, SQL_NTS, 
                             proc_name.s, SQL_NTS, (SQLCHAR *)"%", SQL_NTS);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) proc_schem.s, 129,
                    &proc_schem.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, (SQLPOINTER) proc_name.s, 129,
                    &proc_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) column_name.s, 129,
                    &column_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 5, SQL_C_SHORT, (SQLPOINTER) &arg_type,
                    0, &arg_type_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) type_name.s, 129,
                    &type_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 8, SQL_C_LONG, (SQLPOINTER) & length,
                    0, &length_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 10, SQL_C_SHORT, (SQLPOINTER) &scale,
                    0, &scale_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 13, SQL_C_CHAR, (SQLPOINTER) remarks.s, 255,
                    &remarks.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        sprintf((char *)cur_name, "%s.%s", proc_schem.s, proc_name.s);
        if (strcmp((char *)cur_name, (char *)pre_name) != 0) {
            printf("\n%s\n", cur_name);
        }
        strcpy((char *)pre_name, (char *)cur_name);
        printf("   %s", column_name.s);
        switch (arg_type) 
        { case SQL_PARAM_INPUT : printf(", Input"); break;
          case SQL_PARAM_OUTPUT : printf(", Output"); break;
          case SQL_PARAM_INPUT_OUTPUT : printf(", Input_Output"); break;
        }
        printf(", %s", type_name.s);
        printf(" (%ld", length);
        if (scale_ind != SQL_NULL_DATA) {
            printf(", %d)\n", scale);
        } else {
            printf(")\n");
        }
        if (remarks.ind > 0 ) {
            printf("(remarks), %s)\n", remarks.s);
        }
    }                           /* endwhile */

References

SQLProcedures - Get list of procedure names