Issue with SQL like

Ruby
1

Hi Guys,

I am a newbie, so excuse me if this is a lame question -

I am writing a query -
Airport.find(:all, :conditions =>[“owner_city_state_zip like ‘%?%’”,
_tempZip.zip])

Now, this gives an 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 ‘97103’%‘)’ at line 1: SELECT * FROM airports WHERE
(owner_city_state_zip like ‘%‘97103’%’)

How do I format my query to get rid of this problem?

Also, how do we write a query to written say, 2 fields out of table
instead
of all the fields.

Thx,

–
Rajat G.

Ph: 206-499-9495
Add: 1314 Spring Street, #412
Seattle, WA 98104
Web: http://www.pilotoutlook.com

2

On 18 Mar 2008, at 06:21, Rajat G. wrote:

I am writing a query -
Airport.find(:all, :conditions =>[“owner_city_state_zip like ‘%?%’”,
_tempZip.zip])
Assuming that you are using Rails, you need to do something like this:

Airport.find(:all, :conditions => [“owner_city_state_zip LIKE ?”,
“%#{_tempZip.zip}%”])

Also, how do we write a query to written say, 2 fields out of table
instead
of all the fields.
You need to use the select option as detailed in the API:
http://www.railsbrain.com/api/rails-2.0.2/doc/index.html?a=M001686&name=find

For example, if you only wanted to fetch the id and name fields of
your airport record:

Airport.find(:all, :select => ‘id, name’, :conditions =>
[“owner_city_state_zip like ?”, “%#{_tempZip.zip}%”])

Note that you will still get a full Airport instance but only those
fields in the select will have been loaded.

– Paul

3

This is great. Thanks, Paul

On Tue, Mar 18, 2008 at 3:16 AM, Paul M. [email protected] wrote:

Also, how do we write a query to written say, 2 fields out of table

Note that you will still get a full Airport instance but only those
fields in the select will have been loaded.

– Paul

–
Rajat G.

Ph: 206-499-9495
Add: 1314 Spring Street, #412
Seattle, WA 98104
Web: http://www.pilotoutlook.com

4

On Tue, 18 Mar 2008 01:21:02 -0500, Rajat G. wrote:

Now, this gives an 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 ‘97103’%’)’ at line 1: SELECT * FROM airports WHERE
(owner_city_state_zip like ‘%‘97103’%’)

Yuck. That doesn’t look like the right behavior. I would think that the
quoted question mark should be passed as-is to the database, and not
substituted for a properly escaped string value. (So that the actual
pattern matched is ‘%?%’)

Any idea whether this can be fixed?

5

On Mar 19, 2008, at 1:10 PM, Ken B. wrote:

Any idea whether this can be fixed?

–
Ken (Chanoch) Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu/~kbloom1/

You should write this as:

Airport.find(:all, :conditions =>[“owner_city_state_zip like ?”,
“%#{_tempZip.zip}%”])
Or:

Airport.find(:all, :conditions =>[“owner_city_state_zip like ?”,
“%” + _tempZip.zip + “%”])

Each ? will be replaced with the value later in the array quoted as
appropriate for its type.

-Rob

Rob B. http://agileconsultingllc.com
[email protected]