I''m trying (for the first time) to build an IN clause from an array of
strings and getting an error using MySQL as the DB...
Here''s the code I''m using to build the clause:
zips_clause = ''''
user.get_zipcodes.each {|z|
zips_clause += '','' if !zips_clause.blank?
zips_clause += ''\'''' + z +
''\'''' # wrap string in single-quotes
}
zips_clause = ''('' + zips_clause + '')''
conditions = sanitize_sql(["updated_at > ? AND local_code IN ?",
since_date, zips_clause])
This is the resulting SQL:
SELECT * FROM tips WHERE (updated_at > ''2008-03-14
11:55:29'' AND
local_code IN
''(\''94952\'',\''08054\'',\''11111\'')'')
And this is the error:
Mysql::Error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near
''''(\''94952\'',\''08054\'',\''11111\'')'')''
at line 1: SELECT * FROM
tips WHERE (updated_at > ''2008-03-14 11:55:29'' AND
local_code IN
''(\''94952\'',\''08054\'',\''11111\'')'')
I''m guessing that it''s something about that parenthesized
clause that
follows the IN.
Could it be the backslash that gets included for the single-quotes
around each string? And why *is* that getting inserted?
Or can anybody spot anything else wrong with that SQL statement?
If you have code that shows a different way to build a SQL IN clause
from an array of strings that might help, too.
Thanks in advance.
--~--~---------~--~----~------------~-------~--~----~
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''m trying (for the first time) to build an IN clause from an array of > strings and getting an error using MySQL as the DB... > > Here''s the code I''m using to build the clause: > > zips_clause = '''' > user.get_zipcodes.each {|z| > zips_clause += '','' if !zips_clause.blank? > zips_clause += ''\'''' + z + ''\'''' # wrap string in single-quotes > } > zips_clause = ''('' + zips_clause + '')'' > > conditions = sanitize_sql(["updated_at > ? AND local_code IN ?", > since_date, zips_clause])a = [1,2,3,4] User.find(:all, :conditions => ["id IN (?)", a]) Results in this: SELECT * FROM users WHERE (id IN (1,2,3,4))> > This is the resulting SQL: > > SELECT * FROM tips WHERE (updated_at > ''2008-03-14 11:55:29'' AND > local_code IN ''(\''94952\'',\''08054\'',\''11111\'')'') > > And this is the error: > > Mysql::Error: You have an error in your SQL syntax; check the manual > that corresponds to your MySQL server version for the right syntax to > use near ''''(\''94952\'',\''08054\'',\''11111\'')'')'' at line 1: SELECT * FROM > tips WHERE (updated_at > ''2008-03-14 11:55:29'' AND local_code IN > ''(\''94952\'',\''08054\'',\''11111\'')'') > > I''m guessing that it''s something about that parenthesized clause that > follows the IN. > > Could it be the backslash that gets included for the single-quotes > around each string? And why *is* that getting inserted? > > Or can anybody spot anything else wrong with that SQL statement? > > If you have code that shows a different way to build a SQL IN clause > from an array of strings that might help, too. > > Thanks in advance. > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks, Phillip! I had tried something very close to what you show me, but it didnt work... I was missing the parentheses around the ? in ''(?)''. Sometimes any docs for this are so hard to find (I''d tried googling, the Agile book, Rails Way, etc.) Thanks again. On Mar 14, 1:18 pm, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote:> > I''m trying (for the first time) to build an IN clause from an array of > > strings and getting an error using MySQL as the DB... > > > Here''s the code I''m using to build the clause: > > > zips_clause = '''' > > user.get_zipcodes.each {|z| > > zips_clause += '','' if !zips_clause.blank? > > zips_clause += ''\'''' + z + ''\'''' # wrap string in single-quotes > > } > > zips_clause = ''('' + zips_clause + '')'' > > > conditions = sanitize_sql(["updated_at > ? AND local_code IN ?", > > since_date, zips_clause]) > > a = [1,2,3,4] > User.find(:all, :conditions => ["id IN (?)", a]) > > Results in this: > > SELECT * FROM users WHERE (id IN (1,2,3,4)) > > > > > This is the resulting SQL: > > > SELECT * FROM tips WHERE (updated_at > ''2008-03-14 11:55:29'' AND > > local_code IN ''(\''94952\'',\''08054\'',\''11111\'')'') > > > And this is the error: > > > Mysql::Error: You have an error in your SQL syntax; check the manual > > that corresponds to your MySQL server version for the right syntax to > > use near ''''(\''94952\'',\''08054\'',\''11111\'')'')'' at line 1: SELECT * FROM > > tips WHERE (updated_at > ''2008-03-14 11:55:29'' AND local_code IN > > ''(\''94952\'',\''08054\'',\''11111\'')'') > > > I''m guessing that it''s something about that parenthesized clause that > > follows the IN. > > > Could it be the backslash that gets included for the single-quotes > > around each string? And why *is* that getting inserted? > > > Or can anybody spot anything else wrong with that SQL statement? > > > If you have code that shows a different way to build a SQL IN clause > > from an array of strings that might help, too. > > > Thanks in advance.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---