SQLColumns

SQLColumns

SQLColumns retrieves column information of a specified table as an result set format.

SQLColumnsW() as a Unicode string supports same execution as SQLColumns().

Syntax

SQLRETURN  SQLColumns (
    SQLHSTMT        stmt,
    SQLCHAR *       cName,
    SQLSMALLINT         cNameLength,
    SQLCHAR *       sName,
    SQLSMALLINT         sNameLength,
    SQLCHAR *       tName,
    SQLSMALLINT         tNameLength,
    SQLCHAR *       colName,
    SQLSMALLINT     colNameLength );

Arguments

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle
SQLCHAR*	cName	Input	Catalog name
SQLSMALLINT	cNameLength	Input	The character number of *cName
SQLCHAR *	sName	Input	Name of the schema to retrieve
SQLSMALLINT	sNameLength	Input	The lenth, in bytes, of *sName
SQLCHAR *	tName	Input	Table Name to retrieve
SQLSMALLINT	tNameLength	Input	The length, in bytes, of *tName
SQLCHAR *	colName	Input	Column to retrieve
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 usually used before execution of the command to get column information in the database catalog. SQLColumns() can be used to retrieve all data types returned by SQLTables(). On the contrary, SQLColAttribute() and SQLDescribeCol() functions describe columns of the result set and SQLNumResultCols() returns the number of columns in the result set.

SQLColumns() returns the results in the standard result set format sorted by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and ORDINAL_POSITION.

Some of columns returned by SQLStatistics() are not returned by SQLColumns(). For example, SQLColumns() does not return index columns created by expressions such as "Salary + Benefits" or "DEPT = 0012" and the filter.

Columns Returned by SQLColumns()

The following table lists the columns of the result sets.

Name	No.	Data Type	Description
TABLE_CAT	1	VARCHAR	Always return NULL
TABLE_SCHEM	2	VARCHAR	Schema name; NULL in case not suitable for the database
TABLE_NAME	3	VARCHAR (NOT NULL)	Table Name
COLUMN_NAME	4	VARCHAR (NOT NULL)	Column Name
DATA_TYPE	5	SMALLINT (NOT NULL)	SQL data type
TYPE_NAME	6	VARCHAR (NOT NULL)	Character string representing the name of the data type corresponding to DATA_TYPE.
COLUMN_SIZE	7	INTEGER	or character data types, the maximum string length of the column is returned. For the Date data type, this column returns the number of characters needed to represent the date converted to a string. For numeric data types, this value is the number of digits in the number.
BUFFER_LENGTH	8	INTEGER	The maximum buffer length to store the data
DECIMAL_DIGITS	9	SMALLINT	NULL will be returned when the data type cannot apply the decimal points of the string and the decimal points.
NUM_PREC_RADIX	10	SMALLINT	In case of the numeric data type, it is 10: For COLUMN_SIZE and DECIMAL_DIGIT, decimal digits allowable in this string is given. For example, DECIMAL(12,5) string can return NUM_PREC_RADIX 10, COLUMN_SIZE 12, and DECIMAL_DIGITS 5.
NULLABLE	11	SMALLINT (NOT NULL)	SQL_NO_NULLS when the column is not allowed NULL or SQL_NULLABLE when NULL is allowed.
REMARKS	12	VARCHAR	Description of the column
COLUMN_DEF	13	VARCHAR	Default value of the column
SQL_DATA_TYPE	14	SMALLINT (NOT NULL)	SQL data type
SQL_DATETIME_SUB	15	SMALLINT	Subtype code for the data type. NULL is returned for other data types.
CHAR_OCTET_LENGTH	16	INTEGER	Maximum digits of the character of binary datatype string. For other data types, NULL will be returned.
ORDINAL_POSITION	17	INTEGER (NOT NULL)	Column order of the table. The first column number is 1 in the table.
IS_NULLABLE	18	VARCHAR	NO : When the column does not include NULL:YES : When the column includes NULL:

Diagnosis

SQLSTATE	Description	Comments
08S01	Communication channel error	Communication channel error before the function processing is completed between the Altibase CLI driver and the database.
HY000	General error

Related Functions

SQLBindCol
SQLFetch
SQLStatistics
SQLTables

Example

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

if (SQLColumns(stmt,NULL, 0,
               (SQLCHAR *)USERNAME, SQL_NTS,
               (SQLCHAR *)"DEMO_META2", SQL_NTS,
               NULL, 0) != SQL_SUCCESS)
{
    execute_err(dbc, stmt, "SQLColumns");
    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, szTableName, STR_LEN,&cbTableName);
SQLBindCol(stmt, 4, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);
SQLBindCol(stmt, 5, SQL_C_SSHORT, &DataType, 0, &cbDataType);
SQLBindCol(stmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);
SQLBindCol(stmt, 7, SQL_C_SLONG, &ColumnSize, 0, &cbColumnSize);
SQLBindCol(stmt, 8, SQL_C_SLONG, &BufferLength, 0, &cbBufferLength);
SQLBindCol(stmt, 9, SQL_C_SSHORT, &DecimalDigits, 0, &cbDecimalDigits);
SQLBindCol(stmt, 10, SQL_C_SSHORT, &NumPrecRadix, 0, &cbNumPrecRadix);
SQLBindCol(stmt, 11, SQL_C_SSHORT, &Nullable, 0, &cbNullable);
SQLBindCol(stmt, 17, SQL_C_SLONG, &OrdinalPosition, 0, &cbOrdinalPosition);
SQLBindCol(stmt, 18, SQL_C_CHAR, szIsNullable, STR_LEN, &cbIsNullable);


/* fetches the next rowset of data from the result set and print to stdout */
printf("POSITION\tCOL_NAME\tDATA_TYPE\tPRECISION\tSCALE\tIsNullable\n");
printf("=======================================================================================\n");
while ( (rc = SQLFetch(stmt)) != SQL_NO_DATA)
{
    if ( rc == SQL_ERROR )
    {
        execute_err(dbc, stmt, "SQLColumns:SQLFetch");
        break;
    }
    printf("%-10d\t%-20s%-20s%-10d%-10d%s\n", OrdinalPosition,
            szColumnName, szTypeName, ColumnSize,
            DecimalDigits, szIsNullable);
}