Problems with scaffold's parameter on Oracle


#1

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


#2

hmm


#3

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


#4

Anybody?


#5

You’ll have to specify what you want help with for anybody to help you.
Throw it out there :wink:


#6

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 :wink:

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


#7

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 ?


#8

Dylan S. wrote:

You’ll have to specify what you want help with for anybody to help you.
Throw it out there :wink:

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


#9

No really…please say what the problem is…


#10

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


#11

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


#12

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


#13

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.


#14

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


#15

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.


#16

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


#17

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


#18

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.


#19

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/activerecord/lib/active_record/connection_adapters/oci_adapter.rb

–Wilson.


#20

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/activerecord/lib/active_record/connection_adapters/oci_adapter.rb

–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!