SQL Statement Prefixes for SQL(CHECK)

To enable complete SQL syntax checking at compile time when tables or temporary tables are not in your database, OpenESQL provides SQL statement prefixes that enable you to actually execute specific SQL statements at compile time, and optionally at run time as well.

A statement prefix is coded directly into an EXEC SQL statement, and executed only when compiled with SQL(CHECK).

Syntax:

EXEC SQL [statementPrefix] [errorFlag[...]] SQLStatement END-EXEC

Parameters:

statementPrefix
[ALSO CHECK]
Statement prefix that instructs SQL(CHECK) to execute the following SQLStatement both at compile time and a run time.
[ONLY CHECK]
Statement prefix that instructs SQL(CHECK) to execute the following SQLStatement at compile time only.
[NOCHECK]
Statement prefix that turns off the effects of the SQL(CHECK) directive for the associated SQLStatement only.
errorFlag
[IGNORE ERROR]
Overrides the default behavior of providing a success or error return code upon SQLStatement execution, and instead ignores all errors at compile time and proceeds with compilation.
[WITH WARNING]
Overrides the default behavior of providing a success or error return code upon SQLStatement execution, and instead returns all errors as warnings at compile time, and proceeds with compilation.
SQLStatement
An SQL statement that conforms to the following:
  • A DDL statement such as CREATE TABLE or the specific DML statements INSERT, DELETE or UPDATE
  • No host variables used
  • Written in a syntax understood by the DBMS vendor

Examples:

Example 1
Create a SQL Server temporary table at compile time only so that subsequent SQL that references this table is fully syntax checked by the OpenESQL SQL(CHECK) directive during compilation.
EXEC SQL [ONLY CHECK] 
create table #temp(col1 int,col2 char(32))
END-EXEC 
Example 2
Create a SQL Server temporary table at both compile time and execution time so that, in addition to full compile-time syntax checking, the table is actually created at execution time.
EXEC SQL [ALSO CHECK] 
create table #temp(col1 int,col2 char(32))
END-EXEC
Example 3
Create test data at compile time with Oracle whether or not the table exists at compile time.
EXEC SQL [ONLY CHECK IGNORE ERROR] 
Drop table TT
END-EXEC 

EXEC SQL [ONLY CHECK] 
create table TT (col1 int) 
END-EXEC
 
EXEC SQL [ONLY CHECK] 
Insert into table TT values (1) 
END-EXEC