Advantage Developer Zone

 
 
 

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.