Perrin Harkins <[EMAIL PROTECTED]> wrote: > You keep the handle in a global and then clear it from a cleanup > handler?
I actually keep it in an object instance, but same thing, since I'm caching the object in a global that gets removed from a cleanup handler. The object wraps all the functionality, so all DBI calls are made through it (I don't actually use DBI or the handle, ever, from any other code). That code has to work identically in or out of mod_perl, so it can't have any mod_perl-specific stuff inside it. I use a cleanup handler that calls a class method that blows away everything, and I've checked with debug logging that in fact no instances of my class survive. > My experience has been that there is no need to explicitly call begin, > at least with MySQL InnoDB tables. I use the "read committed" isolation > level, and switching from AutoCommit 1 to 0 seems to have the same > effect as issuing a begin. It is effectively always inside of a > transaction. Read committed is no problem, since it's the default; the problem arises when you want to use serializable. (I use PostgreSQL.) There are two ways to put a transaction into that mode: you can open a transaction and then "set transaction isolation level serializable", or you can open the transaction by doing "begin transaction isolation level serializable". DBI won't let you do the latter at all, it just tells you to use DBI methods for transactions, and there is no DBI method for that. You can't do the first option when AutoCommit is off, because you can't do "begin", but if you do the "set transaction" it tells you there is no transaction open. So I have to turn AutoCommit on, open a transaction, then send the "set transaction" query. Then I have to remember, for that particular database handle, to put AutoCommit back where it was before after a commit or rollback -- which means I have to keep state on each handle I have open. I do so wish for an AutoCommit mode that tells DBI, "just let me handle it, ok thanks". Pass-through mode. Get-the-heck-out-of-my-way mode. I've honestly considered dropping DBI completely and just using the direct C library interface. > If you have a small enough example of code that shows how you manage > your database handles, you're welcome to post it here and I'll take a > look at it. My database wrapper, which provides convenience methods, error checking, logging, and all that, is over 800 lines; it would take a bit to boil it down. :) My bet, though, is that I messed up somewhere in the logic that caches my instances, trying to be a little too clever. Probably in the part where I tried to add the feature, "give me a cached handle if you have one that matches all these connect parameters *except* that I don't care what the schema search path is this time". Which is a feature Apache::DBI doesn't offer, though that one admittedly may be a bit obscure -- and a bit obsessive in minimizing database connections, perhaps. Anyway, I now have all this code that doesn't use prepared statements at all, so I'm not in a good position to quickly try it; since we've determined that it should work properly, maybe someone should go back and take another guess at what the original poster's problem might actually be. :) -- Jeremy | [EMAIL PROTECTED]
