SQLStatistics

SQLStatistics

SQLStatistics retrieves a list of statistics about a single table and the indexes associated with the table. The driver returns the information as a result set.

SQLStatisticsW() as a Unicode string supports same execution as SQLStatistics().

Syntax

SQLRETURN  SQLStatistics (
    SQLHSTMT        stmt,
    SQLCHAR *       cName,
    SQLSMALLINT     cNameLength,
    SQLCHAR *       sName,
    SQLSMALLINT     sNameLength,
    SQLCHAR *       tName,
    SQLSMALLINT     tNameLength,
    SQLSMALLINT     unique,
    SQLSMALLINT     reserved );

Arguments

Data Type	Argument	In/Ouput	Description
SQLHSTMT	stmt	Input	Statement handle
SQLCHAR *	cName	Input	Catalog name
SQLSMALLINT	cNameLength	Input	The length, in bytes, of *cName
SQLCHAR *	sName	Input	Schema nam
SQLSMALLINT	sNameLength	Input	The length, in bytes, of *sName
SQLCHAR *	tName	Input	The table name. Cannot be a NULL pointer.
SQLSMALLINT	tNameLength	Input	The length, in bytes, of *tName
SQLSMALLINT	unique	Input	index type:SQL_INDEX_UNIQUE or SQL_INDEX_ALL
SQLSMALLINT	reserved	Input	Not used

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Description

Returns information about a single table as a standard result set, ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, and ORDINAL_POSITION. The result set combines the statistical information of the table and the data for index.

Column Name	No.	Data Type	Description
TABLE_CAT	1	VARCHAR	Null will be always returned.
TABLE_SCHEM	2	VARCHAR	Schema name including TABLE_NAME
TABLE_NAME	3	VARCHAR (NOT NULL)	Table name of the table to which the statistic or index applies.
NON_UNIQUE	4	SMALLINT	Indicates whether the index prohibits duplicate values: . - SQL_TRUE if the index values can be nonunique. . - SQL_FALSE if the index values must be unique. NULL is returned if TYPE is SQL_TABLE_STAT.
INDEX_QUALIFIER	5	VARCHAR	An empty character string is returned.
INDEX_NAME	6	VARCHAR	Index name; NULL data will be returned when the TYPE is SQL_TABLE_STAT.
TYPE	7	SMALLINT (NOT NULL)	TYPE of the returned data. - SQL_TABLE_STAT: Indicates whether the statistical data for the table are displayed. - SQL_INDEX_BTREE: Indicates B-Tree index. SQL_INDEX_HASHED: Indicates the hashed index. - SQL_INDEX_OTHER : Indicates other index types. (T-Tree)
ORDINAL_POSITION	8	SMALLINT	Position of the columns in order in the index. (Starting with 1) NULL data will be returned when the TYPE is SQL_TABLE_STAT
COLUMN_NAME	9	VARCHAR	Column name. If the column is expression (e.g. SALARY + BENEFITS), the expression will be returned. If the expression cannot be determined, the empty character string will be returned. Null data will be returned when the TYPE is SQL_TABLE_STAT.
ASC_OR_DESC	10	CHAR(1)	Column sorting order. - A: In ascending orders, - D In descending orders, If the database does not support the sorting orders and the TYPE is SQL_TABLE_STAT, NULL data will be returned
CARDINALITY	11	INTEGER	Table or index order. When the TYPE is SQL_TABLE_STAT, the row number will be returned. If the TYPE is not SQL_TABLE_STAT, the unique number of the index will be returned. If the database does not support the TYPE, NULL data will be returned.
PAGES	12	INTEGER	Page number used to store data in the index or table. - If the TYPE is SQL_TABLE_STAT, this column will include the number of pages used to define the table. - If the TYPE is not SQL_TABLE_STAT, this column contains the number of pages used to store the index. - If the database does not support the TYPE, NULL data returns.
FILTER_CONDITION	13	VARCHAR	In case of the filtered index, this column will have the filtering conditions. 30000; filter" conditions cannot be decided, this column is an empty character string. Null In case the filter has not been faltered. When it is not possible to decide whether the index has been filtered or the TYPE is SQL_TABLE_STAT.

[Table 2‑5] Column returned by SQLStatistics()

Diagnosis

SQLSTATE	Description	Comments
08S01	Communication channel error	Communication channel failure before the function processing is completed between the Altibase CLI driver and the database
HY000	General error
HY009	Use an invalid pointer (null pointer)	tName is a NULL pointer cName is a NULL pointer sName is a NULL pointer

Related Functions

SQLBindCol
SQLFetch
SQLPrimaryKeys

Example

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

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

    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_SSHORT, &NonUnique, 0, &cbNonUnique);
    SQLBindCol(stmt, 6, SQL_C_CHAR, szIndexName, STR_LEN, &cbIndexName);
    SQLBindCol(stmt, 8, SQL_C_SSHORT, &OrdinalPosition, 0, &cbOrdinalPosition);
    SQLBindCol(stmt, 9, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);
    SQLBindCol(stmt, 10, SQL_C_CHAR, szAscDesc, 2, &cbAscDesc);