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:

Greetings Michele Costa

Michele Costa wrote: