Kiriakos Georgiou
2007-Jul-30 16:00 UTC
validates_uniqueness_of doesn''t work if value tested is numeric and column is string
Hello all, I am working through the depot example in the rails book. If I add a validates_uniqueness_of to a string column in the model, and then try to create a record with a number in this string column I get an SQL error. Looks like the SQL generated to test for uniqueness does not quote the value if it''s numeric even though it''s comparing against a varchar column. It does something like this: (select * from products where title = 123) This of course needs 123 to be quoted in order for it to work. I thought this would be an easy fix in the quote() method of sybase_adapter.rb but it appears to be doing the right thing by checking the column type. I''d like to figure out if it''s specific to the sybase adapter so if anyone can validates_uniqueness_of a string column and then pass an integer I''d appreciate it. Also that''s the proper way of debugging sybase_adapter.rb''s quote() method? At a minimum I''d like to pepper it with some print statements and be able to see the output in one of the log files. thanks, Kiriakos ps. Few days into RoR, all I can say is wow --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Cynthia Kiser
2007-Jul-31 00:40 UTC
Re: validates_uniqueness_of doesn''t work if value tested is numeric and column is string
> I am working through the depot example in the rails book. If I add a > validates_uniqueness_of to a string column in the model, and then try > to create a record with a number in this string column I get an SQL > error. Looks like the SQL generated to test for uniqueness does not > quote the value if it''s numeric even though it''s comparing against a > varchar column. It does something like this: (select * from products > where title = 123) This of course needs 123 to be quoted in order for > it to work.I think you need to cast 123 to a string. You didn''t give enough context, but before you do whatever was generating the select, try updating the title parameter to be ''title.to_s'' --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Matt Bradarich
2007-Aug-13 16:03 UTC
Re: validates_uniqueness_of doesn''t work if value tested is
Cynthia Kiser wrote:>> I am working through the depot example in the rails book. If I add a >> validates_uniqueness_of to a string column in the model, and then try >> to create a record with a number in this string column I get an SQL >> error. Looks like the SQL generated to test for uniqueness does not >> quote the value if it''s numeric even though it''s comparing against a >> varchar column. It does something like this: (select * from products >> where title = 123) This of course needs 123 to be quoted in order for >> it to work. > > I think you need to cast 123 to a string. You didn''t give enough > context, but before you do whatever was generating the select, try > updating the title parameter to be ''title.to_s''I am having this similar behavior, to validate the uniqueness of a title, but when passing in a number it fails, b/c the parameter is not being seen as a String. Here is the validator - validates_uniqueness_of :title, :message => ApplicationConstants::REPORT_SPEC_TITLE_TAKEN_MSG, :case_sensitive => false, :scope => [:associateID, :view_type_id], :if => Proc.new {|spec| !spec.is_a?(ReportSpecificationClone) } We are using the sybase adapter as well. Tried this in the rails console - Create a spec set title = 123.to_s and get the same results that web app gets. ActiveRecord::StatementInvalid: RuntimeError: SQL Command Failed for ReportSpecification Load: SELECT * FROM report_specifications WHERE (LOWER(report_specifications.title) = 123 AND report_specifications.associateID = 2500000 AND report_specifications.view_type_id = 1 AND report_specifications.id <> 471) Message: Implicit conversion from datatype ''VARCHAR'' to ''INT'' is not allowed. Use the CONVERT function to run this query. : SELECT * FROM report_specifications WHERE (LOWER(report_specifications.title) = 123 AND report_specifications.associateID = 2500000 AND report_specifications.view_type_id = 1 AND report_specifications.id <> 471) Any help or insight to the problem is appreciated. Thanks -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
This is related to: http://groups.google.com/group/rubyonrails-core/browse_thread/thread/572706bfc0ebd894/524df0e3fbcf395f?lnk=gst&q=numconvert&rnum=1#524df0e3fbcf395f validates_uniqueness_of ends up calling #quote_bound_value. as a result no column information is passed into #quote. As a result the value is never quoted. On Aug 13, 11:03 am, Matt Bradarich <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Cynthia Kiser wrote: > >> I am working through the depot example in the rails book. If I add a > >> validates_uniqueness_of to a string column in the model, and then try > >> to create a record with a number in this string column I get an SQL > >> error. Looks like the SQL generated to test for uniqueness does not > >> quote the value if it''s numeric even though it''s comparing against a > >> varchar column. It does something like this: (select * from products > >> where title = 123) This of course needs 123 to be quoted in order for > >> it to work. > > > I think you need to cast 123 to a string. You didn''t give enough > > context, but before you do whatever was generating the select, try > > updating the title parameter to be ''title.to_s'' > > I am having this similar behavior, to validate the uniqueness of a > title, but when passing in a number it fails, b/c the parameter is not > being seen as a String. Here is the validator - > > validates_uniqueness_of :title, > :message => > ApplicationConstants::REPORT_SPEC_TITLE_TAKEN_MSG, > :case_sensitive => false, > :scope => [:associateID, :view_type_id], > :if => Proc.new {|spec| !spec.is_a?(ReportSpecificationClone) > > } > > We are using thesybaseadapter as well. > > Tried this in the rails console - > Create a spec > set title = 123.to_s > and get the same results that web app gets. > > ActiveRecord::StatementInvalid: RuntimeError: SQL Command Failed for > ReportSpecification Load: SELECT * FROM report_specifications WHERE > (LOWER(report_specifications.title) = 123 AND > report_specifications.associateID = 2500000 AND > report_specifications.view_type_id = 1 AND report_specifications.id <> > 471) > Message: Implicit conversion from datatype ''VARCHAR'' to ''INT'' is not > allowed. Use the CONVERT function to run this query. > : SELECT * FROM report_specifications WHERE > (LOWER(report_specifications.title) = 123 AND > report_specifications.associateID = 2500000 AND > report_specifications.view_type_id = 1 AND report_specifications.id <> > 471) > > Any help or insight to the problem is appreciated. > > Thanks > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---