Call Level Interface Guide and Reference

SQLProcedures - Get List of Procedure Names

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

SQLProcedures() returns a list of procedure names that have been registered at the server, and which 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 generated by a query.

Syntax

SQLRETURN   SQLProcedures    (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      FAR  *CatalogName,      /* szProcCatalog */
                SQLSMALLINT       NameLength1,       /* cbProcCatalog */
                SQLCHAR      FAR  *SchemaName,       /* szProcSchema */
                SQLSMALLINT       NameLength2,       /* cbProcSchema */
                SQLCHAR      FAR  *ProcName,         /* szProcName */
                SQLSMALLINT       NameLength3);      /* cbProcName */

Function Arguments

Table 142. SQLTables 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 may contain a pattern-value to qualify the result set by schema name.
For DB2 for MVS/ESA 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. For more information about valid search patterns, refer to Querying System Catalog Information.
SQLSMALLINT NameLength2 Input Length of SchemaName.
SQLCHAR * ProcName Input Buffer that may contain a pattern-value to qualify the result set by table name.
SQLSMALLINT NameLength3 Input Length of ProcName.

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 may contain a `pattern-value` to qualify the result set by schema name. For DB2 for MVS/ESA 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. For more information about valid search patterns, refer to Querying System Catalog Information.
SQLSMALLINT	NameLength2	Input	Length of `SchemaName`.
SQLCHAR *	ProcName	Input	Buffer that may contain a `pattern-value` to qualify the result set by table name.
SQLSMALLINT	NameLength3	Input	Length of `ProcName`.

Usage

DB2 Universal Database version 5 introduced two system catalog views used to store information about all stored procedures on the server (SYSCAT.PROCEDURES and SYSCAT.PROCPARMS). See Appendix G, Catalog Views for Stored Procedures for information on these views. SQLProcedures() returns a list of stored procedures from these views.

Before version 5, DB2 CLI used the pseudo catalog table for stored procedure registration. By default, DB2 CLI will use the new system catalog views. If the application expects to use the pseudo catalog table then the CLI/ODBC configuration keyword PATCH1 should be set to 262144. See Replacement of the Pseudo Catalog Table for Stored Procedures for more information.

If the stored procedure is at a DB2 for MVS/ESA V 4.1 server or later, the name of the stored procedures must be registered in the server's SYSIBM.SYSPROCEDURES catalog table.

For other versions of DB2 servers that do not provide facilities for a stored procedure catalog, an empty result set will be returned.

The result set returned by SQLProcedures() contains the columns listed in Columns Returned By SQLProcedures in the order given. The rows are ordered by PROCEDURE_CAT, PROCEDURE_SCHEMA, and PROCEDURE_NAME.

Since 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 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

If the SQL_ATTR_LONGDATA_COMPAT connection attribute is set, LOB column types will be reported as LONG VARCHAR, LONG VARBINARY, or LONG VARGRAPHIC types.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Columns Returned By SQLProcedures

Column 1 PROCEDURE_CAT (VARCHAR(128))

This is always null.

Column 2 PROCEDURE_SCHEM (VARCHAR(128))

The name of the schema containing PROCEDURE_NAME.

Column 3 PROCEDURE_NAME (VARCHAR(128) NOT NULL)

The name of the procedure.

Column 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 was used in versions of DB2 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). See Replacement of the Pseudo Catalog Table for Stored Procedures for more information.

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

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

Column 7 REMARKS (VARCHAR(254))

Contains the descriptive information about the procedure.

Column 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 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_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 143. SQLProcedures SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY010 Function sequence error.
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for procedure name.
The connected server does not supported schema as a qualifier for procedure name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008	Operation canceled.	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for procedure name. The connected server does not supported schema as a qualifier for procedure name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

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() will return an empty result set.

CLI Sample stpcli.c

(The complete sample stpcli.c is also available here .)

 
/* From the CLI sample STPCLI.C */
/* ... */
 
    /* call SQLProcedures */ 
    printf("\n    Call SQLProcedures for:\n");
    printf("        schemaPattern = %s\n", stpSchemaPattern);    
    printf("        namePattern = %s\n", stpNamePattern);    
    sqlrc = SQLProcedures(hstmt, NULL, 0,
                          stpSchemaPattern, SQL_NTS, 
                          stpNamePattern, SQL_NTS);

References

SQLProcedureColumns - Get Input/Output Parameter Information for A Procedure

[ Top of Page | Previous Page | Next Page ]