Issue with SQL like

ruby-talk
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 Garg

Ph: 206-499-9495
Add: 1314 Spring Street, #412
Seattle, WA 98104
Web: http://www.pilotoutlook.com
-----------------------------------------------------------------------------------------------------
Flying is the second greatest thrill known to man. Landing is the first!

2

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

···

On 18 Mar 2008, at 06:21, Rajat Garg wrote:

3

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?

···

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

[Note: parts of this message were removed to make it a legal post.]

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'%')

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

4

This is great. Thanks, Paul

···

On Tue, Mar 18, 2008 at 3:16 AM, Paul Mucur <mudge@waferbaby.com> wrote:

On 18 Mar 2008, at 06:21, Rajat Garg 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

--
Rajat Garg

Ph: 206-499-9495
Add: 1314 Spring Street, #412
Seattle, WA 98104
Web: http://www.pilotoutlook.com
-----------------------------------------------------------------------------------------------------
Flying is the second greatest thrill known to man. Landing is the first!

5

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 Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

···

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

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

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'%')

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?

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