[prev in list] [next in list] [prev in thread] [next in thread]
List: dbi-dev
Subject: more on $sth->execute(@bindVals)
From: Jimmy Oh <jimmy.oh () icommerce ! com ! sg>
Date: 1998-01-30 0:51:05
[Download RAW message or body]
*** From dbi-users -- To unsubscribe, see the end of this message. ***
Hi,
This posting may be long, but it may share some light into the
param binding problem. It is a test by me which may prove
something crucial. Please do read on if you think that you
are able to help. Thanx.
Table definition for orderdetail
Name Null? Type
------------------------------- -------- ----
ORDER_CODE NOT NULL CHAR(25)
DETAIL_SEQ NOT NULL NUMBER(3)
QUANTITY NOT NULL NUMBER(4)
PRICE NOT NULL NUMBER(5,2)
SUB_TOTAL NOT NULL NUMBER(6,2)
ITEM_CODE NOT NULL CHAR(6)
With DBI 0.90 and DBD-Oracle 0.47 (test1)
=========================================
$sqlQ = 'insert into orderdetail values (?, ?, ?, ?, ?, ?)';
if (defined($sth = $dbh->prepare($sqlQ)))
{
$sth->trace(2);
for ($i=0; $i<@$itemsRef; $i++)
{
if (!defined($sth->execute(@{$itemsRef->[$i]}))) # line 182
{
#display error
}
}
}
else
{
#display error
}
a) With $sth->trace(2) on:
--------------------------
DBI::st=HASH(0x63ac38) debug level set to 2
-> execute for DBD::Oracle::st (DBI::st=HASH(0x62d844)~0x63ac38
'199801292339260000619' 1 '1' '51.80' '51.8' '519001')
bind :p1 <== '199801292339260000619' (attribs: )
bind :p1 <== '199801292339260000619' (size 21/22/0, ptype 7, otype 1)
bind :p2 <== 1 (attribs: )
bind :p2 <== '1' (size 1/2/0, ptype 7, otype 1)
bind :p3 <== '1' (attribs: )
bind :p3 <== '1' (size 1/2/0, ptype 7, otype 1)
bind :p4 <== '51.80' (attribs: )
bind :p4 <== '51.80' (size 5/6/0, ptype 7, otype 1)
bind :p5 <== '51.8' (attribs: )
bind :p5 <== '51.8' (size 4/5/0, ptype 7, otype 1)
bind :p6 <== '519001' (attribs: )
bind :p6 <== '519001' (size 6/7/0, ptype 7, otype 1)
dbd_st_execute (for sql f3 after oci f62, outs 0)...
dbd_st_execute complete (rc0, w00, rpc1, eod0, out0)
<- execute= '1'
-> finish for DBD::Oracle::st (DBI::st=HASH(0x62d844)~0x63ac38)
<- finish= 1
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x63ac38))
<- DESTROY= undef
b) With $sth->trace(2) off:
---------------------------
Can't bind unknown placeholder '4' at
../lib/CS_OrdersManagement.pm line 182.
CS_OrdersManagement::InsertOrder('CS_OrdersManagement=HASH(0x5755c4)', \
'HASH(0x53d838)', 'ARRAY(0x53d898)', 'Apache::DBI::db=HASH(0x589720)') called at \
/export/home/jimmyoh/PROJECTS/COLDSTORAGE/mod_perl/CS_ProcessOrders.pl \
line 288
Apache::ROOT::perl::CS_5fProcessOrders_2epl::handler('Apache=SCALAR(0x11894c)') \
called at /opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131 eval {...} called \
at
/opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131
Apache::Registry::handler('Apache=SCALAR(0x11894c)') called at
-e line 0
eval {...} called at -e line 0
Please note that the 4th place holder is of data type NUMBER(5,2).
Now I do another test with the same version stated above (test2)
================================================================
# NOTE that I change the sequence of the fields being inserted
$sqlQ = 'insert into orderdetail (price, detail_seq, quantity, ';
$sqlQ .= 'order_code, sub_total, item_code) values (?, ?, ?, ?, ?, ?)';
if (defined($sth = $dbh->prepare($sqlQ)))
{
$sth->trace(2);
for ($i=0; $i<@$itemsRef; $i++)
{
my ($str1, $str2, $str3, $str4, $str5, $str6) = @{$itemsRef->[$i]};
#line 183
if (!defined($sth->execute($str4, $str2, $str3, $str1, $str5, $str6)))
{
#display error
}
}
}
else
{
#display error
}
a) With $sth->trace(2) on:
--------------------------
DBI::st=HASH(0x5f063c) debug level set to 2
-> execute for DBD::Oracle::st (DBI::st=HASH(0x5f07ec)~0x5f063c
'51.80' 1 '1' '199801292358170000619' '51.8' '519001')
bind :p1 <== '51.80' (attribs: )
bind :p1 <== '51.80' (size 5/6/0, ptype 7, otype 1)
bind :p2 <== 1 (attribs: )
bind :p2 <== '1' (size 1/2/0, ptype 7, otype 1)
bind :p3 <== '1' (attribs: )
bind :p3 <== '1' (size 1/2/0, ptype 7, otype 1)
bind :p4 <== '199801292358170000619' (attribs: )
bind :p4 <== '199801292358170000619' (size 21/22/0, ptype 7, otype 1)
bind :p5 <== '51.8' (attribs: )
bind :p5 <== '51.8' (size 4/5/0, ptype 7, otype 1)
bind :p6 <== '519001' (attribs: )
bind :p6 <== '519001' (size 6/7/0, ptype 7, otype 1)
dbd_st_execute (for sql f3 after oci f62, outs 0)...
dbd_st_execute complete (rc0, w00, rpc1, eod0, out0)
<- execute= '1'
-> finish for DBD::Oracle::st (DBI::st=HASH(0x5f07ec)~0x5f063c)
<- finish= 1
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x5f063c))
<- DESTROY= undef
b) with $sth->trace(2) off:
----------------------------
Can't bind unknown placeholder '2' at
../lib/CS_OrdersManagement.pm line 183.
CS_OrdersManagement::InsertOrder('CS_OrdersManagement=HASH(0x570a60)', \
'HASH(0x53d8d4)', 'ARRAY(0x53d934)', 'Apache::DBI::db=HASH(0x5897bc)') called at \
/export/home/jimmyoh/PROJECTS/COLDSTORAGE/mod_perl/CS_ProcessOrders.pl \
line 288
Apache::ROOT::perl::CS_5fProcessOrders_2epl::handler('Apache=SCALAR(0x11894c)') \
called at /opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131 eval {...} called \
at
/opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131
Apache::Registry::handler('Apache=SCALAR(0x11894c)') called at
-e line 0
eval {...} called at -e line 0
Now I install DBI 0.91 without re-installing DBD-Oracle 0.47. The
installation for DBI 0.91 and the 'make test' was 100% successful.
Then I did the above tests (test1a, test1b, test2a, test2b) all over
again with SBI 0.91.
test1a : with $sth->trace(2) on:
--------------------------------
DBI::st=HASH(0x5ee904) debug level set to 2
-> execute for DBD::Oracle::st (DBI::st=HASH(0x5eeab4)~0x5ee904Use
of uninitialized value at ../lib/CS_OrdersManagement.pm line 182 (#1)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
NOTE: This is not present with DBI 0.90
(W) An undefined value was used as if it were already defined. It
was
interpreted as a "" or a 0, but maybe it was a mistake. To
suppress this
warning assign an initial value to your variables.
'199801300020220000619' 1 '1' '51.80' '51.8' '519001')
bind :p1 <== '199801300020220000619' (attribs: )
bind :p1 <== '199801300020220000619' (size 21/22/0, ptype 4, otype 1)
bind :p2 <== 1 (attribs: )
bind :p2 <== 1 (size 0/1072693248/0, ptype 2, otype 1)
bind :p3 <== '1' (attribs: )
bind :p3 <== '1' (size 1/2/0, ptype 7, otype 1)
bind 4 <== '51.80' (attribs: )
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
NOTE: Why is this interpreted as '4' instead of ':p4'?
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x5ee904))
<- DESTROY= undef
[Fri Jan 30 00:20:22 1998] Uncaught exception from user code:
Can't bind unknown placeholder '4' at
../lib/CS_OrdersManagement.pm line 182.
CS_OrdersManagement::InsertOrder('CS_OrdersManagement=HASH(0x570b50)', \
'HASH(0x3241c4)', 'ARRAY(0x324224)', 'Apache::DBI::db=HASH(0x58aea8)') called at \
/export/home/jimmyoh/PROJECTS/COLDSTORAGE/mod_perl/CS_ProcessOrders.pl \
line 288
Apache::ROOT::perl::CS_5fProcessOrders_2epl::handler('Apache=SCALAR(0x2c96b4)') \
called at /opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131 eval {...} called \
at
/opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131
Apache::Registry::handler('Apache=SCALAR(0x2c96b4)') called at
-e line 0
eval {...} called at -e line 0
test1b : with$sth->trace(2) off
-------------------------------
Can't bind unknown placeholder '4' at
../lib/CS_OrdersManagement.pm line 182.
CS_OrdersManagement::InsertOrder('CS_OrdersManagement=HASH(0x570b2c)', \
'HASH(0x3241a0)', 'ARRAY(0x324200)', 'Apache::DBI::db=HASH(0x58ae84)') called at \
/export/home/jimmyoh/PROJECTS/COLDSTORAGE/mod_perl/CS_ProcessOrders.pl \
line 288
Apache::ROOT::perl::CS_5fProcessOrders_2epl::handler('Apache=SCALAR(0x2c96b4)') \
called at /opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131 eval {...} called \
at
/opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131
Apache::Registry::handler('Apache=SCALAR(0x2c96b4)') called at
-e line 0
eval {...} called at -e line 0
test2a : with $sth->trace(2) on
--------------------------------
DBI::st=HASH(0x5ee5a0) debug level set to 2
-> execute for DBD::Oracle::st (DBI::st=HASH(0x5ee750)~0x5ee5a0Use
of uninitialized value at ../lib/CS_OrdersManagement.pm line 183 (#1)
(W) An undefined value was used as if it were already defined. It
was
interpreted as a "" or a 0, but maybe it was a mistake. To
suppress this
warning assign an initial value to your variables.
'51.80' 1 '1' '199801300012110000619' '51.8' '519001')
bind :p1 <== '51.80' (attribs: )
bind :p1 <== '51.80' (size 5/6/0, ptype 7, otype 1)
bind 2 <== 1 (attribs: )
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x5ee5a0))
<- DESTROY= undef
[Fri Jan 30 00:12:11 1998] Uncaught exception from user code:
Can't bind unknown placeholder '2' at
../lib/CS_OrdersManagement.pm line 183.
CS_OrdersManagement::InsertOrder('CS_OrdersManagement=HASH(0x570bec)', \
'HASH(0x324260)', 'ARRAY(0x3242c0)', 'Apache::DBI::db=HASH(0x58af44)') called at \
/export/home/jimmyoh/PROJECTS/COLDSTORAGE/mod_perl/CS_ProcessOrders.pl \
line 288
Apache::ROOT::perl::CS_5fProcessOrders_2epl::handler('Apache=SCALAR(0x2c96b4)') \
called at /opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131 eval {...} called \
at
/opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131
Apache::Registry::handler('Apache=SCALAR(0x2c96b4)') called at
-e line 0
eval {...} called at -e line 0
test2b : with $sth-trace(2) off:
--------------------------------
Can't bind unknown placeholder '2' at
../lib/CS_OrdersManagement.pm line 183.
CS_OrdersManagement::InsertOrder('CS_OrdersManagement=HASH(0x570bc8)', \
'HASH(0x32423c)', 'ARRAY(0x32429c)', 'Apache::DBI::db=HASH(0x58af20)') called at \
/export/home/jimmyoh/PROJECTS/COLDSTORAGE/mod_perl/CS_ProcessOrders.pl \
line 288
Apache::ROOT::perl::CS_5fProcessOrders_2epl::handler('Apache=SCALAR(0x2c96b4)') \
called at /opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131 eval {...} called \
at
/opt/gnu/lib/perl5/site_perl/Apache/Registry.pm line 131
Apache::Registry::handler('Apache=SCALAR(0x2c96b4)') called at
-e line 0
eval {...} called at -e line 0
With all the above test, I conclude that somehow when it comes to bind
with a data type of NUMBER(5,2), with DBI 0.90, it has no problem with
$sth->trace(2) on but will not work with $sth->trace(2) off.
With DBI 0.91, it does not work whether with $sth->trace(2) on or
off. The interesting part with the above test is that it has problem
when it comee to the data type NUMBER(5,2).
My questions are:
1. Why the discrepancies between the 2 versions?
2. Am I right to say that $sth->execute(@bindVals) will not work on
NUMBER(5,2)? And possibly with some other data types too.
3. Could it be some table definitions stuff in Oracle which I have
left out?
I have now revert back to DBI 0.90 since it works with the
$sth->trace(2) on. I hope the info I have provided above is
sufficient to help you to trace down the problem with also solved
mine. If you need more info, please feel free to ask and you will be
given.
Any comments and help are greatly appreciated. Thanx
Jimmy
ps: my perl -V
Summary of my perl5 (5.0 patchlevel 4 subversion 4) configuration:
Platform:
osname=solaris, osvers=2.5.1, archname=sun4-solaris
uname='sunos oxford 5.5.1 generic_103640-03 sun4u sparc
sunw,ultra-1 '
hint=recommended, useposix=true, d_sigaction=define
bincompat3=y useperlio=undef d_sfio=undef
Compiler:
cc='gcc -B/usr/ccs/bin/', optimize='-O', gccversion=2.7.2.3
cppflags='-I/opt/gnu/include'
ccflags ='-I/opt/gnu/include'
stdchar='unsigned char', d_stdstdio=define, usevfork=false
voidflags=15, castflags=0, d_casti32=define, d_castneg=define
intsize=4, alignbytes=8, usemymalloc=y, prototype=define
Linker and Libraries:
ld='gcc -B/usr/ccs/bin/', ldflags =' -L/opt/gnu/lib'
libpth=/opt/gnu/lib /lib /usr/lib /usr/ccs/lib
libs=-lsocket -lnsl -lgdbm -ldb -ldl -lm -lc -lcrypt
libc=/lib/libc.so, so=so
useshrplib=false, libperl=libperl.a
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags=' '
cccdlflags='-fpic', lddlflags='-G -L/opt/gnu/lib'
Characteristics of this binary (from libperl):
Built under solaris
Compiled at Oct 24 1997 15:41:23
%ENV:
PERL5LIB="/export/home/jimmyoh/PROJECTS/ICMS"
@INC:
/export/home/jimmyoh/PROJECTS/ICMS
/opt/gnu/lib/perl5/sun4-solaris/5.00404
/opt/gnu/lib/perl5
/opt/gnu/lib/perl5/site_perl/sun4-solaris
/opt/gnu/lib/perl5/site_perl
.
_______________________________________________________________________________
Jimmy Oh
jimmy.oh@icommerce.com.sg
iCommerce Pte Ltd
DID: (65)277-0632
FAX: (65)271-3396
Doing Business The EC Way
------------------------------------------------------------------------------
To unsubscribe from this list, please visit http://www.fugue.com/dbi
If you are without web access, or if you are having trouble with the web page,
please send mail to dbi-request@fugue.com. Please try to use the web
page first - it will take a long time for your request to be processed by hand.
------------------------------------------------------------------------------
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic