SQLProcedures() - Get a list of procedure names

SQLProcedures() returns a list of procedure names that have been registered at the server, and that match the specified search pattern. 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 SQLProcedures()

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

Syntax

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

Function arguments

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

Table 2. SQLProcedures() 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 table name.

If you do not want to qualify the result set by table name, use a null pointer or a zero length string for this argument.
SQLSMALLINT cbProcName input The length, in bytes, of szProcName.

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.

The result set returned by SQLProcedures() contains the columns that are listed in Table 3 in the order given. The rows are ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, and PROCEDURE_NAME.

Because calls to SQLProcedures() 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

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. Table 4 lists these columns.

Table 3. Columns returned by SQLProcedures()
Column number Column name Data type Description
1 PROCEDURE_CAT VARCHAR(128) This is always null.
2 PROCEDURE_SCHEM VARCHAR(128) The name of the schema containing PROCEDURE_NAME.
3 PROCEDURE_NAME VARCHAR(128) NOT NULL The name of the procedure.
4 NUM_INPUT_PARAMS INTEGER not NULL Number of input parameters.
5 NUM_OUTPUT_PARAMS INTEGER not NULL Number of output parameters.
6 NUM_RESULT_SETS INTEGER not NULL Number of result sets returned by the procedure.
7 REMARKS VARCHAR(254) Contains the descriptive information about the procedure.
8 PROCEDURE_TYPE SMALLINT Defines the procedure type:
  • SQL_PT_UNKNOWN: It cannot be determined whether the procedure returns a value.
  • SQL_PT_PROCEDURE: The returned object is a procedure; that is, it does not have a return value.
  • SQL_PT_FUNCTION: The returned object is a function; that is, it has a return value.
Db2 ODBC always returns SQL_PT_PROCEDURE.

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 SQLProcedures() result set in ODBC.

Return codes

After you call SQLProcedures(), 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. SQLProcedures() 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.
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 to 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 supported schema as a qualifier for procedure name.

Restrictions

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 prints a list of procedures registered at the server. The application uses SQLProcedures() to retrieve these procedures and to establish a search pattern.
Figure 1. An application that prints a list of registered procedures
/* ... */
    rc = SQLProcedures(hstmt, NULL, 0, proc_schem.s, SQL_NTS, "%", SQL_NTS);
    gets(proc_schem.s);
    rc = SQLProcedures(hstmt, NULL, 0, proc_schem.s, SQL_NTS, "NTS);
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) proc_schem.s, 129,
                    &proc_schem.ind);
    rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, (SQLPOINTER) proc_name.s, 129,
                    &proc_name.ind);
    rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) remarks.s, 255,
                    &remarks.ind);
    printf("PROCEDURE SCHEMA          PROCEDURE NAME            \n");
    printf("------------------------- ------------------------- \n");
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        printf("%-25s %-25s\n", proc_schem.s, proc_name.s);
        if (remarks.ind != SQL_NULL_DATA) {
          printf(" (Remarks) %s\n", remarks.s);
        }
    }                           /* endwhile */
/* ... */