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.
Simplified, here's what I think is happening:
I'm running an LDAP query (using LDAP::Simple, which returns the query as a big
hash) looking for cn, email, employeeId, and username attributes.
this would be my data set:
'Joe Blow', 'j...@here.com','123','jblow'
'Mike Smith',,'234',
'Jane Someone','jane@elsewhere','567',
My sql is:
insert into emptable (cn, email, eid, uname) values (?,?,?,?)
when I query the LDAP server I'll get a hash for each row:
$return{1}{'cn'} would give me 'Joe Blow'
$return{1}{'email'} would give me 'j...@here.com'
$return{1}{'eid'} would give me '123'
$return{1}{'uname'} would give me 'jblow'
This one works.
$return{2}{'cn'} would give me 'Mike Smith'
$return{2}{'email'} wouldn't exist because there is no email attribute in the
ldap database for Mike Smith.
$return{2}{'eid'} would give me '234'
$return{2}{'uname'} wouldn't exist because there is no uname attribute in the
ldap database for Mike Smith.
This one would throw the error:
'DBD::Oracle::st execute failed: called with 2 bind variables when 4 are needed
[for Statement "insert into emptable (cn, email, eid, uname) values (?,?,?,?)"
with ParamValues: :p1='Mike Smith',:p2='j...@here.com',:p3='234', :p4='jblow']
$return{3}{'cn'} would give me 'Jane Someone'
$return{3}{'email'} would give me 'jane@elsewhere'
$return{3}{'eid'} would give me '567'
$return{3}{'uname'} wouldn't exist because there is no uname attribute in the
ldap database for Jane Someone.
This one would throw the error:
'DBD::Oracle::st execute failed: called with 3 bind variables when 4 are needed
[for Statement "insert into emptable (cn, email, eid, uname) values (?,?,?,?)"
with ParamValues: :p1='Jane Someone',:p2='jane@elsewhere',:p3='567',
:p4='jblow']
I had to look through a bunch of the errors to find the pattern.
Fortunately there were only two fields in the LDAP query I was using that might
not exist in my second data set; checking those and explicitly populating them
with perl nulls ('') if there was no key of that name, fixed it, and the script
runs without errors.
On Feb 18, 2011, at 1:20 PM, Bruce Johnson wrote:
> This is a quickie version of what I'm doing:
>
> $sql ="insert into foo (bar, baz, buz) values(?,?,?)";
> $csr = $dbh->prepare($sql);
> while (ldap query runs){
> @parms =($val1,$val2,$val3);
> if ($val1 eq 'A'){ $csr->execute(@parms);}
> }
>
> The thing is, this is the second query to the LDAP server in the program, the
> first one, using identical DBI code, works.
>
> The only difference is the LDAP query.
>
> On Feb 18, 2011, at 1:10 PM, Furst, Carl wrote:
>
>> Are you calling $csr->finish at the end of each iteration? Might not be the
>> issue but indeed good practice.
>>
>> Carl Furst
>> o/~ What a difference a byte makes... o/~
>>
>> -----Original Message-----
>> From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu]
>> Sent: Friday, February 18, 2011 3:07 PM
>> Cc: DBI Users List (dbi-users@perl.org)
>> Subject: Re: One of us can't count....
>>
>> No, this is done as:
>>
>> $csr=$dbh->prepare($sql)
>>
>> then as $csr->execute(@parms) inside of a loop.
>>
>> On Feb 18, 2011, at 12:33 PM, Bill Ward wrote:
>>
>>> You're probably calling do($sql, @args) when you should call do($sql,
>> undef,
>>> @args)
>>>
>>> On Fri, Feb 18, 2011 at 11:31 AM, Bruce Johnson <
>>> john...@pharmacy.arizona.edu> wrote:
>>>
>>>> I'm getting the following error: (some data has been change to protect
>> the
>>>> bystanders.)
>>>>
>>>> DBD::Oracle::st execute failed: called with 18 bind variables when 19 are
>>>> needed [for Statement "insert into edsbase
>>>>
>> (cn,emplId,employeeType,employeeTitle,employeeStatus,employeeTotalAnnualRate
>> ,sn,givenName,netid,mail,employeeBldgName,employeeBldgNum,employeeCity,emplo
>> yeeFTE,employeePhone,employeePoBox,employeeRoomNum,employeeState,employeeZip
>> )
>>>> values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>>>> " with ParamValues: :p1='Werner S Noname', :p10='some@here', :p11='COLL
>> OF
>>>> PHARMAC', :p12='00207', :p13='TUCSON', :p14='0', :p15='5205114411',
>> :p16='PO
>>>> BOX 210207', :p17='344', :p18='AZ', :p19='85721-0207', :p2='111111111',
>>>> :p3='N', :p4='nosal', :p5='A', :p6='0', :p7='Zimmt', :p8='Werner S',
>>>> :p9='wsz'] at ./edsload.pl line 97, <DATA> line 532.
>>>>
>>>> I've counted three times: I have 19 fields in the db, 19 '?'s, and 19
>> param
>>>> values being passed, why am I getting this error?
>>>>
>>>>
>>>>
>>>> --
>>>> Bruce Johnson
>>>> University of Arizona
>>>> College of Pharmacy
>>>> Information Technology Group
>>>>
>>>> Institutions do not have opinions, merely customs
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Check out my LEGO blog at http://www.brickpile.com/
>>> View my photos at http://flickr.com/photos/billward/
>>> Follow me at http://twitter.com/williamward
>>
>> --
>> Bruce Johnson
>> University of Arizona
>> College of Pharmacy
>> Information Technology Group
>>
>> Institutions do not have opinions, merely customs
>>
>>
>>
>>
>>
>>
>>
>> **********************************************************
>>
>> MLB.com: Where Baseball is Always On
>
> --
> Bruce Johnson
> University of Arizona
> College of Pharmacy
> Information Technology Group
>
> Institutions do not have opinions, merely customs
>
>
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
