Programming

Syntax Checking Options

CitOESQL is an open implementation for the ANSI Embedded SQL standard, and as such can be used with a wide variety of databases, each of which can accept different and sometimes unique SQL syntax. To accommodate these differences, by default, CitOESQL does minimal SQL syntax checking at compile time. You can increase the level of CitOESQL SQL syntax checking at compile time by using the SQL(CHECK) directive with other associated directives.

SQL(CHECK) connects to the database during compilation, and asks the database to validate SQL syntax with the existing database SQL objects such as tables, columns, etc.

In addition to SQL(CHECK), you must also specify the SQL(DB) directive, and optionally the SQL(PASS) directive. This combination ensures a successful connection to your database at compile time and returns applicable SQL syntax errors.

In addition to the topics below, see the CHECK compiler directive topic in the CitOESQL Reference Manual for more information on the SQL(CHECK) compiler directive option.

SQL(CHECK) and Schema Objects

All databases contain Schemas that include SQL objects like tables, columns, views, and temporary tables. If all the SQL objects in a database are available at compile time, using the SQL(CHECK) directive ensures that all SQL syntax is fully checked by the database during compilation.

However, some schema objects might not be available in the database at compile time. In this case, CitOESQL offers two solutions:

SQL(IGNORESCHEMAERRORS) directive

Use this directive with SQL(CHECK) when tables or other SQL objects do not exist in the database. The addition of SQL(IGNORESCHEMAERRORS) enables CitOESQL to ignore invalid object reference errors returned by the database and continue compilation.

[ALSO CHECK] and [ONLY CHECK] statement prefixes

Use these statement prefixes on individual SQL statements in your code if you want CitOESQL to create SQL objects in the database at compile time. With the objects in the database, all invalid object reference errors are returned during compilation, and optionally at run time as well.

For more information on [ALSO CHECK] and [ONLY CHECK], see SQL Statement Prefixes for SQL(CHECK).

SQL(CHECK) Command-line Options

You can improve SQL syntax checking in some scenarios by combining SQL(CHECK) with additional compiler directive options and/or using a local database.

SQL(CHECK) with SQL(DB) and, optionally, SQL(PASS)

When you use the SQL(CHECK) and SQL(DB) directives, together with SQL(PASS) if necessary, CitOESQL opens a connection to a data source at compile time and uses the data source to perform additional checking. This is the recommended way to use CitOESQL and is much more reliable at detecting errors. In addition to detecting syntax errors that are specific to a particular data source, it can also detect misspelled names and invalid use of reserved words.

SQL(CHECK), local database, deployment schema

CitOESQL is at its most effective when you use SQL(CHECK) with a local database that uses the same schema that is used when the application is deployed. This combination compiles programs faster than accessing a networked server for compile-time checking.

SQL(CHECK), local database, no deployment schema

When you do not have access to a data source with the deployment schema installed you can still use SQL(CHECK) to perform additional syntax checking, but you must also use SQL(IGNORSCHEMAERRORS) to avoid errors for invalid name use.

SQL(IGNORESCHEMAERRORS) is also helpful when your program uses temporary tables that exist only at run time.

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, CitOESQL provides SQL statement prefixes that enable you to execute specific SQL statements at compile time and optionally at run time also.

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

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 CitOESQL 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 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

Tuning Performance

Cursor Types and Performance

CitOESQL handles ambiguously declared embedded SQL cursors by making them forward and readonly, and incapable of retrieving locks. This change improves performance and efficiency, optimizing CitOESQL as most DBMS SQL cursor access plans do.

You can further optimize CitOESQL by using the BEHAVIOR directive, which enables you to change your embedded SQL cursor characteristics (including prefetch processing) without changing any code in your SQL application sources.

Statement Cache

Embedded SQL statements are optimized for repeat execution. When first executed a statement is prepared at the data source. This is analogous to program compilation and means that information is retained about how to execute the statement so that it does not have to be recompiled on subsequent executions.

Prepared statements consume memory on both the client and server; CitOESQL maintains a cache to limit how much memory is consumed by prepared statements. The cache is managed on a Least Recently Used (LRU) basis. When the cache reaches its limit the least recently used statement that can safely be removed from the cache is replaced with the statement currently being executed. Any resources used by the replaced prepared statement are freed.

The default cache size is 20 which is quite small, however, this limit has the ability to avoid problems with some database products that have low limits on server resource consumption. The size of the cache can be changed via the STMTCACHE directive.

In practice, a large batch program may benefit from a statement cache size of 300 or possibly more. Many factors affect the optimum cache size, so it is best to experiment. Initial increments will generally improve performance of programs that use connections with long lifespans, but eventually additional increments benefits may reduce performance. Finding the optimum cache size may take a little effort.

Datetime Data Type Handling

