CitOESQL Directives

Each SQL compiler directive option is used either at compile time, run time, or both. The behavior at run time is described as one of the following:

Source file

When a source file specifies the directive, the value set in the source file is used. If a source file does not specify the directive, then the process behavior is used.

Process

These directives affect connections. When the first-encountered EXEC SQL statement is executed, usually an EXEC SQL CONNECT statement, the run-time system uses the directive settings for the source file containing the statement. These settings apply for the remainder of the process lifetime. The run-time behavior varies depending on the type of connection as follows:

• ODBC with THREAD=ISOLATE - Each thread in the process uses its own set of global directive settings

• ODBC with THREAD=SHARE - One set of global directive settings applies for the entire process

For additional information on the scope of each SQL compiler directive option, see its corresponding CitOESQL directive below.

Process-based CitOESQL Directives:

• ALLOWNULLCHAR
• ANSI92ENTRY
• AUTOCOMMIT
• CHECKDUPCURSOR
• CHECKSINGLETON
• CLOSE_ON_COMMIT
• CLOSE_ON_ROLLBACK
• CONNECTIONPOOL
• CURSORCASE
• DECDEL
• ISOLATION
• NIST
• ODBCTRACE
• ODBCV3
• PARAMARRAY • PREFETCH
• RESULTARRAY
• STMTCACHE
• TARGETDB
• THREAD
• TRACELEVEL
• USECURLIB

ALLOWNULLCHAR

Allows programs to use PIC X(n) host variables, and to select/insert/update the null character (x00) into CHAR columns without changing source to use SQL TYPE BINARY host variables.

Syntax:

[NO]ALLOWNULLCHAR

Properties:

Default: NOALLOWNULLCHAR

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

• With ALLOWNULLCHAR, CitOESQL does not truncate the contents of the input host variable when the first embedded NULL character is encountered.

• With NOALLOWNULLCHAR, however, the database is presented a truncated value, including all characters up to the first embedded NULL.

• PostgreSQL does not support the embedding of the NULL character into CHAR columns; therefore, ALLOWNULLCHAR is not supported in PostgreSQL CitOESQL applications.

ALLOWSERVERSELECT

Passes unrecognized EXEC SQL SELECT statements through to the server, thus enabling serverspecific behavior.

Syntax:

[NO]ALLOWSERVERSELECT

Properties:

Default: NOALLOWSERVERSELECT

Scope:

• Used at compile time: No

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

• NOALLOWSERVERSELECT gives an error when CitOESQL does not recognize an EXEC SQL SELECT statement.

• When ALLOWSERVERSELECT is set, CitOESQL simply passes unrecognized EXEC SQL SELECT statements through to the server, thus enabling server-specific behavior.

ANSI92ENTRY

If this is set, CitOESQL conforms to the SQL ANSI'92 entry level standard.

Syntax:

[NO]ANSI92ENTRY

Properties:

Default: NOANS192ENTRY

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

• When ANSI92ENTRY is not specified or NOANSI92ENTRY is specified, CitOESQL does not set any error or warning conditions.

• When ANSI92ENTRY is specified and neither CHECKSINGLETON nor NOCHECKSINGLETON is specified (default), CitOESQL returns:

• SQLCODE = -1 SQLSTATE = 21000 SQLWARN4 = W

In conformance with the ANSI-92 standard, CitOESQL does the following when ANSI92ENTRY is specified:

• Sets the isolation level to serializable.
• Closes non-HOLD cursors at the end of a transaction.
• When an EXEC SQL FETCH statement is executed on an unopened cursor, returns SQLSTATE 24000. If ODBCV3 is also set, returns SQLSTATE 07005.
• When an EXEC SQL FETCH statement returns a null value but no indicator host variable has been supplied, returns SQLCODE -19425.
• When an EXEC SQL OPEN statement is executed on an opened cursor, returns SQLCODE 19516 and SQLSTATE 07005.

AUTOCOMMIT

Regulates an SQL connection's autcommit attribute.

Syntax:

[NO]AUTOCOMMIT

Properties:

Default: NOAUTOCOMMIT

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

• Because NOAUTOCOMMIT is used by default, CitOESQL default behavior explicitly turns off the SQL connection's autocommit attribute regardless of how the connection was created.

• For CitOESQL connections, NOAUTOCOMMIT allows your application to control local transactions with normal COMMIT and ROLLBACK statements.

• When AUTOCOMMIT is specified explicitly, the SQL connection's autocommit attribute is not altered, whether the connection was created by CitOESQL or by some other means. The result for CitOESQL connections is that AUTOCOMMIT commits every SQL statement in your application, operating in "autocommit mode." With external connections, the autocommit attribute is not modified, so your application continues to participate in the transaction managed externally.

• The SET AUTOCOMMIT embedded SQL statement overrides the AUTOCOMMIT compiler directive option.

• An application can programmatically control the autocommit setting for a connection by executing the EXEC SQL SET AUTOCOMMIT statement.

• An application in autocommit mode can start a local database transaction with the EXEC SQL BEGIN TRANSACTION statement. The transaction ends when the next COMMIT or ROLLBACK statement is executed.

• When a transaction ends, if the connection's autocommit attribute is on, the connection reverts to autocommit mode; otherwise a new local database transaction is started automatically.

AUTOFETCH

Sets the AUTOFETCH attribute on SELECT statements. Compiling with this directive can help the performance of your application. It also serves as a primitive directive for the BEHAVIOR directive option.

Syntax:

[NO]AUTOFETCH

Properties:

Default: NOAUTOFETCH

Scope:

• Used at compile time: No

• Behavior at run time: Source file

See CitOESQL Directives for more information.

BEHAVIOR

Instructs CitOESQL to properly match your COBOL cursors with the database you are using, thus maximizing database cursor performance. BEHAVIOR makes use of multiple primitive directives and associates a default value for each primitive directive depending on the target DBMS. You can also override the default setting of any primitive directive.

Syntax:

BEHAVIOR={OPTIMIZED \ UNOPTIMIZED \ ANSI \ MAINFRAME } [*primitivedirective* [*value*]]...

Parameters:

• OPTIMIZED - Enables CitOESQL to optimize ambiguous cursor declarations; enhances the access speed
• UNOPTIMIZED - Provides backward compatibility with earlier Micro Focus products, where ambiguous cursors are both updateable and scrollable
• ANSI - Enables CitOESQL to work like the ANSI standard; enhances the access speed
• MAINFRAME - Synonym for OPTIMIZED
• primitivedirective - Optional directives that enable the fine-tuning of BEHAVIOR

Properties:

Default: BEHAVIOR=OPTIMIZED

Scope:

• Used at compile time: Yes

• Behavior at run time: N/A

See CitOESQL Directives for more information.

Default Primitive Directives:

Table 1. Default Behavior Primitive Table for SQL Server

Table 2. Default Behavior Primitive Table for SQL Server

You can fine tune the BEHAVIOR directive and override these default settings by providing alternate values for primitive directives. If you do this, you must set the TARGETDB directive.

Comments:

• Setting this directive can also affect the SQL types returned when using Dynamic SQL. See the DESCRIBE Statement topic for more information.
• OpenESQL sets default values for primitive directives depending on the value of BEHAVIOR and your target database. See the topic Primitive Directives for more information.

Primitive Directives

These directives work only with when you have your target database set and you have specified the BEHAVIOR directive as well. Specify them only when you want to override the default settings for BEHAVIOR. For example:

SQL(TARGETDB=INFORMIX BEHAVIOR=OPTIMIZED DEF_CURSOR=UPD)

• DEF_CURSOR

  This cursor directive specifies the default cursor type for ambiguous COBOL cursors, either read only or updateable. Use this directive when the DECLARE CURSOR statement has neither the FOR READONLY nor the FOR UPDATE OF clause.

• IC_FH_ISOLATION

  This directive sets the transaction isolation for independent firehose cursor connections, enabling simultaneous access to firehose cursors.

• PF_RO_CURSOR

  This is a prefetch directive that enables you to retrieve more than one read only record at a time from the DBMS with one client request.

• PF_UPD_CURSOR

  This is a prefetch directive that enables you to retrieve more than one updateable record at a time from the DBMS with one client request.

• RO_CURSOR

  This directive determines what type of database cursor your COBOL read-only cursors use.

• UPD_CONCURRENCY

  This directive enables you to choose the level of concurrency for updateable COBOL cursors.

• UPD_CURSOR

  This directive determines what type of database cursor updateable COBOL cursors use.

DEF_CURSOR

This cursor directive specifies the default cursor type for ambiguous COBOL cursors, either read only or updateable. Use this directive when the DECLARE CURSOR statement has neither the FOR READONLY nor the FOR UPDATE OF clause.

Syntax:

DEF_CURSOR={RO \ UPD}

Parameters:

• RO Read Only

• UPD Updateable

Scope:

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

Comments:

While the default when BEHAVIOR=ANSI is DEF_CURSOR=UPD, if the original developer assumed RO, then use of UPD could adversely affect performance.

IC_FH_ISOLATION

This directive sets the transaction isolation for independent firehose cursor connections, enabling simultaneous access to firehose cursors.

Syntax:

IC_FH_ISOLATION={UR \ CR \ RR \ SZ}

Parameters:

• UR Uncommitted Read
• CR Committed Read
• RR Repeatable Read
• SZ Serializable isolation in ODBC

