SQLSpecialColumns

SQLSpecialColumns

SQLSpecialColumns retrieves the following information about columns within a specified table:

• The optimal set of columns that uniquely identifies a row in the table.
• Columns that are automatically updated when any value in the row is updated by a transaction.

SQLSpecialColumnsW() as a Unicode string supports same execution as SQLSpecialColumns().

Syntax

SQLRETURN  SQLSpecialColumns (
    SQLHSTMT        stmt,
    SQLSMALLINT     fColType,
    SQLCHAR *       szTableQual,
    SQLSMALLINT     cbTableQual,
    SQLCHAR *       szTableOwner,
    SQLSMALLINT     cbTableOwner,
    SQLCHAR *       szTableName,
    SQLSMALLINT     cbTableName,
    SQLSMALLINT     fScope,
    SQLSMALLINT     fNullable );

Arguments

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle
SQLSMALLINT	fColType	Input	Type of the column to be returned SQL_BEST_ROWID: Returns the optimal column that uniquely identify the rows in the table by searching column value(s).
SQLCHAR *	szTableQual	Input	Null will be always returned.
SQLSMALLINT	cbTableQual	Input	The length, in bytes, of *szTableQual
SQLCHAR *	szTableOwner	Input	Schema name
SQLSMALLINT	cbTableOwner	Input	The length, in bytes, of *szTableOwner
SQLCHAR *	szTableName	Input	The table name. Cannot be a NULL pointer.
SQLSMALLINT	cbTableName	Input	The length, in bytes, of *szTableName
SQLSMALLINT	fScope	Input	Not used
SQLSMALLINT	fNullable	Input	Not used (Does not allow NULL data because the corresponding columns are returned to the primary keys.)

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Description

When fColType is SQL_BEST_ROWID, SQLSpecialColumns() returns column(s) that uniquely identify each row in the table. These columns can be used in select-list or Where clause.

SQLSpecialColumns() is used to return these columns because SQLColumns() does not return the columns that are automatically updated when columns or rows are updated by the transaction.

If there are no columns that uniquely identifies each row in the table, SQLSpecialColumns() will return the row set without rows. To subsequently call SQLFetch() on the command syntax, return SQL_NO_DATA.

When the fact that the database does not support fColType, fScope, and fNullable arguments is stated, SQLSpecialColumns() will return the empty result set.

Name	No.	Data Type	Description
SCOPE	1	SMALLINT	SQL_SCOPE_SESSION value is fixed to 2.
COLUMN_NAME	2	VARCHAR (NOT NULL)	Column Name.As for the unnamed string, Altibase CLI driver returns the empty character string.
DATA_TYPE	3	SMALLINT (NOT NULL)	SQL data type
TYPE_NAME	4	VARCHAR (NOT NULL)	Character string representing the name of the data type corresponding to DATA_TYPE.
COLUMN_SIZE	5	INTEGER	Column Size. NULL will be returned when the string size is not proper.
BUFFER_LENGTH	6	INTEGER	The maximum byte storing the data
DECIMAL_DIGITS	7	SMALLINT	The NULL will return the data type that cannot apply the decimal points of the column and the decimal points.
PSEUDO_COLUMN	8	SMALLINT	Maximum digits of the character of binary datatype string. For other data types, NULL will be returned.

Diagnosis

SQLSTATE	Description	Comments
08S01	Communication line failure	Communication channel failure before the function processing is completed between the Altibase CLI driver and the database
HY009	Use an invalid pointer (null pointer)	szTableName is a NULL pointer.

Related Functions

SQLBindCol
SQLColumns
SQLFetch
SQLPrimaryKeys

Example

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

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

  SQLBindCol(stmt, 2, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);
  SQLBindCol(stmt, 3, SQL_C_SSHORT, &DataType, 0, &cbDataType);
  SQLBindCol(stmt, 4, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);
  SQLBindCol(stmt, 5, SQL_C_SLONG, &ColumnSize, 0, &cbColumnSize);
  SQLBindCol(stmt, 6, SQL_C_SLONG, &BufferLength, 0, &cbBufferLength);
  SQLBindCol(stmt, 7, SQL_C_SSHORT, &DecimalDigits, 0, &cbDecimalDigits);