| Subject: | execute with SQL using like ending in a ' ' broken |
| Date: | Mon, 10 May 2010 14:10:53 -0500 |
| To: | bug-DBD-Oracle [...] rt.cpan.org |
| From: | Jeff Gleixner <glex [...] qwest.net> |
DBI::Oracle version: 1.23
DBD version : DBD.pm 11723 2008-09-02 10:09:51Z mjevans
DBI version: 1.609 ( .../5.8.8/i386-linux-thread-multi/DBI.pm )
CentOS 2.6.18-164.6.1.el5PAE
perl: This is perl, v5.8.8 built for i386-linux-thread-multi
use DBI;
my $order = 2951271;
my $dbh = DBI->connect( "DBI:Oracle:someDB", 'id', 'pw',
{RaiseError => 1, AutoCommit => 0});
my $sql = q{ select col_name from some_table where col_name like ? };
my $sth = $dbh->prepare($sql);
$sth->execute( "%$order%" );
$sth->fetchall_arrayref();
That takes a very long time to run, and doesn't return any values. I'm
guessing it's looking for '%1234567% ' or something that's not found
because if I run it with simply
my $order_id; #no value
it exhibits the exact same results. (taking a long time to run and not
returning any matches).
However, using the exact same code, with the only change being no space
at the end of $sql, e.g.
my $sql = q{ select col_name from some_table where col_name like ?};
it runs as expected and finds the correct rows.
I set trace and debug to 15 and it seems like the correct values are
binded:
DBI::st=HASH(0x96a56cc) trace level set to 0x0/15 (DBI @ 0x0/0) in
DBI 1.609-ithread (pid 464)
-> debug in DBD::_::common for DBD::Oracle::st
(DBI::st=HASH(0x96a57ec)~0x96a56cc 15) thr#948c008
<- debug= 15 at ./test.pl line 18
-> execute for DBD::Oracle::st (DBI::st=HASH(0x96a57ec)~0x96a56cc
'%2951271%') thr#948c008
dbd_bind_ph(): bind :p1 <== '%2951271%' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :p1 as '%2951...' (not-utf8, ftype 1
(VARCHAR), csid 0, csform 0, inout 0)
dbd_rebind_ph_char() (1): bind :p1 <== '%2951...' (size 9/12/0, ptype
4(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :p1 <== ''%2951...' (size 9/12, otype
1(VARCHAR), indp 0, at_exec 1)
bind :p1 as ftype 1 (VARCHAR)
OCIBindByName(96df800,97096a4,96d8690,":p1",placeh_len=3,value_p=970a098,value_sz=12,dty=1,indp=97096bc,alenp=0,rcodep=97096b4,maxarr_len=0,curelep=0
(*=0),mode=DATA_AT_EXEC,2)=SUCCESS
OCIBindDynamic(96df640,96d8690,9709680,e39ce0,9709680,e39fe0)=SUCCESS
OCIAttrGet(96df640,OCI_HTYPE_BIND,9709690,0,31,96d8690)=SUCCESS
dbd_rebind_ph(): bind :p1 <== '%2951...' (in, not-utf8, csid 1->0->1,
ftype 1 (VARCHAR), csform 0->0, maxlen 12, maxdata_size 0)
OCIAttrSet(96df640,OCI_HTYPE_BIND,bff8f458,0,31,96d8690)=SUCCESS
dbd_st_execute SELECT (out0, lob0)...
Statement Execute Mode is 0 (DEFAULT)
in ':p1' [0,0]: len 9, ind 0, value='%2951...'
It seems to be OK using MySQL, which would lead me to a problem in
DBD::Oracle, instead of DBI.