Scope

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

PF_RO_CURSOR

This is a prefetch directive that enables you to retrieve more than one read only record at a time from the DBMS with one client request.

Syntax:

PF_RO_CURSOR={*numberofrows* \ *buffersize*K \ *buffersize*B}

Properties:

Default for SQL Server connections: 8

Parameters:

• numberofrows - The number of rows to retrieve

• buffersizeK - The size of the buffer you want to fill in kilobytes

• buffersizeB - The size of the buffer you want to fill in bytes

Scope:

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

Comments:

• PF_RO_CURSOR must be set after TARGETDB and BEHAVIOR
• Does not work with FORWARD or IC_FH selection in RO_CURSOR
• SQL Server read-only server cursors now use PF_RO_CURSOR consistently across all runtimes

PF_UPD_CURSOR

This is a prefetch directive that enables you to retrieve more than one updateable record at a time from the DBMS with one client request.

Syntax:

PF_UPD_CURSOR={*numberofrows* \ *buffersize*K \ *buffersize*B}

Parameters:

numberofrows - The number of rows to retrieve

buffersizeK - The size of the buffer you want to fill in kilobytes buffersizeB The size of the buffer you want to fill in bytes

Scope:

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

Comments:

• PF_UPD_CURSOR must be set after TARGETDB and BEHAVIOR
• Does not work with FORWARD selection in RO_CURSOR

RO_CURSOR

This directive determines what type of database cursor your COBOL read-only cursors use.

Syntax:

RO_CURSOR={SCROLL \ FORWARD \ DYNAMIC \ KEYSET \ STATIC \ FF \ IC_FH}

Parameters:

• FF - Fast Forward
• IC_FH - Independent Connection Fire Hose

Scope:

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

Comments:

• SCROLL, FORWARD, DYNAMIC, and KEYSET are standard ODBC types.
• FF and IC_FH are cursors specific to MS SQL Server.
• Under MS SQL Server, prefetching does not work with FORWARD and IC_FH.
• As its name indicates, an IC_FH cursor has its own independent connection to MS SQL Server. Because of this, these cursors are sensitive to the isolation level and locking protocol of previous data access in the application program.
• When using a SQL Server connection that supports MARS, MARS is automatically enabled and IC_FH is converted to FORWARD.

UPD_CONCURRENCY

This directive enables you to choose the level of concurrency for updateable COBOL cursors.

Syntax:

UPD_CONCURRENCY={LOCK \ OPTIMISTIC \ OPTCC \ OPTCCVAL}

Scope:

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

Comments:

LOCK, OPTIMISTIC, OPTCC, and OPTCCVAL are standard ODBC types.

UPD_CURSOR

This directive determines what type of database cursor updateable COBOL cursors use.

Syntax:

UPD_CURSOR={SCROLL \ FORWARD \ DYNAMIC \ KEYSET \ STATIC}

Scope:

• Used at compile time: No

• Behavior at run time: Source File

See CitOESQL Directives for more information.

Comments:

• SCROLL, FORWARD, DYNAMIC, KEYSET, and STATIC are standard ODBC types.
• Prefetching does not work with FORWARD.

CHECK

Sends each SQL statement to the database at compilation time.

Syntax:

[NO]CHECK

Properties:

Default NOCHECK

Dependencies:

CHECK requires that you also:

• Set the DB compiler directive option or identify the database using the DB2DBDFT environment variable.
• If the connection to the database specified requires authentication, then the PASS compiler directive option is also required.
• When using SQL(CHECK) with an ODBC connection to Oracle, you must have at least the same user privileges as are required to execute the SQL statements in the program.

Scope:

• Used at compile time: Yes

• Behavior at run time: N/A

See CitOESQL Directives for more information.

Comments:

• Depending on your driver and/or DBMS and in certain circumstances, CHECK does not flag invalid SQL statements.

• You can specify the [NOCHECK], [ALSO CHECK], or [WITH CHECK] statement prefix to affect specific SQL statements. See SQL Statement Prefixes for CHECK Directive for complete information.

CHECKDUPCURSOR

Instructs CitOESQL to determine if the cursor has been opened twice, and if so takes action accordingly.

Syntax:

[NO]CHECKDUPCURSOR

Properties:

Default NOCHECKDUPCURSOR

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

• If the cursor has been opened twice and the NOANSI92ENTRY directive has also been specified, CHECKDUPCURSOR returns SQLCODE -516 and sets SQLSTATE=24000.

• If the cursor has been opened twice and ANSI92ENTRY has not been specified, NOCHECKDUPCURSOR automatically closes the cursor and then re-opens it.

CHECKSINGLETON

Instructs CitOESQL to check if singleton SELECTs return more than one row when executed.

Syntax:

[NO]CHECKSINGLETON

Properties:

Default None

Returns:

*When ANSI92ENTRY is specified without CHECKSINGLETON or NOCHECKSINGLETON (default), SQLCODE returns -1 and SQLSTATE returns 21000. See ANSI92ENTRY for additional details.

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

• CHECKSINGLETON is provided for DB2 compatibility.
• NOCHECKSINGLETON is provided for applications that require SQLCODE 0.
• For applications that require SQLCODE -1, specify ANSI92ENTRY without CHECKSINGLETON or NOCHECKSINGLETON.

CitOESQL returns the following SQLDA diagnostics when a singleton SELECT returns more than one row:

CLOSE_ON_COMMIT

Specifies whether to close cursors not defined WITH HOLD or leave them open for further fetches after a COMMIT.

Syntax:

CLOSE_ON_COMMIT={YES \ NO}

Parameters:

• YES - Close cursors on COMMIT
• NO - Leave cursors open on COMMIT

Properties:

Default YES

Scope:

• Used at compile time: Yes

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

By default, meaning CLOSE_ON_COMMIT is not specified or CLOSE_ON_COMMIT=YES is specified, all cursors not declared WITH HOLD are closed after a COMMIT. CLOSE_ON_COMMIT works with the BEHAVIOR directive as follows:

• BEHAVIOR=UNOPTIMIZED*

  CLOSE_ON_COMMIT is ignored. Cursor selection is least optimal. Cursor remains open after COMMIT.

• BEHAVIOR=OPTIMIZED or ANSI, CLOSE_ON_COMMIT=YES (default) Cursor selection is optimal.

  Cursor is closed after COMMIT.

• BEHAVIOR=OPTIMIZED or ANSI, CLOSE_ON_COMMIT=NO

  Cursor selection is optimal. Cursor remains open after COMMIT.

CLOSE_ON_ROLLBACK

Specifies whether to close cursors or leave them open for further fetches after a ROLLBACK.

Syntax:

CLOSE_ON_ROLLBACK={YES \ NO}

Parameters:

• YES - Close cursors on ROLLBACK

• NO - Leave cursors open on ROLLBACK

Properties:

Default YES

Scope:

• Used at compile time: Yes

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

By default, meaning CLOSE_ON_ROLLBACK is not specified or CLOSE_ON_ROLLBACK=YES is specified, all cursors are closed after a ROLLBACK.

CLOSE_ON_ROLLBACK works with the BEHAVIOR directive as follows:

• BEHAVIOR=UNOPTIMIZED

  CLOSE_ON_ROLLBACK is ignored. Cursor selection is least optimal. Cursor remains open after ROLLBACK.

• BEHAVIOR=OPTIMIZED or ANSI, CLOSE_ON_ROLLBACK=YES (default)

  Cursor selection is optimal. Cursor is closed after ROLLBACK.

• BEHAVIOR=OPTIMIZED or ANSI, CLOSE_ON_COMMIT=NO

  Cursor selection is optimal. Cursor remains open after ROLLBACK.

CONNECTIONPOOL

Enables use of ODBC 3.0 connection pooling. When a connection is closed, the Driver Manager keeps it alive for a timeout period and saves the overhead of re-establishing a connection from scratch if the application re-opens an identical connection. ODBC allows you to choose between having a pooling for an ODBC environment or for each driver. See your ODBC documentation for details.

This option is only useful for applications that frequently open and close connections. Note that some environments, such as Microsoft Transaction Server (MTS), control connection pooling themselves. This option will probably improve the performance of ISAPI applications that are not running under MTS.

Syntax:

CONNECTIONPOOL={DRIVER \ ENVIRONMENT \ NONE}

Properties:

Default CONNECTIONPOOL=NONE.

Scope:

• Used at compile time: No

• Behavior at run time: Process

CURSORCASE

NOCURSORCASE means that cursor names are not case sensitive. CURSORCASE means that they are case sensitive.

Syntax:

[NO]CURSORCASE

Properties:

Default NOCURSORCASE

Scope:

• Used at compile time: No

• Behavior at run time: Process

DATE

Specifies the explicit date format to use when date values are returned from database date columns into character output host variables.

When used in addition to DETECTDATE, also specifies the explicit data format to recognize in character input host variables.

Syntax:

DATE={ODBC \ ISO \ USA \ EUR \ JIS}

Parameters:

• ODBC - yyyy-mm-dd (ISO 8601 default format)

• ISO - yyyy-mm-dd (mainframe default format)

• USA - mm/dd/yyyy

• EUR - dd.mm.yyyy

• JIS - yyyy-mm-dd

Properties:

Default ODBC (ISO 8601 default format)

Dependencies:

For input host parameters, requires that the DETECTDATE SQL compiler directive option is also set.

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

