Very large data types

Both Informix^® and Sybase ASE provide special data types to store very large texts or images.

Sybase ASE provides the text and image data types as equivalent of Informix TEXT and BYTE types.

Important: Sybase ASE 15.5 does not support text/image expressions in WHERE clauses.

The ASE driver is implemented with the Sybase Open Client Library C API. In Sybase version 15.5, this API has a 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.

Solution

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

When INSERTing TEXT/BYTE 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 and 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 BYTE column at the end of the SELECT list:

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