SQLProcedures - Get list of procedure names

SQLProcedures - Get list of procedure names

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

Syntax
SQLRETURN   SQLProcedures    (SQLHSTMT          StatementHandle,
                              SQLCHAR           *CatalogName,
                              SQLSMALLINT       NameLength1,
                              SQLCHAR           *SchemaName,
                              SQLSMALLINT       NameLength2,
                              SQLCHAR           *ProcName,
                              SQLSMALLINT       NameLength3);
Function arguments

Table 1. SQLProcedures 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 Universal Database™ 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 Universal Database, 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.

Usage

The result set returned by SQLProcedures() contains the columns listed in Table 2 in the order given. The rows are ordered by PROCEDURE_CAT, PROCEDURE_SCHEMA, and PROCEDURE_NAME.
Because calls to SQLProcedures() in many cases map to a complex and thus expensive query against the system catalog, use them sparingly, and save the results rather than repeating calls.
Although new columns might be added and the names of the existing columns might be changed in future releases, the position of the current columns does not change.
Table 2. Columns returned by SQLProcedures
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) NOT NULL The name of the procedure.
4 NUM_INPUT_PARAMS INTEGER not NULL Number of input parameters.
This column should not be used, it is reserved for future use by ODBC.
It is used in versions of DB2^® UDB CLI before version 5. For backward compatibility it can be used with the old DB2CLI.PROCEDURES pseudo catalog table (by setting the PATCH1 CLI/ODBC Configuration keyword).
5 NUM_OUTPUT_PARAMS INTEGER not NULL Number of output parameters.
This column should not be used, it is reserved for future use by ODBC.
It was used in versions of DB2 UDB CLI before version 5. For backward compatibility it can be used with the old DB2CLI.PROCEDURES pseudo catalog table (by setting the PATCH1 CLI/ODBC Configuration keyword).
6 NUM_RESULT_SETS INTEGER not NULL Number of result sets returned by the procedure.
This column should not be used, it is reserved for future use by ODBC.
It was used in versions of DB2 UDB CLI before version 5. For backward compatibility it can be used with the old DB2CLI.PROCEDURES pseudo catalog table (by setting the PATCH1 CLI/ODBC Configuration keyword).
7 REMARKS VARCHAR(254) This contains the descriptive information about the procedure.

The column names used by DB2 UDB CLI 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

SQL_SUCCESS

SQL_SUCCESS_WITH_INFO

SQL_ERROR

SQL_INVALID_HANDLE

Error conditions

Table 3. SQLProcedures 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.
HY001 Memory allocation failure DB2 UDB 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 UDB 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 to SQL_NTS.
HYC00 Driver not capable DB2 UDB 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

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

By using the code examples, you agree to the terms of the Code license and disclaimer information.
/* From CLI sample procs.c */
/* ... */
 
    printf("Enter Procedure Schema Name Search Pattern:\n");
    gets((char *)proc_schem.s);
 
    rc = SQLProcedures(hstmt, NULL, 0, proc_schem.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, 7, SQL_C_CHAR, (SQLPOINTER) remarks.s, 255,
                    &remarks.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    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 */
References
SQLProcedureColumns - Get input/output parameter information for a procedure

Parent topic:
DB2 UDB CLI functions

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 Universal Database™ 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 Universal Database, 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.

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) NOT NULL	The name of the procedure.
4 NUM_INPUT_PARAMS	INTEGER not NULL	Number of input parameters. This column should not be used, it is reserved for future use by ODBC. It is used in versions of DB2^® UDB CLI before version 5. For backward compatibility it can be used with the old DB2CLI.PROCEDURES pseudo catalog table (by setting the PATCH1 CLI/ODBC Configuration keyword).
5 NUM_OUTPUT_PARAMS	INTEGER not NULL	Number of output parameters. This column should not be used, it is reserved for future use by ODBC. It was used in versions of DB2 UDB CLI before version 5. For backward compatibility it can be used with the old DB2CLI.PROCEDURES pseudo catalog table (by setting the PATCH1 CLI/ODBC Configuration keyword).
6 NUM_RESULT_SETS	INTEGER not NULL	Number of result sets returned by the procedure. This column should not be used, it is reserved for future use by ODBC. It was used in versions of DB2 UDB CLI before version 5. For backward compatibility it can be used with the old DB2CLI.PROCEDURES pseudo catalog table (by setting the PATCH1 CLI/ODBC Configuration keyword).
7 REMARKS	VARCHAR(254)	This contains the descriptive information about the procedure.
The column names used by DB2 UDB CLI 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.

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.
HY001	Memory allocation failure	DB2 UDB 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 UDB 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 to SQL_NTS.
HYC00	Driver not capable	DB2 UDB 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