SQL Script Overview

Advantage SQL Engine

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:

Writing a stored procedure using an SQL script may not be suitable for all situations:

Security Issues

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 |

sql_statement

See

DECLARE

Assignment Statements

IF

WHILE

OPEN, CLOSE, FETCH

EXECUTE IMMEDIATE

TRY. . . CATCH. . . FINALLY

RAISE

RETURN

CACHE

Any SQL Statement

System Variables

Parameters

Using SQL Scripts to Write Stored Procedures

 

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.

TIP: When a script is executed as a query and the last statement in the script is a SELECT statement, a cursor is returned to the client.