Catalog Procedures (Metadata)
ODBC listing procedures supply the client with catalog table information. The PeopleSoft ODBC driver supports listings of queries and columns using PeopleSoft metadata.
Syntax
RETCODE SQLProcedures(hstmt,szProcQualifier,cbProcQualifier, szProcOwner,cbProcOwner,szProcName,cbProcName)
Description
SQLProcedures returns the list of procedure names that are stored in a specific data source. Procedure is a generic term used to describe executable objects or named entities that can be invoked using input and output parameters and that can return result sets similar to the results returned by SQL Select statements.
This function is typically used before statement execution to retrieve information about procedures available from the data source catalog.
Parameters
This table describes the parameters:
|
Argument |
Type |
Use |
Description |
|---|---|---|---|
|
hstmt |
HSTMT |
Input |
Statement handle. |
|
szProcQualifier |
UCHAR FAR * |
Input |
Procedure qualifier. |
|
cbProcQualifier |
SWORD |
Input |
Length of szProcQualifier.. |
|
szProcOwner |
UCHAR FAR * |
Input |
String search pattern for procedure owner names. |
|
cbProcOwner |
SWORD |
Input |
Length of szProcOwner. |
|
szProcName |
UCHAR FAR * |
Input |
String search pattern for procedure names. |
|
cbProcName |
SWORD |
Input |
Length of szProcName. |
SQLProcedures returns the results as a standard result set (when SQLFetch is called), ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, PROCEDURE_REMARKS, and PROCEDURE_TYPE.
This table lists the columns that are in the PeopleSoft result set:
|
Column Name |
Data Type |
Description |
|---|---|---|
|
PROCEDURE_QUALIFIER |
SQL_CHAR(128) |
‘’ |
|
PROCEDURE_OWNER |
SQL_CHAR(128) |
‘QUERY’ |
|
PROCEDURE_NAME |
SQL_CHAR(128) |
Query name with punctuation and spaces converted to underscore. |
|
REMARKS |
SQL_CHAR(256) |
Description of the Query, currently unused. |
|
PROCEDURE_TYPE |
SQL_INT |
SQL_PT_PROCEDURE |
Syntax
RETCODE SQLProcedureColumns(hstmt,szProcQualifier, cbProcQualifier,szProcOwner,cbProcOwner,szProcName, cbProcName,szColumnName,cbColumnName)
Description
SQLProcedureColumns returns a list of input and output parameters, as well as the columns that make up the result set for the specified procedures. The driver returns the information as a result set on the specified statement handle.
This function is typically used before statement execution to retrieve information about procedure parameters and columns from the data source’s catalog.
The PeopleSoft driver returns information for the first query that is requested only. It does not return results for multiple queries. The driver will the new query API functions QpmDescribeParm and QpmDescribeCol. QpmDescribeParm walks the query definition that is stored in hstmt and for each prompt variable returns a SQLProcedureColumns result row of COLUMN_TYPE equal to SQL_PARM_INPUT. QpmDescribeParm walks the same query definition and for each result column returns a SQLProcedureColumns result row of COLUMN_TYPE equal to SQL_RESULT_COL. The szProcQualifier and szProcOwner criteria are ignored. The result set returned is for the current user ID. The result set columns for Procedure Qualifier and Procedure Remarks do not apply and are set to NULL with a one-byte column length. The Procedure Owner column is set to either the user ID or Public.
Parameters
This table describes the parameters:
|
Argument |
Type |
Use |
Description |
|---|---|---|---|
|
hstmt |
HSTMT |
Input |
Statement handle |
|
szProcQualifier |
UCHAR FAR* |
Input |
Procedure qualifier name |
|
cbProcQualifier |
SWORD |
Input |
Length of szProcQualifier |
|
szProcOwner |
UCHAR FAR* |
Input |
String search pattern for procedure owner names |
|
cbProcOwner |
SWORD |
Input |
Length of szProcOwner |
|
szProcName |
UCHAR FAR* |
Input |
String search pattern for procedure names |
|
cbProcName |
SWORD |
Input |
Length of szProcName |
|
szColumnName |
UCHAR FAR* |
Input |
String search pattern for column names |
|
cbColumnName |
SWORD |
Input |
Length of szColumnName |
SQLProcedureColumns returns the results as a standard result set (when SQLFetch is called), ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCDURE_NAME, and COLUMN_TYPE.
This table lists the columns in the result set:
|
Column Name |
Data Type |
Description |
|---|---|---|
|
PROCEDURE_QUALIFIER |
SQL_CHAR(128) |
N/A |
|
PROCEDURE_OWNER |
SQL_CHAR(128) |
N/A |
|
PROCEDURE_NAME |
SQL_CHAR(128) |
Procedure identifier |
|
COLUMN_NAME |
SQL_CHAR(128) |
Procedure column identifier |
|
COLUMN_TYPE |
SQL_INT |
SQL_PARAM_INPUT or SQL_RESULT_COL |
|
DATA_TYPE |
SQL_INT |
SQL data type |
|
TYPE_NAME |
SQL_CHAR(128) |
Data type name of procedure column |
|
PRECISION |
SQL_INT |
Precision of the procedure column |
|
LENGTH |
SQL_INT |
Length in bytes of data transferred on a SQLGetData or SQLFetch operation |
|
SCALE |
SQL_INT |
Scale of procedure column |
|
RADIX |
SQL_INT |
N/A |
|
NULLABLE |
SQL_INT |
Whether the procedure column accepts a NULL value |
|
REMARKS |
SQL_CHAR(256) |
A description of the procedure column |