Map COBOL elements as specific SQL data types?

Originally from ticket #1719.

Question

Hello, 

In Postgres I have a table defined with a column of type BYTEA, an arbitrary-length string of bytes.  If I compare it with a host variable of type PIC X(18) in the statement:

EXEC SQL DECLARE C1 CURSOR FOR 
    select xid,xbyte 
    from pbytea 
    where xbyte  > :keybyte 
END-EXEC.

I get the error message from Postgres:

ERROR: operator does not exist: bytea > character varying 
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. 
Position: 42

Which says that the > operator cannot handle comparing "bytea" and "character varying" data.  How do I tell Elastic COBOL what datatype keybyte should be interpreted as?

Answer

Elastic COBOL will assume JDBC datatype VARCHAR(n) for COBOL elements defined as PIC X(n) unless otherwise informed of a different datatype. 

There are four ways to resolve this issue, choose (1) or (2) or (3) or (4):

1. DDL Fix.  Change the type of the column to VARCHAR(n)
2. Postgres Fix. Add a Postgres "CAST" to one comparison operator argument to align it with the other; change the "DECLARE CURSOR" statement to
   

   EXEC SQL DECLARE C1 CURSOR FOR
       select xid,xbyte
       from pbytea
       where CAST(xbyte AS TEXT) > :keybyte
   END-EXEC.

3. DB2 Fix. Change the COBOL element declaration line to include the SQL TYPE IS clause:
   

   EXEC SQL BEGIN DECLARE SECTION END-EXEC
       77 xid PIC X(5).
       77 xbyte PIC X(18).
       77 keybyte pic X(18) SQL TYPE IS BYTEA.
   EXEC SQL END DECLARE SECTION END-EXEC.

4. Oracle Pro*COBOL Fix.  Add a statement following the syntax of the Oracle Pro*COBOL preprocessor VAR hostvar IS statement:
   

   EXEC SQL
       VAR keybyte IS BYTEA
   END-EXEC

Note:   BYTEA is not supported by all databases. Use BLOB(n), CLOB(n), VARBINARY(n) or LONG_VARBINARY(n) in databases that don't support BYTEA.