`SQLProcedureColumns()` - Get procedure input/output parameter information

SQLProcedureColumns() returns a list of input and output parameters that are associated with a procedure. The information is returned in an SQL result set. This result set is retrieved by the same functions that process a result set that is generated by a query.

ODBC specifications for `SQLProcedureColumns()`

Table 1. `SQLProcedureColumns()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	No	No

Syntax

SQLRETURN   SQLProcedureColumns (
                              SQLHSTMT          hstmt,
                              SQLCHAR      FAR  *szProcCatalog,
                              SQLSMALLINT       cbProcCatalog,
                              SQLCHAR      FAR  *szProcSchema,
                              SQLSMALLINT       cbProcSchema,
                              SQLCHAR      FAR  *szProcName,
                              SQLSMALLINT       cbProcName,
                              SQLCHAR      FAR  *szColumnName,
                              SQLSMALLINT       cbColumnName);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLProcedureColumns()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Statement handle.
SQLCHAR *	szProcCatalog	input	Catalog qualifier of a three-part procedure name. This must be a null pointer or a zero length string.
SQLSMALLINT	cbProcCatalog	input	The length, in bytes, of szProcCatalog. This must be set to 0.
SQLCHAR *	szProcSchema	input	Buffer that can contain a pattern-value to qualify the result set by schema name. If you do not want to qualify the result set by schema name, use a null pointer or a zero length string for this argument.
SQLSMALLINT	cbProcSchema	input	The length, in bytes, of szProcSchema.
SQLCHAR *	szProcName	input	Buffer that can contain a pattern-value to qualify the result set by procedure name. If you do not want to qualify the result set by procedure name, use a null pointer or a zero length string for this argument.
SQLSMALLINT	cbProcName	input	The length, in bytes, of szProcName.
SQLCHAR *	szColumnName	input	Buffer that can 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 szProcName and/or szProcSchema. If you do not want to qualify the result set by parameter name, use a null pointer or a zero length string for this argument.
SQLSMALLINT	cbColumnName	input	The length, in bytes, of szColumnName.

Usage

Registered stored procedures are defined in the SYSIBM.SYSROUTINES catalog table. For servers that do not provide facilities for a stored procedure catalog, this function returns an empty result set.

Db2 ODBC returns 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. Table 3 lists the columns in the result set.

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.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 bytes to be consistent with ANSI/ISO SQL standard of 1992 limits. Because Db2 names are less than 128 bytes, the application can choose to always set aside 128 bytes (plus the nul-terminator) for the output buffer. Alternatively, you can call SQLGetInfo() with the InfoType argument set to each of the following values:

SQL_MAX_CATALOG_NAME_LEN, to determine the length of TABLE_CAT columns that the connected database management system supports
SQL_MAX_SCHEMA_NAME_LEN, to determine the length of TABLE_SCHEM columns that the connected database management system supports
SQL_MAX_TABLE_NAME_LEN, to determine the length of TABLE_NAME columns that the connected database management system supports
SQL_MAX_COLUMN_NAME_LEN, to determine the length of COLUMN_NAME columns that the connected database management system supports

Applications should be aware that columns beyond the last column might be defined in future releases. Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns does not change. The following table lists these columns.

Table 3. Columns returned by `SQLProcedureColumns()`
Column number	Column name	Data type	Description
1	PROCEDURE_CAT	VARCHAR(128)	This field is always null.
2	PROCEDURE_SCHEM	VARCHAR(128)	The name of the schema containing PROCEDURE_NAME. (This is also NULL for Db2 for z/OS® `SQLProcedureColumns()` result sets.)
3	PROCEDURE_NAME	VARCHAR(128)	Name of the procedure.
4	COLUMN_NAME	VARCHAR(128)	Name of the parameter.
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.¹ 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.¹ SQL_RESULT_COL: this parameter is actually a column in the result set.¹ Requirement: For SQL_PARAM_OUTPUT and SQL_RETURN_VALUE support, you must have ODBC 2.0 or higher.
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. For the XML data type in native SQL procedures, zero is returned (the XML data type has no length).
9	BUFFER_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 nul-terminator. For exact numeric data types, the length accounts for the decimal and the sign. For the XML data type in native SQL procedures, zero is returned (the XML data type has no length).
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 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(254)	The default value for the column. If the default value is: A numeric literal, this column contains the character representation of the numeric literal with no enclosing single quotes. A character string, this column is that string enclosed in single quotes. A pseudo-literal, such as for DATE, TIME, and TIMESTAMP columns, this column contains the keyword of the pseudo-literal (for example, CURRENT DATE) with no enclosing single quotes. NULL, this column returns the word NULL, with no enclosing single quotes. If the default value cannot be represented without truncation, this column contains TRUNCATED with no enclosing single quotes. If no default value is specified, this column is NULL.
15	SQL_DATA_TYPE	SMALLINT NOT NULL	The SQL data type. This columns is the same as the DATA_TYPE column. 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: SQL_CODE_DATE SQL_CODE_TIME SQL_CODE_TIMESTAMP For all other data types, this column returns a null value.
17	CHAR_OCTET_LENGTH	INTEGER	The maximum length in bytes of a character data type column. For the XML data type in native SQL procedures, zero is returned (the XML data type has no length). For all other data types, this column returns null value.
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 provided on the CALL statement. The leftmost argument has an ordinal position of 1.
19	IS_NULLABLE	VARCHAR(128)	One of the following: "NO", if the column does not include null values "YES", if the column can include null values Zero-length string if nullability is unknown. The value returned for this column is different than the value returned for the NULLABLE column. (See the description of the NULLABLE column.)
Note: These values are not returned.

The column names used by Db2 ODBC 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

After you call SQLProcedureColumns(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLProcedureColumns()` SQLSTATEs
SQLSTATE	Description	Explanation
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
24000	Invalid cursor state.	A cursor is opened on the statement handle.
42601	PARMLIST syntax error.	The PARMLIST value in the stored procedures catalog table contains a syntax error.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090	Invalid string or buffer length	The value of one of the name length arguments is less than 0, but not equal SQL_NTS.
HYC00	Driver not capable.	This SQLSTATE is returned for one or more of the following reasons: Db2 ODBC does not support catalog as a qualifier for procedure name. The connected server does not support schema as a qualifier for procedure name.