DATE can be used with the DATEDELIM directive to specify an alternative delimiter that separates day, month, and year components.

DATE, with or without DATEDELIM, changes the display format of output host variables as specified.

When you specify both DATE and DETECTDATE, CitOESQL uses DATE (with or without DATEDELIM) to also recognize date values in your input host variables. See DETECTDATE for more information.

DATEDELIM

Specifies a single character as the delimiter between the year, month, and day components to override the default delimiter determined by the DATE directive specification, or implicitly based on default ISO 8601 format (yyyy-mm-dd).

The specified delimiter is used in character output host variables and, if DETECTDATE is also specified, in character input host variables.

Syntax:

DATEDELIM=*character*

Properties:

Default None

Dependencies:

None; however, DATEDELIM can be used with DATE to specify an alternative delimiter. See DATE for details.

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

• DATEDELIM set without DATE overrides the default ISO 8601 delimiter, a dash (-) character, for date values.

• DATEDELIM set with DATE overrides the default delimiter for the specified DATE parameter. For example, the default delimiter for DATE=USA is a forward slash character (/).

See the DATE and DETECTDATE sections along with the CitOESQL Datetime Data Types Handling section in the CitOESQL User Guide for more information.

DB

Identifies a data source that defines database connection information.

Syntax:

DB=connection

NODB

Parameter:

• connection

  The value for this parameter varies depending on the run time system:

  ODBC Run-time - An ODBC DSN

  JVM Managed Run-time - The name of a JNDI DataSource object

Properties:

Default NODB

Dependencies:

At compile time, DB is required when you set SQL(CHECK). At run time, DB is required when you set SQL(INIT).

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file See CitOESQL Directives for more information.

Comments:

The .int file generated is the same except for the TARGETDB number embedded in the file.

DBMAN

Specifies the preprocessor to use. This directive is not required when compiling programs with CitOESQL.

Syntax:

DBMAN={ODBC}

Parameters:

ODBC For native code

Properties:

Default DBMAN=ODBC for native applications

Scope:

• Used at compile time: Yes

• Behavior at run time: Process

DECDEL

Specifies the decimal delimiter to use for decimal variables.

Syntax:

DATE={ODBC \ ISO \ USA \ EUR \ JIS}

Parameters:

• PERIOD - Always use a period (.) as a decimal delimiter.
• COMMA - Always use a comma (,) as a decimal delimiter.
• LOCAL - Call GetLocaleInfo one time to get the decimal delimiter.
• NODECDEL - Call GetLocaleInfo every time a decimal variable is referenced. Use this when your application dynamically changes its effective locale at run time.

Properties: Default - LOCAL

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

DESCRIBEDTCHAR

When using dynamic SQL, described or prepared SQL statements with DATE, TIME, and DATETIME columns are suitable for PIC X(n) character host variables or DATE, TIME, and TIMESTAMP SQL TYPEs.

Syntax:

DESCRIBEDTCHAR

Properties:

Default None

Dependencies:

BEHAVIOR=OPTIMIZED automatically sets DESCRIBEDTCHAR.

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

For dynamic SQL, by default, CitOESQL expects DATE, TIME and TIMESTAMP columns to be placed into host variables of DATE, TIME, and TIMESTAMP-RECORD SQL TYPEs in ODBC format record structures.

When BEHAVIOR=OPTIMIZED or DESCRIBEDTCHAR is set, CitOESQL expects DATE, TIME, and DATETIME columns to be placed into PIC X(n) character host variables or DATE, TIME, and TIMESTAMP SQL TYPEs.

DESCRIBEDTREC

When using dynamic SQL, described or prepared SQL statements with DATE, TIME, and DATETIME, columns are suitable for the DATE, TIME, and TIMESTAMP-RECORD SQL TYPEs in ODBC format record structures.

Syntax:

DESCRIBEDTREC

Properties:

Default DESCRIBEDTREC

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

For dynamic SQL, by default, CitOESQL expects DATE, TIME and TIMESTAMP columns to be placed into host variables of DATE, TIME, and TIMESTAMP-RECORD SQL TYPEs in ODBC format record structures.

Use DESCRIBEDTREC to override BEHAVIOR=OPTIMIZED set by DESCRIBEDTCHAR.

DESCRIBEVARCHAR49

When using dynamic SQL, described or prepared SQL statements with VARCHAR, columns are suitable for VARCHAR host variables with level 49 sub-fields for length and data.

Syntax:

DESCRIBEVARCHAR49

Properties:

Default None

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

For dynamic SQL, by default, CitOESQL expects VARCHAR columns to be placed into PIC X host variables.

Use DESCRIBEVARCHAR49 to enable the use of VARCHAR host variables with level 49 sub-fields for length and data.

DESCRIBEVARCHARPICX

When using dynamic SQL, described or prepared SQL statements with VARCHAR, columns are suitable for PIC X host variables.

Syntax:

DESCRIBEVARCHARPICX

Properties:

Default DESCRIBEVARCHARPICX

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

For dynamic SQL, by default, CitOESQL expects VARCHAR columns to be placed into PIC X host variables.

DETECTDATE

Allows datetime values for PIC X character input host variables in an CitOESQL application to be in different formats than the standard ISO 8601 formats.

DETECTDATE enables you to specify alternative formats for input host variables. CitOESQL manages the translation between the format specified by DETECTDATE and the format recognized by your DBMS. This is done for each SQL call on both input and output.

For example, instead of the standard ISO formats, you might prefer to use EUR formats for date and time data.

Syntax:

[NO]DETECTDATE

DETECTDATE={CLIENT \ SQLTYPE \ SERVER \ PICX}

Parameters:

• CLIENT

  Applies to DBMAN=ODBC only.

• Input Host Variables

  For PIC X character input host variables, CitOESQL recognizes specified datetime formats and translates the data into the ISO 8601 format acceptable to your DBMS. Specified datetime formats for input host variables are:

• Date

  ISO 8601 default, can be overridden by specifying the DATE directive and/or the DATEDELIM directive.

• Time

  ISO 8601 default, can be overridden by specifying the TIME directive and/or the TIMEDELIM directive.

• Datetime

  ISO 8601 default, can be overridden by specifying the TSTAMPSEP directive.

  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 (:).

  • Output Host Variables*

    For PIC X output character host variables, CitOESQL returns the data for output host variables in the following datetime formats:

  • Date

    ISO 8601 default, can be overridden by specifying the DATE directive and/or the DATEDELIM directive.

  • Time

    ISO 8601 default, can be overridden by specifying the TIME directive and/or the TIMEDELIM directive.

  • Timestamp

    ISO 8601 default, can be overridden by specifying the TSTAMPSEP directive. When TSTAMPSEP is set to a dash (-) character (TSTAMPSEP="-"), CitOESQL returns datetime columns in the following format: yyyy-mm-dd-hh.mm.ss.ffffff

• SERVER

  Applies to DBMAN=ODBC only.

  Issues SQLDescribeParam calls to the DBMS to identify which input and output host variables are associated with specific character or datetime columns in the database. Host variables associated with datetime columns are translated with the datetime formats listed in CLIENT. Character columns are not translated.

• PICX

Identical to the CLIENT option. Provided for backward compatibility.

Properties:

Defaults: When DETECTDATE is not specified, the default is: NODETECTDATE When DETECTDATE with no argument is specified, the default is: DETECTDATE=CLIENT

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

• Use DETECTDATE=CLIENT only when your CitOESQL application does not use character columns in the database to store data that is a match to any of the alternative formats for the date, time or datetime fields in your DBMS.

• Use DETECTDATE=SERVER when your CitOESQL application uses datetime values in both datetime and character columns of tables in your database.

  Note

  DETECTDATE=SERVER does cause CitOESQL to perform extra overhead processing on each applicable SQL statement.

• Datetime formats are defined implicitly unless you set them explicitly by specifying the DATE, DATEDELIM, TIME, and/or TIMEDELIM directives. See the DATE, TIME, DATEDELIM and TIMEDELIM sections for more information.

ERRORMAP

This directive specifies the name of the error map file to use, and enables SQL error mapping.

Syntax:

ERRORMAP=*map-name*

Parameter:

map-name - The prefix of the error map filename (no file extension)

Properties:

Default None

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

IGNORESCHEMAERRORS

Suppresses compile-time errors resulting from missing schema objects.

Syntax:

[NO]IGNORESCHEMAERRORS

Properties:

Default NOIGNORESCHEMAERRORS

Dependencies:

To use IGNORESCHEMAERRORS, you must also set CHECK.

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

By setting both IGNORESCHEMAERRORS and CHECK, CitOESQL checks for SQL syntax errors without reference to the database schema. This can be helpful when planning an application migration, as you can use the COBOL compiler and CitOESQL to identify the statements that require remediation without having to first migrate the database schema. It also provides enhanced CitOESQL syntax checking during the development phase.

INIT

When set without parameters, the preprocessor automatically generates code to make the connection to the database. When set with the PROT parameter, protects the database when an application terminates abnormally.

Syntax:

INIT[={[PROT\P]}]

NOINIT

Parameters:

• INIT - Generates a CONNECT statement in shared mode and an exit handling code

• INIT=PROT - Generates exit handling code only.

• INIT=P

• NOINIT - No code generation whatsoever.

Properties:

Default NOINIT

Dependencies:

