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.

Microsoft™ SQL Server

Microsoft SQL Server provides the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types to store large object data.

The SQL Server TEXT, NTEXT and IMAGE data types still exist, but are considered as obsolete and will be removed in a future version.

In SQL Server, the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) types have a limit of 2 gigabytes (2^31 -1 actually).

Solution

In Genero programs connecting to SQL Server, the TEXT and BYTE data types of DDL statements such as CREATE TABLE are respectively converted to VARCHAR(MAX) and VARBINARY(MAX) types.

SQL Server database drivers make the appropriate bindings to use TEXT and BYTE Genero types as SQL parameters and fetch buffers, and can be used for SQL SERVER text, image or VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns.

Genero TEXT/BYTE program variables and the SQL Server large object types have the same a limit of 2 gigabytes.

Note: When using a stored procedure that has SET/IF statements and produces a result set with LOBs, the LOB columns must appear at the end of the SELECT list. If LOB columns are followed by other columns with regular types, the fetching rows will fail. Using SET NOCOUNT ON in the stored procedure does not help, because the cursor type is changed from a server cursor to a default result set cursor.
The TEXT and BYTE types translation can be controlled with the following FGLPROFILE entries:
dbi.database.dsname.ifxemul.text = { true | false }
dbi.database.dsname.ifxemul.byte = { true | false }
For more details see IBM Informix emulation parameters in FGLPROFILE.