Rails 1.2.6 errors on Oracle 8i DB

Hi all,

I'm getting some OCI8 driver errorsd when migrating my Rails app from
PostgreSQL and MySQL to Oracle 8i DB.

My application's environment:
Rails 1.2.6
Oracle 8i
Windows XP PRO

The errors occurs in two areas:

1.
The "has_and_belongs_to_many" method generates SQL statement that
Oracle 8i doesn't like at all:

My model:
class ProductLink < ActiveRecord::Base
  has_and_belongs_to_many :products,
                          :join_table => 'products_product_links'
end

The Oracle error and SQL:
OCIError: ORA-00933: SQL command not properly ended: SELECT * FROM
product_links INNER JOIN products_product_links ON product_links.id =
products_product_links.product_link_id WHERE
(products_product_links.product_id = 2 ) )

Stacktrace:
C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
`log'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb:222:in
`execute'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb:490:in
`select'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:427:in
`find_by_sql'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:997:in
`find_every'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:418:in
`find'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations/has_and_belongs_to_many_association.rb:67:in
`find'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:163:in
`find_target'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations/association_proxy.rb:131:in
`load_target'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations/association_proxy.rb:122:in
`method_missing'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations/has_and_belongs_to_many_association.rb:91:in
`method_missing'
    C:/cvsroot/PORTAL0101/portal/app/views/your_devices/_third_levels.rhtml:10:in
`_run_rhtml_47app47views47your_devices47_third_levels46rhtml'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:325:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:325:in
`compile_and_render_template'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:301:in
`render_template'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:260:in
`globalize_old_render_file'
    C:/cvsroot/PORTAL0101/portal/vendor/plugins/globalize/lib/globalize/rails/action_view.rb:18:in
`render_file'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:275:in
`render'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/partials.rb:59:in
`render_partial'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:26:in
`benchmark'
    C:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
    C:/ruby/lib/ruby/1.8/benchmark.rb:307:in `realtime'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:26:in
`benchmark'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/partials.rb:58:in
`render_partial'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:287:in
`render'
    C:/cvsroot/PORTAL0101/portal/app/views/your_devices/info.rhtml:42:in
`_run_rhtml_47app47views47your_devices47info46rhtml'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:325:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:325:in
`compile_and_render_template'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:301:in
`render_template'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_view/base.rb:260:in
`globalize_old_render_file'
    C:/cvsroot/PORTAL0101/portal/vendor/plugins/globalize/lib/globalize/rails/action_view.rb:18:in
`render_file'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:812:in
`render_file'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:717:in
`render_with_no_layout'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/layout.rb:247:in
`render_without_benchmark'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:50:in
`render'
    C:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:50:in
`render'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:1102:in
`perform_action_without_filters'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/filters.rb:696:in
`call_filters'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/filters.rb:688:in
`perform_action_without_benchmark'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:66:in
`perform_action_without_rescue'
    C:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:66:in
`perform_action_without_rescue'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/rescue.rb:83:in
`perform_action'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:435:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:435:in
`process_without_filters'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/filters.rb:684:in
`process_without_session_management_support'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/session_management.rb:114:in
`process_without_test'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/test_process.rb:15:in
`process'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:334:in
`process'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/railties/lib/dispatcher.rb:41:in
`dispatch'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:267:in
`process'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:152:in
`get'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:508:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:508:in
`get'
    test/integration/pmp_test.rb:59:in `test_your_devices'
    C:/ruby/lib/ruby/1.8/test/unit/testcase.rb:78:in `__send__'
    C:/ruby/lib/ruby/1.8/test/unit/testcase.rb:78:in `run'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:463:in
`run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `each'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `each'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/ui/testrunnermediator.rb:46:in
`run_suite'
    C:/ruby/lib/ruby/1.8/test/unit/ui/console/testrunner.rb:67:in
`start_mediator'
    C:/ruby/lib/ruby/1.8/test/unit/ui/console/testrunner.rb:41:in
`start'
    C:/ruby/lib/ruby/1.8/test/unit/ui/testrunnerutilities.rb:29:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:216:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:12:in `run'
    C:/ruby/lib/ruby/1.8/test/unit.rb:278
    C:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake/rake_test_loader.rb:5

2.
I'm getting the same error when invoking the find method with the
":include" option:

My model's find method:
Ticket.find_all_by_status('open', :include => [:user, :ticket_category])