By default, CitOESQL supports ODBC/ISO 8601 formats for all input and output character host variables associated with datetime columns in your DBMS.

For example, when using a SQL Server DBMS, the default data type formats for character host variables are:

In addition to SQL Server, these formats generally apply to other DBMS vendors that accept ISO 8601 formats. CitOESQL also supports alternative formats for both input and output character host variables. We provide several SQL compiler directive options that enable you to specify alternative formats that override the default.

Input Host Variables - DETECTDATE

The DETECTDATE SQL compiler option directive instructs CitOESQL to examine the contents of PIC X character input host variables, looking for data that matches the default ISO 8601 formats. You can override the default formats by specifying one or more additional directives:

DATE

Specify an alternative DATE format.

DATEDELIM

Specify an alternative delimiter for date columns.

• When used with DATE, the alternative delimiter is applied to the alternative date format specified.

• When used without DATE, the alternative delimiter is applied to the ISO 8601 date format.

TIME

Specify an alternative TIME format.

TIMEDELIM

Specify an alternative delimiter for time columns.

• When used with TIME, the alternative delimiter is applied to the alternative time format specified.

• When used without TIME, the alternative delimiter is applied to the ISO 8601 time format.

TSTAMPSEP

Specify a one-character delimiter used between the date and time portions of your input host variables.

The dash character instructs CitOESQL to look for a specific set of delimiters, including a dash, a space, and a T. For example, if you do not specify any alternative date or time formats, and you set TSTAMPSEP to a dash character (-), CitOESQL recognizes the following formats in your input host variables:

• yyyy-mm-dd-hh.mm.ss.ffffff

• yyyy-mm-dd hh.mm.ss.ffffff

• yyyy-mm-dd hh:mm:ss.ffffff

• yyyy-mm-ddThh.mm.ss.ffffff

• yyyy-mm-ddThh:mm:ss.ffffff

All other characters instruct CitOESQL to search for that specific character between each date and time format, where the date portion is delimited by a dash character (-) and the time portion is delimited by a colon (:).

If you do not specify TSTAMPSEP, CitOESQL defaults to searching for a space character as the delimiter between the date and time formats, where the date portion is delimited by a dash character (-) and the time portion is delimited by a colon (:).

Guidelines for using DETECTDATE

Use of the DETECTDATE directive can create significant processing overhead. To minimize this, we recommend that you follow the guidelines presented in the following usage scenarios:

Scenario	DETECTATE option
My application uses date, time and datetimes values in PIC X input host variables, but I am happy with the supported ODBC/ISO 8601 formats and have no use for alternative formats.	Not required.*
My application uses date, time and datetime values in PIC X input host variables, but I only use those values in date, time, or datetime columns in my database.	CLIENT
My application uses ODBC escape sequences for date, time, and datetimes values in PIC X input host variables, but I only use those values in date, time, or datetime columns in my database.	CLIENT
My application uses date, time, and datetime values in PIC X input host variables, but I only use those values in character columns in my database. Also, my SQL does not use either implicit or explicit characters for date, time, or datetime2 data types.	Not required. Do not use DETECTDATE, DATE, or TIME SQL compiler directive options.
I only use SQLTYPE host variables with date, time and datetime columns, and never use PIC X host variables with date, time or datetime columns.	Not required.**
My application uses date, time and datetime values in PIC X input host variables, and I use those values both in character columns and in date, time and datetime columns in my database, and my character columns might use data in formats that could be confused with the formats for date, time or datetime values.	SERVER

* We recommend that you use DETECTDATE when also using TIME values with Oracle.

** Optionally, you can use alternative datetime SQL compiler directive options.

Note

For complete information on all DETECTDATE options, see the DETECTDATE SQL compiler directive option in the CitOESQL Reference Manual.

Output Host Variables

By default, CitOESQL returns date, time and datetime data types in the ISO 8601 default format. You can override the default format by specifying additional CitOESQL directives as follows:

Changing the error code logic in an application containing logic originally designed for a specific database can be cumbersome. Consider these scenarios:

• My code expects Oracle SQLCODE 1403 at end of result set processing and not SQLCODE 100 as produced by other databases.

• My code expects z/OS DB2 SQLCODE-811 when a SELECT INTO statement returns more than one row.

• My code does not expect data truncation warnings after a FETCH statement, but my new database sets SQLCODE 1.

• When inserting a row that results in a duplicate key error, my code expects original database error codes.

These are just a few simple examples, however, error code mapping allows maximum flexibility in preserving the current error handling in your application code. When you provide search criteria based on what the new database returns in error situations, (using value 0 when SQLCODE or SQLSTATE values are returned that do not matter), and specify the error values for the original database, you can ensure that your application receives the error codes it expects.

