Rails - Jul 2007 - 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
-~----------~----~----~----~------~----~------~--~---

> 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
-~----------~----~----~----~------~----~------~--~---

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
-~----------~----~----~----~------~----~------~--~---