If your INIT call generates a connection, you can use it with the DB and PASS SQL compiler directive options.

Comments:

• For the following reasons, we strongly recommend that you consider placing an EXEC SQL CONNECT statement into your code instead of using INIT, INIT=S or INIT=X :

  • INIT stores user credentials in your code, so its use can raise security concerns.
  • If the DBMS vendor were to change the underlying APIs used to implement a database connection, this could cause compatibility problems when using INIT.

• Set the INIT directive, with or without PROT, only once for each application. Do not set INIT for SQL programs called by other SQL programs. Instead, specify the INIT option for the first SQL program executed in a run unit. Compiling more than one module in an application with the INIT option could cause your program to terminate abnormally.

• INIT is ignored when used within an OO program.

ISOLATION

This directive specifies the isolation level that CitOESQL uses as a connection attribute. It also serves as a primitive directive for the BEHAVIOR directive option.

Syntax:

ISOLATION={UR \ CR \ RR \ SZ}

Parameters:

• UR - Uncommitted Read
• CR - Committed Read
• RR - Repeatable Read
• SZ - Serializable isolation in ODBC

Properties:

Default: CR

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

NIST

Sets CitOESQL to conform to the NIST interpretation of the SQL ANSI 92 entry level standard.

Syntax:

[NO]NIST

Properties:

Default: NONIST

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

ODBCTRACE

ODBCTRACE=USER enables you to control ODBC tracing via odbc.ini from which you can specify the file that the trace goes into.

ALWAYS lets you control ODBC tracing via a directive, which is more convenient from within the IDE. ALWAYS generates the trace into MFSQLTRACE.LOG in the current directory, regardless of the settings in odbc.ini.

NEVER means that the application will never be traced and overrides odbc.ini . As ODBC trace files can contain sensitive information, use NEVER in production applications in secure environments. For more information see the database driver documentation.

Syntax:

ODBCTRACE={ALWAYS \ NEVER \ USER}

Properties:

Default: ODBCTRACE=USER.

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

ODBCV3

This directive causes an application to register itself as an ODBC Version 3 application.

Syntax:

[NO]ODBCV3

Properties:

Default: NOODBCV3

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

If ODBCV3 is not specified, the application registers itself as an ODBC Version 2 application. There can be a small performance benefit in registering as an ODBC 3 application. However, registering as an ODBC Version 3 application can result in error and warning conditions returning different values for SQLCODE and SQLSTATE.

We recommend that you use this directive with care.

ODBCV3 is an alias for ODBCVER=38.

ODBCVER

This directive causes an application to register itself as an ODBC Version 2, 3.x or 3.8 application.

Syntax:

ODBCVER={20 \ 30 \ 38}

Properties:

Default: ODBCVER=20

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

If ODBCVER is not specified, the application registers itself as an ODBC version 2 application.

There can be a small performance benefit in registering as an ODBC version 3 or 3.8 application. However, registering as an ODBC version 3 or 3.8 application can result in error and warning conditions returning different values for SQLCODE and SQLSTATE.

We recommend that you use this directive with care.

ODBCV3 is an alias for ODBCVER=38.

OPTIMIZECURSORS

Optimizes memory consumption. Also applies the same data integrity rules on all databases for embedded SQL cursors that use WITH HOLD and FOR UPDATE clauses.

Syntax:

OPTIMIZECURSORS={YES\NO}

Parameters:

• YES - Optimize cursors. This can result in considerable memory savings and performance gains for large results sets and ensures that SQL cursors that use WITH HOLD and FOR UPDATE clauses have appropriate database locks when positioned updates/deletes occur.

• NO - Provided for backward compatibility.

Properties:

Default: YES

Dependencies:

To use OPTIMIZECURSORS, you must set DBMAN to ODBC explicitly, or by setting an SQL compiler directive option that sets DBMAN to ODBC implicitly.

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

PARAMARRAY

If PARAMARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all input parameters.

Syntax:

[NO]PARAMARRAY

Properties:

Default: PARAMARRAY.

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

PASS

The login to use to connect to the data source. This option works in conjunction with the INIT and/or CHECK options.

Syntax:

PASS={*password* \ *userid.password*}

NOPASS

Properties:

Default: NOPASS

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

PICXBINARY

Enables programs to use PIC X(n) host variables to receive data from BINARY, VARBINARY,

LONGVARBINARY columns in binary format without changing source to use SQL TYPE BINARY host variables.

Syntax:

[NO]PICXBINARY

Properties:

Default: NOPICXBINARY

Scope:

• Used at compile time: No

• Behavior at run time: Source file

See CitOESQL Directives for more information.

PICXBINDING

Specifies the handling of fixed-length PIC X(n) host variables.

Syntax:

PICXBINDING={DEFAULT \ PAD \ TRIM \ TRIMALL \ FIXED \ VARIABLE}

Parameters:

DEFAULT

• SQL Server - SBCS locales

  Always passes PIC X(n) host variables to SQL Server as fixed-length data, and trims trailing spaces. The ODBC driver pads the data with spaces before presenting it to SQL Server.

• All other DBMSs in all locales

  Always passes PIC X(n) host variables to the DBMS as fixed-length data, and trims trailing spaces. If the PIC X(n) host variable is all spaces, present one space to the DBMS.

PAD or FIXED

Always preserves trailing spaces for all DBMSs in all locales and passes the data to the DBMS as fixed-length data.

TRIM or VARIABLE

Always trims trailing spaces for all DBMSs in all locales. If the PIC X(n) host variable is all spaces, presents one space to the DBMS and passes the data to the DBMS as variable-length data.

TRIMALL

Always trims trailing spaces for all DBMSs in all locales. If the PIC X(n) host variable is all spaces, presents an empty string to the DBMS and passes the data to the DBMS as variablelength data.

Properties:

Default: DEFAULT

Dependencies:

ALLOWNULLCHAR is compatible with PICXBINDING only when PICXBINDING is omitted (meaning it is set to DEFAULT by default) or explicitly set to DEFAULT. Because PICXBINDING is designed to be used with PIC X(n) host variables that contain character data, not embedded binary data such as the NULL character ('\0'), an attempt to use ALLOWNULLCHAR when PICXBINDING is set to VARIABLE or FIXED could return unpredictable results.

Scope:

• Used at compile time: No

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

For PIC X(n) host variables that contain all spaces, consider the following effects of PICXBINDING parameters when inserting into VARCHAR columns:

• DEFAULT with SQL Server inserts a space-padded column

• DEFAULT with all non-SQL Server DBMSs inserts one space into the column

• PAD with all DBMSs inserts a space-padded column

• TRIM with all DBMSs inserts one space into the column

• TRIMALL with Oracle inserts a NULL value into the column

• TRIMALL with all non-Oracle DBMSs inserts an empty string into the column

PREFETCH

An application can use this directive to request that CitOESQL use block fetches for cursors. This can provide performance benefits similar to array fetching, without having to change program logic. The performance benefit depends on the value of n and on whether the ODBC driver in use is already configured to use prefetching.

If n is less than 1000, it controls the number of rows to be fetched per batch and the same number of rows is fetched for all cursors. If n is greater than or equal to 1000, it sets the size of the prefetch buffer for each cursor. All cursors will have the same buffer size but the number of rows prefetched will depend on the overall size of the row returned by the query for each cursor.

When PREFETCH=n is used with Microsoft SQL Server, AUTOFETCH is also used for read only cursors. Cursors which are not read only are forced to be keyset cursors and can be used for positioned updates. PREFETCH=n is only supported with DB2, Oracle and Microsoft SQL Server.

Syntax:

PREFETCH=*n*

Properties:

Default: PREFETCH=8

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

Much of the functionality provided by PREFETCH is now incorporated into the functionality of the BEHAVIOR SQL compiler directive option. As a result, PREFETCH is likely to be deprecated in a future release.

QUALFIX

Causes the CitOESQL preprocessor to append three characters to the name of the host variables when declaring them to SQL. This ensures problems caused by qualification (where two or more host variables have identical names when not qualified) are avoided but has the side-effect that SQL error messages sometimes display the names of host variables with the three additional characters appended to them.

Syntax:

[NO]QUALFIX

Properties:

Default: NOQUALFIX

Scope:

• Used at compile time: Yes

• Behavior at run time: N/A

See CitOESQL Directives for more information.

RESULTARRAY

If RESULTARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all output parameters.

Syntax:

[NO]RESULTARRAY

Properties:

Default: RESULTARRAY.

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

SAVE-RETURN-CODE

Specifies whether or not to save and then restore RETURN-CODE.

Syntax:

[NO]SAVE-RETURN-CODE

Properties:

Default: SAVE-RETURN-CODE

Scope:

• Used at compile time: Yes

• Behavior at run time: NA

See CitOESQL Directives for more information.

Comments:

Use NOSAVE-RETURN-CODE if your COBOL dialect does not recognize the COBOL special register RETURN-CODE.

STMTCACHE

The number of prepared SQL statements CitOESQL can cache such that the statements never again require preparation during a program run, thus improving performance.

Syntax:

STMTCACHE=*n*

Parameter:

n Any number; however we recommend that you set this to a number between 20 and 300

Properties:

Default: STMTCACHE=20

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

Exercise caution with this directive as STMTCACHE is a trade off between performance and memory use.

TARGETDB

Set this directive if you want to optimize performance for a specific data source.

Syntax:

