Activerecord-jdbc-adapter 0.9 oracle number id problem


#1

I’m having a problem using jruby on rails with a legacy oracle database
(Oracle Database 10g Enterprise Edition Release 10.1.0.3.0). The oracle
number primary key is getting converted into a decimal, which is causing
restful style URIs not to work. For example I’m seeing URIs like
http://localhost:3000/members/show/7201.0?tab=names where the id should
be an integer, not a double, and they result in “Missing template”
errors.

A friend of mine said that the same problem arose for him when he
updated from activerecord-jdbc-adapter version 0.8 to version 0.9, which
is what I’m using. Is this a known issue? Is there a workaround short
of changing the database to use integer primary keys? That isn’t an
option for us.

Here’s my configuration:

jruby 1.1.6 (ruby 1.8.6 patchlevel 114) (2008-12-17 rev 8388)
[amd64-java]
rails (2.2.2)
activerecord (2.2.2)
activerecord-jdbc-adapter (0.9)
activerecord-oracle-adapter (1.0.0.9250)


#2

On Tue, Mar 3, 2009 at 1:40 PM, Mark Becker
removed_email_address@domain.invalid wrote:

I’m having a problem using jruby on rails with a legacy oracle database
(Oracle Database 10g Enterprise Edition Release 10.1.0.3.0). The oracle
number primary key is getting converted into a decimal, which is causing
restful style URIs not to work.

activerecord-oracle-adapter (1.0.0.9250)

Have you tried the activerecord-oracle_enhanced-adapter ? It solved a
number of issues for me.


Greg D.
http://destiney.com/


#3

On Tue, Mar 3, 2009 at 1:40 PM, Mark Becker
removed_email_address@domain.invalid wrote:

updated from activerecord-jdbc-adapter version 0.8 to version 0.9, which
activerecord-jdbc-adapter (0.9)
If you could file a bug 1 and provide a short test case, we’ll try
to have this fixed for 0.9.1, due out in a week or two. In your
example/test case, please be sure to provide the schema or migration
that you use to create the table.

Thanks,
/Nick


#4

Hello Nick,
I experience the same bug described by Mark with version 0.9.1, so I
guess he did not file a bug report in Jira.
Can you please confirm, so I open a bug ?
One last question, when do you plan to release a 0.9.2 version ?

Thanks again for your time and your work you give to the ruby and
rails community.

Sylvain


#5

Hi,

I think I’ve found where the problem is.

In the file lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.1/lib/
jdbc_adapter/jdbc_oracle.rb, there’s this :

module JdbcSpec

module Column

private
def simplified_type(field_type)
case field_type
when /^number(1)$/i : :boolean
when /char/i : :string
when /float|double/i : :float
when /int/i : :integer
when /num|dec|real/i : @scale ==
0 ? :integer : :decimal
when /date|time/i : :datetime
when /clob/i : :text
when /blob/i : :binary
end
end
end
end

When I read my model columns, both integer and number(x,0) Oracle
columns report as following :

#<ActiveRecord::ConnectionAdapters::JdbcColumn:0x18af9c0
@precision=nil, @limit=nil, @sql_type=“NUMBER”, @name=“id”,
@type=:decimal, @scale=nil, @null=false, @primary=true, @default=nil>

As @scale is nil and not 0, the simplified_type returned is :decimal,
not :integer.

The corresponding case line :
when /num|dec|real/i : @scale ==
0 ? :integer : :decimal
should be replaced by :
when /num|dec|real/i : (@scale.nil? ||
@scale == 0) ? :integer : :decimal

This works fine for me. If it does for you, could you please open an
issue so that the next version includes it ?

Kind regards,


Guillaume D.
http://www.rubyfrance.org/


#6

Thanks Guillaume, you’re terrific !

On Mar 25, 5:27 pm, Guillaume D. removed_email_address@domain.invalid


#7

On Wed, Mar 25, 2009 at 10:27 AM, Guillaume D.
removed_email_address@domain.invalid wrote:

module Column
when /date|time/i : :datetime
#<ActiveRecord::ConnectionAdapters::JdbcColumn:0x18af9c0
should be replaced by :
when /num|dec|real/i : (@scale.nil? ||
@scale == 0) ? :integer : :decimal

This works fine for me. If it does for you, could you please open an
issue so that the next version includes it ?

Guillaume, merci bien pour votres enquêtes.

I can certainly make the fix, but if possible it would be nice to have
an Oracle test to verify the problem and ensure we don’t regress. Can
you tell me how the column in your database is set up so we can create
a test case?

Cordialement,

/Nick


#8

Hello,

sorry for answering so late.

Guillaume, merci bien pour votres enquêtes.

You’re welcome. Thanks for your work, Nick.

I can certainly make the fix, but if possible it would be nice to have
an Oracle test to verify the problem and ensure we don’t regress. Can
you tell me how the column in your database is set up so we can create
a test case?

The ID column is defined as NUMBER(38) and the PAGES is an INTEGER
one. The ID is not nullable while the PAGES one is.

Regards,


Guillaume D.
http://www.rubyfrance.org/


#9

Hello,

First of all, thank you Guillaume very much for the workaround.

I would like to ask whether this issue had been reported in JRuby bug
tracker? If so, does anyone knows the ticket #>I can’t find it
anywhere…

Since this problem still exists in 0.9.1 version I’d like to stress that
it actually renders Rails running on JRuby using Oracle backend (via
JDBC adapter) almost inoperational. None of the generated path/URL
helpers (model_url, edit_model_path etc.) and functions that use them
(like form_for) work. Everything starts running properly with the
described workaround in place. Note there still could be a related issue
in Rails as well but the BigDecimal typecast for ID column certainly
contributes to it.

Thanks

~Ales~


#10

I don’t know of one. Ales, if you could create a new issue for this at
http://kenai.com/jira/browse/ACTIVERECORD_JDBC, that would be great.

Thanks,
/Nick

On Thu, May 14, 2009 at 8:40 AM, Ales Bukovsky


#11

It’s officially reported now:

http://kenai.com/jira/browse/ACTIVERECORD_JDBC-17

Thanks

-Ales-