Some drivers offer catalog functions. A catalog function allows the application to obtain information about the structure of the data source. For example, the subroutine f90SQLColumns returns a list of the columns in a given table of the data source. Catalog functions are extremely useful for applications that need to support a certain degree of generality. The results of catalog requests are always returned as recordsets that must be fetched the same way as the resulting record set from a common SQL statement. f90SQL includes some added functionality that facilitates the use of catalog functions. The columns of record sets resulting from catalog functions are fixed. f90SQL declares structures to contain these columns (f90SQL structures are stored in the module F90SQLSTRUCTURE.F90. To use these structures you must include the statement "use f90SQLStructures" in your application). In addition, f90SQL includes several subroutines that allow you to bind all the fields in these structures to all the columns in the resulting recordset. This is done in a signle operation (see Chapter 10 on Catalog Subroutines). The code in Example 4.6 shows one of such examples. The subroutine f90SQLBindCol, binds all the fields of a f90SQL_TYPE_INFO_STRUCT structure to all the columns in the recordset that result from a call to the subroutine f90SQLGetTypeInfo.
The ODBC-API contains a rich set of catalog functions. Table 5.1 summarizes some of them (using their f90SQL names).
Table 5.1. ODBC catalog functions in f90SQL.
| Subroutine | Operation |
| f90SQLColumns | Returns a list of columns and associated privileges for one or more tables. Returns the list of column names in specified tables. |
| f90SQLForeignKeys | Returns a list of column names that make up foreign keys, if they exist for a specified table. |
| f90SQLPrimaryKeys | Returns the list of column names that make up the primary key for a table. |
| f90SQLProcedureColumns | Returns the list of input and output parameters, as well as the columns that make up the result set for the specified procedures. |
| f90SQLProcedures | Returns the list of procedure names stored in a specific data source. |
| f90SQLSpecialColumns | Returns information about the optimal set of columns that uniquely identify a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction. |
| f90SQLStatistics | Returns statistics about a single table and the list of indexes associated with the table. |
| f90SQLTablePrivileges | Returns a list of tables and the privileges associated with each table. |
| f90SQLTables | Returns the list of table names stored in a specific data source. |
| f90SQLGetTypeInfo | Returns a list of the SQL data types supported by the data source. These data types are generally used in CREATE TABLE and ALTER TABLE statements. |
It is important to keep in mind that not all drivers implement all catalog functions. It is always a good idea to check if a driver does so by calling subroutine f90SQLGetFunctions.
In the Example 5.3, we connect to a data source selected by the user and call the subroutine f90SQLTables to obtain a list of the tables in the data source. Before calling this subroutine, however, we make sure that the data source supports the subroutine by calling f90SQLGetFunctions.
A typical call to f90SQLGetFunctions is done as follows (check the reference section of this manual for a complete description.):
| call f90SQLGetFunctions | (ConnectionHandle, FunctionId, Supported, iRet) |
Where ConnectionHandle is a connection handle, FunctionId is an identification value for the function, and Supported is a value returned by the driver indicating if the function is supported or not. f90SQL has constant definitions for all the function IDs in ODBC. For example, the ID for the subroutine f90SQLTables is stored in the constant SQL_API_SQLTABLES. The ID for f90SQLColumns is stored in the constant SQL_API_SQLCOLUMNS, and so on. If we want to know whether the driver supports the subroutine f90SQLColumns, we pass the constant SQL_API_SQLCOLUMNS in the argument FunctionId to f90SQLGetFunctions. If the subroutine is supported, the driver returns SQL_TRUE in parameter Supported:
|
Another important point to note in Example 5.3 is the use of the catalog function f90SQLTables. A typical call to this subroutine takes place as follows (Check the reference section of this manual for a complete description.):
| call f90SQLTables | (StatementHandle, CatalogName, SchemaName, TableName, TableType, iRet) |
Where StatementHandle is a statement handle, CatalogName is the name of a catalog (or a wildcard for catalog names), SchemaName is the name of a schema (or a wildcard for schema names), and TableName is the name of a table (or a wildcard for table names). Argument TableType is a filter for the type of tables that will be returned in the list. For example, if you only want views, then you pass "VIEW" in argument TableType. If you want information for all Catalogs and Schemas, you can pass a NULL string for these arguments. This can be done by passing the constant f90SQL_NULL_CHAR. Note that catalog subroutines differentiate between null strings and empty string ('', in Fortran). An empty string retrieves all Catalogs, Schemas or Tables without a name. Also note that some drivers (among them MS-Access) do not support Catalog or Schema names. So, you must pass a null string as parameters for these drivers.
To finish with Example 5.3, note that we used some of the additional functionality of f90SQL by defining the variable Tables as a structure of type f90SQL_TABLES_INFO_STRUCT, and then calling subroutine f90SQLBindCol passing Tables as an argument. This subroutine binds all the fields in the structure at once. In this way, your application does not have to bind them one by one. For more information on this subroutine and the structure f90SQL_TABLES_INFO_STRUCT, see the reference section of this manual.
|