TARGETDB={MSSQLSERVER \ ORACLE \ INFORMIX \

    SYBASE \ DB2 \ ORACLE7 \ POSTGRESQL}

NOTARGETDB

Properties:

Default: NOTARGETDB

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

With the vast majority of databases, the FOR READ ONLY clause has no effect on the access plan that the database server generates for a query. This, in combination with the fact that FOR READ ONLY syntax is not supported by most servers, CitOESQL removes the generated FOR READ ONLY clause at compile time unless TARGETDB is set to DB2. Removing this clause can facilitate code migration between different database servers and can also facilitate the incorporation of code that works with multiple types of database servers.

THREAD

Specifies the handling of threads with regard to connections.

Syntax:

THREAD={SHARE \ ISOLATE}

Parameters:

SHARE

All SQL connections, cursors, etc. in an application are shared by all threads. For example, if you have a hard-coded CONNECT statement and thread 1 executes it and then thread 2 executes it, thread 2 gets an error because the connection is already open.

ISOLATE

ODBC only. All connections, cursors, etc. are local to the thread that creates them. This is required for multi-threaded application server environments

Properties:

Default: SHARE

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

TIME

Specifies an explicit time format to use when time values are returned from database time columns into character output host variables.

When used in addition to DETECTDATE, specifies the explicit time format to recognize in character input host variables.

Syntax:

TIME={ODBC \ ISO \ USA \ EUR \ JIS}

Parameters:

• ODBC hh:mm:ss(ISO 8601 default format)

• ISO hh.mm.ss (mainframe default format)

• USA hh:mm (AM \ PM)

• EUR hh.mm.ss

• JIS hh:mm:ss

Properties:

Default: ODBC (ISO 8601 default format)

Dependencies:

For input host parameters, requires that the DETECTDATE SQL compiler directive is also set.

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

• TIME can be used with the TIMEDELIM directive to specify an alternative delimiter that separates hour, minute, and second components.

• TIME, with or without TIMEDELIM, changes the display format of output host variables as specified.

• When you specify both TIME and DETECTDATE, CitOESQL uses TIME (with or without TIMEDELIM) to also recognize time values in your input host variables. See DETECTDATE for more information The following apply to USA format:

  • Minutes can be omitted. For example, 1 PM is equivalent to 1:00 PM.
  • AM and PM are not case sensitive.
  • There must be a single blank before AM or PM.
  • The hour must not be greater than 12 and cannot be 0 except for the special case of 00:00 AM.

TIMEDELIM

Specifies a single character as the delimiter between the hour, minute, and second components to override the default delimiter determined by the TIME directive specification, or implicitly based on default ISO 8601 format (hh:mm:ss).

Syntax:

TIMEDELIM=*character*

Properties:

Default: None

Dependencies:

For input host parameters, requires that the DETECTDATE SQL compiler directive is also set.

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file See CitOESQL Directives for more information.

Comments:

• TIMEDELIM set without TIME overrides the default ISO 8601 delimiter, a colon (:), for time values.

• TIMEDELIM set with TIME overrides the default delimiter for the specified TIME parameter. For example, the default delimiter for TIME=EUR is a dot character (.).

• See the TIME and DETECTDATE sections along with the CitOESQL Datetime Data Types Handling section in the CitOESQL User Guide for more information.

TRACELEVEL

Produces a statistical analysis of application behavior by tracing certain operations in native applications. The report produced by this directive provides better readability and is inherently more useful than a traditional ODBC trace.

The statistical analysis information is written to a logfile named OpenESQLTrace.processID.log, which is created the first time you use TRACELEVEL. With each subsequent use of TRACELEVEL, tracing information is appended to the end of the file. A separator record is written at the end of each trace to help identify different traces.

CitOESQL creates the log files under the directory where the application is located. If file/directory permissions prevent file creation in that location, CitOESQL creates the log files under the directory referenced in the %TEMP% environment variable.

All trace records contain the elapsed run time, accurate to one microsecond.

Syntax:

TRACELEVEL={T \ 1 \ 2 \ 3 \ 4 \ 5 \ 6 \ D}

Scope:

• Used at compile time: No

• Behavior at run time: Process

See CitOESQL Directives for more information.

Comments:

TRACELEVEL=T

When TRACELEVEL=T, the following information is written to the trace file:

• BEGIN - traces main SQL directives
• END - indicates end of run
• DIRECTIVES - traces per compilation unit directives the first time a compilation unit is encountered at run time.

TRACELEVEL=1

When TRACELEVEL=1, the following information is written to the trace file in addition to the information written when you specify TRACELEVEL=T:

• PREPARE - identifies the original source code when a statement is prepared.
• DISPOSE - provides summary information for overall statement usage when a statement is removed from the prepared statement cache at disconnect time
• FLUSH - provides summary information for overall statement usage when a statement is flushed from the cache usually due to a cache overflow

TRACELEVEL=2

When TRACELEVEL=2, the following information is written to the trace file in addition to the information written when you specify TRACELEVEL=1:

- OPEN
- EXECUTE - provides the number of rows selected, inserted, or updated
- EXEC_IMMED EXECUTE - provides the number of rows selected, inserted, or updated
- ODBCCLOSE - provides summary information for the current cursor use
- STMT_CHANGED - reports new concurrency and scroll option settings when the ODBC driver uses different settings than those requested by CitOESQL.

TRACELEVEL=3

When TRACELEVEL=3, the following information is written to the trace file in addition to the information written when you specify TRACELEVEL=2:

- ODBCFETCH - provides the number of rows fetched
- COBOLFETCH - provides the number of rows returned to the COBOL application

TRACELEVEL=4

When TRACELEVEL=4, the following information is written to the trace file in addition to the information written when you specify TRACELEVEL=3:

- EXEC_SQL_BEGIN
- EXEC_SQL_END

TRACELEVEL=5

When TRACELEVEL=5, the following ODBC API call information is written to the trace file in addition to the information written when you specify TRACELEVEL=4:

- ODBC_CALL_START
- ODBC_CALL_END

TRACELEVEL=6

When TRACELEVEL= 6, only the following information is written to the trace file:

- ODBC_CALL_START
- ODBC_CALL_END

TRACELEVEL=D

When TRACELEVEL=D, a reduced version of TRACELEVEL=4 is produced for debugging purposes. It contains only the following information:

- BEGIN
- END
- EXEC_SQL_BEGIN
- EXEC_SQL_END
- DIRECTIVES

TRANSACTION

This directive provides CitOESQL with specifications for managing run-time transactions and, in some cases, enabling compile-time checking.

Syntax:

TRANSACTION={GLOBAL \ LOCAL \ MIXED \ AUTO}

Parameters:

• GLOBAL - Manages transactions via a distributed transaction manager, such as a Java application server, and its external SQL connection. Checks for the following statements that are not compatible with Java application server applications: BEGIN TRANSACTION COMMIT CONNECT DISCONNECT ROLLBACK SET AUTOCOMMIT SET CONNECT SET TRANSACTION [ISOLATION] Reports an error for each incompatible statement found.

• LOCAL - Manages transactions via a data source and its CitOESQL connection.

• MIXED - Manages transactions via a distributed transaction manager (similar to GLOBAL), but does not perform compile-time checking. This allows EXEC SQL CONNECT statements to create local transactions with an CitOESQL connection in addition to the connection provided by the distributed transaction manager.

• AUTO - Use this option when you want to AUTOCOMMIT each statement executed on an CitOESQL connection:

  An application can programmatically control the autocommit setting for a connection by executing the EXEC SQL SET AUTOCOMMIT statement

  An application in autocommit mode can start a local database transaction with the EXEC SQL BEGIN TRANSACTION statement. The transaction ends when the next COMMIT or ROLLBACK statement is executed

  When a transaction ends, if the connection's autocommit attribute is on, the connection reverts to autocommit mode; otherwise a new local database transaction is started automatically.

Properties:

Default: TRANSACTION=LOCAL

Scope:

• Used at compile time: Yes

• Behavior at run time: N/A

See CitOESQL Directives for more information.

TSTAMPSEP

Specifies a single character to use as the separator between the date and time parts when datetime values are returned from database datetime columns into character output host variables.

When used in addition to DETECTDATE, specifies the explicit datetime format to recognize in character input host variables.

Syntax:

TSTAMPSEP='*character*

Parameters:

• Character

  Any single character, including:

• (dash character)

  When TSTAMPSEP='-' and DETECTDATE is not specified

  Character output host variables are returned in the following format: yyyy-mm-dd-hh.mm.ss.ffffff

  When TSTAMPSEP='-' and DETECTDATE is specified**

  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

  • Any other character

  When DETECTDATE is not also specified:

  Character output host variables are returned in the following format: yyyy-mm-ddChh:mm:ss.ffffff

  Where C is any character except a dash (-) character, and the number of fractional seconds is platform dependent.

  When DETECTDATE is also specified

  Character input host variables are returned and scanned using the following format:

  yyyy-mm-ddChh:mm:ss.ffffff

  Where C is any character except a dash (-) character, and the number of fractional seconds is platform dependent.

Properties:

Default: Space character (ISO 8601 default)

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

TSTAMPSEP changes the display format of output host variables as specified.

You can use TSTAMPSEP directive to override the delimiter used in the output format to separate date and time components.

WHERECURRENT

Allows PostgreSQL and MySQL to accept updateable SELECT and CURSOR statements when no positioned UPDATEs or DELETEs are required.

