OpenESQL Fixed-length Character Strings Handling

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?

Solutions

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. See 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.