Chapter 4. Ingres-specific Peculiarities

The driver will not be loaded at all by dbi_initialize() if the Ingres environment variables are not set (e.g. by ~/.ingIIbash). If the current user or specified user does not have appropriate Ingres privileges, the connection will fail ("User authorization check failed" or similar).

When connecting, the driver uses the values of the dbname, username and password options, if set.

'Autocommit' mode is enabled by default for the DBMS connection. In this mode, every query is committed as an individual transaction.

With autocommit disabled (see ingres_autocommit connection option), a transaction is begun by the first query statement after connection, commit or rollback. The transaction remains open until the next explicit commit, rollback or disconnection (which implies commit). Commit and rollback cannot be done using an OpenAPI query, but must use separate API functions. To work around this, the Ingres driver makes a specific check for COMMIT and ROLLBACK queries and calls the appropriate API function instead of issuing the query.

See Ingres OpenAPI and SQL documentation for more information.

In the Ingres OpenAPI, result sets must be accessed sequentially. Furthermore, Ingres does not provide the row count; this must be determined by fetching rows until no more is available. For this reason, the driver must fetch all result data into memory immediately after a query. Then libdbi functions can be used to get random access to rows as usual.

A workaround, for example if your result sets are very large, would be to use SELECT for random access; for example, query once to get a set of key values, then issue SELECTs on individual rows or smaller subsets of rows.

If your result set or BLOB data is likely to exhaust memory, and workarounds such as the above are not appropriate, then you will need to use OpenAPI directly to access them sequentially or by segments, instead of using libdbi and this driver.

dbi_conn_sequence_last() is not usable in Ingres' autocommit mode (the default). To use sequences to identify INSERTed rows, first obtain the NEXT VALUE using dbi_conn_sequence_next() then use that sequence value directly in a separate INSERT transaction.

Parsing DATE columns into timestamp values will only work correctly when returned values are in the default (US) date format; i.e. dd-mmm-yyyy [hh:mm:ss] where mmm is a partial month name. The parsing routine will accept any separators and either a numeric or named month, but the D-M-Y ordering is required. If needed, a workaround for date format incompatibility is to format into a string using a SQL function such as date_gmt(date).

dbi_conn_get_engine_version_string() returns a string like II 9.0.4 (int.lnx/104)NPTL (internally this is implemented as SELECT dbmsinfo('_version').

dbi_conn_get_table_list() returns matching objects of all types - tables, views, indexes and partitions - in the iitables catalog for the named database (except those beginning with ii, which are reserved for Ingres). These names are space padded fixed width fields.

The driver respects dbd_set_verbosity() level in reporting messages. At verbosity 1, only errors and abnormalities are reported; at 2, more information is shown; at 3, detailed debugging information is shown.

Blanks are preserved in column values (except that DECIMAL and MONEY columns are converted to strings, and then stripped of trailing blanks). Note that names returned by dbi_conn_get_table_list() and dbi_conn_get_db_list() are padded with blanks to a fixed width.

The Ingres OpenAPI may return multiple error messages for a given query/operation. The dbi_conn_error() returns the error code only for the last error message, but the message text will include all relevant messages. At verbosity level 2, errors directly relating to libdbi function calls are logged. At verbosity 3, all errors are logged including those caused by API calls made internally by the driver.

Environment variables II_API_TRACE and II_API_LOG control logging by the API itself. See Chapter 6 of the Ingres OpenAPI User Guide for details.

All Ingres option values are specified as strings (using dbi_conn_set_option()).

This option specifies the size of individual segments returned for BLOB columns (by default this is approx 2000 bytes). If your BLOBs are large, increasing this value reduces the number of API calls required by the Ingres driver.