Advantage Database Server: The Official Guide

While you can create a data dictionary, as well as many of the objects they contain, with Advantage SQL, there are limits to what you can do. For example, you can create a data dictionary, and you can create a table in a data dictionary, but there are no commands in Advantage SQL to add a free table to a data dictionary.

The reason for this relates to standards. For the most part, Advantage SQL is designed to conform to most of the ANSI (American National Standards Institute) SQL/92 standard. No database vendor complies 100 percent with this standard, but all try to support most of it, and Advantage is no different.

The problem with conforming to standards is that they discourage customization, such as the introduction of new base keywords. While CREATE, GRANT, and INVOKE are part of the SQL/92 specification, there are no base keywords such as ADD and REMOVE.

In order to maintain a close conformance with ANSI SQL/92 and still be able to handle the many necessary data dictionary maintenance tasks through SQL, Advantage chose to implement these tasks by using stored procedures built into ADS and ALS. In ADS version 7.0, there are 17 data dictionary–related system stored procedures. The names of these system stored procedures are listed in Table 11-11.

Each of these stored procedures requires one or more input parameters, and can only be executed from an administrative (ADSSYS) connection (with the exception of sp_ModifyUserProperty, which can also be executed from a user connection). The operations performed by these stored procedures are applied to the data dictionary that the administrative account is connected to.

The following sections describe how to use the stored procedures listed in Table 11-11. These sections are divided into six functional areas, including changing database properties, adding free tables and indexes to a data dictionary, creating and dropping referential integrity definitions, creating and dropping links, changing table and field properties, and creating and modifying users and groups.

In addition, many of these sections contain tables that list the valid values that you can pass to one or more of the stored procedures. These tables and values are based on ADS 7.0, although later versions of ADS may include additional valid values. If you are working with a later version of the server, and you cannot find a value that you expect to find, refer to the ADS documentation.

Note also that no attempt is made in these sections to describe the features being affected by the stored procedures. For example, while the section on creating and dropping data dictionary links describes how to perform those tasks, it does not describe what a data dictionary link is, nor does it go into detail about the various link options. ADS features affected by these stored procedures are described elsewhere in this book. For details on the features being described, please refer to the appropriate chapter of this book.

But first, let’s discuss how to create a well-formed stored procedure invocation using SQL.

Executing Stored Procedures

You execute system stored procedures using the SQL EXECUTE PROCEDURE keywords. You follow these keywords with the name of the stored procedure, and the stored procedure’s input parameters enclosed in parentheses. For example, the following query sets the password of the data dictionary’s administrative account to password:

EXECUTE PROCEDURE sp_ModifyDatabase('ADMIN_PASSWORD', 'password')

If a stored procedure includes an input parameter, but supplying a value for that parameter is optional, you can simply pass the value NULL. This is demonstrated in the following stored procedure invocation, which creates a new group but does not set the group’s description:

EXECUTE PROCEDURE sp_CreateGroup('accounting', NULL)

Changing Database Properties

The CREATE DATABASE SQL statement permits you to create a new data dictionary, as well as set several of its properties, including administrative password, encryption, and description. If you want to set any of the other properties, or modify one of the properties other than data dictionary encryption, you call the sp_ModifyDatabase system stored procedure. The following is the syntax of this stored procedure:

sp_ModifyDatabase( Property char(200), Value memo )

The first parameter is a string that identifies the property that you want to change. The valid values for this parameter are listed in Table 11-12.

In the second parameter, you pass the value you want to set for the property identified in the first parameter. This value will always be a string expression, even though the value you are setting may be a Boolean or integer value. For example, the following statement enables Internet access for the connected data dictionary:

EXECUTE PROCEDURE sp_ModifyDatabase('ENABLE_INTERNET', 'True')

Similarly, the next statement sets the data dictionary’s major version number to 2:

EXECUTE PROCEDURE sp_ModifyDatabase('VERSION_MAJOR', '2')

Adding Free Tables and Indexes

ADS provides a system stored procedure for adding a free table to a data dictionary. It also provides a procedure to add a nonstructural index to a data dictionary–bound table, which makes that index an auto-open index. These system stored procedures are described in this section.

Adding Free Tables

You use the sp_AddTableToDatabase stored procedure to add a free table to the connected data dictionary. The following is the syntax of this procedure:

sp_AddTableToDatabase( TableName char(200), TablePath char(260), TableType integer, CharType integer, IndexFiles memo, Comment memo )

The first parameter is the name of the table using any valid SQL table reference. This name does not have to match the name of the table, though most developers prefer that it match. The second parameter is the table’s filename. If you omit the path, the default path of the data dictionary will be assumed.

