You can use COBOL fixed-length character strings, declared as PIC X(n), as input, output, or a combination of input and output host variables in EXEC SQL statements. The following examples illustrate
different uses of fixed-length character strings:
- Example 1: Input using the HVIN host variable, two statements
EXEC SQL INSERT INTO TAB1 (COL1)VALUES (:HVIN) END-EXEC
EXEC SQL DELETE * FROM TAB1 WHERE COL1 LIKE :HVIN END-EXEC
- Example 2: Output using the HVOUT host variable, one statement
EXEC SQL SELECT COL1 INTO :HVOUT FROM DUAL END-EXEC
- Example 3: Input using the HVIN host variable, and output using the HVOUT host variable, two statements
EXEC SQL SELECT COUNT(*) INTO :HVOUT FROM TAB1 WHERE COL1 = :HVIN END-EXEC
EXEC SQL SELECT 'START' || :HVIN ||'END' INTO :HVOUT FROM SYSIBM.SYSDUMMY1 END-EXEC
Problem - preserve or trim trailing spaces
Typically, COBOL PIC X(n) host variables are not terminated with the NULL character ('\0'), but rather with trailing spaces (see
Fixed-length Character Strings for details). Given the different ways PIC X(n) host variables can be constructed and used with varying DBMS SQL column definitions such as CHAR or VARCHAR, OpenESQL has
difficulty determining whether to:
- Preserve trailing spaces, meaning to present the trailing spaces to the database
- Trim trailing spaces, meaning to omit the trailing spaces from the database
Consider the INSERT statement in
Example 1 above. If COL1 is a VARCHAR column in TAB1, should trailing spaces contained in HVIN be preserved or trimmed?
Consider also the DELETE statement in
Example 1 in the same context. When used with the LIKE predicate, should trailing spaces contained in HVIN be preserved or trimmed?
- Use the OpenESQL default behavior
- The current OpenESQL PIC X(n) handling of trailing spaces has evolved over the years to address VARCHAR and WHERE expression issues. Currently by default,
OpenESQL strips all trailing spaces before presenting input host variable values to the database with one exception, that
is, when a PIC X(n) host variable contains only spaces. In that case, OpenESQL presents a single space to the database. The advantage of this
approach is that the database itself pads host variables with trailing spaces before placing them into CHAR columns, and does
not pad VARCHAR columns with trailing spaces.
- Replace fixed-length host variables with variable-length host variables
- Outside of the OpenESQL default behavior, best practice (insofar as it is possible) for ensuring the proper handling of trailing
spaces in VARCHAR DBMS columns is to replace PIC X(n) host variables with variable-length character strings (see
Variable-length Character Strings for details). In this case, the program itself explicitly controls the VARCHAR length and value.
- Use the RTRIM and CAST functions
- Another approach to resolving this problem is to re-write specific SQL statements in a portable manner using the RTRIM(:hvin) function to trim trailing spaces, and the CAST(:hvin as CHAR(n)) function to preserve trailing spaces, where
hvin represents an input host variable.
- Use the PICXBINDING directive
- If you need your database to preserve space padding in VARCHAR columns, but you cannot change fixed-length host variables
to variable-length, nor can you use the RTRIM or CAST functions, try using the OpenESQL PICXBINDING SQL compiler directive
option to preserve or trim trailing spaces. This can be helpful, for instance, when using LIKE expressions with SQL Server.
PICXBINDING for details.
Note: PICXBINDING applies to all SQL statements and associated host variables in a given COBOL source file. If you need more granularity,
however, you must use variable-length host variables, or the RTRIM or CAST functions on specific SQL statements.