Pervasive PSQL System Catalog Functions
The system catalog functions allow you to obtain database metadata from the data dictionary files (also known as the catalog). The system catalog functions can be used only in a FROM clause.
Pervasive PSQL can also return metadata information by calling ODBC catalog APIs or by using system stored procedures (see System Stored Procedures). Those two methods, however, do not store the metadata in a view that can be JOINed or UNIONed with other tables. To provide JOIN and UNION capability with other tables, the system catalog functions are required.
Note that some access methods, such as ADO.NET, require system catalog functions for entity support so that JOIN and UNION capabilities are available.
A temporary view schema for each system catalog function is created during SQLPrepare and data is stored in the view by calling a corresponding ODBC catalog API during SQLExecute.
The following table lists the available system catalog functions.
Table 109
System Catalog Functions
Pervasive PSQL Function
Returns
ODBC Function Comparison1
dbo.fSQLColumns
The list of columns and their corresponding information for a specified table, from the current database or the specified database
SQLColumns
dbo.fSQLForeignKeys
The foreign key information for the specified table in the current database
SQLForeignKeys
dbo.fSQLPrimaryKeys
The primary key information for the specified table, from the current database or the database specified
SQLPrimaryKeys
dbo.fSQLProcedures
The names of stored procedures in the current database or the specified database
SQLProcedures
dbo.fSQLProcedureColumns
The list of input and output parameters and the columns that make up the result set for the specified procedure
SQLProcedureColumns
dbo.fSQLSpecialColumns
Information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction.
SQLSpecialColumns
dbo.fSQLStatistics
Statistics about a single table and the list of indexes associated with the table, from the current database or the specified database
SQLStatistics
dbo.fSQLTables
A list of tables along with their corresponding information, from the current database or the specified database
SQLTables
1 Because the Pervasive PSQL catalog functions are based on the ODBC ones, you may refer to the ODBC documentation for additional information if you choose. This chapter provides enough information for you to understand and use the Pervasive PSQL catalog functions, but does not provide exhaustive technical details.
Return Status
Each system catalog function can return one of the following statuses depending on the execution results:
•
SQL_SUCCESS
•
SQL_SUCCESS_WITH_INFO
•
SQL_STILL_EXECUTING
•
SQL_ERROR
•
SQL_INVALID_HANDLE
Summary
The following characteristics apply to the system catalog functions:
•
They return metadata information.
•
They work in the same manner as views.
•
They can be referenced only in a FROM clause of a SELECT statement.
•
The parameters can be only in the form of constants or dynamic parameters (?).