Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 57357
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: glex [...] qwest.net
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 2.5k
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.
Download (untitled) / with headers
text/plain 3.3k
On Mon May 10 15:11:08 2010, glex@qwest.net wrote: Show quoted text
> 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 Show quoted text
> (*=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.
Have you got a standalone example which exhibits this problem as the example below works fine for me: use DBI; use strict; use warnings; use Data::Dumper; my $h = DBI->connect() or die "cannot connect"; eval {$h->do(q/drop table mje/)}; $h->do(q/create table mje (a char(50))/); my $s = $h->prepare(q/insert into mje values(?)/); foreach (1..100) { $s->execute("FRED" . $_); } print Dumper($h->selectall_arrayref(q/select * from mje/)); my $p = 100; my $sql = q{ select a from mje where a like ? }; $s = $h->prepare($sql); $s->execute("%$p%"); print Dumper($s->fetchall_arrayref); Martin -- Martin J. Evans Wetherby, UK
Subject: Re: [rt.cpan.org #57357] execute with SQL using like ending in a ' ' broken
Date: Mon, 10 Jan 2011 10:25:00 -0600
To: bug-DBD-Oracle [...] rt.cpan.org
From: Jeff Gleixner <glex [...] qwest.net>
Download (untitled) / with headers
text/plain 202b
Martin J Evans via RT wrote: Show quoted text
Hi Martin, I forgot about that. A newer version of DBD had a fix for this problem, so the bug can be closed.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.