Large OBject (LOB) types

IBM^® Informix^® and Genero support the TEXT and BYTE types to store large objects: TEXT is used to store large text data, while BYTE is used to store large binary data like images or sound.

Microsoft™ SQL SERVER 2005 and higher provides the VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types to store large object data. The 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). The old text, ntext and image types have the same limit.

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.