Re: [CDBI] Re: ANNOUNCE: Ima::DBI 0.35 released

> Said wacko can get the desired effect pretty simply by subclassing
> Ima::DBI (which you're supposed to do anyway) and overriding set_sql.
> That's what Class::DBI is doing actually, for some of the parameters
> to set_sql.

That wasn't obvious to this wacko, but it might have helped.

> Would you guys mind explaining how this is causing problems for you?
> I've used prepare_cached on Oracle, Postgres, and MySQL, and never had
> any unexpected behavior from it.  I use it practically everywhere. And
> I don't even set the if_active flag.

I ended up looking at the prepare_cached handling in Ima::DBI and CDBI
while trying to work around DBD::SQLite's schema error:
  "DBD::SQLite::db selectrow_array failed: database schema has changed(1)"

I'd use CDBI classes which would call Ima::DBI set_sql and prepare_cache
the statement handle.  After bulk loading, I'd ANALYZE the database which
invalidated all cached statement handles.  Hacking a local copy of
CDBI to never cache fixed the problem but in the end I opted to clear
$dbh->{CachedKids} when required instead.

The DBI-only sample code:

  # http://www.perlmonks.org/index.pl?node_id=619312
  $dbh->prepare_cached('select "hello" from a');
  $dbh->do("analyze");
  my $sth = $dbh->prepare_cached('select "hello" from a');
  print "got ", $dbh->selectrow_array($sth), "\n";

I now consider it to be a bug in DBD::SQLite (1.13), a later version should
use the sqlite3_prepare_v2 call which internally handles the schema change
condition.  During the debugging process I couldn't find a clean way
to disable the caching via CDBI.  I think that most of the solutions discussed
here would've helped although I just tried $if_active = 3 without success.

This case alone isn't enough to motivate changes to CDBI/Ima::DBI but
it's all I've got.  I hope it's some help.

Brad

PS.  Thanks for updating Ima::DBI Perrin.

-- 
   There is a saying of the elders' that goes, "Step from under the eaves
   and you're a dead man. Leave the gate and the enemy is waiting." This
   is not a matter of being careful. It is to consider oneself as dead
   beforehand.                     -- Hagakure http://bereft.net/hagakure/