The third parameter is an integer that identifies the type of table, and the fourth parameter is an integer that identifies the type of character set. The valid integers for TableType and CharType are listed in Tables 11-13 and 11-14, respectively.

The fifth parameter is a semicolon-separated list of any nonstructural index files that should be added to the data dictionary along with the table. You should include the index file paths if the files are not in the data dictionary’s default directory. The final parameter is an optional description for the table.

The following is an example call to sp_AddTableToDatabase. In this example, the CUST.ADT table is assumed to be a free table located in the data dictionary’s default directory, and no additional index files are added:

EXECUTE PROCEDURE sp_AddTableToDatabase( 'CUST', 'CUST.ADT', 3, 1, NULL, 'A sample temporary table' )

Adding Nonstructural Indexes to Tables

You use the sp_AddIndexFileToDatabase stored procedure to add an existing, nonstructural index file to a data dictionary–bound table. The following is the syntax of this procedure:

sp_AddIndexFileToDatabase( TableName char(200), IndexFilePath char(515), Comment memo )

The first parameter is the name of the table to which you want to add the nonstructural index file as it appears in the Advantage Database Manager’s tree view. The second parameter is the name of the index file, which should include path information if it is not located in the data dictionary’s default directory. The third parameter is an optional description of the index.

The following example demonstrates how you can add the index file named TEMPIDX.ADI to the data dictionary, associating it with the table named TEMP. Since this file does not exist, you cannot actually run this example unless you create a free table named TEMP, as well as a nonstructural index for TEMP named TEMPIDX.ADI:

EXECUTE PROCEDURE sp_AddIndexFileToDatabase( 'TEMP', 'TEMPIDX.ADI', NULL )

Creating and Dropping RI Definitions

There are two system stored procedures that you can use to create and drop referential integrity definitions. These stored procedures are discussed in this section.

Creating RI Definitions

You call the sp_CreateReferentialIntegrity stored procedure to create a new referential integrity definition. The following is the syntax of this procedure:

sp_CreateReferentialIntegrity( Name char(200), PrimaryTable char(200), ForeignTable char(200), ForeignKey char(200), UpdateRule shortint, DeleteRule shortint, FailTable char(515), PrimaryKeyError memo, CascadeError memo )

The first parameter of this stored procedure is the name of the RI definition. The second and third parameters are the names of tables bound to the connected data dictionary—the parent and child table names, respectively.

The fourth parameter is the name of the child table’s foreign key index order. Note that you do not pass the name of the parent table’s primary key index order. ADS uses the primary key index property of the parent table to determine this value.

The fifth and sixth parameters define the update rule and the delete rule, respectively. The valid values for these parameters are listed in Table 11-15.

The seventh parameter is the name of the optional fail table. If you provide a fail table name, the named fail table is created, overwriting a previous table of that name, if it existed, and the violating records are written to the table. If you pass a value of NULL in this parameter, violating records are deleted.

The eighth and ninth parameters are the optional error messages that you can associate with primary key and cascading errors.

In order to execute this stored procedure, ADS must be able to temporarily obtain exclusive access to all tables involved in this definition. If any of these tables are already open by another user, sp_CreateReferentialIntegrity will fail.

The following statement demonstrates how to replicate the Employee Sales RI definition described in Chapter 5. If you want to test this stored procedure against your DemoDictionary database, you need to first manually delete the Employee Sales RI definition, or execute the sp_DropReferentialIntegrity stored procedure, passing to it the string 'Employee Sales'. (An example of how to drop this definition using sp_DropReferentialIntegrity is shown in the next section.)

EXECUTE PROCEDURE sp_CreateReferentialIntegrity('Employee Sales', 'EMPLOYEE', 'INVOICE', 'Employee ID', 1, 2, 'c:\Program Files\Extended Systems\Advantage\ADSbook\RIFAIL.ADT', 'You must provide an Employee ID', NULL)

Dropping RI Definitions

You drop an RI definition by calling sp_DropReferentialIntegrity, passing to it the name of the RI definition you want to drop. The following is the syntax of this stored procedure:

sp_DropReferentialIntegrity( Name char(200) )

In order to execute this stored procedure, ADS must be able to temporarily obtain exclusive access to all tables involved in this definition. If any of these tables are already open by another user, sp_DropReferentialIntegrity will fail.

The following statement demonstrates how to drop the Employee Sales RI definition. If you actually drop this definition, please be sure to restore it, either manually using the Advantage Data Architect or by executing the sp_CreateReferentialIntegrity statement provided in the preceding section.

EXECUTE PROCEDURE sp_DropReferentialIntegrity( 'Employee Sales' )

Creating and Dropping Links

ADS provides two system stored procedures for working with data dictionary links. The first permits you to create links and the second permits you to drop links. These procedures are covered in this section.

