Preparing your ESQL source code for use with the pre-compiler is straightforward. All ESQL statement blocks must start with the keywords EXEC SQL and end with the keyword END-EXEC. The structure and syntax of the SQL statements within each code block should conform to the variant of SQL required by the target database.
Note that the pre-compiler mode used to perform syntax checking can create variability with respect to errors encountered at runtime and, therefore, should be taken into consideration when you prepare your source code. For example, if you direct the pre-compiler to pass ESQL statements to the database engine for syntax checking (provided that your ODBC driver supports the return of syntax errors), you will know exactly what is acceptable and unacceptable to the database engine. This method ensures that your SQL statements are syntactically correct when the source code successfully pre-compiles. If, instead, you use the pre-compiler’s relaxed syntax checking mode (by specifying the -Pr option at compile time), some of your ESQL statements will pass through the pre-compiler without any test against the syntax accepted by the target database, increasing the likelihood of undetected SQL syntax errors causing problems at run time. See Using AcuSQL as a Standalone Program for a description of the -Pr option.
By default, the pre-compiler performs syntax checking for compliance with the ISO/ANSI SQL 92 standard. For complete information about pre-compiler options, see section Using the Pre-compiler.
If your program will access a DB2 database, your ESQL statements should conform to the IBM SQL standard described in IBM publication S10J-8158-00, Embedded SQL Programming Guide, Version 5. A reference level definition of IBM SQL is included in IBM publication S10J-8165, SQL Reference. When you pre-compile your program, specify the -Pk DB2 option to direct the pre-compiler to perform DB2-specific syntax checking, or use the -Pc option to have the DB2 engine perform direct validation.
If your program will access a Microsoft SQL Server database, specify the -Pk mssql option to direct the pre-compiler to perform SQL Server-specific syntax checking.
In order to accommodate SQL Server function syntax (db_name(), for example), AcuSQL also supports SELECT statements that do not include FROM … tablelist. Note that if this is the only SQL Server-specific syntax your program contains, you do not need to use the -Pk mssql option on your command line.
AcuSQL supports the following SQL Server syntax for embedded transactions:
The name will be passed to SQL Server as is, and must match the rules for embedded transaction names. For information on configuration variables supported by AcuSQL in a SQL Server environment, see section Runtime Configuration Variables for SQL Server. For additional information on running applications with AcuSQL in a SQL Server environment, see section Running Your Application with Microsoft SQL Server.
In addition, it is possible to nest transactions, as supported by SQL Server. When you use nested transactions, note how many transactions you BEGIN, because you must COMMIT the same number of transactions for the transaction to be considered complete. For example, if you execute three BEGIN TRANSACTION statements, you must later execute three COMMIT TRANSACTION or COMMIT WORK statements to commit the work you have done.
Note, however, that a single ROLLBACK rolls back all nested transactions. This behavior is a feature of SQL Server, and is not controlled by the AcuSQL interface.