Syntax:

[NO]WHERECURRENT

Properties:

Default: WHERECURRENT

Scope:

• Used at compile time: Yes

• Behavior at run time: Source file

See CitOESQL Directives for more information.

Comments:

• For PostgreSQL, depending on the table definition, the required pseudo column (oid) on a positioned UPDATE or DELETE might be missing.

• For MySQL, depending on the table definition, the required pseudo column (_rowid) on a positioned UPDATE or DELETE might be missing.

• If positioned UPDATEs and DELETEs are required, you might need to change your PostgreSQL or MySQL table definition to expose the appropriate pseudo column and make it available.

• When positioned UPDATEs and DELETEs are not required, use NOWHERECURRENT.

SQL Data Types

ODBC SQL/COBOL Data Type Mappings

The following table shows the mappings used by CitOESQL when converting between ODBC SQL and COBOL data types.

SQL Data Types

• Integer Data Types
• Character Data Types
• Numeric Data Types
• Binary Data Types
• Date and Time Data Types
• Miscellaneous Data Types

Integer Data Types

Small Integer: A small integer (SMALLINT) is a 2-byte integer SQL data type.

Host Variable Formats:

CITOESQL

01 shortint1 PIC S9(4) COMP.

01 shortint2 PIC S9(4) COMP-4.

01 shortint3 PIC X(2) COMP-5.

01 shortint4 PIC S9(4) COMP-5.

01 shortint5 PIC X(2) COMP-X.

01 shortint6 PIC 9(4) COMP-X.

01 shortint7 PIC S9(4) BINARY.

Integer

An integer (INT) is a 4-byte integer SQL data type.

Host Variable Formats:

CITOESQL

All of the following definitions are valid for host variables to map directly onto the INT data type.

01 longint1 PIC S9(9) COMP.

01 longint2 PIC X(4) COMP-5.

01 longint3 PIC S9(9) COMP-5.

01 longint4 PIC X(4) COMP-X.

01 longint5 PIC 9(9) COMP-X. 01 longint6 PIC S9(9) BINARY.

For the most efficient access, we recommend that you declare integers as COMP-5.

Big Integer

A big integer (BIGINT) is an 8-byte integer SQL data type.

Host Variable Formats: CITOESQL

01 bigint1 PIC S9(18) COMP-3.

01 bigint2 PIC S9(18) COMP-5.

01 bigint3 PIC X(8) COMP-5.

01 bigint4 PIC X(8) COMP-X.

In non-COBOL applications, a BIGINT data type can hold a value larger than PIC S9(18). If you define your host variable for a COBOL data time with a value larger than S9(18), your data might be truncated.

Character Data Types

Fixed-length Character Strings

Fixed-length character strings (CHAR) are SQL data types with a driver-defined maximum length. They are declared in COBOL as:

PIC X(*n*) where n is an integer between 1 and the maximum length.

Host Variable Formats:

CITOESQL

01 char-field1 PIC X(5).

01 char-field2 PIC X(254).

01 char-field3 SQL TYPE IS CHAR(200).

The char-field3 format uses the CHAR SQL TYPE.

Variable-length Character Strings

Variable-length character strings (VARCHAR and LONGVARCHAR) are SQL data types with a variable maximum length.

Host Variable Formats:

CITOESQL

01 varchar1.

49 varchar1-len PIC 9(4) COMP.

49 varchar1-data PIC X(200).

01 longvarchar1.

49 longvarchar1-len PIC 9(4) COMP.

49 longvarchar1-data PIC X(30000).

01 clob1 SQL TYPE IS CLOB(32K).

• The level number for group items containing only two elementary items must be 49. The first item is a 2-byte field declared with usage COMP or COMP-5 that represents the effective length of the character string. The length field can be signed or unsigned. The second item is a PIC X(n) data type, where n is an integer representing the length of the field that holds the data.
• SQL statements must reference the group name.
• If the data being copied to a SQL CHAR, VARCHAR or LONG VARCHAR data type is longer than the defined length, then the data is truncated and the SQLWARN1 flag in the SQLCA data structure is set. If the data is smaller than the defined length, a receiving CHAR data type might be padded with blanks.
• The clob1 format uses the CLOB SQL TYPE.

In addition to the above definitions, the following definitions are also valid for CitOESQL:

01 varchar2 PIC X(20) VARYING.

01 varchar3 SQL TYPE IS CHAR-VARYING(200).

01 longvarchar1 SQL TYPE IS LONG-VARCHAR(50000).

• The varchar3 format uses the CHAR-VARYING SQL TYPE.
• The longvarchar1 format uses the LONG-VARCHAR SQL TYPE.

Large Character Strings (CLOB)

Large character strings (CLOB) enable you to store large amounts of data in columns.

Host Variable Formats:

• The level number for group items containing only two elementary items must be 49. The first item is a 4-byte field declared with usage COMP or COMP-5 that represents the effective length of the character string. The length field can be signed or unsigned. The second item is a PIC X(n) data type, where n is an integer representing the length of the field that holds the data.
• SQL statements must reference the group name.

Unicode Character Strings

Unicode character strings (UNI) are SQL data types similar to fixed-length character strings, but are encoded using UTF-16 characters instead of single- or mixed-byte characters.

Host Variable Formats:

CITOESQL

03 uni-field1 PIC N(X) USAGE NATIONAL.

Unicode Variable-length Character Strings

Unicode variable-length character strings (unichar) are SQL data types similar to variable-length character strings, but are encoded using UTF-16 characters instead of single- or mixed-byte characters.

Host Variable Formats:

CITOESQL

01 unichar1.

49 unichar1-len PIC S9(4) COMP-5.

49 unichar1-data PIC N(200) USAGE NATIONAL.

01 unichar2.

49 unichar1-len PIC S9(4) COMP.

49 unichar1-data PIC N(200) USAGE NATIONAL.

• The level number for group items containing only two elementary items must be 49. The first item is a four-byte field declared with usage COMP or COMP-5 that represents the effective length of the character string. The length field can be signed or unsigned. The second item is a PICn NATIONAL data type, where n is an integer representing the length of the field that holds the data.
• SQL statements must reference the group name.

Unicode Large Character Strings (DBCLOB)

Unicode large character strings (DBCLOB) enable you to store large amounts of Unicode data in columns.

Host Variable Formats:

CITOESQL

01 dbclob1 SQL TYPE IS DBCLOB(2M).

The dbclob1 format uses the DBCLOB SQL TYPE. The following definition is also valid:

01 dbclob2.

49 dbclob2-len PIC S9(9) COMP-5.

49 dbclob2-data PIC N(32000) USAGE NATIONAL.

• The level number for group items containing only two elementary items must be 49. The first item is a four-byte field declared with usage COMP or COMP-5 that represents the effective length of the character string. The length field can be signed or unsigned. The second item is a PICn NATIONAL data type, where n is an integer representing the length of the field that holds the data.
• SQL statements must reference the group name.

Numeric Data Types

Approximate Numeric Data Types

Approximate numeric data types (FLOAT, DOUBLE, and REAL) are SQL data types that enable floating points.

Host Variable Formats:

CITOESQL

01 real1 USAGE COMP-1.

01 float1 USAGE COMP-2.

• The real1 format is for the 32-bit (single-precision) SQL floating-point data type, REAL.
• The float1 format is for 64-bit (double-precision) SQL floating-point data types, FLOAT and DOUBLE.

Exact Numeric Data Types

Exact numeric data types (DECIMAL and NUMERIC) can hold values up to a driver-specified precision and scale.

Host Variable Formats:

CITOESQL

01 packed1 PIC S9(8)V9(10) USAGE COMP-3.

01 packed2 PIC S9(8)V9(10) USAGE PACKED-DECIMAL.

01 packed3 PIC S9(8)v9(10) USAGE DISPLAY.

CitOESQL supports:

• Unsigned and signed DISPLAY numerics
• Leading and trailing signs

Unicode Numeric Data Types

Unicode numeric data types (signed and unsigned) can hold values up to a driver-specified precision and scale.

Host Variable Formats:

CITOESQL

01 uninum-us PIC 9(5)v9(5) USAGE NATIONAL.

01 uninum-sls PIC S9(5) SIGN LEADING SEPARATE USAGE NATIONAL.

01 uninum-sts PIC S9(5)v9(5) SIGN TRAILING SEPARATE USAGE NATIONAL.

CitOESQL supports:

• Unsigned and signed Unicode numerics
• Leading and trailing signs

Binary Data Types

Fixed-length Binary Strings

Fixed-length binary data types (RAW, BINARY, and CHAR(x) FOR BIT DATA) are SQL data types with a driver-defined maximum length.

Host Variable Formats:

CITOESQL

03 bin-field1 PIC X(5).

03 bin-field2 SQL TYPE IS BINARY(200).

• SQL BINARY, VARBINARY and IMAGE data are represented in COBOL as PIC X (n) fields.

• CitOESQL does not perform data conversion.

• When data is fetched from the database, if the host-variable field is smaller than the amount of data fetched, the data is truncated and the SQLWARN1 field in the SQLCA data structure is set to W. If the host-variable field is larger than the amount of data, the field is padded with null (x"00") bytes.

• Any of the following enable you to insert data into BINARY, VARBINARY or LONG-VARBINARY columns:

  • Use dynamic SQL statements
  • Compile your application with the ALLOWNULLCHAR directive
  • Use SQL TYPE host variables