Creating a Data Dictionary Link

You create a data dictionary link by calling the sp_CreateLink stored procedure. This procedure has the following syntax:

sp_CreateLink( Name char(200), Dictionary char(515), Global logical, StaticPath logical, AuthenticateActiveUser logical, UserName char(50), Password char(20) )

The first parameter is the name of the link you are creating, and the second parameter is the name of the data dictionary that the link refers to. If the data dictionary is not in the current data dictionary’s default directory, provide the path to the link dictionary.

If you want this link definition to remain in the data dictionary, making it potentially available to all users (so long as they are granted access rights to the link), set the third parameter, Global, to True. If you pass a Boolean False in the third parameter, the link is temporary and can only be used by the connection that created it.

If you want to use the connected user’s user name and password to authenticate to the linked data dictionary, pass a Boolean True in the fourth parameter. If you pass a Boolean False in this fourth parameter, you must set a value to the fifth and sixth parameters, which hold the user name and password to authenticate with, respectively. If you pass a Boolean False in the fourth parameter, pass NULL in the fifth and sixth parameters. The following example demonstrates how to add a data dictionary link to the connected data dictionary:

EXECUTE PROCEDURE sp_CreateLink('NEWLink', 'DATA\GENERAL.ADD', True, False, True, NULL, NULL)

In this example, a link is created to a data dictionary named GENERAL.ADD, which is located in the DATA directory beneath the default directory of the connected data dictionary (a relative path was given here). This link is a global link that does not use a static path, and which authenticates with the user name and password of the current connection.

Dropping a Data Dictionary Link

You drop a link using the sp_DropLink stored procedure. The following is the syntax of this procedure:

sp_DropLink(NameOrPath char(200), Global logical)

This stored procedure requires two parameters. The first parameter is either the name of the link or a path to which the link refers. If you enter a path, and more than one link is associated with that path, the first link that is found will be dropped. If the link being dropped is a global link, set the second parameter to True. The following is an example of a call to drop the link created in the preceding example:

EXECUTE PROCEDURE sp_DropLink ('NEWLink', True)

Changing Table and Field Properties

ADS provides you with two system stored procedures for setting or modifying table properties, one for the table-level properties and the other to modify properties of the table’s fields.

Modifying Table Properties

You set or modify the properties of a table using the sp_ModifyTableProperty stored procedure. The following is the syntax of this procedure:

sp_ModifyTableProperty(TableName char(200), Property char(200), Value memo, ValidationOption char(25), FailTable char(515))

The first parameter of this stored procedure is the name of the table and the second parameter is the property that you want to set or modify. Table 11-16 contains a list of the valid table property strings.

Use the third parameter to assign a value to the property you specify in the second parameter. Always pass this value as a string. ADS will convert the string to the appropriate data type.

If you are changing the table’s record-level constraint (TABLE_VALIDATION_ EXPR), use the fourth parameter to specify how ADS validates the data. The valid validation option codes are listed in Table 11-17.

If you are changing the table’s record-level constraint, and you pass the string APPEND_FAIL or WRITE_FAIL in the fourth parameter, supply the name of the fail table in the fifth parameter. The following example demonstrates how to set the auto-create property of the table named ITEMS to True:

EXECUTE PROCEDURE sp_ModifyTableProperty( 'ITEMS', 'TABLE_AUTO_CREATE', 'True', NULL, NULL)

Modifying Field Properties

You use the stored procedure named sp_ModifyFieldProperty to change properties for individual fields in a table. This procedure has the following syntax:

sp_ModifyFieldProperty(TableName char(200), FieldName char(200), Property char(200), Value memo, ValidationOption char(25), FailTable char(515))

The first parameter of this stored procedure is the name of the table to which the field belongs, and the second is the name of the field whose property you want to change. The third parameter is the name of the property. The valid field property codes are listed in Table 11-18.

The fourth parameter is the value that you want to assign to the property specified in the third parameter. You always pass this value as a string, regardless of the data type of the property you are modifying.

If you are changing the table’s FIELD_CAN_BE_NULL, FIELD_MIN_VALUE, or FIELD_MAX_VALUE properties, you must use the fifth parameter to define what you want ADS to do if data currently in the table fails the validation. The values that you can use in this parameter are listed in Table 11-17. Just as you do with table properties, if you need to set the fifth parameter to APPEND_FAIL or WRITE_FAIL, you must supply a fail table name in the sixth parameter.

The following SQL statement demonstrates how you can set the FIELD_CAN_ BE_NULL property for the Invoice No field of the INVOICE table:

