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
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()…
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
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!
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.
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.
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.
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.
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.
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.
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’.
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’.
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 forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
