In this post, originally written by Glenn Paulley and posted to sybase.com in August of 2009, Glenn talks about validation of SQL statements against the ANSI/ISO SQL Standard from within SQL Anywhere. Note also that the flagger has been enhanced to support SQL:2008 in more recent versions of SQL Anywhere.
The flagging of SQL statements is defined in the ANSI/ISO SQL:2008 standard as language features F812 (“basic” flagging) and F813 (“extended” flagging). Flagging is the notion of identifying non-conformance of specific SQL constructions with respect to the ANSI/ISO SQL standard. Here’s a simple example using the sample DEMO database with SQL Anywhere:
SELECT SQLFLAGGER( 'SQL:2003/Core', 'SELECT TOP 10 * FROM Customers WHERE State IN (''NJ'', ''NC'') AND Country LIKE ''%USA%'' ORDER BY Surname' );
Flagging in SQL Anywhere
SA_ANSI_STANDARD_PACKAGESsystem procedure; and
- flagging capabilities in the SQL preprocessor (SQLPP).
The SQLFLAGGER function
SQLFLAGGER() function analyzes a single SQL statement, or batch, passed as a string argument, for compliance with a given SQL standard. The statement or batch is parsed, but not executed. The function returns a
LONG VARCHAR containing any error messages output by the flagger. The first parameter is the standard/implementation to which the SQL statement is to be compared; SQL Anywhere supports compliance comparisons with SQL:2003 (Core/Package), SQL:1999 (Core/Package), SQL:1992 (Full, Intermediate, Entry) and Ultralite.
At this time, it is unknown if the forthcoming Innsbruck release of SQL Anywhere will offer Flagging support for the current SQL:2008 standard.
The SQL_FLAGGER_ERROR_LEVEL and SQL_FLAGGER_WARNING_LEVEL connection options
SQL_FLAGGER_WARNING_LEVEL connection options invoke the SQL Flagger for any statement prepared or executed for the connection. If the statement does not comply with the option setting, which is a specific ANSI standard or UltraLite, the statement either terminates with an error (SQLSTATE 0AW03), or returns a warning (SQLSTATE 01W07), depending upon the option setting. If the statement complies, statement execution proceeds normally.
The SA_ANSI_STANDARD_PACKAGES system procedure
SA_ANSI_STANDARD_PACKAGES() system procedure analyzes a statement, or batch, for the use of optional packages from the ANSI/ISO SQL:2003 or SQL:1999 international standards. The procedure takes two parameters: the first, a string that identifies the desired standard, and the second is the SQL statement to be analyzed. The result of the procedure is a list of the optional SQL standard packages utilized by the statement. Here is an example:
Flagging in the SQL preprocessor
The SQL preprocessor (SQLPP) has the ability to flag static SQL statements in an embedded SQL application at compile time. This feature can be especially useful when developing an UltraLite application, to verify SQL statements for UltraLite compatibility. Using the flagger with SQLPP simply involves setting additional command line switches when invoking SQLPP.