SQLTables

SQLTables

SQLTables returns the list of table, catalog, or schema names, and table types, stored in a specific data source. The driver returns the information as a result set.

SQLTablesW() as a Unicode string supports same execution as SQLTables().

Syntax

SQLRETURN  SQLTables (
    SQLHSTMT        stmt,
    SQLCHAR *       cName,
    SQLSMALLINT     cNameLength,
    SQLCHAR *       sName,
    SQLSMALLINT     sNameLength,
    SQLCHAR *       tName,
    SQLSMALLINT     tNameLength,
    SQLCHAR *       tableType,
    SQLSMALLINT     tableTypeLength);

Argument

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle for the searched result
SQLCHAR *	cName	Input	Catalog Name
SQLSMALLINT	cNameLength	Input	The length, in bytes, of *cName
SQLCHAR *	sName	Input	Schema Name
SQLSMALLINT	sNameLength	Input	The length, in bytes, of *sName
SQLCHAR *	tName	Input	Table name
SQLSMALLINT	tNameLength	Input	The length, in bytes, of *tName
SQLCHAR *	tableType	Input	Table type list to compare
SQLSMALLINT	tableTypeLength	Input	The length, in bytes, of *tableType

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Description

SQLTables() displays all table information within the required range. Some users may or may not have the SELECT privilege to any of these tables.

The application must be able to handle a situation where the user selects a table for which SELECT privileges are not granted.

The following special syntaxes are defined for cName, sName, tName, and tableType of SQLTables() to support the catalogs, schema, and the list of table types:

If sName is SQL_ALL_SCHEMAS and cName and tName are empty character strings, the result set will include the schema list valid for the database. (All columns expect that TABLE_SCHEM columns will include NULL data.)

If tableType is SQL_ALL_TABLE_TYPES and cName, sName, and tName are empty character strings, the result set will include the list of table types valid for the database. (All columns except TABLE_TYPE column will include the NULL data.)

An application must alwas specify the tableType in capital letters. One of the following can be specified: SQL_ALL_TABLE_TYPES, "SYSTEM TABLE", "TABLE", "VIEW", "QUEUE", "SYNONYM", "MATERIALIZED VIEW".

SQLTables() returns the data in the standard result set format sorted by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME.

The following table lists the columns in the result set.

Column Name	No.	Data Type	Description
TABLE_CAT	1	VARCHAR	Null will be always returned.
TABLE_SCHEM	2	VARCHAR	Schema name including TABLE_Name. NULL if not applicable to the database
TABLE_NAME	3	VARCHAR (NOT NULL)	Table Name
TABLE_TYPE	4	VARCHAR	Table type name. One of the following types are returned. - SQL_ALL_TABLE_TYPES - 'SYSTEM TABLE' - 'TABLE' - 'VIEW' - 'QUEUE' - 'SYNONYM' - 'MATERIALIZED VIEW'
REMARKS	5	VARCHAR	Not used

[Table 2‑7] Columns Returned by SQLTables()

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

Related Functions

SQLBindCol
SQLColumns
SQLFetch
SQLStatistics

Example

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

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

    if (SQLBindCol(stmt, 2, SQL_C_CHAR,
                   schem, sizeof(schem), &schem_ind) != SQL_SUCCESS)
    {
        execute_err(dbc, stmt, "SQLBindCol");
        SQLFreeStmt(stmt, SQL_DROP);
        return SQL_ERROR;
    }

    if (SQLBindCol(stmt, 3, SQL_C_CHAR,
                   name, sizeof(name), &name_ind) != SQL_SUCCESS)
    {
        execute_err(dbc, stmt, "SQLBindCol");
        SQLFreeStmt(stmt, SQL_DROP);
        return SQL_ERROR;
    }

    if (SQLBindCol(stmt, 4, SQL_C_CHAR,
                   type, sizeof(type), &type_ind) != SQL_SUCCESS)
    {
        execute_err(dbc, stmt, "SQLBindCol");
        SQLFreeStmt(stmt, SQL_DROP);
        return SQL_ERROR;
    }
while ( (rc = SQLFetch(stmt)) != SQL_NO_DATA)
    {
        if ( rc == SQL_ERROR )
        {
            execute_err(dbc, stmt, "SQLFetch");
            break;
        }
        printf("%-40s%-40s%s\n", schem, name, type);
    }