EXECUTE PROCEDURE sp_ModifyFieldProperty( 'INVOICE', 'Invoice No', 'FIELD_CAN_BE_NULL', 'False', 'WRITE_FAIL', 'FAILTAB.ADT')

Creating and Modifying Users and Groups

There are eight system stored procedures that you use to create, modify, and drop users and groups. These stored procedures are discussed in this section.

Creating Groups

You create a group in a data dictionary using the sp_CreateGroup stored procedure. This stored procedure has the following syntax:

sp_CreateGroup(GroupName char(100), Comment memo)

This stored procedure takes two parameters. Pass the name of the group you are creating in the first parameter and an optional group description in the second parameter. The following example demonstrates the creation of a group named POWERUSER:

EXECUTE PROCEDURE sp_CreateGroup( 'POWERUSER', 'Group given power user privileges')

Creating Users

You create a new user account by calling the sp_CreateUser stored procedure. The syntax of this procedure is shown here:

sp_CreateUser(UserName char(50), Password char(20), Comment memo)

As you can see, this stored procedure takes three parameters. You pass the new user name in the first parameter, and pass the user’s password in the second parameter. Note that the password is case sensitive. You pass an optional description of the user in the third parameter. The following example demonstrates the creation of a new user named SARAH:

EXECUTE PROCEDURE sp_CreateUser( 'SARAH', 'password', 'Sarah is a power user')

Adding a User to a Group

You invoke the sp_AddUserToGroup stored procedure to add a user to a group. The following is the syntax of sp_AddUserToGroup:

sp_AddUserToGroup(UserName char(50), GroupName char(100))

This simple procedure takes two parameters, the user you are adding to the group and the name of the group. The following statement demonstrates how to add the user SARAH to the group POWERUSER:

EXECUTE PROCEDURE sp_AddUserToGroup( 'SARAH', 'POWERUSER')

Modifying Group Properties

You call the sp_ModifyGroupProperty stored procedure to change a property of a group. This procedure has the following syntax:

sp_ModifyGroupProperty(GroupName char(100), Property char(200), Value memo)

The first parameter of this stored procedure is the name of the group, the second is the property you want to change, and the third is the value you want to assign to this property. Groups have only one property, COMMENT. The following example demonstrates how to change the COMMENT property for the group POWERUSER:

EXECUTE PROCEDURE sp_ModifyGroupProperty( 'POWERUSER', 'COMMENT', 'The power user group')

Modifying User Properties

Call the sp_ModifyUserProperty stored procedure to change a property for a user. The syntax of the procedure is shown here:

sp_ModifyUserProperty(UserName char(50), Property char(200), Value memo)

Like sp_ModifyGroupProperty, this stored procedure takes three parameters, the user whose property you want to change, the property being changed, and the value you are setting the property to. The valid values for the second parameter, Property, are listed in Table 11-19. The value you pass in the third parameter is a string, even when setting the Boolean ENABLE_INTERNET property.

The following statement sets the password for the user SARAH:

EXECUTE PROCEDURE sp_ModifyUserProperty( 'SARAH', 'USER_PASSWORD', 'password')

There is only one property, USER_PASSWORD, that you can change with this stored procedure for the ADSSYS administrative account. From a user account, you can invoke sp_ModifyUserProperty to change that user’s password.

Removing a User from a Group

Call sp_RemoveUserFromGroup to remove a user from a group. As shown in the following syntax listing, this stored procedure takes two parameters; the user name and the group this user should be removed from:

sp_RemoveUserFromGroup(UserName char(50), GroupName char(100))

The following example demonstrates how to remove the user SARAH from the POWERUSER group:

EXECUTE PROCEDURE sp_RemoveUserFromGroup('SARAH', 'POWERUSER')

Removing Users

Remove a user from the data dictionary to which you are connected using the sp_DropUser stored procedure. This procedure takes a single parameter: the name of the user to remove. Following is the syntax of this procedure:

sp_DropUser(UserName char(50))

If you drop a user while that user is connected, that user will remain connected until their connection terminates. After that, the user will no longer be able to log in with that user name.

If the data dictionary to which this user belonged does not check user rights, a connected, removed user will continue to have access to data dictionary objects. If the data dictionary does check user rights, a connected, removed user is prevented from performing any data access operations that require rights be checked.

Removing Groups

You remove a group from a data dictionary using the sp_DropGroup procedure, which takes the name of the group to drop as its sole parameter. The following is the syntax of this stored procedure:

sp_DropGroup(GroupName char(100))

Any users belonging to a group that is dropped are removed from that group, and any additional rights conveyed by that group are revoked. The following statement removes the POWERUSER group from the connected data dictionary:

EXECUTE PROCEDURE sp_DropGroup('POWERUSER')

The next five chapters describe how to work with the Advantage Database Server from various development environments.