SQL QUERY PROCEDURE-COLUMNS Statement

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

 

The SQL QUERY PROCEDURE-COLUMNS statement returns a result set that lists and describes the input and output parameters, as well as the columns in the result set, if any, for a specified stored procedure of an InstantSQL connection.  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 procedure-columns 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 procedure-columns query.  This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

ProcedureName (input).  This argument must specify a nonnumeric value.  The value identifies the procedure name of the stored procedure or procedures for which the parameter and column descriptions are to be listed.  Procedure names can be obtained with the SQL QUERY PROCEDURES statement.  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.

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 column 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 procedure s but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those procedure s 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 column 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.

ColumnName (input).  This argument must refer to a nonnumeric value.  The value specifies the column name or names for which the procedure column descriptions are to be returned.  This argument is optional.  The default is to return all procedure columns.  This argument identifies the column 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 PROCEDURE-COLUMNS statement is executed, SQL FETCH ROW statements can be executed to fetch the procedure parameter and column description rows.  Each row describes one parameter or column of the specified stored procedure.  The result set is ordered by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, and COLUMN_TYPE (see result set description below for an explanation of these result column names).  Parameter and column names are returned for each procedure in the following order:  the name of the return value, the names of each parameter in the procedure invocation (in call order), and then the names of each column in the result set returned by the procedure (in column order).  (The preceding sentence was added to ODBC 3.0 documentation, but conflicts with the ordering on COLUMN_TYPE.)

Note  SQL QUERY PROCEDURE-COLUMNS might not return all columns used by a procedure.  For example, a driver might only return information about the parameters used by a procedure and not the columns in a result set it generates.

The SQL GET DATA statement can be used to transfer the data that describes the parameter or column, 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 procedure-columns query from the beginning, if desired.  The SQL QUERY PROCEDURE-COLUMNS statement starts the procedure-columns query, so an initial SQL START QUERY statement is not required.

The SQL END QUERY statement can be used to end the procedure-columns query.

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

A successful SQL QUERY PROCEDURE-COLUMNS 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.)

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

  #

Column Name (1)

Data Type (2)

Comments

1

PROCEDURE_CAT (PROCEDURE_QUALIFIER)

Varchar

Procedure catalog name; 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 name; 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
not NULL

Procedure name.  An empty string is returned for a procedure that does not have a name.

4

COLUMN_NAME

Varchar
not NULL

Procedure column name. The driver returns an empty string for a procedure column that does not have a name.

5

COLUMN_TYPE

Smallint
not NULL

Defines the procedure column as a parameter or a result set column:

sql-Param-Type-Unknown:  The procedure column is a parameter whose type is unknown.

sql-Param-Input:  The procedure column is an input parameter.

sql-Param-Input-Output:  The procedure column is an input/output parameter.

sql-Param-Output:  The procedure column is an output parameter.

sql-Return-Value:  The procedure column is the return value of the procedure.

sql-Result-Col: The procedure column is a result set column.

6

DATA_TYPE

Smallint
not NULL

SQL data type of the procedure column.  This may be an ODBC SQL data type or a driver-specific SQL data type.  For datetime and interval data types, this column returns the concise data type (such as SQL_TYPE_DATE or SQL_­INTERVAL_­YEAR_­TO_­MONTH, rather than the non-concise data type such as SQL_DATETIME or SQL_INTERVAL).

The data types that are returned for ODBC 3.x and ODBC 2.x applications may be different.

7

TYPE_NAME

Varchar
not NULL

Data source–dependent data type name; for example, "CHAR", "VARCHAR", "MONEY", "LONG VARBINAR", or "CHAR ( ) FOR BIT DATA".

8

COLUMN_SIZE (PRECISION)

Integer

The column size of the procedure column on the data source.  NULL is returned for data types where column size is not applicable.

9

BUFFER_LENGTH (LENGTH)

Integer

The length in bytes of data transferred to the C data buffer on an SQL FETCH ROW operation.  For numeric data, this size may be different than the size of the data stored on the data source.  This value is the same as the COLUMN_SIZE column for character or binary data.  This size is generally different from the size of the data transferred to the COBOL data item for an SQL GET DATA statement.

10

DECIMAL_DIGITS (SCALE)

Smallint

The decimal digits of the procedure column on the data source.

NULL is returned for data types where decimal digits is not applicable.

11

NUM_PREC_RADIX (RADIX)

Smallint

For numeric data types, either 10 or 2.

