TEXT and BYTE (LOB) types

Informix®

Informix provides the TEXT, BYTE, CLOB and BLOB data types to store very large texts or binary data.

Legacy Informix 4GL applications typically use the TEXT and BYTE types.

Genero BDL does not support the Informix CLOB and BLOB types.

SAP ASE

SAP® ASE provides the TEXT and IMAGE data types for large objects storage.

SAP ASE 16.0 does not support TEXT/IMAGE expressions in WHERE clauses.

Solution

TEXT and BYTE data types are supported by the SAP ASE database interface, with some limitation.

Important: The SAP ASE ODI driver is implemented with the SAP Open Client Library C API. In SAP ASE version 16.0, this API has limited support for LOBs, especially when it comes to update LOB data in the database. You cannot directly INSERT large LOB data, you must first INSERT nulls and then UPDATE the row with the real data. Additionally, UPDATE can only take one LOB parameter at a time. Fetching LOB data is supported, with the following limitation: LOB columns must appear at the end of the SELECT list.

When inserting TEXT/BYTE data in a table, you must first insert with nulls, the update the new row, and only with one TEXT/BYTE parameter at a time:

DEFINE ptext TEXT, pbyte BYTE
...
LOCATE ptext IN ...
LOCATE pbyte IN ...
CREATE TABLE tab (k INT, t TEXT, b BYTE)
-- First INSERT a new row with NULLs
INSERT INTO tab VALUES (123,null,null)
-- Then UPDATE first TEXT column
UPDATE tab SET t = ptext WHERE k = 123
-- Then UPDATE second BYTE column
UPDATE tab SET b = pbyte WHERE k = 123

Fetching TEXT/BYTE columns is possible as long as the columns appear at the end of the SELECT list. For example, if you have a statement such as (where pdata is a TEXT or BYTE column):

SELECT pid, pdata, ptimestamp FROM pic WHERE ...

Put the TEXT/BYTE column at the end of the SELECT list:

SELECT pid, ptimestamp, pdata FROM pic WHERE ...
The TEXT and BYTE types translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.datatype.text = { true | false }
dbi.database.dsname.ifxemul.datatype.byte = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.