Advantage System Tables and System Procedures

Sunday, December 14, 2003

With the release of Advantage Database Server version 7.0, several new system tables have been added to the server. These system tables allow the developer to obtain metadata (schema information) about a data dictionary using SQL statements.

Along with the new System Table support, Advantage version Database Server 7 introduced a set of built in stored procedures called System Procedures. These System Procedures can be used to manipulate the objects in a data dictionary.

Virtual Table Support

One of the new system tables is the IOTA table. The IOTA table contains a single row with one logical field whose value is NULL. The main purpose of the IOTA table is to provide an efficient method for evaluating an SQL expression on the server. Examples of using the IOTA table include:

To get the current date and time (timestamp) from the server:

SELECT NOW()FROM System.IOTA

Get the currently logged in user:

SELECT USER()FROM System.IOTA

Get a random number from the server:

SELECT RAND FROM System.IOTA

Dictionary Metadata

Obtaining information about your data dictionary (database) from within your application used to involve several Advantage API calls or the use of the TAdsDictionary Component in Delphi. With the addition of system tables, dictionary metadata can now be obtained with SQL statements accessible from nearly all Advantage clients.

Fifteen system tables have been added that contain information about the data dictionary (database) that the application is connected to. These tables include:

To view the information contained in these tables, use an SQL SELECT statement. For example:

SELECT*FROM System.Dictionary

This query will return a cursor containing one record containing information about the properties of the data dictionary. Some of the properties returned are the major and minor version numbers, the default table path, the temp table path, and if logins are required.

The contents of each system table are outlined in the Advantage Database Server Help file.

System Procedures

Prior to version 7.0, to change data dictionary properties or manipulate data dictionary objects (e.g., Tables, Referential Integrity, Advantage Extended Procedures, etc.), you had to use the data dictionary functions of the ACE API or the TAdsDictionary component in Delphi. Advantage Database Server 7.0 includes several built-in System Procedures that can be used to manipulate data dictionary objects using SQL. These system procedures are listed below: