IBM Books

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.

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.

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 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 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 7  REMARKS (VARCHAR(254))
Contains the descriptive information about the procedure.

Column 8  PROCEDURE_TYPE (SMALLINT)
Defines the procedure type:

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

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

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.

Example

See the CLI Sample procs.c .

References


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]