Re: One of us can't count....
On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote:
Figured it out.
The second ldap query did not return all the attributes I was looking for,
and when it returned no value for that attribute, the bind variable wasn't
populated, leading to the mismatch between expected and actual. The error is
correct.
The error MESSAGE, however contained the last known value for that number
bind variable, which may be a bug.
I wasn't quite correct, the error message actually lists the parameter values
of the last successful insert statement.
Here is a test script that demonstrates the problem. (I saw this on oracle,
dunno if it's actually a DBD oracle issue or a DBI issue.)
---
#!/usr/bin/perl
use DBI;
$login=useyourown;
$dbpass=useyourown;
$dbname=host=server.name;sid=sid_name;
# Create table statement
$sqcreate = SQ;
create table test (
foo varchar2(10),
bar varchar2(10),
baz number)
SQ
$sql = insert into test (bar, baz, foo) values (?,?,?);
$dbh = DBI-connect(dbi:Oracle:$dbname, $login, $dbpass);
$dbh-do($sqcreate) or die $dbh-errstr;
$csr = $dbh-prepare($sql) or die $dbh-errstr;
$parms{1}{'foo'}=Bill;
$parms{1}{'bar'}=Kaboom;
$parms{1}{'baz'}=123;
$parms{2}{'foo'}=Mike;
$parms{3}{'foo'}=Jane;
$parms{3}{'bar'}=Kuunch;
$parms{4}{'foo'}=Alice;
$parms{4}{'bar'}=Dorrp;
$parms{4}{'baz'}=456;
$parms{5}{'foo'}=Pat;
$parms{5}{'bar'}=PaDing;
for ($i=1;$i6;$i++){
@inparms =();
foreach $k(sort keys %{$parms{$i}}){
push @inparms, $parms{$i}{$k};
}
$csr-execute(@inparms);
}
exit;
--
This is the output of this script:
DBD::Oracle::st execute failed: called with 1 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
43.
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
43.
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Dorrp', :p2=456, :p3='Alice'] at ./testofparamarray.pl line
43.
My environment is:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
DBI (1.607) - Database independent interface for Perl
DBD::Oracle (1.22) - Oracle database driver for the DBI module
perl --version
This is perl, v5.10.0 built for i686-linux
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: One of us can't count....
On Feb 21, 2011, at 11:11 AM, Bruce Johnson wrote:
On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote:
Figured it out.
The second ldap query did not return all the attributes I was looking for,
and when it returned no value for that attribute, the bind variable wasn't
populated, leading to the mismatch between expected and actual. The error is
correct.
The error MESSAGE, however contained the last known value for that number
bind variable, which may be a bug.
I wasn't quite correct, the error message actually lists the parameter values
of the last successful insert statement.
Here is a test script that demonstrates the problem.
Here is a somewhat clearer one that tells you what data we're trying to insert:
-
#!/usr/bin/perl
use DBI;
$login=pharmmail;
$dbpass=nhy329;
$dbname=host=tonic.pharmacy.arizona.edu;sid=phmweb;
# Create table statement
$sqcreate = SQ;
create table test (
foo varchar2(10),
bar varchar2(10),
baz number)
SQ
$sql = insert into test (bar, baz, foo) values (?,?,?);
$dbh = DBI-connect(dbi:Oracle:$dbname, $login, $dbpass);
$dbh-do($sqcreate) or die $dbh-errstr;
$csr = $dbh-prepare($sql) or die $dbh-errstr;
$parms{1}{'foo'}=Bill;
$parms{1}{'bar'}=Kaboom;
$parms{1}{'baz'}=123;
$parms{2}{'foo'}=Mike;
$parms{3}{'foo'}=Jane;
$parms{3}{'bar'}=Kuunch;
$parms{4}{'foo'}=Alice;
$parms{4}{'bar'}=Dorrp;
$parms{4}{'baz'}=456;
$parms{5}{'foo'}=Pat;
$parms{5}{'bar'}=PaDing;
for ($i=1;$i6;$i++){
@inparms =();
print inserting data for $parms{$i}{'foo'}\n;
foreach $k(sort keys %{$parms{$i}}){
push @inparms, $parms{$i}{$k};
}
$csr-execute(@inparms);
}
exit;
And the error:
oraweb@tonic:~/perl/pharmmail ./testofparamarray.pl
inserting data for Bill
inserting data for Mike
DBD::Oracle::st execute failed: called with 1 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
44.
inserting data for Jane
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Kaboom', :p2=123, :p3='Bill'] at ./testofparamarray.pl line
44.
inserting data for Alice
inserting data for Pat
DBD::Oracle::st execute failed: called with 2 bind variables when 3 are needed
[for Statement insert into test (bar, baz, foo) values (?,?,?) with
ParamValues: :p1='Dorrp', :p2=456, :p3='Alice'] at ./testofparamarray.pl line
44.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Re: One of us can't count....
On Mon, 21 Feb 2011 11:11:09 -0700 Bruce Johnson john...@pharmacy.arizona.edu wrote: On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote: The error MESSAGE, however contained the last known value for that number bind variable, which may be a bug. I wasn't quite correct, the error message actually lists the parameter values of the last successful insert statement. That's hideous. ... just ... hideous.
Re: One of us can't count....
On Feb 21, 2011, at 11:20 AM, Bruce Johnson wrote: On Feb 21, 2011, at 11:11 AM, Bruce Johnson wrote: On Feb 18, 2011, at 1:55 PM, Bruce Johnson wrote: Figured it out. The second ldap query did not return all the attributes I was looking for, and when it returned no value for that attribute, the bind variable wasn't populated, leading to the mismatch between expected and actual. The error is correct. The error MESSAGE, however contained the last known value for that number bind variable, which may be a bug. I wasn't quite correct, the error message actually lists the parameter values of the last successful insert statement. Here is a test script that demonstrates the problem. Here is a somewhat clearer one that tells you what data we're trying to insert: - #!/usr/bin/perl use DBI; $login=pharmmail; $dbpass= Ever have one of those days? My year has been like that so far...:-( forgot to scrub the 'better version' of actual account info. schema password changed, please forget you ever saw this :-/ (this oracle user only has access to a handful of tables on a db that cannot be reached from the outside without authentication, and is used on a web script that also cannot be reached from the outside without authentication..as security breaches go, this was about as good as it gets.) -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
