DBI and like statment

sixcolors has asked for the wisdom of the Perl Monks concerning the following question:

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;
[download]

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

Comment on DBI and like statment Select or Download Code

Replies are listed 'Best First'.
Re: DBI and like statment by mr.nick (Chaplain) on May 19, 2001 at 09:06 UTC
Hm, I'm thinking that the resultant `$sql` isn't what you expect it to be. Add a `warn $sql;` [download] 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'";` [download]	[reply] [d/l] [select]
Re: DBI and like statment by yakko (Friar) on May 19, 2001 at 07:33 UTC
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();` [download] (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.	[reply] [d/l]
Re: DBI and like statment by chipmunk (Parson) on May 19, 2001 at 19:25 UTC
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();` [download] 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.	[reply] [d/l] [select]
Re: Re: DBI and like statment by sixcolors (Initiate) on May 31, 2001 at 08:49 UTC
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 . "\%";	[reply]
Re: DBI and like statment by AidanLee (Chaplain) on May 19, 2001 at 08:13 UTC
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	[reply]