DBI:SQLite bind_param problem

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

Hello all, Not sure if this is a bug, i'm starting to think it could be. Works fine for other queries, but not this one for some reason. bind_param simply doesn't replace the param no matter what I do:

my $blogTagToShow           =       shift;
my $dbh = DBI->connect("dbi:SQLite:dbname=$db");
$dbh->trace(4);
my $query = "SELECT * FROM tblBlogPosts WHERE fldTags LIKE '%?%' ORDER
+ BY fldDate DESC";    
my $sth = $dbh->prepare($query);
$sth->bind_param(1, $blogTagToShow);
$dbh->trace(0);
[download]

When looking at the trace, it seems to go through the motions of binding the param, but doesn't.

sqlite trace: prepare statement: SELECT * FROM tblBlogPosts WHERE fldT
+ags LIKE '%?%' ORDER BY fldDate DESC at dbdimp.c line 258
    <- prepare= DBI::st=HASH(0x93bba20) at Libraries//libBlog.pm line 
+42 via  at index.pl line 152
    -> bind_param for DBD::SQLite::st (DBI::st=HASH(0x93bba20)~0x93b64
+ec 1 'windows')
sqlite trace: bind into 0x93b6504: 1 => windows (0) pos 0
 at dbdimp.c line 464
    <- bind_param= 1 at Libraries//libBlog.pm line 43 via  at index.pl
+ line 152
    -> trace for DBD::SQLite::db (DBI::db=HASH(0x93b62dc)~0x93b627c 0)
    <- trace= 4 at Libraries//libBlog.pm line 45 via  at index.pl line
+ 152
    -> execute for DBD::SQLite::st (DBI::st=HASH(0x93bba20)~0x93b64ec)
sqlite trace: execute at dbdimp.c line 325
sqlite trace: Execute returned 6 cols
[download]

Anyone got any ideas ? Cheers!, Dave

Comment on DBI:SQLite bind_param problem Select or Download Code

Replies are listed 'Best First'.
Re: DBI:SQLite bind_param problem by roboticus (Chancellor) on Jun 17, 2007 at 17:24 UTC
davehope: You're attempting to ask the driver to replace a fragment of a parameter with your argument. That's not the way it works. It doesn't want to look inside quotes for your parameters. Try instead: `my $query="SELECT * FROM tblBlogPosts WHERE fldTags LIKE ? ORDER BY fl +dDate DESC"; my $sth = $dbh->prepare($query); $sth->bind_param(1, '%'.$blogTagToShow.'%');` [download] ...roboticus Note: I'm basing this on how it works with SQL Server. I've never tried SQLite, so the rules may be different, and I may be talking through my hat.	[reply] [d/l]
Re: DBI:SQLite bind_param problem by shmem (Chancellor) on Jun 17, 2007 at 17:25 UTC
`WHERE fldTags LIKE '%?%' ORDER` [download] That looks pretty weird to me. Try `my $blogTagToShow = '%' . (shift) . '%'; my $query = "SELECT * FROM tblBlogPosts WHERE fldTags LIKE ? ORDER BY +fldDate DESC"; my $sth = $dbh->prepare($query); $sth->bind_param(1, $blogTagToShow);` [download] --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply] [d/l] [select]
Re^2: DBI:SQLite bind_param problem by davehope (Initiate) on Jun 17, 2007 at 17:35 UTC
Thanks very much to the both of you, didn't realise that bind_param() didn't look inside quotes.	[reply]
Re: DBI:SQLite bind_param problem by andreas1234567 (Vicar) on Jun 18, 2007 at 08:34 UTC
A small off-topic piece of advice: `SELECT * FROM tblBlogPosts WHERE fldTags LIKE '%?%'` [download] Beware the performance implications of using Wildcard characters and the LIKE operator: Under most circumstances, the original LIKE or GLOB operator is still tested against each input row .. This means your database backend must do a full table scan for every query. As the database grows, the queries will take increasingly long time to complete. Disclaimer: I've no experience with SQLLite. The above applies to all other database I know of: MySQL, Postgres, Oracle, DB2, SQL Server. -- print map{chr}unpack(q{A3}x24,q{074117115116032097110111116104101114032080101114108032104097099107101114})	[reply] [d/l]