When error mapping is enabled, it is processed after an embedded SQL statement completes execution. If SQLCODE is non-zero or SQLSTATE is not 00000, the error map is used to determine if SQLCODE, SQLSTATE, and optionally the associated error message, should be replaced with values from the error map. This is done by scanning error map records in order until either of the following conditions are met:

• It reaches the end of the map, in which case SQLCODE, SQLSTATE, and the error message are left unchanged.

• A match is made on some combination of SQLCODE, SQLSTATE, and a substring present in the error message.

SQL error mapping files

You control error mapping using an error mapping file. This is a simple text file that specifies which error conditions to map, the replacement values for SQLCODE and SQLSTATE, and optionally replacement values for the error message, including complete suppression of the error message.

You can specify mappings based on the returned values of SQLCODE, SQLSTATE or a substring within the error message, or any combination of these.

LOCATION

The default location for mapping files is %COBOLITDIR%\etc (Windows) or $COBOLITDIR/etc (Linux)

Note

You can override the default location using the CIT_ERRORMAP_PATH system environment variable.

FILENAME

You can name an SQL error mapping file using any prefix you choose; however, all error mapping files must have an .emap extension.

CONTENTS

Each record in a mapping file contains the following values in this order, delimited by commas:

{SC-ret-val|0},{SS-ret-val|0},[msg-substr],SC-repl-val,SS-repl-val,[msg-substr-repl-val]

Where:

SC-ret-val|0

The returned database value for SQLCODE, or 0 (zero), to indicate that the returned database SQLCODE value does not matter.

SS-ret-val|0

The returned database value for SQLSTATE, or 0 (zero), to indicate that the returned database SQLSTATE value does not matter.

msg-substr

The returned database error message substring, if applicable. Specify a string of characters that appear in the message returned by the database. The error is mapped if the substring is present in the error message, and when the SQLCODE and SQLSTATE conditions are also satisfied. The following syntax rules apply when providing a substring:

• If the substring contains a comma, enclose the entire substring in single (') or double (“) quotes

• Message substrings are case sensitive.

Note

The full error message is used for the substring search rather than the 70 bytes subset returned in SQLERRMC.

Other than providing a substring, you also have these two options:

• Omit a value by including a space before the next comma delimiter. The original message is returned, effectively switching off error message replacement for substrings.

• Specify a single tilde (¬) character. This populates the message-receiving field, consisting of SQLERRMC, MFSQLMESSAGETEXT (or the host variable for

MESSAGE_TEXT with GET DIAGNOSTICS), with spaces. SQLERRML in the SQLCA is also set to zero rather than the number of characters returned in SQLERRMC.

SC-repl-val

Original database replacement value for SQLCODE.

SS-repl-val

Original database replacement value for SQLSTATE.

msg-substr-repl-val

Replacement value for the error message, if applicable. Syntax rules for msg-substr also apply to msg-substr-repl-val. When omitted, the initial error message is not replaced. Use a single tilde (¬) character to completely suppress the message.

SQL error mapping record examples

EXAMPLE 1:

This example is based on a migration from DB/2 for z/OS to PostgreSQL where a SELECT INTO statement returns more than the expected one row. The following mapping file entry changes the returned SQLCODE from 1 to -811 while leaving the PostgreSQL error intact. It does this when PostgreSQL returns SQLCODE 1 and SQLSTATE 21000 (the matching criteria):

1, 21000, ,-811, 21000

EXAMPLE 2:

This example is based on a migration from DB/2 for z/OS to PostgreSQL. The record maps errors that contain the string "duplicate" when a primary key or unique constraint error occurs, and changes the error message to "Unique constraint violation". Notice that the return values PostgreSQL provides for both SQLCODE and SQLSTATE do not matter. The search criteria is based solely on the returned PostgreSQL error message alone:

0, 00000,"duplicate", -803, 22002, Unique constraint violation

EXAMPLE 3: ODBC generates a warning when a host variable is smaller than the returned value. If an application tests for SQLCODE being non-zero rather than negative, this can break application logic. To completely suppress the warning condition, including the error message, the following record matches warnings where SQLSTATE has the value 01004:

0, 01004, , 0, 00000, ~

EXAMPLE 4: As an alternative to Example 3, when migrating a legacy application to an environment using UTF-8 and you want to test whether your host variables are large enough, the following record changes this warning to an error with SQLCODE -55 and SQLSTATE "22XYZ":

0, 01004, , -55, 22XYZ, Host variable too small

SQL error mapping enablement

Use the CitOESQL ERRORMAP compiler directive option to enable error mapping, as documented in the CitOESQL Directive section of the CitOESQL Reference Manual.

If you intend to use multiple error mapping files in one program, use the SQL Statement SET ERRORMAP, as documented in SQL Statements section of the CitOESQL Reference Manual.