Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Bug Report: DBI, string fn REGEXP bound variable

by cynetix (Initiate)
on Feb 03, 2006 at 20:55 UTC ( #527813=perlquestion: print w/replies, xml ) Need Help??
cynetix has asked for the wisdom of the Perl Monks concerning the following question:

DBI does not recognize this syntax for a mySQL query:


using a bound variable. The REPLACE above could probably be substituted by another function, and using LIKE instead of REGEXP produces the same problem. In other words, this is broken:

q|SELECT * FROM t WHERE REPLACE(t.col,"","") LIKE ?|

and this is not, of course:


Substituting the REGEXP inline instead of using a bound variable fixes the problem, as does running the query in mySQL directly.

I'm guessing this is a DBI problem because it's the parsing of the query and the variable-bind that's causing the problem. If this is not the right forum for this issue, or it needs to be reported to another place, please tell me where. Thank you!

Replies are listed 'Best First'.
NOT A DBI BUG! Re: Bug Report: DBI, string fn REGEXP bound variable
by rnahi (Curate) on Feb 04, 2006 at 08:46 UTC

    Where is your code? If you want to submit a bug report, you should present a repeatable test case.

    Anyway, there is no bug with that query. As the following snippet shows, the query you have proposed works just fine.

    #!/usr/bin/perl use strict; use warnings; use Data::Dumper; use DBI; my $user = undef; my $password= undef; my $dbh=DBI->connect("dbi:mysql:test;host=localhost" . ";mysql_read_default_file=$ENV{HOME}/.my.cnf", $user, $password, {RaiseError => 1}) or die "Can't connect: $DBI::errstr\n"; $dbh->do(qq{DROP TABLE IF EXISTS t}) ; $dbh->do(qq{ CREATE TABLE t ( i int not null, c char(10), primary key (i)) }); $dbh->do(qq{ insert into t (i,c) values (1, 'cat'), (2,'dog'), (3,'rabbit'), (4,'horse') }) ; my $query = q|SELECT * FROM t WHERE REPLACE(c,"","") REGEXP ?|; my $sth = $dbh->prepare($query); $sth->execute('t$'); print "one by one\n"; while (my $rec = $sth->fetchrow_arrayref()) { print Dumper($rec); } print "all at once\n"; print Dumper $dbh->selectall_arrayref($query,{ Slice=>{} }, '^d' ); one by one $VAR1 = [ 1, 'cat' ]; $VAR1 = [ 3, 'rabbit' ]; all at once $VAR1 = [ { 'c' => 'dog', 'i' => 2 } ];

    Perhaps, you should consider a bug in YOUR code.


Re: Bug Report: DBI, string fn REGEXP bound variable
by Fletch (Chancellor) on Feb 03, 2006 at 21:10 UTC

    The proper forum would be most likely, in the queue for DBD::mysql more specifically.

    That aside I'm pretty sure that most DBDs have limitations of some form or another (usually imposed by the underlying database libraries themselves) as to where exactly placeholders can be used. You didn't post what error message you're getting, so this is just a WAG.

    Update: Changed phrasing about placeholders from "what can be used" to the above. Was thinking "what places you can use placeholders" and didn't clean up the draft.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://527813]
Approved by planetscape
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2018-05-21 21:31 GMT
Find Nodes?
    Voting Booth?