SQLProcedureColumns

SQLProcedureColumns

SQLProcedureColumns returns the 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.

SQLProcedureColumnsW() as a Unicode string supports same execution as SQLProcedureColumns().

Syntax

SQLRETURN  SQLProcedureColumns (
    SQLHSTMT        stmt,
    SQLCHAR *       cName,
    SQLSMALLINT     cNameLength,
    SQLCHAR *       sName,
    SQLSMALLINT     sNameLength,
    SQLCHAR *       pName,
    SQLSMALLINT     pNameLength,
    SQLCHAR *       colName,
    SQLSMALLINT     colNameLength );

Arguments

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle
SQLCHAR *	cName	Input	Procedure catalog name
SQLSMALLINT	cNameLength	Input	The length, in bytes, of *cName
SQLCHAR *	sName	Input	Procedure schema name
SQLSMALLINT	sNameLength	Input	The length, in bytes, of *sName
SQLCHAR *	pName	Input	Procedure name. Cannot be a NULL pointer. pName must not include the character string search pattern.
SQLSMALLINT	pNameLength	Input	The length, in bytes, of *pName
SQLCHAR *	colName	Input	Column name
SQLSMALLINT	colNameLength	Input	The length, in bytes, of *colName

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Description

This function is used before a statement is executed to retrieve the procedure parameters and the columns that form the result sets returned by the procedure.

SQLProcedureColumns() returns the results in the standard result set sorted in order by PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, and COLUMN_TYPE.

The column names will be returned for each procedure in the following order:

• Name of returned value
• Each parameter names to call the procedures (or calling order)
• Column names of the result set returned by the procedure (or column order)

The columns returned by SQLProcedureColumns() are shown in [Table 2-3].

Name	No.	Data Type	Description
PROCEDURE_CAT	1	VARCHAR	Always returns NULL
PROCEDURE _SCHEM	2	VARCHAR	Procedure schema name NULL if not applicable to the database
PROCEDURE _NAME	3	VARCHAR (NOT NULL)	Procedure name
COLUMN_NAME	4	VARCHAR (NOT NULL)	Procedure column name. The driver returns an empty string for a procedure column that does not have a name.
COLUMN_TYPE	5	SMALLINT (NOT NULL)	Defines the procedure column as a parameter or a result set column - SQL_PARAM_INPUT: The procedure column is the input parameter. - SQL_PARAM_INPUT_OUTPUT: The procedure column is the input/output parameters. - SQL_PARAM_OUTPUT: The procedure column is the output parameter.
DATA_TYPE	6	SMALLINT (NOT NULL)	SQL data type
TYPE_NAME	7	VARCHAR (NOT NULL)	Name of the data type corresponding to the database
COLUMN_SIZE	8	INTEGER	Column Size. NULL will be returned when the column size is not proper.
BUFFER_LENGTH	9	INTEGER	The maximum byte storing the data
DECIMAL_DIGITS	10	SMALLINT	The NULL will return the data type that cannot apply the decimal points of the string and the decimal points.
NUM_PREC_RADIX	11	SMALLINT	In case of the numeric data type 10, For COLUMN_SIZE and DECIMAL_DIGIT, decimal digits allowable in this string is be given. For example, DECIMAL(12,5) string can return NUM_PREC_RADIX 10, COLUMN_SIZE 12, and DECIMAL_DIGITS 5.
NULLABLE	12	SMALLINT (NOT NULL)	SQL_NO_NullS when the procedure column does not allow NULL value, or SQL_Nullable when NULL is allowed.
REMARKS	13	VARCHAR	Description of the procedure column
COLUMN_DEF	14	VARCHAR	The default value of the column. If NULL was specified as the default value, this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, 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.
SQL_DATA_TYPE	15	SMALLINT (NOT NULL)	SQL data type
SQL_DATETIME_SUB	16	SMALLINT	Subtype code for the Date data type. ∅ is returned for any other data type
CHAR_OCTET_LENGTH	17	INTEGER	Maximum digits of the character string or binary data-type string.
ORDINAL_POSITION	18	INTEGER (NOT NULL)	Location of the order of the parameters in the procedure definition (starting with 1)
IS_NULLABLE	19	VARCHAR	- NO : When the string does not contain the NULL - YES : When the string contain the NULL

[Table 2‑3] Columns Returned by SQLProcedureColumns()

Diagnosis

SQLSTATE	Description	Comments
HY000	General error
HY009	Invalid arguments used (null pointer)	CName is a NULL pointer

Related Functions

SQLBindCol
SQLFetch
SQLProcedures

Example

< Refer to: $ALTIBASE_HOME/sample/SQLCLI/demo_meta6.cpp >

if (SQLProcedureColumns(stmt,
                        NULL, 0,
                        NULL, 0,
                        "DEMO_META6_PROC", SQL_NTS,
                        NULL, 0) != SQL_SUCCESS)
{                       
      execute_err(dbc, stmt, "SQLProcedureColumns");
      SQLFreeStmt(stmt, SQL_DROP);
      return SQL_ERROR;
}

  SQLBindCol(stmt, 1, SQL_C_CHAR, szCatalog, STR_LEN,&cbCatalog);
  SQLBindCol(stmt, 2, SQL_C_CHAR, szSchema, STR_LEN, &cbSchema);
  SQLBindCol(stmt, 3, SQL_C_CHAR, szProcName, STR_LEN,&cbProcName);
  SQLBindCol(stmt, 4, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);
  SQLBindCol(stmt, 5, SQL_C_SSHORT, &ColumnType, 0, &cbColumnType);
  SQLBindCol(stmt, 7, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);
  SQLBindCol(stmt, 8, SQL_C_SLONG, &ColumnSize, 0, &cbColumnSize);
  SQLBindCol(stmt, 10, SQL_C_SSHORT, &DecimalDigits, 0, &cbDecimalDigits);
  SQLBindCol(stmt, 11, SQL_C_SSHORT, &NumPrecRadix, 0, &cbNumPrecRadix);
  SQLBindCol(stmt, 18, SQL_C_SLONG, &OrdinalPosition, 0, &cbOrdinalPosition);