Asp Forum - Issue with SQL like

Rajat Garg

3/18/2008 6:21:00 AM

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

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.piloto...
-----------------------------------------------------------------------------------------------------
Flying is the second greatest thrill known to man. Landing is the first!

6 Answers

Paul Mucur

3/18/2008 10:17:00 AM

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&...

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

3/18/2008 3:41:00 PM

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

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&...
>
> 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.piloto...
-----------------------------------------------------------------------------------------------------
Flying is the second greatest thrill known to man. Landing is the first!

Ken Bloom

3/19/2008 5:08:00 PM

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

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

Rob Biedenharn

3/19/2008 5:45:00 PM

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

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://agileconsult...
Rob@AgileConsultingLLC.com

THULL ?

7/11/2008 12:45:00 PM

In article <BcCdk.69831$Jx.62532@pd7urf1no>,
"Robert Wiersema" <robwISNTAThome@THISshaw.ADDRESSca> wrote:

> <Patrick1765@aol.com> wrote in message
> news:092fed89-1ded-4454-bc8a-ae7ce59bd8cd@y21g2000hsf.googlegroups.com...
> On Jul 11, 12:06?am, "Robert Wiersema"
> <robwISNTATh...@THISshaw.ADDRESSca> wrote:
> > " but most normal
> > people don't seem to have a problem understanding what I am saying 99
> > percent of the time."
> >
> > Ah, but I'm not most people.
> > And for the record, 96% of those 99% think you're stupid. ?They're just
> > too
> > polite -- or not as actively avoiding actual work as I am -- to point it
> > out
> > to you.
>
>
> "And you know this how ? Oh yeah.. you don't. You are making it up
> just like most statistics you come up with."
>
> a) You're the one who started with the made-up statistics, Patrick.
>
> and b) "You are making it up
> just like most statistics you come up with." -- give me one example. Go on.
>
>

You know, the county does "lean con-servative."

--

"How can The Replacements be the best band of the 80s when I've never
even heard of them?" - Jon Bon Jovi, Musician Magazine (10/89)

John Bigboote

7/11/2008 2:57:00 PM

On Jul 10, 10:07 pm, "Robert Wiersema"
<robwISNTATh...@THISshaw.ADDRESSca> wrote:

> Okay -- the idea of crossing swords with him is more than enough to make me
> keep my distance. Thanks for that.
>
> RW
> (there's one guy in a threesome. One. And it's me. No potential for
> swordfights that way.)
>
> "Jus' tryin' to help,"
>
> Oh, you did. Now I need to vacuum and disinfect the inside of my head.

When I said "crossing swords," it was metaphorical. Not porcine. That
one was all you, buddy.

;-)

-jb

comp.lang.ruby

Issue with SQL like

Rajat Garg

Paul Mucur

Rajat Garg

Ken Bloom

Rob Biedenharn

THULL ?

John Bigboote

x Login to ForumsZone