DBI and like statment

[sixcolors]

I am getting this error with this code:

[Fri May 18 19:40:00 2001] asdma.pl: DBD::CSV::Statement=HASH(0x8286b18) is not a valid SQL::Statement object at /usr/lib/perl5/DBD/File.pm line 171.

Also I know the '%$query%' in not a proper statment... any ideas?

my $query;
foreach $query (@astype) {
print $query;
my $sql = "select title from $TABLE2 where astype like '%$query'";
my $sth = $dbh->prepare($sql) or die "Cannot Prepare: " .
$dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();

while (@row = $sth->fetchrow_array()) {
$adlocation = ( join (",", @row));
}
print $adlocation;

Edited 2001-05-19 by mirod: added <code> tags

[mr.nick]

Hm, I'm thinking that the resultant $sql isn't what you expect it to be. Add a

   warn $sql;

after you build your $sql and see if it's what you think it should be. If that doesn't work, try escaping like this:

my $sql = "select title from $TABLE2 where astype like '%\Q$query\E'";

[yakko]

dunno if your DBD you're using can support placeholders, but if it does, you can do:

my $sql = "select title from $TABLE2 where astype like ?";
...
$sth->execute("%$query") or die "Cannot execute: " . $sth->errstr();

(update: chromatic advised me that I can't use placeholders for the table name. Fixed.)
(update2: chipmunk says that I can't use placeholders as part of a value. Well, the intention was right, but I guess I got this one COMPLETELY WRONG! Fixed that, too. Go upvote the other replies instead :o)

--
Me spell chucker work grate. Knead grandma chicken.

[chipmunk]

I think that seeing more of the actual code would be helpful. The question is, why is your DBD::CSV::Statement object not a valid SQL::Statement object?

It may be that $query contains some metacharacter that is causing problems. Using either $dbh->quote() or placeholders will avoid this problem: my $sql = "select title from $TABLE2 where astype like " . $dbh->quote("%$query"); I generally prefer using placeholders:

my $sql = "select title from $TABLE2 where astype like ?";
my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr
+();
$sth->execute("%$query") or die "Cannot execute: " . $sth->errstr();

Note that, if you use a placeholder, the placeholder must substitute for the entire string being matched, including any wildcards.

P.S. $ and @ interpolate in double-quoted strings, but % does not.

[sixcolors]

Actualy that one I figured out... it was pulling info from one db and then using it to ret data from another... the issue that was causing the error was Taint... so I did $astype =~ /(.*)/; and then it worked also to fix the % issue I did $whatever = "\%" . $whatever . "\%";

[AidanLee]

I think the problem may just be that you're using double quotes in your assignment to $sql. This means it's going to try to evaluate the % symbol as the beginning to a hash value. You need to escape it with a backslash, methinks