SQL QUERY PROCEDURES Statement

< > Browse Statements Query Statements Connection Statements Example Flow Chart Table of Contents

The SQL QUERY PROCEDURES statement returns a result set that lists and describes the stored procedures of the data source identified by an InstantSQL connection handle. The result set is identified by the query handle value that is returned.

QueryHandle (output). This argument must refer to a numeric integer data item with at least six digits of precision. The argument specifies the data item where the query handle value that identifies the procedures query is to be stored.

ConnectionHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the connection to be used for the procedures query. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

CatalogName (input). This argument must refer to a nonnumeric value. The value specifies the catalog name (or, for ODBC v2, the qualifier name) of the procedure or procedures for which the procedure descriptions are to be returned. This argument is optional. The default is no catalog name constraint on the query results. For letters in this string, case is significant. CatalogName cannot contain a string search pattern. If a driver supports catalogs for some procedures but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those procedures that do not have catalogs. This argument must be omitted (or specified as OMITTED) if the data source does not support catalog names.

SchemaName (input). This argument must refer to a nonnumeric value. The value specifies the schema name (or, for ODBC v2, the owner name) of the procedure or procedures for which the procedure descriptions are to be returned. This argument is optional. The default is no schema name constraint on the query results. If a driver supports schemas for some procedures but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those procedures that do not have schemas. This argument identifies the schema name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant. If the driver does not support schema names, this argument must be omitted (or specified as OMITTED); otherwise, an ODBC error may occur with SQLSTATE S1C00, optional feature not implemented.

ProcedureName (input). This argument must refer to a nonnumeric value. The value specifies the procedure name or names for which the procedure descriptions are to be returned. This argument is optional. The default is to return all procedures. This argument identifies the procedure name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant.

Valid query handle values returned by InstantSQL are in the range 1 through 9999. The application program may use the value zero in a query handle data item to indicate that the query has not been created or has been dropped.

After the SQL QUERY PROCEDURES statement is executed, SQL FETCH ROW statements can be executed to fetch the procedure description rows. Each row describes one stored procedure of the specified connection. The result set is ordered by PROCEDURE_CAT, PROCEDURE_SCHEMA, and PROCEDURE_NAME (see result set description below for an explanation of these result column names).

The SQL GET DATA statement can be used to transfer the data that describes the procedure or the SQL BIND COLUMN statement can be used so that the data is transferred into COBOL data items as part of the SQL FETCH ROW statement.

The SQL START QUERY statement can be used with the returned query handle value to re-start the procedures query from the beginning, if desired. The SQL QUERY PROCEDURES statement starts the procedures query, so an initial SQL START QUERY statement is not required.

The SQL END QUERY statement can be used to end the procedures query.

A successful SQL QUERY PROCEDURES statement sets the type of the query identified by the value of the QueryHandle argument to sql-QryProcs. (The type of a query can be obtained using the SQL DESCRIBE QUERY statement.)

A successful SQL QUERY PROCEDURES statement sets the status of the query identified by the value of the QueryHandle argument to sql-StatExecuting. (The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.)

Some drivers may return rows for procedures that are not executable by the user. Drivers are required to provide information on whether this is the case. The sql-ConAccessibleProcedures data item in the extended connection description group returned by an SQL DESCRIBE CONNECTION statement contains this information.

The columns in the result set for a procedures query are as follows:

#	Column Name (1)	Data Type (2)	Comments
1	PROCEDURE_CAT (PROCEDURE_QUALIFIER)	Varchar	Procedure catalog identifier; NULL if not applicable to the data source. If a driver supports catalogs for some procedures but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those procedures that do not have catalogs.
2	PROCEDURE_SCHEM (PROCEDURE_OWNER)	Varchar	Procedure schema identifier; NULL if not applicable to the data source. If a driver supports schemas for some procedures but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those procedures that do not have schemas.
3	PROCEDURE_NAME	Varchar	Procedure name.
4	NUM_INPUT_PARAMS	N/A	Reserved for future use. Applications should not rely on the data returned in these result columns.
5	NUM_OUTPUT_PARAMS	N/A	Reserved for future use. Applications should not rely on the data returned in these result columns.
6	NUM_RESULT_SETS	N/A	Reserved for future use. Applications should not rely on the data returned in these result columns.
7	REMARKS	Varchar	A description of 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.

Note 1 The first column names listed are those returned by ODBC drivers that conform to ODBC version 3.0 and later. ODBC drivers that conform to prior versions return the column names shown second in parentheses when the column name is different. Because of these version issues, getting or binding data for a procedures query should use column numbers rather than column names. (The version of ODBC supported by the driver can be obtained in sql-ConDriverODBCVersion by using the SQL DESCRIBE CONNECTION statement with an extended connection description group.)

Note 2 The lengths of VARCHAR columns for the result set are not shown because the actual lengths depend on the data source. To determine the actual lengths of the PROCEDURE_CAT, PROCEDURE_SCHEM, and PROCEDURE_NAME columns, an application can use the SQL DESCRIBE CONNECTION statement with an extended connection description group. The maximum lengths of the indicated columns will then be available in sql-ConMaxCatalogNameLength, sql-ConMaxSchemaNameLength, and sql-ConMaxProcedureNameLength, respectively. Also, the SQL DESCRIBE COLUMN statement can be used to obtain the maximum length of any column in the result set.

Some drivers may return additional driver-specific columns. The existence of such columns can be determined using the SQL DESCRIBE QUERY statement to obtain the sql-QryNoCols data item and comparing its value to 8. The descriptions of such columns can be obtained with the SQL DESCRIBE COLUMN statement. Column numbers relative to the end of the result set should be used to access driver-specific columns.

SQL QUERY PROCEDURES Statement Examples:

           SQL QUERY PROCEDURES
               sql-QueryHandle,
               sql-ConnectionHandle.

           SQL QUERY PROCEDURES
               sql-QueryHandle,
               sql-ConnectionHandle,
               "Northwind",   *> catalog (qualifier) name
               "dbo",         *> schema (owner) name
               "Sales%".      *> proc names beginning with "Sales"