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.

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

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

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.
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)	This 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 CLI always returns SQL_PT_PROCEDURE.
Note: The column names used by DB2 for i 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 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 to 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

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