Forum: Ruby on Rails Problems with scaffold's parameter on Oracle

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Kim (Guest)
on 2006-01-13 16:56
Hi

I run Ruby on Rails on Oracle XE.

I have a simple table:
create table customer(id number primary key, name varchar2(100));

And

ruby script\generate model Customer
ruby script\generate controller Customer

http://localhost:3000/customer/

- show a fine list, but when I click show/edit/destroy, I get an
ORA-01722:

OCIError: ORA-01722: invalid number: select * from (select raw_sql_.*,
rownum raw_rnum_ from (SELECT * FROM customers WHERE (customers.id =
'5.0') ) raw_sql_ where rownum <= 1) where raw_rnum_ > 0

The problem comes from the parameter applied:

Request
Parameters: {"id"=>"5.0"}

id is a number, not a text string !?!

Is this a configuration issue, or?

If I change the url from
http://localhost:3000/customer/show/5.0
to
http://localhost:3000/customer/show/5

it works fine, but this is not nice.

br
Kim
Leon L. (Guest)
on 2006-01-13 23:33
(Received via mailing list)
hmm
Kim (Guest)
on 2006-01-16 16:09
I forgot to mention that I edit the file customer_controller.rb, where I
add the line
  scaffold :customer
between the class line and the end line.

/Kim
Kim (Guest)
on 2006-01-19 01:07
I GUESS the problem originates to the parameter being CONCATENATED into
the sql string, instead of being binded (which would be the proper way
of using parameters here).

I used NUMBER as the datatype. This is probably converted into a float
or similar, and then simply concatenated like this v_sql += "select ..."
+ v_param.
While it should have been preparedStatement.setFloat()....

Or am I wrong ?
Kim (Guest)
on 2006-01-24 22:22
Anybody?
Dylan S. (Guest)
on 2006-01-24 22:31
(Received via mailing list)
You'll have to specify what you want help with for anybody to help you.
Throw it out there ;)
James L. (Guest)
on 2006-01-24 23:56
(Received via mailing list)
On 1/24/06, Dylan S. <removed_email_address@domain.invalid> wrote:
> You'll have to specify what you want help with for anybody to help you.
> Throw it out there ;)

It's a bump of an older thread about some Oracle problem.  Gmail shows
it as a new thread though, because the subject line changed.

-- James
Kim (Guest)
on 2006-02-15 22:52
Dylan S. wrote:
> You'll have to specify what you want help with for anybody to help you.
> Throw it out there ;)

I would like to get this feature working as the tutorials says it does.

And if I am right about the reason, I would very much appreciate that it
could be corrected to something better.

Br

Kim
Kelly F. (Guest)
on 2006-02-15 23:10
(Received via mailing list)
No really...please say what the problem is....
Kim (Guest)
on 2006-02-16 14:41
Kelly F. wrote:
> No really...please say what the problem is....

The problem is as I have already stated:

I run Ruby on Rails on Oracle XE.

I have a simple table:
create table customer(id number primary key, name varchar2(100));

And

ruby script\generate model Customer
ruby script\generate controller Customer

http://localhost:3000/customer/

- show a fine list, but when I click show/edit/destroy, I get an
ORA-01722:

OCIError: ORA-01722: invalid number: select * from (select raw_sql_.*,
rownum raw_rnum_ from (SELECT * FROM customers WHERE (customers.id =
'5.0') ) raw_sql_ where rownum <= 1) where raw_rnum_ > 0

The problem comes from the parameter applied:

Request
Parameters: {"id"=>"5.0"}

id is a number, not a text string !?!

Is this a configuration issue, or?

If I change the url from
http://localhost:3000/customer/show/5.0
to
http://localhost:3000/customer/show/5

it works fine, but this is not nice.

br
Kim
Gael P. (Guest)
on 2006-02-16 15:06
(Received via mailing list)
On 16/02/06, Kim <removed_email_address@domain.invalid> wrote:
> I have a simple table:
> create table customer(id number primary key, name varchar2(100));

I think it would work better if your primary key "id" was an integer,
not a varchar.

Gael
Ben M. (Guest)
on 2006-02-16 21:03
(Received via mailing list)
No, look more closely... he has two columns: "id number primary key" and
"name varchar2(100)".

I wonder if that should be "integer" instead of "number"... not sure
what difference that
would make. But, in any case, you definitely don't want floats as IDs!

b
Tony G. (Guest)
on 2006-02-16 22:43
(Received via mailing list)
Second that... you definitely want "integer" instead of number.  The
Oracle "number" datatype is a float, and Rails seems to always see
those with a decimal point.  You might be able to work around it, but
it'd be easier to just use an integer datatype.
Steve K. (Guest)
on 2006-02-16 23:06
I'd go even further to point out that Rails expects the id field on a
table to not only be an integer, but an auto-incrementing, unique
primary key set by the database. There's no reason to make it
floating-point.

