Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

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:

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

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:

q|SELECT * FROM t WHERE t.col LIKE ?|

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!

Comment on Bug Report: DBI, string fn REGEXP bound variable
Select or Download 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 rt.cpan.org 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.

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.

    PEBCAK.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://527813]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (8)
As of 2014-07-25 07:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (169 votes), past polls