SQL::Abstract - generating q(like "%?%")?

[David K. Wall]

I've been playing with SQL::Abstract, but I can't seem to get it to
generate the SQL I want. I want the following, with wildcards around
the placeholder:

WHERE ( DB_FIELD LIKE "%?%" )
OR ( DB_FIELD LIKE "%?%" )
OR ( DB_FIELD LIKE "%?%" )

The closest I've been able to get to it is this:

use SQL::Abstract;
my $test = SQL::Abstract->new( cmp => 'like' );
my ($stmt, @bind) = $test->where(
{ DB_FIELD => [qw(word1 word2 word3)] }
);
print $stmt;


which produces (formatted for usenet)

WHERE ( ( ( DB_FIELD LIKE ? )
OR ( DB_FIELD LIKE ? )
OR ( DB_FIELD LIKE ? ) ) )

I don't care about the extra parentheses one way or another.

Any ideas?

 

[gnari]

I've been playing with SQL::Abstract, but I can't seem to get it to
generate the SQL I want. I want the following, with wildcards around
the placeholder:

WHERE ( DB_FIELD LIKE "%?%" )
OR ( DB_FIELD LIKE "%?%" )
OR ( DB_FIELD LIKE "%?%" )

I was under the impression thatbthis is illegal
placeholder syntax

have you actually managed to use a SQL statement
using such syntax? I think it would be practical to
verify that befor going any further.

gnari

 

[David K. Wall]

I was under the impression that this is illegal
placeholder syntax

have you actually managed to use a SQL statement
using such syntax? I think it would be practical to
verify that before going any further.

Unfortunately I think you're right. Sigh.

In my defense, I'm rewriting an old program that I first wrote as an
exercise to learn DBI. It turned out to be useful and is still being
used 4 years later -- but it's so convoluted that, even though I
wrote it, it's hard to tell what it's doing, and I made a mistake
reading it.

I started to post the code that caused my confusion, but it's so ugly
that I cringe from looking at it. If MJD needs a program for a Red
Flags article, he could always dissect this one to find various
stupidities. Oh, the shame.

 

[Uri Guttman]

g> I was under the impression thatbthis is illegal
g> placeholder syntax

i think you can just have ? there and put the % wrapper in the data
before you send it to dbi. it really is just a string with %% around it
and it shouldn't be any different than other uses of placeholders.

uri

 

[gnari]

g> I was under the impression thatbthis is illegal
g> placeholder syntax

i think you can just have ? there and put the % wrapper in the data
before you send it to dbi. it really is just a string with %% around it
and it shouldn't be any different than other uses of placeholders.

and by some coincidence , that is exactly what SQL::Abstract generated.


gnari

 

[Matt Garrish]

I've been playing with SQL::Abstract, but I can't seem to get it to
generate the SQL I want. I want the following, with wildcards around
the placeholder:

WHERE ( DB_FIELD LIKE "%?%" )
OR ( DB_FIELD LIKE "%?%" )
OR ( DB_FIELD LIKE "%?%" )

The closest I've been able to get to it is this:

use SQL::Abstract;
my $test = SQL::Abstract->new( cmp => 'like' );
my ($stmt, @bind) = $test->where(
{ DB_FIELD => [qw(word1 word2 word3)] }
);
print $stmt;


which produces (formatted for usenet)

WHERE ( ( ( DB_FIELD LIKE ? )
OR ( DB_FIELD LIKE ? )
OR ( DB_FIELD LIKE ? ) ) )

I don't care about the extra parentheses one way or another.

You can't put a placeholder within a string. Incorporate the wildcards into
your string values before you execute the statement. Assuming you have the
above three placeholders in your statement, you could then do something like
this:

$my_sth->execute("%$val1%", "%$val2%", "%$val3%");

Matt

 

[David K. Wall]

You can't put a placeholder within a string. Incorporate the wildcards
into your string values before you execute the statement. Assuming you
have the above three placeholders in your statement, you could then do
something like this:

$my_sth->execute("%$val1%", "%$val2%", "%$val3%");

And oddly enough, that's what my original program was doing. I just read
it wrong. But thanks to you and gnari for pointing it out.

 

[Uri Guttman]

g> and by some coincidence , that is exactly what SQL::Abstract
g> generated.

well, i just applied PSI::ESP and Add::More::Brains and voila!

uri