If it is 10, the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of decimal digits allowed for the procedure column.  For example, a DECIMAL(12,5) column would return a NUM_PREC_RADIX of 10, a COLUMN_SIZE of 12, and a DECIMAL_DIGITS of 5; a FLOAT column could return a NUM_PREC_RADIX of 10, a COLUMN_SIZE of 15 and a DECIMAL_DIGITS of NULL.

If it is 2, the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of bits allowed in the procedure column.  For example, a FLOAT column could return a RADIX of 2, a COLUMN_SIZE of 53, and a DECIMAL_DIGITS of NULL.

NULL is returned for data types where NUM_PREC_RADIX is not applicable.

12

NULLABLE

Smallint
not NULL

Whether the procedure column accepts a NULL value:

sql-No-Nulls if the procedure column does not accept NULL values.

sql-Nullable if the procedure column accepts NULL values.

sql-Nullable-Unknown if it is not known if the procedure column accepts NULL values.

The value returned for this column is different from the value returned for the IS_NULLABLE column (column number 19).  The NULLABLE column indicates with certainty that a column can accept NULLs, but cannot indicate with certainty that a column does not accept NULLs.  The IS_NULLABLE column indicates with certainty that a column cannot accept NULLs, but cannot indicate with certainty that a column accepts NULLs.

13

REMARKS

Varchar

A description of the procedure column.

14

COLUMN_DEF

Varchar

The default value of the procedure column.  The value in this column should be interpreted as a string if it is enclosed in quotation marks.

If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks.  If the default value cannot be represented without truncation, then this column contains TRUNCATED, with no enclosing single quotation marks.  If no default value was specified, then this column is NULL.

The value of COLUMN_DEF can be used in generating a new column definition, except when it contains the value TRUNCATED.

15

SQL_DATA_TYPE

Smallint
not NULL

SQL data type, as it appears in the SQL_DESC_TYPE record field in the IRD.  This may be an ODBC SQL data type or a driver-specific SQL data type.  This column is the same as the DATA_TYPE column, with the exception of datetime and interval data types.  This column returns the non-concise data type (such as SQL_DATETIME or SQL_INTERVAL), rather than the concise data type (such as  SQL_TYPE_DATE or SQL_­INTERVAL_­YEAR_­TO_­MONTH) for datetime and interval data types.  If this column returns SQL_DATETIME or SQL_INTERVAL, the specific data type can be determined from the SQL_DATETIME_SUB column

16

SQL_DATETIME_SUB

Smallint

The subtype code for datetime and interval data types.  For other data types, this column returns a NULL.

17

CHAR_OCTET_LENGTH

Integer

The maximum length in bytes of a character or binary data type column.  For all other data types, this column returns a NULL.

18

ORDINAL_POSITION

Integer
not NULL

For input and output parameters, the ordinal position of the parameter in the procedure definition (in increasing parameter order, starting at 1). 

For a return value (if any), 0 is returned.

For result-set columns, the ordinal position of the column in the result set, with the first column in the result set being number 1.  If there are multiple result sets, column ordinal positions are returned in a driver-specific manner.

19

IS_NULLABLE

Varchar(3)

"NO" if the column does not include NULLs.

"YES" if the column could include NULLs.

This column returns a zero-length string if nullability is unknown.

ISO rules are followed to determine nullability.  An ISO SQL – compliant DBMS must not return an empty string.

The value returned for this column is different from the value returned for the NULLABLE column (column number 12).  (See the description of the NULLABLE column for the reason.)

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 listed second in parentheses when the column name is different and only return columns 1 through 13.  Because of these version issues, getting or binding data for a procedure-columns 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, PROCEDURE_NAME, and COLUMN_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, sql-ConMaxProcedureNameLength, and sql-ConMaxColumnNameLength, 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 19 (13 for ODBC 2.x drivers).  The descriptions of such columns can be obtained with the SQL DESCRIBE COLUMN statement.  For ODBC drivers that are not ODBC version 3.0 or later conformant, driver-specific columns start with column 14.  Column numbers relative to the end of the result set should be used to access driver-specific columns.

SQL QUERY PROCEDURE-COLUMNS Statement Example:

 

           SQL QUERY PROCEDURE-COLUMNS
               sql-QueryHandle,
               sql-ConnectionHandle,
               sql-ProcedureName.

           SQL QUERY PROCEDURE-COLUMNS
               sql-QueryHandle,
               sql-ConnectionHandle,
               sql-ProcedureName,
               "Northwind",  *> catalog (qualifier) name
               "dbo",        *> schema (owner) name
               "Emp%".       *> column names beginning with "Emp"

Copyright ©2000 Liant Software Corp.  All rights reserved.