Re: Clarification on DBI module

On 5/9/07, ramesh thangamani <ramesh_thangamani@xxxxxxxxxxx> wrote:

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.



My reading of 'perldoc DBI' (at
http://search.cpan.org/~timb/DBI-1.55/DBI.pm#DBI_STATEMENT_HANDLE_OBJECTS)
suggests that the $sth->bind_param() method makes the values bound sticky -
the types definitely are sticky - and therefore, once values have been
supplied, those values are remembered. The $sth->bind_param_inout() - which
isn't supported by all drivers - stores references to variables, so it uses
the value at the time the $sth->execute() is called.

In other words, what you're seeing is what I'd expect to see. If you want
to provoke the error, try (it might not work) supplying one value instead of
the half-dozen needed; that might generate an error, though I'd not want to
rely on that.


--
Jonathan Leffler <jonathan.leffler@xxxxxxxxx> #include <disclaimer.h>
Guardian of DBD::Informix - v2007.0226 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

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: Connection between library cache latches and statistics?
    ... do many of the queries on these tables query against ... are the queries looking for values that did not exist two days ... I trace a SQL Server based connection to our DB ... Look for heavy parsing, use BIND variables". ...
    (comp.databases.oracle.server)
  • 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)
  • Re: Clarification on DBI module
    ... You get the same results for the second execute because the record buffer ... The sql query can have bind variables or they may not ... > Ahhh...imagining that irresistible "new car" smell? ...
    (perl.dbi.users)