Using the OpenLink Rails ODBC Adapter with Oracle

Table of Contents

Configuration

The jetfix configuration option in the OpenLink Oracle agent/Lite driver must be set as follows when working with Rails, to ensure the correct type mappings are enforced between the Oracle native types and ActiveRecord abstract types.

  • Rails 1.1 / ActiveRecord 1.14.x - jetfix on
  • Rails 1.2 / ActiveRecord 1.15.x - jetfix off

When creating fixtures for testing using the SQL scripts in active-record-root/test/fixtures/db_definitions, use the oracle_odbc.sql/oracle_odbc.drop.sql and oracle2_odbc.sql/oracle2_odbc.drop.sql files rather than the oracle.sql/oracle.drop.sql and oracle2.sql/oracle2.drop.sql files. The latter are for use with the native OracleAdapter.

Oracle Sequences

ActiveRecord relies heavily on the ability of databases to generate integer primary key values automatically, typically through an autoincrementing column type or a sequence associated with each table. As Oracle does not have an autoincrementing column type, the OpenLink adapter assumes that each table has an associated sequence, named tablename_seq. This must be created manually, unless the table is created through the ActiveRecord *create_table* method, in which case the sequence is created automatically.

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 Oracle agents/Lite drivers are shown below.

Active Record abstract type Ruby type Oracle type
:primary_key - NUMBER(10) PRIMARY KEY NOT NULL
:string String VARCHAR2(4000)
:boolean Object NUMBER(10) (NUMBER(1) if :emulate_boolean connection option is true)
:datetime Time TIMESTAMP
:time Time TIMESTAMP
:timestamp Time TIMESTAMP
:date Date TIMESTAMP
:text String CLOB
:integer Fixnum NUMBER(10)
:float Float BINARY_DOUBLE
:binary String BLOB
:decimal BigDecimal NUMERIC

It is suggested you use these native database types when creating tables to support your Rails models. Example table definitions are attached below:
  • oracle_odbc.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.

Restrictions

Type Mappings

  • Oracle does not support a pure DATE type. Consequently, ActiveRecord's date type (equivalent to a Ruby Date) is mapped to an Oracle TIMESTAMP. This mapping is not ideal in that an Oracle TIMESTAMP includes both time and date components, whereas a Ruby Date includes only a date component.
  • Oracle does not support a pure BOOLEAN type. Consequently, ActiveRecord's boolean type is mapped to an Oracle integer type, _NUMBER(10)_.
  • In contrast to the 'native' Oracle Rails adapter, OracleAdapter, the ODBCAdapter uses NUMBER(10) rather than INTEGER as the preferred type for a primary key. INTEGER is in fact an alias for NUMBER(38). An integer of this precision is too large to be contained in an ODBC SQL type of SQL_INTEGER which has a maximum precision of 10.
ODBC-Rails and the ODBC-Rails website are Copyright (C) OpenLink Software 2006-2008