• If you use PIC X host variables, compile your application with the ALLOWNULLCHAR directive to prevent the truncation of transferred data to or from the host variable if a null (x"00") is encountered.

• The bin-field2 format uses the BINARY SQL TYPE.

Variable-length Binary Strings

Variable-length binary data types (VARBINARY, LONG-VARBINARY, and LONG VARCHAR FOR BIT DATA) are SQL data types with a driver-defined maximum variable length.

Host Variable Formats:

CITOESQL

01 varbin-field1 SQL TYPE IS VARBINARY(2000).

01 varbin-field2 SQL TYPE IS LONG-VARBINARY(20000). 01 varbin-field3.

49 varbin-field3-len PIC S9(4) COMP-5.

49 varbin-field3-data PIC X(2000).

• SQL BINARY, VARBINARY and IMAGE data are represented in COBOL as PIC X (n) fields.

• CitOESQL does not perform data conversion.

• When data is fetched from the database, if the host-variable field is smaller than the amount of data fetched, the data is truncated and the SQLWARN1 field in the SQLCA data structure is set to W. If the host-variable field is larger than the amount of data, the field is padded with null (x"00") bytes.

• Any of the following enable you to insert data into BINARY, VARBINARY or LONGVARBINARY columns:

  • Use dynamic SQL statements
  • Compile your application with the ALLOWNULLCHAR directive
  • Use SQL TYPE host variables

• The varbin-field1 format uses the VARBINARY SQL TYPE.

• The varbin-field2 format uses the LONG-VARBINARY SQL TYPE.

Large Binary Strings (BLOB)

Large binary string data types (BLOB) are SQL data types that enable you to store large amounts of data, from sources such as JPG files, in binary columns.

Host Variable Formats:

CITOESQL

01 blob1 SQL TYPE IS BLOB(2M).

• SQL BINARY, VARBINARY and IMAGE data are represented in COBOL as PIC X (n) fields.

• CitOESQL does not perform data conversion.

• When data is fetched from the database, if the host-variable field is smaller than the amount of data fetched, the data is truncated and the SQLWARN1 field in the SQLCA data structure is set to W. If the host-variable field is larger than the amount of data, the field is padded with null (x"00") bytes.

• Any of the following enable you to insert data into BINARY, VARBINARY or LONGVARBINARY columns:

  • Use dynamic SQL statements
  • Compile your application with the ALLOWNULLCHAR directive
  • Use SQL TYPE host variables

Date and Time Data Types

COBOL does not support date and time data types directly. Therefore, date and time data columns are converted to COBOL character representations.

DATE

Data Format:

Default and alternative date value formats vary. For CitOESQL, review the DATE and DATEDELIM SQL compiler directive option topics for more information.

For example, one supported date format is:

yyyy-mm-dd

An example value for this format is: 1994-05-24

Host Variable Formats:

CITOESQL

   01 date1    PIC X(10).
   01 date2    SQL TYPE IS DATE.
   01 date4    PIC X(n).

date1 format

• Move date data into the host variable using the form:

  MOVE yyyy-mm-dd TO host-var.

• Review the DETECTDATE SQL compiler directive option topic to determine whether or not it applies to your application.

date2 format

• Move date data into the host variable using the form:

  MOVE yyyy-mm-dd TO host-var.

• Preferred format; use whenever possible.

• Uses the DATE SQL TYPE.

• Similar to the date1 format, date2 never requires DETECTDATE for input host variable processing.

date4 format

• Move date data into host variables using these forms:

  MOVE *yyyy* TO *host-var-year*
  
  MOVE *mm* TO *host-var-month*
  
  MOVE *dd* to *host-var-day*
  

• Uses the DATE-RECORD SQL TYPE.

TIME

Data Format:

Default and alternative time value formats vary. For CitOESQL, review the TIME and TIMEDELIM SQL compiler directive option topics for more information.

For example, one supported time format is:

hh:mm:ss

An example value for this format is: 12:34:00

Host Variable Formats:

• Move time data into a host variable using any of these forms:

  MOVE hh:mm:ss TO host-var.
  
  MOVE hh.mm.ss TO host-var.
  
  MOVE hh:mm PM TO host-var.
  

  - Review the DETECTDATE SQL compiler directive option topic to determine whether or not it applies to your application.

time2 format

• Move time data into a host variable using these forms:

  MOVE `hh:mm:ss` TO `host-var`.
  
  MOVE `hh.mm.ss` TO `host-var`.
  
  MOVE `hh:mm` PM" TO `host-var`.
  

  - Preferred format - use whenever possible. - Uses the TIME SQL TYPE. - Similar to the time1 format, never requires DETECTDATE for input host variable processing.

time4 format

• Move time data into host variables using this form:

  MOVE *hh* TO *host-var-hour*
  
  MOVE *mm* TO *host-var-min*
  
  MOVE *ss* TO *host-var-sec*
  

  - Uses the TIME-RECORD SQL TYPE.

TIMESTAMP

Default and alternative timestamp value formats vary. For CitOESQL, review the TSTAMPSEP SQL compiler directive option topic for more information. For example, one supported time format is:

yyyy-mm-dd hh:mm:ss[.f[f[...]]] where the number of fractional digits is driver-defined. An example value for this format is: 1994-05-24 12:34:00.000

Host Variable Formats:

CITOESQL

01 timestamp1 PIC X(29).

01 timestamp2 SQL TYPE IS TIMESTAMP.

The timestamp2 format uses the TIMESTAMP SQL TYPE.

01 timestamp4 SQL TYPE IS TIMESTAMP-RECORD.

timestamp1 format

• Move timestamp data into a host variable using the form:

  MOVE yyyy-mm-dd hh:mm:ss TO timestamp1

• Review the DETECTDATE SQL compiler directive option topic to determine whether or not it applies to your application.

timestamp2 format

• Move timestamp data into a host variable using the form:

MOVE yyyy-mm-dd hh:mm:ss TO timestamp2

• Preferred format - use whenever possible.
• Uses the TIMESTAMP SQL TYPE.
• Similar to the timestamp1 format, but when using Visual COBOL version 2.3 or later, never requires DETECTDATE for input host variable processing.

timestamp4 format

• Move timestamp data into host variables using these forms:

  MOVE yyyy TO host-var-year
  
  MOVE mm TO *host-var-month
  
  MOVE dd TO host-var-day
  
  MOVE hh TO host-var-hour
  
  MOVE mm TO host-var-minute
  
  MOVE ss TO host-var-sec
  
  MOVE ff TO host-varc-fra
  

• Uses the TIMESTAMP-RECORD SQL TYPE.

TIMESTAMPOFFSET

If a COBOL output host variable is defined for an SQL timestamp value with an offset, the date and time are specified in the following format:

yyyy-mm-dd hh:mm:ss[.f[f[...]]] {+ \ -}hh:mm

where the number of fractional digits is driver-defined. For example: 1994-05-24 12:34:00.000 +02:00

Host Variable Formats:

CITOESQL

01 timestampoffset1 PIC X(36).

01 timestampoffset2 SQL TYPE IS TIMESTAMP-OFFSET.

01 timestampoffset3 SQL TYPE IS TIMESTAMP-OFFSET-RECORD.

• The timestampoffset2 format uses the TIMESTAMP-OFFSET SQL TYPE.
• The timestampoffset3 format uses the TIMESTAMP-OFFSET-RECORD SQL TYPE.

Miscellaneous Data Types

PIC X VARYING Data Type

Syntax:

PIC X(*n*) VARYING

Where n is an integer representing the length of the field that holds the data.

Example:

01 ename PIC X(15) VARYING.

generates

01 ename.

05 ename-len PIC S9(4) COMP.

05 ename-ARR PIC X(15).

SQL TYPEs

• BINARY SQL Type
• BLOB SQL Type
• CHAR SQL Type
• CHAR-VARYING SQL Type
• CLOB SQL Type
• DATE SQL Type
• DATE-RECORD SQL Type
• DBCLOB SQL Type
• LONG-VARBINARY SQL Type
• LONG-VARCHAR SQL Type
• TIME SQL Type
• TIME-RECORD SQL Type
• TIMESTAMP SQL Type
• TIMESTAMP-RECORD SQL Type
• TIMESTAMP-OFFSET SQL Type
• TIMESTAMP-OFFSET-RECORD SQL Type
• VARBINARY SQL Type

BINARY SQL Type

Syntax:

SQL [TYPE] [IS] BINARY(*n*)

Example:

01 hv-name SQL TYPE IS BINARY(200).

generates

01 hv-name PIC X(200).

BLOB SQL Type

Syntax:

SQL [TYPE] [IS] BLOB(*lob-length*)

Where lob-length is a value between 1 and 1073741823 expressed either as a number or a number followed by K (kilobytes), M (megabytes), or G (gigabytes).

Example:

01 hv-name SQL TYPE IS BLOB(2M).

generates

01 hv-name.

03 hv-name-length PIC S9(9) COMP-5.

03 hv-name-data PIC X(2097152).

CHAR SQL Type

Syntax:

SQL [TYPE] [IS] CHAR(*n*)

Example:

01 hv-name SQL TYPE IS CHAR(200).

generates

01 hv-name PIC X(200).

CHAR-VARYING SQL Type

Syntax:

