Well, we finally discovered how prepare_cached can "cause problems", as the docs so eloquently state. I'm amazed we evaded it this long. We use prepare_cached literally everywhere for performance reasons, so it was only a matter of time before somewhere, 10 or 15 levels deep in method calls, someone tried to reuse a statement while it was active somewhere farther up the call stack.
I had solved this problem in our DBI subclass, but this seems a useful (and benign) enough change that I went ahead and integrated it into our local DBI sources. Tiny code and large doc patch attached. Do with it what you will. -- Stephen Clouse <[EMAIL PROTECTED]> Senior Programmer, IQ Coordinator Project Lead The IQ Group, Inc. <http://www.theiqgroup.com/>
Index: DBI.pm
===================================================================
RCS file: /home/cvs/patched-sources/DBI/DBI.pm,v
retrieving revision 1.1.1.1
diff -u -r1.1.1.1 DBI.pm
--- DBI.pm 28 Feb 2002 01:05:12 -0000 1.1.1.1
+++ DBI.pm 28 Feb 2002 04:31:46 -0000
@@ -1239,7 +1239,8 @@
my $key = ($attr) ? join("~~", $statement, @attr_keys, @{$attr}{@attr_keys}) :
$statement;
my $sth = $cache->{$key};
if ($sth) {
- if ($sth->FETCH('Active')) {
+ $allow_active = 0 if !defined $allow_active;
+ if ($sth->FETCH('Active') && $allow_active != 2) {
Carp::carp("prepare_cached($statement) statement handle $sth was still
active")
if !$allow_active;
$sth->finish;
@@ -2879,14 +2880,6 @@
corresponding cached C<$sth> will be returned without contacting the
database server.
-This caching can be useful in some applications, but it can also cause
-problems and should be used with care. If the cached C<$sth> being
-returned is active (i.e., is a C<SELECT> that may still have data to be
-fetched) then a warning will be generated and C<finish> will be called
-for you. The warning can be suppressed by setting C<$allow_active> to
-true. The cache can be accessed (and cleared) via the L</CachedKids>
-attribute.
-
Here are some examples of C<prepare_cached>:
sub insert_hash {
@@ -2908,6 +2901,44 @@
$sth = $dbh->prepare_cached("SELECT * FROM $table $qualifier");
return $dbh->selectall_arrayref($sth, {}, @values);
}
+
+Caveat emptor: This caching can be useful in some applications, but it can also
+cause problems and should be used with care. Here is a contrived case where
+caching would cause a significant problem:
+
+ my $sth = $dbh->prepare_cached('SELECT * FROM foo');
+ $sth->execute(@values);
+ while (my $data = $sth->fetch) {
+ my $sth2 = $dbh->prepare_cached('SELECT * FROM foo');
+ $sth2->execute(@more_values);
+ while (my $data2 = $sth2->fetch) {
+ do_stuff($data,$data2);
+ }
+ }
+
+In this example, since both handles are preparing the exact same statement,
+C<$sth2> will not be its own statement handle, but a duplicate of C<$sth>
+returned from the cache. The results will certainly not be what you expect,
+and, depending on the pickiness of the SQL engine, may throw an error when it
+returns to the outer loop expecting to fetch more data when the inner loop has
+already exhausted it. The C<$allow_active> parameter lets you adjust DBI's
+behavior when prepare_cached is returning a statement handle it sees as active.
+There are three settings:
+
+=over 4
+
+0: A warning will be generated, and C<finish> will be called on the statement
+handle before it is returned. This is the default behavior if C<$allow_active>
+is not passed.
+
+1: C<finish> will be called on the statement handle, but the warning is
+suppressed.
+
+2: DBI will not touch the statement handle before returning it. You will need
+to check C<$sth->E<gt>C<{Active}> on the returned statement handle and deal
+with it in your own code.
+
+=back
=item C<commit>
msg09231/pgp00000.pgp
Description: PGP signature
