Using the OpenLink Rails ODBC Adapter with Informix

Configuration

In order to match the formats of Ruby's Date, Time and DateTime types, the following settings for Informix were used during testing:

  • DBDATE=Y4MD-
  • DBTIME=%Y-%m-%d %H:%M:%S

To support mixed-case/quoted table names:

  • DELIMIDENT=y

To allow embedded newlines in quoted strings:

  • set ALLOW_NEWLINE=1 in the ONCONFIG configuration file.
    • Although this setting may not be required for your Rails application, it is required for the adapter to pass some of the ActiveRecord tests in the ActiveRecord test suite.

Features

Retrieval of Last SERIAL

ActiveRecord relies heavily on the ability of a database to automatically generate primary key values, and on the ability of a Rail adapter to return the primary key value auto-generated by an INSERT. The ODBC adapter relies on a SQLGetStmtOption extension implemented by some ODBC drivers (SQL_LASTSERIAL=1049) to retrieve the last value auto-generated by an insert into a SERIAL primary key column. OpenLink Informix ODBC drivers and the native IBM Informix ODBC drivers all support SQLGetStmtOption(1049)
  • For this feature to work correctly:
    • OpenLink Informix ODBC Lite Driver for WIN32: In the driver setup dialog, ensure the checkbox 'Force connection dormant after ESQL operation' is not checked.
    • OpenLink Informix agent / OpenLink Informix ODBC Lite Driver for other platforms: Ensure the FORCE_DORMANT environment variable is not set.

Type Mappings

ActiveRecord defines a number of abstract data types. These and the corresponding Ruby data types are listed in the table below. Which native database types the abstract types map to will depend to some extent on the underlying ODBC driver used by the OpenLink Rails ODBC Adapter. The native type mappings for the OpenLink Informix agents/Lite drivers are shown below.

Active Record abstract type Ruby type Informix type
:primary_key - SERIAL PRIMARY KEY
:string String VARCHAR(255)
:boolean Object SMALLINT
:datetime Time DATETIME YEAR TO FRACTION (5)
:time Time DATETIME HOUR TO SECOND
:timestamp Time DATETIME YEAR TO FRACTION (5)
:date Date DATE
:text String TEXT
:integer Fixnum INTEGER
:float Float FLOAT
:binary String BYTE
:decimal BigDecimal DECIMAL

It is suggested you use these native database types when creating tables to support your Rails models. Example table definitions are attached below:

  • informix.sql These are the table definitions used for creating fixtures during testing - similar fixture definitions can be found for other databases in the ActiveRecord sources under active-record-root/test/fixtures/db_definitions

Using IBM's Informix ODBC Driver

IBM's native Informix ODBC driver has been validated with the OpenLink Rails ODBC adapter on Linux. The native driver supports the SQLGetStmtOption option 1049 (defined as SQL_GET_SERIAL_VALUE in infxcli.h) required by the #last_insert_method. In the version of the Informix Client SDK used for testing (v2.9 UC1), there's a bug in SQLStatistics whereby the function returns an invalid return code. This in turn causes an exception in the ActiveRecord #indexes method. You shouldn't ordinarily encounter this problem, but it may appear if you try to run some of your Rails application's tests (e.g. "rake test_functional" or "rake test_units".) Rails tries to replicate the development database's schema in the test database and, in the course of trying to do this, calls #indexes. This method fails and no tables are created in the test database. There also appears to be a bug in SQLGetStmtOption in the Window's version of the native Informix ODBC driver. SQLGetStmtOption(1049) returns a short int instead of a long int. The Linux version of the driver doesn't suffer from this problem.

ODBC-Rails and the ODBC-Rails website are Copyright (C) OpenLink Software 2006-2008