Advantage Developer Zone

 
 
 

Advantage System Tables and System Procedures

Sunday, December 14, 2003

Advantage System Tables and System Procedures
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:

Columns
Dictionary
Indexes
Index Files
Links Objects
Permissions
Relations
Stored Procedures
Tables
Triggers
User Group Members
User Groups
Users
Views

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:

sp_AddIndexFileToDatabase
sp_AddTableToDatabase
sp_AddUserToGroup
sp_CreateGroup
sp_CreateLink
sp_CreateReferentialIntegrity
sp_CreateUser
sp_DropGroup
sp_DropLink
sp_DropReferentialIntegrity
sp_DropUser
sp_ModifyDatabase
sp_ModifyFieldProperty
sp_ModifyFieldProperty
sp_ModifyGroupProperty
sp_ModifyUserProperty
sp_RemoveUserFromGroup

System Procedures are called using an SQL EXECUTE PROCEDURE query. For example to change the comment associated with a data dictionary use the sp_ModifyDatabase System Procedure.

EXECUTE PROCEDURE sp_ModifyDatabase( 'COMMENT',
'This is my data dictionary' );

See the Advantage Database Server documentation for all the details on this and the other System Procedures available in Advantage Database Server version 7.