Issue with SQL like

[Rajat Garg]

[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.findall, :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

 

[Paul Mucur]

I am writing a query -
Airport.findall, :conditions =>["owner_city_state_zip like '%?%'",
_tempZip.zip])

Assuming that you are using Rails, you need to do something like this:

Airport.findall, :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.findall, :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]

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

This is great. Thanks, Paul

I am writing a query -
Airport.findall, :conditions =>["owner_city_state_zip like '%?%'",
_tempZip.zip])

Assuming that you are using Rails, you need to do something like this:

Airport.findall, :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.findall, :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

 

[Ken Bloom]

[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.findall, :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?

 

[Rob Biedenharn]

Hi Guys,

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

I am writing a query -
Airport.findall, :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?

You should write this as:

Airport.findall, :conditions =>["owner_city_state_zip like ?",
"%#{_tempZip.zip}%"])
Or:

Airport.findall, :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
(e-mail address removed)