Restrictions

SQLProcedureColumns() does not return information about the attributes of result sets that stored procedures can return.

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

Example

The following example shows an application that retrieves input, input/output, and output parameters associated with a procedure.

Figure 1. An application that retrieves parameters associated with a procedure

 /******************************************************************/
 /*  Invoke SQLProcedureColumns and enumerate all rows retrieved.  */
 /******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include "sqlcli1.h"
int main( )
{
   SQLHENV         hEnv    = SQL_NULL_HENV;
   SQLHDBC         hDbc    = SQL_NULL_HDBC;
   SQLHSTMT        hStmt   = SQL_NULL_HSTMT;
   SQLRETURN       rc      = SQL_SUCCESS;
   SQLINTEGER      RETCODE = 0;
   char            *pDSN = "STLEC1";
   char            procedure_name [20];
   char            parameter_name [20];
   char            ptype          [20];
   SQLSMALLINT     parameter_type = 0;
   SQLSMALLINT     data_type = 0;
   char            type_name      [20];
   SWORD           cbCursor;
   SDWORD          cbValue3;
   SDWORD          cbValue4;
   SDWORD          cbValue5;
   SDWORD          cbValue6;
   SDWORD          cbValue7;
   char            ProcCatalog [20] = {0};
   char            ProcSchema  [20] = {0};
   char            ProcName    [20] = {"DOIT%"};
   char            ColumnName  [20] = {"P%"};
   SQLSMALLINT     cbProcCatalog = 0;
   SQLSMALLINT     cbProcSchema  = 0;
   SQLSMALLINT     cbProcName    = strlen(ProcName);
   SQLSMALLINT     cbColumnName  = strlen(ColumnName);
 
   (void) printf ("**** Entering CLIP12.\n\n");
  /*****************************************************************/
  /* Allocate environment handle                                   */
  /*****************************************************************/
   RETCODE = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Allocate connection handle to DSN                             */
  /*****************************************************************/
   RETCODE = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
   if( RETCODE != SQL_SUCCESS )      // Could not get a connect handle
     goto dberror;
  /*****************************************************************/
  /* CONNECT TO data source (STLEC1)                               */
  /*****************************************************************/
   RETCODE = SQLConnect(hDbc,        // Connect handle
                        (SQLCHAR *) pDSN, // DSN
                        SQL_NTS,     // DSN is nul-terminated
                        NULL,        // Null UID
                        0   ,
                        NULL,        // Null Auth string
                        0);
   if( RETCODE != SQL_SUCCESS )      // Connect failed
     goto dberror;
  /*****************************************************************/
  /* Allocate statement handles                                    */
  /*****************************************************************/
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
  if (rc != SQL_SUCCESS)
    goto exit;
  /*****************************************************************/
  /* Invoke SQLProcedureColumns and retrieve all rows within       */
  /* answer set.                                                   */
  /*****************************************************************/
  rc = SQLProcedureColumns (hStmt                  ,
                            (SQLCHAR *) ProcCatalog,
                            cbProcCatalog          ,
                            (SQLCHAR *) ProcSchema ,
                            cbProcSchema           ,
                            (SQLCHAR *) ProcName   ,
                            cbProcName             ,
                            (SQLCHAR *) ColumnName ,
                            cbColumnName);

  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** SQLProcedureColumns Failed.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind procedure_name
                   3,
                   SQL_C_CHAR,
                   procedure_name,
                   sizeof(procedure_name),
                   &cbValue3);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** Bind of procedure_name Failed.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind parameter_name
                   4,
                   SQL_C_CHAR,
                   parameter_name,
                   sizeof(parameter_name),
                   &cbValue4);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** Bind of parameter_name Failed.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind parameter_type
                   5,
                   SQL_C_SHORT,
                   &parameter_type,
                   0,
                   &cbValue5);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** Bind of parameter_type Failed.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind SQL data type
                   6,
                   SQL_C_SHORT,
                   &data_type,
                   0,
                   &cbValue6);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** Bind of data_type Failed.\n");
    goto dberror;
  }
  rc = SQLBindCol (hStmt,           // bind type_name
                   7,
                   SQL_C_CHAR,
                   type_name,
                   sizeof(type_name),
                   &cbValue7);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** Bind of type_name Failed.\n");
    goto dberror;
  }
  /*****************************************************************/
  /* Answer set is available - Fetch rows and print parameters for */
  /* all procedures.                                               */
  /*****************************************************************/
  while ((rc = SQLFetch (hStmt)) == SQL_SUCCESS)
  {
    (void) printf ("**** Procedure Name = %s. Parameter %s",
                   procedure_name,
                   parameter_name);
    switch (parameter_type)
    {
      case SQL_PARAM_INPUT        :
        (void) strcpy (ptype, "INPUT");
        break;
      case SQL_PARAM_OUTPUT       :
        (void) strcpy (ptype, "OUTPUT");
        break;
      case SQL_PARAM_INPUT_OUTPUT :
        (void) strcpy (ptype, "INPUT/OUTPUT");
        break;
      default                     :
        (void) strcpy (ptype, "UNKNOWN");
        break;
    }
    (void) printf (" is %s. Data Type is %d. Type Name is %s.\n",
                   ptype     ,
                   data_type ,
                   type_name);
  }
  /*****************************************************************/
  /* Deallocate statement handles -- statement is no longer in a   */
  /* prepared state.                                               */
  /*****************************************************************/
  rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
  /*****************************************************************/
  /* DISCONNECT from data source                                   */
  /*****************************************************************/
   RETCODE = SQLDisconnect(hDbc);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Deallocate connection handle                                  */
  /*****************************************************************/
   RETCODE = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Free Environment Handle                                       */
  /*****************************************************************/
   RETCODE = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
   if (RETCODE == SQL_SUCCESS)
     goto exit;
   dberror:
   RETCODE=12;
   exit:
   (void) printf ("**** Exiting  CLIP12.\n\n");
   return RETCODE;
}

SQLProcedureColumns() - Get procedure input/output parameter information

ODBC specifications for SQLProcedureColumns()