Using SQL Persistent Stored Modules in Triggers and Stored Procedures
Monday, May 22, 2006 With the release of Advantage version 8.0, a subset of the ANSI 2003 SQL PSM standard has been implemented. This allows developers to write triggers and stored procedures as SQL scripts. Triggers and stored procedures written as scripts can run on all supported server platforms, including Novell NetWare, which in the past could only run simple trigger scripts.
What Is Supported?
The Advantage SQL engine supports a subset of the ANSI SQL 2003 PSM (Persistent Stored Module) scripting language standard including (but not limited to) variables, cursors, branches, loops, and exception handling. Please see the “SQL PSM” book in the Advantage help file for details.
How Would I Use It?
In addition to being useful when utilized directly from your applications, SQL scripts are most commonly used in triggers and stored procedures. One benefit of using scripts is that these objects do not have to be compiled outside of your data dictionary, like Advantage Extended Procedures (AEPs) and DLL triggers do. This also allows for support for triggers and stored procedures on Novell NetWare, which in the past could only execute simple trigger scripts.
The use of the IF statement allows for conditional execution. This example trigger writes the username into a field when records are updated. If the current user is ADSSYS, we want to write the string “SYSTEM” as the user.
IF USER() = 'adssys' THEN
UPDATE Employee SET "Modified By" = 'SYSTEM'
WHERE "Employee Number" =
(SELECT "Employee Number" FROM __new);
ELSE
UPDATE Employee SET "Modified By" = USER()
WHERE "Employee Number" =
(SELECT "Employee Number" FROM __new);
ENDIF;
Stored procedures can also be written using the SQL scripting language. These procedures are stored within the data dictionary and work across all supported platforms. This support also allows for easier transition from other database systems to Advantage. The following example SQL statement creates a stored procedure using a script which looks up the customer’s state and then determines the appropriate sales tax rate.
CREATE PROCEDURE AddInvoice
( CustomerID Integer, SubTotal Money, Shipping Money)
BEGIN
DECLARE input cursor as SELECT * FROM __input;
DECLARE customer cursor;
DECLARE salestax money;
DECLARE tr double;
DECLARE st char(2);
-- open the table with the input parameters
OPEN input;
FETCH input;
-- find the customer placing the order, and get their state
OPEN customer AS SELECT * FROM customer WHERE [Customer ID] = input.CustomerID;
IF FETCH customer THEN
st = TRIM( UPPER( customer.state ) );
-- Get the tax rate for the customer's state
tr = (SELECT TaxRate FROM TaxRates WHERE State = st);
-- calculate the sales tax on the subtotal
salestax = input.SubTotal * tr;
INSERT INTO orders ( CustomerID, SubTotal, Shipping,
Tax, Total )
VALUES ( input.CustomerID, input.SubTotal, input.Shipping, salestax, input.SubTotal +
input.Shipping + salestax );
ELSE
RAISE CustomerNotFoundError( 1, 'Invalid Customer ID' );
ENDIF;
END;
After running the above script, the stored procedure is defined and can be called using the EXECUTE PROCEDURE statement. The procedure takes three parameters; Customer ID, SubTotal and Shipping charge. It will return success or the custom error message raised in the else statement.
Error Handling
The TRY … CATCH block is very effective for handling errors that may occur during execution of your script.
TRY
CREATE TABLE Test( id integer, name char( 20 ) );
CATCH ADS_SCRIPT_EXCEPTION
-- Only do something if the error code indicates
-- table already exists
IF __errcode = 2010 or __errcode = 5189 THEN
DROP TABLE Test;
CREATE TABLE Test( id integer, name char( 20 ) );
ELSE
RAISE; -- re-raise the exception
END IF;
END TRY;
The above example will create a table called test. If the table already exists, it will delete the existing table and replace it with the new table. Any other error will be re-raised.
Summary
Using SQL Persistent Stored Modules can be a very powerful tool for your database application. This SQL scripting support will allow you to enhance your data dictionaries to include your triggers and stored procedures with all supported operating systems. It will also allow you to import triggers and stored procedures from other databases to include them with your Advantage database.
|