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 |
Procedure
name. An empty string is returned for
a procedure that does not have a name. |
4 |
COLUMN_NAME |
Varchar |
Procedure
column name. The driver returns an empty string for a procedure column that does
not have a name. |
5 |
COLUMN_TYPE |
Smallint |
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 |
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 |
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 |
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 |
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 |
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.