Re: Clarification on DBI module

I tried your suggestion, but still getting the same result. When i tried printing $rc in my old code i get '0E0' which means success.

John Scoles <scoles@xxxxxxxxxxx> wrote: well this is your problem

my $rc = $sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

You are expecting statement handle "$sth" to return a recordset into $rc when it calls the execute method. It does not work like that.

try this

my $sth = $dbh->prepare( q{select ename from emp where empno = ?})
or die "Can't prepare statement: $DBI::errstr";

@bind = (7902);

$sth->execute(@bind) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}

my @bind1 = ();

$sth->execute(@bind1) or die "Can't execute statement: $DBI::errstr";

while ( $row = $sth->fetchrow_arrayref() ) {
print Dumper $row;
}


In your old code try printing out the value of $rc you might see the error code there.

You code is working correctly as it is written by the way.
You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place.

----- Original Message -----
From: ramesh thangamani
To: John Scoles ; dbi-users@xxxxxxxx ; dbi-users-help@xxxxxxxx
Sent: Wednesday, May 09, 2007 8:10 AM
Subject: Re: Clarification on DBI module


Hi John,

I am attaching the code.

Here is the result:

$VAR1 = [
'FORD'
];
$VAR1 = [
'FORD'
];

Thanks,
Ramesh

John Scoles <scoles@xxxxxxxxxxx> wrote: Hard to say without some of the orginal code could be a number of things.

Do you have raiserror or pringerror set on the handle. If you are only
printing an error you may not see it and you end up just rereading the
cached data from the last query.

Post you code so we can have a look at it.

cheers
John Scoles

----- Original Message -----
From: "ramesh thangamani"
To: ;
Sent: Wednesday, May 09, 2007 3:09 AM
Subject: Clarification on DBI module


Hi,

Can you please clarify my doubts regarding DBI perl module used for
database connection.

In my environment I am using single module to prepare and execute the sql
queries. The sql query can have bind variables or they may not have. In
order to improve performance i used prepare() and execute() sequence for
the queries.

Recently I am facing a issue. When i prepare a query with bind variables
and pass the bind variables in execute() method it works fine, but second
time if i invoke without passing bind variables it returns the previous
query results and it is not throwing the error:

DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD
ERROR: OCIStmtExecute) [for Statement "

Which i believe is the expected behaviour since i should pass bind
variables without which the query should fail. How come the execute
functions fine without bind variables in the second/multiple query runs.

Is there a way to solve this issue other that re preparing the query ?.

Tried searching on Web regarding this issue but couldn't find any
discussion on this.

Thanks,
Ramesh


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.




---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! Autos.


---------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check outnew cars at Yahoo! Autos.

Relevant Pages

  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • Re: Clarification on DBI module
    ... What I am expecting is getting error message saying enough bind variables are not passed. ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer in the statment handle is not cleaned out because no execute took place. ... The sql query can have bind variables or they may not have. ... > Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)
  • Re: Clarification on DBI module
    ... May be i guess before calling execute i should check if the query has bind variables and whether they are passed otherwise i should error out. ... Check outnew cars at Yahoo! ...
    (perl.dbi.users)