SQLExtendedProcedures function (CLI) - Get list of procedure names

The SQLExtendedProcedures() function returns a list of stored procedure names that are registered at the server, and which match the specified search pattern. The information is returned in an SQL result set, which you can retrieve by using the same functions that you use to process a result set that is generated by a query.

Specification:

  • CLI 9.7
Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLExtendedProceduresW(). For information about ANSI to Unicode function mappings, see Unicode functions (CLI).

Modules are an extension to the concept of schemas. Applications connecting to Db2® version 9.7 or later data servers can create modules inside their schema and can create procedures inside the modules. The fully qualified name of a procedure in a module would be <SCHEMA NAME>.<MODULE NAME>.<PROCEDURE NAME>. The SQLExtendedProcedures() and SQLExtendedProcedureColumns() functions provide information about modules. These functions are not part of the current ODBC specification. For more information, see ModulesModules in SQL Procedural Languages: Application Enablement and Support.

Syntax

SQLRETURN   SQLExtendedProcedures    (
               SQLHSTMT          StatementHandle,   /* hstmt */
               SQLCHAR           *CatalogName,      /* szProcCatalog */
               SQLSMALLINT       NameLength1,       /* cbProcCatalog */
               SQLCHAR           *SchemaName,       /* szProcSchema */
               SQLSMALLINT       NameLength2,       /* cbProcSchema */
               SQLCHAR           *ProcName,         /* szProcName */
               SQLSMALLINT       NameLength3),      /* cbProcName */
               SQLCHAR           *ProcModule,       /* szProcModule */
               SQLSMALLINT       NameLength4;       /* cbProcModule */

Function arguments

Table 1. SQLExtendedProcedures arguments
Data type Argument Use Description
SQLHSTMT StatementHandle Input The statement handle.
SQLCHAR * CatalogName Input A catalog qualifier of a 3-part table name. If the target DBMS does not support 3-part naming, and CatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that supports 3-part naming.
SQLSMALLINT NameLength1 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR * SchemaName Input A buffer that can contain a pattern value to qualify the result set by schema name.

For Db2 for MVS/ESA V 4.1 and later, all the stored procedures are in one schema; the only acceptable value for the SchemaName argument is a null pointer. If a value is specified, an empty result set and SQL_SUCCESS are returned. For Db2, SchemaName can contain a valid pattern value. For more information about valid search patterns, see the catalog functions input arguments.

SQLSMALLINT NameLength2 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR * ProcName Input A buffer that can contain a pattern value to qualify the result set by table name.
SQLSMALLINT NameLength3 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ProcName, or SQL_NTS if ProcName is null-terminated.
SQLCHAR * ProcModule Input A buffer that can contain a pattern value to qualify the result set by module name.
SQLSMALLINT NameLength4 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store ProcModule, or SQL_NTS if ProcModule is null-terminated.

Usage

The result set that is returned by the SQLExtendedProcedures() function contains the columns that are listed in Columns returned by SQLExtendedProcedures in the order given. The rows are ordered by PROCEDURE_CAT, PROCEDURE_SCHEMA, and PROCEDURE_NAME.

In many cases, calls to the SQLExtendedProcedures() function map to a complex and thus expensive query against the system catalog, so you should use the calls sparingly, and save the results rather than repeating calls.

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 the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns that are supported by the connected DBMS.

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

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

If the stored procedure is at a Db2 for MVS/ESA V4.1 up to V6 server, the name of the stored procedures must be registered in the server's SYSIBM.SYSPROCEDURES catalog table. For V8 and later servers, the stored procedure must be registered in the server's SYSIBM.SYSROUTINES and SYSIBM.SYSPARAMS catalog tables.

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

You can specify *ALL as a value in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. Alternatively, you can set the SchemaFilter IBM® Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL.

Columns returned by SQLExtendedProcedures

Column 1 PROCEDURE_CAT (VARCHAR(128))
The procedure catalog name. The value is NULL if this procedure does not have catalogs.
Column 2 PROCEDURE_SCHEM (VARCHAR(128))
The name of the schema that contains PROCEDURE_NAME.
Column 3 PROCEDURE_NAME (VARCHAR(128) NOT NULL)
The name of the procedure.
Column 4 NUM_INPUT_PARAMS (INTEGER not NULL)
The number of input parameters. INOUT parameters are not counted as part of this number.

To determine information regarding INOUT parameters, examine the COLUMN_TYPE column that is returned by SQLProcedureColumns().

Column 5 NUM_OUTPUT_PARAMS (INTEGER not NULL)
The number of output parameters. INOUT parameters are not counted as part of this number.

To determine information regarding INOUT parameters, examine the COLUMN_TYPE column that is returned by SQLProcedureColumns().

Column 6 NUM_RESULT_SETS (INTEGER not NULL)
The number of result sets that are returned by the procedure.

You should not use this column, 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 does not have a return value
  • SQL_PT_FUNCTION: The returned object is a function that has a return value.

CLI always returns SQL_PT_PROCEDURE.

Column 9 SPECIFIC_NAME (VARCHAR(128))
The unique specific name of PROCEDURE_NAME.
Column 10 PROCEDURE_MODULE (VARCHAR(128))
The name of the module that contains PROCEDURE_NAME within the schema.
Note:
  • The column names that are used by CLI follow the X/Open CLI CAE specification style. The column types, contents, and order are identical to those defined for the SQLExtendedProcedures() result set in ODBC.
  • If two modules contain procedures that share the same name, the SQLExtendedProcedures() function returns details about both procedures.

Return codes

  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_STILL_EXECUTING
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO

Diagnostics

Table 2. SQLExtendedProcedures 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008 Operation was Canceled. Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, SQLCancel() was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
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.

The function was called before a statement was prepared on the statement handle.
HY014 No more handles. Db2 CLI was unable to allocate a handle due to resource limitations.
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.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. You can set the timeout period byt using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

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, the SQLExtendedProcedures() function returns an empty result set.