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
--
Posted via http://www.ruby-forum.com/.
hmm On 1/13/06, Kim <kim@dbasol.dk> wrote:> > 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- First they laugh at you, then they ignore you, then they fight you. Then you win. -- Mahatma Karamchand Gandhi -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060113/d5dac74d/attachment-0001.html
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 -- Posted via http://www.ruby-forum.com/.
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 ? -- Posted via http://www.ruby-forum.com/.
Anybody? -- Posted via http://www.ruby-forum.com/.
You''ll have to specify what you want help with for anybody to help you. Throw it out there ;) On 1/24/06, Kim <kim@dbasol.dk> wrote:> > Anybody? > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/0502a7c6/attachment-0001.html
On 1/24/06, Dylan Stamat <dylans@gmail.com> 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
Dylan Stamat 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 -- Posted via http://www.ruby-forum.com/.
No really...please say what the problem is.... On 2/15/06, Kim <kimant123456@bigfoot.com> wrote:> > Dylan Stamat 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060215/251675ad/attachment.html
Kelly Felkins 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 -- Posted via http://www.ruby-forum.com/.
On 16/02/06, Kim <kimant123456@bigfoot.com> 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
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 Gael Pourriel wrote:> On 16/02/06, Kim <kimant123456@bigfoot.com> 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 > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
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. On 2/16/06, Ben Munat <bent@munat.com> 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 > > Gael Pourriel wrote: > > On 16/02/06, Kim <kimant123456@bigfoot.com> 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 > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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 Gambone 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.-- Posted via http://www.ruby-forum.com/.
Ben Munat 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! > > bHi 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 -- Posted via http://www.ruby-forum.com/.
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. On 2/16/06, Kim Ant <kimant123456@bigfoot.com> wrote:> Ben Munat 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Tony Gambone 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 -- Posted via http://www.ruby-forum.com/.
Tony Gambone 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 -- Posted via http://www.ruby-forum.com/.
On 2/28/06, Kim <Kim@dbasol.dk> wrote:> Tony Gambone 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 ;-( >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.
Wilson Bilkovich 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! -- Posted via http://www.ruby-forum.com/.