The Advantage SQL engine supports a subset of the ANSI SQL 2003 PSM (Persistent Stored Module) scripting language standard including variables, cursors, branches, loops, and exception handling. SQL scripts provide a convenient method for moving complex processing to the server and are the ideal platform for implementing most stored procedures and triggers.
SQL script-based stored procedures and triggers can be created in using either SQL statements, CREATE PROCEDURE and CREATE TRIGGER; or the Advantage Client Engine API AdsDDAddProcedure and AdsDDCreateTrigger.
Implementing the stored procedures or triggers using an SQL script provides the following advantages over using external libraries:
• The stored procedures or triggers can be implemented without the need for a third-party compiler.
• The same stored procedure or trigger can be run on any Advantage Database Server regardless of the operating system.
• No additional files must be deployed because the stored procedures and triggers are contained completely inside the Advantage Data Dictionary.
• The Advantage Database Server has greater control over the execution of SQL script-based stored procedures and triggers. This reduces the chance that a faulty stored procedure or trigger can crash the database server. In addition, it allows stored procedures to be more easily canceled.
Writing a stored procedure using an SQL script may not be suitable for all situations:
• The functionality supported by the Advantage SQL Script language may not be sufficient for a particular requirement. For example, an SQL script cannot make system calls. For further limitations, see Using SQL Scripts to Write Stored Procedures.
• Debugging complex SQL scripts may be difficult. However, this may improve as the support for SQL scripting in Advantage continues to evolve.
If your application builds SQL statements based on user-provided values, those values must be checked for validity.
Consider a web page that asks for a username and password, and intends on building the following SQL statement:
SELECT * FROM orders WHERE customer_name = ‘the_customer’
If instead of typing in a valid name, the user entered:
the_customer’; DROP TABLE orders; //
the following valid SQL statements would be generated:
SELECT * FROM orders WHERE customer_name=’the_customer’;
DROP TABLE orders; // ‘
There are techniques you can use to protect your application from this sort of attack. A web search on "sql security semicolon" will return a number of pages worth reading.
Each statement in an SQL script must be terminated with a semi-colon. The following sections describe the script statements that are supported as well as other general SQL script related information:
The BNF of an SQL script is defined as:
script ::= declare_statements | declare_statements;statement_block | statement_block
statement_block ::= script_statement | script_statement;statement_block
script_statement ::= assignment_statement | if_statement | while_statement |
cursor_statement | execute_statement | try_statement |
raise_statement | return_statement | cache_statement |
The following statements are defined in the ANSI PSM standard but are not supported in this release of the Advantage SQL Engine. However, their functionality may be implemented using other supported statements:
FOR, REPEAT and LOOP: All loop functionality can be implemented using the WHILE statement.
SIGNAL and RESIGNAL: Exception handling can be implemented using the TRY. . . CATCH. . . FINALLY … statement.