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

OpenESQL
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.
  • OpenESQL 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
    • Use .NET Byte host variables
  • The varbin-field1 format uses the VARBINARY SQL TYPE.
  • The varbin-field2 format uses the LONG-VARBINARY SQL TYPE.
Note: When using 01 varbin-field3, if SQLSTATE returns a 01004 error (data truncated), we recommend that you try 01 varbin-field1 SQL TYPE IS VARBINARY(2000) instead.
OpenESQL .NET Managed Runtime
In addition to the definitions valid for OpenESQL, the following definition is also valid for the OpenESQL .NET Managed Runtime:
01 varbin-field4      type System.Byte[].

OpenESQL .NET Managed Runtime applications have no byte limit.

DB2 ECM
01 varbin-field3.
   49 varbin-field3-len    PIC S9(4) COMP-5.
   49 varbin-field3-data   PIC X(2000).
  • Use CHAR FOR BIT DATA to represent BINARY.
  • Use VARCHAR(n) FOR BIT DATA to represent VARBINARY.
  • Use LONG VARCHAR FOR BIT DATA to represent LONG-VARBINARY.
  • The IBM ODBC driver returns the BINARY, VARBINARY and LONG VARBINARY data types instead of the IBM equivalent.
  • The IMAGE data type can be represented by BLOB.
  • DB2 uses LOBs (Character Large Object, Binary Large Object or Graphical Large Object) to define very large columns (2 Gigabytes maximum). You can use static SQL with these data types.