On 5/9/07, ramesh thangamani <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> #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."