If you want an ID with some other kind of value -- an alphanumeric, a
floating-point number (which would be bizarre, but go for it if you
want) or whatever -- that should be another field entirely.

Tony G. wrote:
> Second that... you definitely want "integer" instead of number.  The
> Oracle "number" datatype is a float, and Rails seems to always see
> those with a decimal point.  You might be able to work around it, but
> it'd be easier to just use an integer datatype.
Kim A. (Guest)
on 2006-02-16 23:10
Ben M. wrote:
> No, look more closely... he has two columns: "id number primary key" and
> "name varchar2(100)".
>
> I wonder if that should be "integer" instead of "number"... not sure
> what difference that
> would make. But, in any case, you definitely don't want floats as IDs!
>
> b

Hi all

Nice to see some feedback!

In Oracle, there are no float/integer/.. types for well .. numbers.
There are only a number type.

That is why I expect Rails to expect/restrict to integers.

Br
Kim
Tony G. (Guest)
on 2006-02-16 23:37
(Received via mailing list)
That's true... but you can use INTEGER for the datatype anyway.  It is
a synonym for a NUMBER with 38 precision and no scale - a 38-digit
integer, then, if I remember correctly how that works.  You might try
using "NUMBER(7,0)" or something, since you probably don't need
38-digit IDs.  My guess is, if the second argument is 0, the id will
appear in Ruby as an Integer.
Kim A. (Guest)
on 2006-02-17 00:15
Tony G. wrote:
> That's true... but you can use INTEGER for the datatype anyway.  It is
> a synonym for a NUMBER with 38 precision and no scale - a 38-digit
> integer, then, if I remember correctly how that works.  You might try
> using "NUMBER(7,0)" or something, since you probably don't need
> 38-digit IDs.  My guess is, if the second argument is 0, the id will
> appear in Ruby as an Integer.

Hi Tony

Thank You for the suggestion - I will give it a try, but I think the
real place to solve this would be in Rails.
Also, I suspect that it is possible to sneak in extra sql by placing
string terminators and union select username, password etc. instead of
the id.
It could be coded a lot nicer by using bind variables, which would also
put a lot less preassure on the database.

Br
Kim
Kim (Guest)
on 2006-03-01 03:20
Tony G. wrote:
> That's true... but you can use INTEGER for the datatype anyway.  It is
> a synonym for a NUMBER with 38 precision and no scale - a 38-digit
> integer, then, if I remember correctly how that works.  You might try
> using "NUMBER(7,0)" or something, since you probably don't need
> 38-digit IDs.  My guess is, if the second argument is 0, the id will
> appear in Ruby as an Integer.

It does not help.

I have done this:

ALTER TABLE ALERT_CONTACTS MODIFY (ID NUMBER(7,0))

I guess that I can only conclude that Ruby on Rails are not running on
Oracle, although it is stated on www.rubyonrails.org.

What a shame ;-(

Br
Kim
Wilson B. (Guest)
on 2006-03-01 05:44
(Received via mailing list)
On 2/28/06, Kim <removed_email_address@domain.invalid> wrote:
> I have done this:
>
> ALTER TABLE ALERT_CONTACTS MODIFY (ID NUMBER(7,0))
>
> I guess that I can only conclude that Ruby on Rails are not running on
> Oracle, although it is stated on www.rubyonrails.org.
>
> What a shame ;-(
>

Rails works great on Oracle. I'm using it in a number of apps, and
quite a few others are as well.
Try using a Migration to create your tables from scratch. Then you can
take a look at what a correct schema looks like, and model anything
you do by hand on it.
A Rails-friendly table should have a properly-named sequence, and a
NUMBER(38) NOT NULL PRIMARY KEY column called 'id'.

Here's the code for the Oracle adapter. Check out the
'native_database_types' method to see what it's expecting:
http://dev.rubyonrails.org/svn/rails/trunk/activer...

--Wilson.
Kim (Guest)
on 2006-03-01 12:26
Wilson B. wrote:

> Rails works great on Oracle. I'm using it in a number of apps, and
> quite a few others are as well.
> Try using a Migration to create your tables from scratch. Then you can
> take a look at what a correct schema looks like, and model anything
> you do by hand on it.
> A Rails-friendly table should have a properly-named sequence, and a
> NUMBER(38) NOT NULL PRIMARY KEY column called 'id'.
>
> Here's the code for the Oracle adapter. Check out the
> 'native_database_types' method to see what it's expecting:
> 
http://dev.rubyonrails.org/svn/rails/trunk/activer...
>
> --Wilson.

Hi Wilson

You are 100% correct - I created a new project and new tables and it
worked like it should. Now I only have to find out what has messed up my
initial project.

Thank You!
This topic is locked and can not be replied to.