SQL [TYPE] [IS] CHAR-VARYING(*n*)

• CHAR-VARYING data is passed to CitOESQL as SQL_VARCHAR.
• Data sent to the data source eliminates trailing spaces except for the first space if the value is all spaces.
• Values fetched from the data source are padded with spaces.

Example:

01 hv-name SQL TYPE IS CHAR-VARYING(200).

generates

01 hv-name PIC X(200).

CLOB SQL Type

Syntax:

SQL [TYPE] [IS] CLOB(lob-length)

Where lob-length is a value between 1 and 1073741823 expressed either as a number or a number followed by K (kilobytes), M (megabytes), or G (gigabytes).

Example:

01 hv-name SQL TYPE IS CLOB(2M).

generates

01 hv-name.

03 hv-name-length PIC S9(9) COMP-5.

03 hv-name-data PIC X(2097152).

DATE SQL Type

Syntax:

SQL [TYPE] [IS] DATE

Use DATE to generate a single working-storage record to contain all date information.

Example:

01 hv-name SQL TYPE IS DATE.

generates

01 hv-name PIC X(10).

DATE-RECORD SQL Type

Syntax:

SQL [TYPE] [IS] DATE-RECORD

Use DATE-RECORD to generate a group-level record for the date containing individual records for each element of the date as follows:

• Year
• Month
• Day

Example:

01 hv-name SQL TYPE IS DATE.

generates

01 hv-name PIC X(10).

DBCLOB SQL Type

Syntax:

SQL [TYPE] [IS] DBCLOB(lob-length)

Where lob-length is a value between 1 and 1073741823 expressed either as a number or a number followed by K (kilobytes), M (megabytes), or G (gigabytes).

Example:

01 hv-name SQL TYPE IS DBCLOB(2M).

generates

01 hv-name.

03 hv-name-length PIC S9(9) COMP-5.

03 hv-name-data PIC N(2097152).

LONG-VARBINARY SQL Type

Syntax:

SQL [TYPE] [IS] LONG-VARBINARY(*n*)

Example:

01 hv-name SQL TYPE IS LONG-VARBINARY(2000).

generates

01 hv-name.

03 hv-name-len PIC S9(9) COMP-5.

03 hv-name-val PIC X(2000).

LONG-VARCHAR SQL Type

Syntax:

SQL [TYPE] [IS] LONG-VARCHAR(*n*)

Example:

01 hv-name SQL TYPE IS LONG-VARCHAR(65000).

generates

01 hv-name.

03 hv-name-len PIC S9(9) COMP-5.

03 hv-name-val PIC X(65000).

TIME SQL Type

Syntax:

SQL [TYPE] [IS] TIME

Use TIME to generate a single working-storage record to contain all time information.

Example:

01 hv-name SQL TYPE IS TIME.

generates

01 hv-name PIC X(8).

TIME-RECORD SQL Type

Syntax:

SQL [TYPE] [IS] TIME-RECORD

Use TIME-RECORD to generate a group-level record for the time containing individual records for each element as follows:

• Hour
• Minutes -Seconds

To insert data, you must pass valid data in the generated field names.

Example:

01 hv-name SQL TYPE IS TIME-RECORD.

generates

01 hv-name.

03 hv-name-hour PIC 9(4) COMP-5. 03 hv-name-min PIC 9(4) COMP-5. 03 hv-name-sec PIC 9(4) COMP-5.

TIMESTAMP SQL Type

Syntax:

SQL [TYPE] [IS] TIMESTAMP

Use TIMESTAMP to generate a single working-storage record to contain all timestamp information.

• Data must be organized in fixed date/time formats.
• Fractional seconds are supported up to nine digits. However, this value can vary depending on your target DBMS and your ODBC driver. See your DBMS or ODBC driver documentation for more information.
• Fractional data is passed left justified and must contain the number of digits defined in your record. For example, to pass a fractional value of 678 to a record that defines fractional data as nine digits, move the value 678000000.
• Fractional data is right justified when returned from a SELECT or FETCH statement.
• Because of the way SQL Server stores date/time values, it might round the last fractional digit of a fractional value up or down depending on the digit. For example:

Example:

01 hv-name SQL TYPE IS TIMESTAMP.

generates

01 hv-name PIC X(29).

TIMESTAMP-RECORD SQL Type

Syntax:

SQL [TYPE] [IS] TIMESTAMP-RECORD

Use TIMESTAMP-RECORD to generate a group-level record for the timestamp containing individual records for each element of the timestamp as follows:

• Year
• Month
• Day
• Hour
• Minute
• Second
• Fractional second

To insert data, you must pass valid data in the generated field names.

• Data must be organized in fixed date/time formats.
• Fractional seconds are supported up to nine digits. However, this value can vary depending on your target DBMS and your ODBC driver. See your DBMS or ODBC driver documentation for more information.
• Fractional data is passed left justified and must contain the number of digits defined in your record. For example, to pass a fractional value of 678 to a record that defines fractional data as nine digits, move the value 678000000.
• Fractional data is right justified when returned from a SELECT or FETCH statement.
• Because of the way SQL Server stores date/time values, it might round the last fractional digit of a fractional value up or down depending on the digit. For example:

Example:

01 hv-name SQL TYPE IS TIMESTAMP-RECORD.

generates

01 hv-name.

03 hv-name-year PIC S9(4) COMP-5.

03 hv-name-month PIC 9(4) COMP-5.

03 hv-name-day PIC 9(4) COMP-5. 03 hv-name-hour PIC 9(4) COMP-5. 03 hv-name-min PIC 9(4) COMP-5.

03 hv-name-sec PIC 9(4) COMP-5. 03 hv-name-frac PIC 9(9) COMP-5.

TIMESTAMP-OFFSET SQL Type

Syntax:

SQL [TYPE] [IS] TIMESTAMP-OFFSET

Use TIMESTAMP-OFFSET to generate a single working-storage record to contain all timestamp information.

Example:

01 hv-name SQL TYPE IS TIMESTAMP-OFFSET.

generates

01 hv-name PIC X(36).

TIMESTAMP-OFFSET-RECORD SQL Type

Syntax:

SQL [TYPE] [IS] TIMESTAMP-OFFSET-RECORD

Use TIMESTAMP-OFFSET-RECORD to generate a group-level record for the timestamp containing individual records for each element of the timestamp as follows:

• Year
• Month
• Day
• Hour
• Minute
• Second
• Fractional second
• Offset hours
• Offset minutes

To insert data, you must pass valid data in the generated field names.

Example:

01 hv-name SQL TYPE IS TIMESTAMP-OFFSET-RECORD.

generates

01 hv-name.

03 hv-name-year PIC S9(4) COMP-5.

03 hv-name-month PIC 9(4) COMP-5.

03 hv-name-day PIC 9(4) COMP-5. 03 hv-name-hour PIC 9(4) COMP-5. 03 hv-name-min PIC 9(4) COMP-5.

03 hv-name-sec PIC 9(4) COMP-5. 03 hv-name-frac PIC 9(9) COMP-5.

03 hv-name-tz-hour PIC S9(4) COMP-5.

03 hv-name-tz-min PIC S9(4) COMP-5.

VARBINARY SQL Type

Syntax:

SQL [TYPE] [IS] VARBINARY(n)

Example:

01 hv-name SQL TYPE IS VARBINARY(2000).

generates

01 hv-name.

49 hv-name-len PIC S9(4) COMP-5.

49 hv-name-val PIC X(2000).

49 hv-name-text REDEFINES hv-name-val PIC X(2000).

USAGE Data Types

CitOESQL supports the following non-COBOL USAGE Clauses:

• USAGE VARCHAR
• USAGE LONG VARCHAR -USAGE VARRAW
• USAGE LONG VARRAW
• USAGE VARYING

USAGE VARCHAR Data Type

Syntax:

USAGE VARCHAR

Example:

01 HV-NAME PIC X(30) USAGE VARCHAR.

generates

01 HV-NAME.

03 HV-NAME-LEN PIC S9(4) COMP-5 VALUE 0.

03 HV-NAME-ARR PIC X(30).

USAGE LONG VARCHAR Data Type

Syntax:

USAGE VARCHAR

Example:

01 HV-NAME PIC X(300) USAGE LONG VARCHAR.

generates

01 HV-NAME.

03 HV-NAME-LEN PIC S9(4) COMP-5 VALUE 0.

03 HV-NAME-ARR PIC X(300).

USAGE VARRAW Data Type

Syntax:

USAGE VARRAW

Example:

01 HV-NAME PIC X(30) USAGE VARRAW.

generates

01 HV-NAME.

03 HV-NAME-LEN PIC S9(9) COMP-5 VALUE 0.

03 HV-NAME-ARR PIC X(30).

USAGE LONG VARRAW Data Type

Syntax:

USAGE LONG VARRAW

Example:

01 HV-NAME PIC X(300) USAGE LONG VARRAW.

generates

01 HV-NAME.

03 HV-NAME-LEN PIC S9(9) COMP-5 VALUE 0.

03 HV-NAME-ARR PIC X(300).

USAGE VARYING Data Type

Syntax:

USAGE VARYING

Example:

01 HV-NAME PIC X(30) USAGE VARYING.

generates

01 HV-NAME.

03 HV-NAME-LEN PIC S9(4) COMP-5 VALUE 0.

03 HV-NAME-ARR PIC X(30).