Re: Re: DBI (Prepare once execute many fails)

[Itatsumaki]

From the DBI documentation:

When all the data has been fetched from a SELECT statement, the driver
+ should automatically call finish for you. So you should not normally
+ need to call it explicitly except when you know that you've not fetc
+hed all the data from a statement handle. The most common example is 
+when you only want to fetch one row, but in that case the selectrow_*
+ methods may be better anyway. Adding calls to finish after each fetc
+h loop is a common mistake, don't do it, it can mask genuine problems
+ like uncaught fetch errors.

And also says:

Calling finish resets the /Active attribute for the statement. It may 
+also make some statement handle attributes (such as NAME and TYPE) un
+available if they have not already been accessed (and thus cached). 

The finish method does not affect the transaction status of the databa
+se connection. It has nothing to do with transactions. It's mostly an
+ internal "housekeeping" method that is rarely needed. See also /disc
+onnect and the /Active attribute.

Update: The DBI docs (same link) also says for the execute method:

If execute() is called on a statement handle that's still active ($sth
+->{Active} is true) then it should effectively call finish() to tidy 
+up the previous execution results before starting this new execution.

[Grygonos]

to clarify... it is neccesary when you don' want to retrieve all the data provided by a select statement, which should rarely be the case since you would filter the rows via your query. but in most cases of $sth->execute() it is not neccesary because most ppl do while(@row=$sth->fetchrow_array){} and if it doesn' work update drivers correct?

[runrig]

to clarify... it is neccesary when you don' want to retrieve all the data provided by a select statement

If it is necessary in any DBD driver, then it should be considered a bug and reported to the author as such. The only reason to call finish is to free up any temporary database resources that your statement might be using, e.g. a temporary table required for an "order by" clause. As mentioned elsewhere, the DBD itself should be calling finish itself before an execute on an active statement handle. That being said, if you're only fetching one row, you ought to call finish, but it shouldn't be necessary. If that fixes the OP's problem, then there's a bug in the DBD he's using.

[Itatsumaki]

Grygonos, it seems gmax and I disagree on this point.

My understanding is that DBI specifies that execute() is called on an active-statement handle, the DBD is supposed to automatically reset the Active flag (i.e. call finish()). Discussions on the DBI mailing list seem to have come to the consensus that finish() is unnecessary and can hide real problems. The one case where it might be useful is to instruct the DB to free up resources after you're done with an $sth that hasn't been fully fetched through.

So, in summary my reading of the documentation indicates it isn't needed and should be avoided as much as possible. There was a pretty extensive discussion of this in December/January on the list [e-mail] which you might want to check out.

For DBD::ODBC there was a bug in versions prior to 1.02 that required finish() in certain circumstances, but that has been resolved and it's worth upgrading to a more recent version.

Hope this helps!


-Tats