Data Type Compatibility

With the exception of the data types listed in Unsupported JDBC Features, the JDBC Driver supports conversion of Ingres data values into Java/JDBC values as required by the JDBC specification.

Because Ingres does not support all the JDBC data types, the following conventions are used when sending Java/JDBC parameters to the DBMS:

Generally, NULL values sent to the DBMS are associated with the data type provided in the setNULL() or setObject() method call or the data type implied by the setXXX() method call. A generic or typeless NULL value can be sent to the DBMS using one of the following method calls:

Boolean values are sent to the DBMS as single byte integers with the value 0 or 1.

Long values are sent to the DBMS as DECIMAL (if supported by the DBMS) or DOUBLE values when BIGINT is not supported by the DBMS.

BigDecimal values are sent as DOUBLE values when DECIMAL is not supported by the DBMS. Avoid using the BigDecimal constructor that takes a parameter of type double. This constructor can produce decimal values that exceed the scale/precision supported by Ingres.

For earlier versions of Ingres and Enterprise Access gateways in which ANSI date/time data types are not supported, Ingres supports a single date data type, which is used for DATE, TIME, and TIMESTAMP values. Ingres dates do support date without time values and this form is used for JDBC DATE values.

For earlier versions of Ingres and Enterprise Access gateways in which ANSI date/time data types are not supported, Ingres supports a single date data type that is used for DATE, TIME, and TIMESTAMP values. Ingres dates do not support date without time values. The JDBC Driver adds the JDBC date epoch 1970-01-01 to JDBC TIME values. The Ingres DBMS adds the current date to time-only values.

Zero length CHAR values are sent as VARCHAR values. For conventions associated with NCS enabled databases, see National Character Set Columns. For information on automatic conversion to LONGVARCHAR, see the end of this section.

For conventions associated with NCS enabled databases, see National Character Set Columns. For information on automatic conversion to LONGVARCHAR, see the end of this section.

The LONGVARCHAR type is used by the driver to represent Character and NCS Large Object values passed to the driver as data streams. For conventions associated with NCS enabled databases, see National Character Set Columns.

The LONGVARBINARY type is used by the driver to represent Binary Large Object values passed to the driver as data streams.

The BLOB type is used by the driver to represent Locators to Binary Large Object values residing in the database. Blob objects can be used to access the blob value. Blob values in the database are read-only, therefore Blob objects load and cache the referenced blob value locally when modification requests are made.

Driver handling of Blob parameters is dependent on the value represented by the Blob object. If a Blob object represents a Locator associated with the connection, the driver sends the Locator as the parameter value. If a Blob object represents a Locator associated with a different connection, a cached blob value, or is an object which did not originate from the driver, the driver sends the blob parameter value as a LONGVARBINARY data stream.

The CLOB type is used by the driver to represent Locators to Character and NCS Large Object values residing in the database. Clob objects can be used to access the clob value. Clob values in the database are read-only, therefore Clob objects cache the referenced clob value when modification requests are made.

Driver handling of Clob parameters is dependent on the value represented by the Clob object. If a Clob object represents a Locator associated with the connection, the driver sends the Locator as the parameter value. If a Clob object represents a Locator associated with a different connection, a cached clob value, or is an object which did not originate from the driver, the driver sends the clob parameter value as a LONGVARCHAR data stream.

In addition to the JDBC types listed above, the following conventions are used when certain Java data values are provided to the setObject() method:

Byte arrays are sent by default as VARBINARY values.

While not required by JDBC, character arrays are supported by the JDBC Driver and are sent by default as CHAR values. For conventions associated with NCS enabled databases, see National Character Set Columns. For information on automatic conversion to LONGVARCHAR, see the end of this section.

Strings are sent by default as VARCHAR values. For conventions associated with NCS enabled databases, see National Character Set Columns. For information on automatic conversion to LONGVARCHAR, see the end of this section.

While not required by JDBC, InputStream objects are supported by the JDBC Driver and are sent by default as LONGVARBINARY values.

While not required by JDBC, Reader objects are supported by the JDBC Driver and are sent by default as LONGVARCHAR values. For conventions associated with NCS enabled databases, see National Character Set Columns.

JDBC requires BINARY, VARBINARY, CHAR, and VARCHAR parameter values to be converted to LONGVARBINARY/LONGVARCHAR when their length exceeds some DBMS dependent maximum.

The default maximum used by the JDBC Driver is 2000 bytes. This default maximum value can be incorrect for an Ingres database that has been configured with non-default page sizes and for EDBC or Enterprise Access gateways.

The JDBC Driver uses the following entries in the iidbcapabilities system catalog to determine at runtime the appropriate size limits:

SQL_MAX_BYTE_COLUMN_LEN
SQL_MAX_VBYT_COLUMN_LEN
SQL_MAX_CHAR_COLUMN_LEN
SQL_MAX_VCHR_COLUMN_LEN

Not all releases of the Ingres DBMS, Vector DBMS, EDBC, and Enterprise Access gateways have these entries in their iidbcapabilities system catalogs. These entries can be entered manually to provide accurate size information for the driver. Depending on the DBMS involved, special permissions are required to update the system catalog.