The Oracle error and SQL:
OCIError: ORA-00933: SQL command not properly ended: SELECT tickets.id
AS t0_r0, tickets.user_id AS t0_r1, tickets.priority AS t0_r2,
tickets.status AS t0_r3, tickets.ticket_category_id AS t0_r4,
tickets.subject AS t0_r5, tickets.body AS t0_r6, tickets.created_at AS
t0_r7, tickets.updated_at AS t0_r8, tickets.closed_at AS t0_r9, users.id
AS t1_r0, users.role_id AS t1_r1, users.subscriber_id AS t1_r2,
users.setting_method AS t1_r3, users.name AS t1_r4, users.email AS
t1_r5, users.hashed_password AS t1_r6, users.salt AS t1_r7,
users.reset_code AS t1_r8, users.created_at AS t1_r9, users.logged_at AS
t1_r10, ticket_categories.id AS t2_r0, ticket_categories.name AS t2_r1
FROM tickets LEFT OUTER JOIN users ON users.id = tickets.user_id LEFT
OUTER JOIN ticket_categories ON ticket_categories.id =
tickets.ticket_category_id WHERE (tickets.status = 'open' AND
tickets.user_id = 1) )

Stacktrace:
C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:128:in
`log'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb:222:in
`execute'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/oracle_adapter.rb:490:in
`select'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations.rb:1187:in
`select_all_rows'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations.rb:1044:in
`find_with_associations'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations.rb:1042:in
`catch'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/associations.rb:1042:in
`find_with_associations'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:996:in
`find_every'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:1219:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:1219:in
`method_missing'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/../../activesupport/lib/active_support/deprecation.rb:44:in
`silence'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/activerecord/lib/active_record/base.rb:1219:in
`method_missing'
    C:/cvsroot/PORTAL0101/portal/app/models/ticket.rb:41:in
`find_ticket_by_user_and_status'
    C:/cvsroot/PORTAL0101/portal/app/models/ticket.rb:27:in
`find_pending'
    C:/cvsroot/PORTAL0101/portal/app/controllers/tickets_controller.rb:49:in
`pending'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:1101:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:1101:in
`perform_action_without_filters'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/filters.rb:696:in
`call_filters'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/filters.rb:688:in
`perform_action_without_benchmark'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:66:in
`perform_action_without_rescue'
    C:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:66:in
`perform_action_without_rescue'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/rescue.rb:83:in
`perform_action'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:435:in
`send'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:435:in
`process_without_filters'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/filters.rb:684:in
`process_without_session_management_support'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/session_management.rb:114:in
`process_without_test'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/test_process.rb:15:in
`process'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/base.rb:334:in
`process'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/railties/lib/dispatcher.rb:41:in
`dispatch'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:267:in
`process'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:152:in
`get'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:116:in
`follow_redirect!'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:134:in
`post_via_redirect'
    ./test/integration/tickets_test.rb:70:in `create_ticket'
    ./test/integration/tickets_test.rb:29:in `test_ticket_life_cycle'
    C:/ruby/lib/ruby/1.8/test/unit/testcase.rb:78:in `__send__'
    C:/ruby/lib/ruby/1.8/test/unit/testcase.rb:78:in `run'
    C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:463:in
`run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `each'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `each'
    C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/ui/testrunnermediator.rb:46:in
`run_suite'
    C:/ruby/lib/ruby/1.8/test/unit/ui/console/testrunner.rb:67:in
`start_mediator'
    C:/ruby/lib/ruby/1.8/test/unit/ui/console/testrunner.rb:41:in
`start'
    C:/ruby/lib/ruby/1.8/test/unit/ui/testrunnerutilities.rb:29:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:216:in `run'
    C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:12:in `run'
    C:/ruby/lib/ruby/1.8/test/unit.rb:278
    C:/ruby/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake/rake_test_loader.rb:5

Everything works great with the others databases, so i think there's a
problem with the oracle_adapter.

Anyone encounters my same problems and have found a solution for it?
I ggogle them, but was unable to find anything.

Greetings
Michele

I've solved my problem.

It seems that our old friend Oracle 8 does not support ANSI SQL for the
join statements.

Also if u open the oracle_adapter.rb file of ActiveRecord library, it
says:
# Support for Oracle8 is limited by Rails' use of ANSI join syntax,
which
# is supported in Oracle9i and later. You will need to use #finder_sql
for
# has_and_belongs_to_many associations to run against Oracle8.

In short u have to substitute all the JOINs statement with the following
syntax and call it with the :finder_sql option (or with the
select_by_sql method):

INNER JOINs:
SELECT table1.value, table2.value FROM table1 INNER JOIN table2 ON
table1.value_id = table2.id

is trasformed in:

SELECT table1.value, table2.value FROM table1, table2 WHERE
table1.join_value = table2.join_value

LEFT OUTER JOINS:
SELECT table1.value, table2.value FROM table1 LEFT OUTER JOIN table2 ON
table1.join_value = table2.join_value

is trasformed in:

SELECT table1.value, table2.value FROM table1, table2 WHERE
table1.join_value = table2.join_value(+)

All this trasformation are mandatory in absence of a valid Oracle 8
adapter...
Hope this will help, for further info:
http://bytes.com/forum/thread639225.html

Greetings
Michele Costa

Michele Costa wrote: