Advantage Developer Zone

 
 
 

Advantage SQL Enhancements

Friday, November 14, 2003

With the release of Advantage Database Server version 7.0, several important improvements have been made to the SQL engine. These improvements fall into three main categories: performance enhancements, new grammar support, and new functionality.

Performance Enhancements

Sort operations on large datasets, i.e., 100,000+ records, have been dramatically improved. For example, GROUP BY statement performance may be more than 10 times faster on data sets of 1 million records or more. These enhancements can also improve the speed of UNION, DISTINCT and ORDER BY operations.

New Grammar Support

1) The Advantage SQL Engine now supports transaction syntax. This includes the following commands:

BEGIN TRAN[SACTION]
COMMIT WORK
ROLLBACK WORK
SET TRAN[SACTION] AUTOCOMMIT_ON | AUTOCOMMIT_OFF | EXPLICIT


This allows a developer to start a transaction using an SQL statement rather than using the transaction property of the connection object. Advantage supports one transaction per connection. Therefore, the BEGIN TRAN statement is ignored if a transaction is already active on the given connection.

SET TRAN sets the behavior of Data Manipulation Language (DML) (e.g. INSERT, UPDATE, DELETE, etc.) statements on the server. The AUTOCOMMIT feature allows a transaction to be automatically begun for a DML statement. With AUTOCOMMIT_ON, a transaction is begun and committed after a successful completion and is rolled back if an error occurs. With AUTOCOMMIT_OFF, the developer or user must explicitly commit or rollback the transaction. The EXPLICIT (default) option means a transaction must be explicitly begun, then committed or rolled back. As stated above, Advantage supports one active transaction per connection. Therefore, DML statements inside an active transaction will not begin their own transactions.

2) Data dictionary (database) permissions can be manipulated using the GRANT and REVOKE SQL commands. The GRANT command assigns rights to a user or user group, and the REVOKE command removes rights from the user or user group.

Each object has its own set of rights depending on the type of object. The following list contains the available rights:

Permission Description Applicable Objects
SELECT The ability to view the object Table, View or Column
INSERT The ability to add data to the object Table, View or Column
UPDATE The ability to change data contained in the object Table, View or Column
DELETE The ability to remove data from the object Table, View or Column
ACCESS Ability to use a link between data dictionaries. Link
EXECUTE The ability to invoke an Advantage Extended Procedure (AEP) defined in the dictionary AEP
INHERIT Sets the users effective permissions based on the permissions defined in the group(s) the user is a member of. Table, View, Column, AEP or Link
ALL Sets all applicable permissions for the object Table, View, Column, AEP or Link

The grantee can be either a user or user group. The syntax for these two SQL commands is below.

GRANT <permission> ON <object> TO <grantee>
REVOKE <permission> ON <object> FROM <grantee>


3) Two new CREATE statements have been added to the SQL Engine. CREATE DATABASE can be used to create a new Data Dictionary. CREATE TRIGGER can be used to create a trigger for a given table. The syntax for these statements is below.

CREATE DATABASE <database name> [ PASSWORD <adssys password> ] [ DESCRIPTION <database description> ] [ ENCRYPT <TRUE | FALSE> ]
CREATE TRIGGER <trigger-name> ON <table-name> <trigger-type> <trigger-event> <trigger-container> [trigger-options]


Advantage supports two types of Triggers: standard SQL scripted triggers and trigger containers. Trigger containers are a DLL, COM object, or .NET assembly that contains code to be executed as a trigger. Like an Advantage Extended Procedure, this allows triggers to perform a variety of complex operations. Example SQL statements for creating each type of trigger are below.

CREATE TRIGGER mytrigger ON orders AFTER DELETE
BEGIN
INSERT INTO backup_orders SELECT * FROM __old;
END

CREATE TRIGGER mytrigger ON orders INSTEAD OF UPDATE
FUNCTION MyFunction IN ASSEMBLY MyAssembly.MyClass PRIORITY 2


New Functionality
1) Multiple DML statements can be run as an SQL script. The statements must be delimited with a semi-colon ( ; ). For example:

CREATE TABLE "DEMO10" ( DEPTNUM Short, LASTNAME Char( 12 ), FIRSTNAME Char( 12 ), DOH Date, SALARIED Logical, EMPID Integer, PHONE Char( 8 ), DOB Date, EXTENSION Short, SOC_SEC_NU Char( 11 ), MARRIED Logical, DIVISION Char( 10 ), BRANCH Char( 16 ));
INSERT INTO "DEMO10" VALUES( 6, 'Coles', 'Fawn', '1987-01-14', True, 1, '345-4275', '1935-07-02', 913, '020-02-9169', False, 'R and D', 'Los Angeles' );
INSERT INTO "DEMO10" VALUES( 12, 'Coles', 'Becky', '1929-03-01', False, 2, '349-7117', '1938-02-02', 1137, '641-51-5371', False, 'Production', 'New York' );
INSERT INTO "DEMO10" VALUES( 5, 'Frasier', 'Mona', '1989-10-02', False, 3, '345-5265', '1968-01-06', 1763, '515-21-5411', True, 'R and D', 'Chicago' );
CREATE INDEX "EMPID" on DEMO10( EMPID );

This entire statement can be sent to the server from any Advantage client, and it will be processed entirely on the server. You cannot return cursors from an SQL script.

2) The CAST scalar function has been added to the Advantage SQL engine. CAST allows for precision data type conversions. There is an optional precision and scale value. The syntax is as follows:

CAST ( expr AS data-type [( precision [, scale ] ) ] )


3) The RAND scalar function has been improved and now exhibits a better “random” behavior.

For additional information on new features in Advantage v7.0, please refer to the Advantage Readme file included with your Advantage product.