Long Varchar and Long Byte Data Types
The gateways support the Ingres long varchar and long byte data types as large objects (LOBs). The only differences in support are that restrictions on data type manipulation differ slightly from gateway to gateway.
The following table lists the capacity of LOBs in each gateway. When using the gateway to create a table, see
Data Type Mapping for the mapping of long varchar and long byte data types.
Microsoft has marked three Large Object types (LOBs) for future deprecation in Microsoft SQL Server. They are mapped to the following Enterprise Access types.
Enterprise Access now supports the following new LOBs, which Microsoft documents as the preferred types, introduced in Microsoft SQL Server 2005:
*MAX indicates the maximum storage size as 2^31 – 1 characters. This size matches the Ingres supported LOB size.
Note: If you want to migrate existing Microsoft SQL tables that use the old LOB format to the new format, we recommend that you follow Microsoft SQL migration procedures.
Oracle Gateway Only
Starting with Enterprise Access 2.7, the Oracle gateway no longer maps the Ingres Long Varchar and Long Byte data types to the Oracle LONG and LONG RAW data types. The Oracle gateway maintained a limit of 65,535 bytes of data that could be stored in the Long Varchar and Long Byte data types. Oracle has deprecated the LONG and LONG RAW data types and recommends using CLOB and BLOB data types. Oracle will not enhance the deprecated data types and recommends migrating to BLOB and CLOB. See the Oracle documentation for information about converting from LONG/LONG RAW to CLOB/BLOB. The gateway requires no change in order to use the new data types.
Since Enterprise Access 2.6, the default mapping of Long Varchar and Long Byte columns has been to CLOB and BLOB to provide support for larger LOBs. In order to take advantage of larger LOBs for existing Long Varchar and Long Byte columns, you will need to migrate the underlying Oracle columns LONG/LONG RAW to CLOB/BLOB using Oracle utilities. When the CREATE TABLE DDL statement is received by the gateway, Long Varchar and Long Byte columns will map to CLOB and BLOB columns.
The following DDL statements are not supported for Long Varchar and Long Byte columns:
• create table as select
• insert into select
Data Handlers
To read and write large objects, create routines called data handlers. When the query is executed, the data handler routine is invoked to read or write the column. A Long Byte data handler and a Long Varchar data handler differ. See the Ingres OpenSQL Reference Guide for more information about using data handlers.
The following example shows how to insert data into a column using ESQL and a data handler routine:
/* Insert non-lob data */
exec sql insert into lob_table (col1_int, col2_string)
values (:data1_int, :data2_string);
/* update lob data */
exec sql update lob_table
set col3_lob = datahandler (my_inputhdlr() )
where col1_int = :data1_int;
The following example shows how to select data from a column using ESQL and a data handler routine:
exec sql select col2_string, col3_lob
into :data2_string, datahandler( my_outputhdlr() )
from lob_table
where col1_int = :data1_int;
Note: For maximum portability, create the LOB access separate from the non-LOB columns.
Restrictions on LOB Manipulation
LOB manipulation is limited by the restrictions imposed by the target DBMS. See the target DBMS documentation for further details.
LOB manipulation has the following restrictions:
• Must use host variables in LOB DML statements.
• Must use the where clause in LOB update statements and is recommended for select statements.
Note: When passing in data, most DBMSs expect the data to be the same LOB type as the target column. That is, when the target is a BLOB (LONG BYTE) column, the data handler must be a binary handler. This is especially important for empty (or zero length) BLOB values because ESQL/C defaults to sending a CLOB (LONG VARCHAR) unless an explicit PUT DATA call is made with a binary buffer of zero length.
Last modified date: 08/22/2022