Rails 1.2.6 errors on Oracle 8i DB

[miclab]

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:

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:inexecute’
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:inselect_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:infind_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:infind’
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:inload_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:inmethod_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:insend’
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:inrender_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:inrender_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:inrender_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:inmeasure’
C:/ruby/lib/ruby/1.8/benchmark.rb:307:in realtime' C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:26:inbenchmark’
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:inrender’
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:insend’
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:inrender_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:inrender_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:inrender_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:inrender’
C:/ruby/lib/ruby/1.8/benchmark.rb:293:in measure' C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/benchmarking.rb:50:inrender’
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:incall_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:inperform_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:inperform_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:insend’
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:inprocess_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:inprocess’
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:indispatch’
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:inget’
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:inget’
test/integration/pmp_test.rb:59:in test_your_devices' C:/ruby/lib/ruby/1.8/test/unit/testcase.rb:78:insend’
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:inrun’
C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:in run' C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:ineach’
C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in run' C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:inrun’
C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in each' C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:inrun’
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:instart_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:inrun’
C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:216:in run' C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:12:inrun’
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

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:inexecute’
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:inselect_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:infind_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:infind_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:insend’
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:insilence’
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:infind_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:inpending’
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:inperform_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:inperform_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:inmeasure’
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:inperform_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:inprocess_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:inprocess_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:inprocess’
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:inprocess’
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:infollow_redirect!’
C:/cvsroot/PORTAL0101/portal/vendor/rails/actionpack/lib/action_controller/integration.rb:134:in
post_via_redirect' ./test/integration/tickets_test.rb:70:increate_ticket’
./test/integration/tickets_test.rb:29:in test_ticket_life_cycle' C:/ruby/lib/ruby/1.8/test/unit/testcase.rb:78:insend’
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:inrun’
C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:in run' C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:ineach’
C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in run' C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:34:inrun’
C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:in each' C:/ruby/lib/ruby/1.8/test/unit/testsuite.rb:33:inrun’
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:instart_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:inrun’
C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:216:in run' C:/ruby/lib/ruby/1.8/test/unit/autorunner.rb:12:inrun’
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

[miclab]

Some additional info:

Installed Gems:
activerecord-oracle_enhanced-adapter (1.1.5)
cgi_multipart_eof_fix (2.5.0)
fastthread (1.0.1)
fxri (0.3.6)
fxruby (1.6.12)
gem_plugin (0.2.3)
hoe (1.7.0)
hpricot (0.6)
image_science (1.1.3)
log4r (1.0.5)
mongrel (1.1.3)
mongrel_service (0.3.4)
mysql (2.7.3)
rake (0.8.1, 0.7.3)
ruby-postgres (0.7.1.2006.04.06)
rubyforge (1.0.0)
rubygems-update (1.2.0)
RubyInline (3.7.0)
sources (0.0.1)
sqlite3-ruby (1.2.2)
win32-api (1.2.0, 1.0.4)
win32-clipboard (0.4.3)
win32-dir (0.3.2)
win32-eventlog (0.4.6)
win32-file (0.5.4)
win32-file-stat (1.2.7)
win32-process (0.5.3)
win32-sapi (0.1.4)
win32-service (0.5.2)
win32-sound (0.4.1)
windows-api (0.2.0)
windows-pr (0.8.0, 0.7.2)

Thank u

[miclab]

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:

results of left outer join and =(+) in oracle are different - Oracle Database

Greetings
Michele Costa

Michele Costa wrote:

Some additional info:

Installed Gems:
activerecord-oracle_enhanced-adapter (1.1.5)
cgi_multipart_eof_fix (2.5.0)
fastthread (1.0.1)
fxri (0.3.6)
fxruby (1.6.12)
gem_plugin (0.2.3)
hoe (1.7.0)
hpricot (0.6)
image_science (1.1.3)
log4r (1.0.5)
mongrel (1.1.3)
mongrel_service (0.3.4)
mysql (2.7.3)
rake (0.8.1, 0.7.3)
ruby-postgres (0.7.1.2006.04.06)
rubyforge (1.0.0)
rubygems-update (1.2.0)
RubyInline (3.7.0)
sources (0.0.1)
sqlite3-ruby (1.2.2)
win32-api (1.2.0, 1.0.4)
win32-clipboard (0.4.3)
win32-dir (0.3.2)
win32-eventlog (0.4.6)
win32-file (0.5.4)
win32-file-stat (1.2.7)
win32-process (0.5.3)
win32-sapi (0.1.4)
win32-service (0.5.2)
win32-sound (0.4.1)
windows-api (0.2.0)
windows-pr (0.8.0, 0.7.2)

Thank u