[Zope-Perl] ZOPE DBI adapter

[Gisle Aas]

Harry Danilevsky <hdanilevsky@siichi.com> writes:

> > > > 
> > > 
> > > That's the strangest thing: no, it doesn't run each time, 
> > which means
> > > the connections get created not from DB.__init__, correct?
> > 
> > Correct.  I think DBI can take the actually database connections
> > up/down behind the scenes.  Does the DBD::sybase documentation say
> > anything about this.
> > 
> 
> 
> Here's what seems to be happenning: 
> 
> DBD::Sybase would open an additional connection internally,
> if there are pending data on the open connection
> (or, to be more specific, if there is already an active statement handle
> for this db handle)  but only if AutoCommit is ON (which is the default).
> When I added a line
> 
> sth.finish()
> 
> to the DB.query() method, everything went back to normal.
> I'm not sure why it's necessary (finish() should be called internally).

Can you reproduce this is a short test script using dbi directly from Python?
Does an identical perl script using DBI behave the same?

I would also guess that finish would be called internally when the sth
object is cleared, but perhaps we have a reference count error that
fails to destroy the object.  If the test above reveals different
behaviour between perl/python then this need investigation.

> I thought it happens when there is a db-level error, and the statement
> handle
> is not properly closed, but it happens for "good" queries too (not always,
> which makes it even more puzzling).
> Anyway, having an extra sth.finish() call should be at least harmless.

Not if it hides a real error somewhere.  I want to understand a bit
more before we do this.

> Now, about the change in alpha5. I'm not sure if setting AutoCommit to 0
> and performing transaction management on the client side is
> a good thing. The problem is that you may be executing a batch
> (or more typically, a stored procedure) containing statements which
> can't be placed inside transaction, such as CREATE TABLE, or TRUNCATE TABLE
> (again, I'm talking about Trasnact-SQL in Sybase and MS SQL server; I don't
> know
> if this is a problem for other databases). Implementing transaction
> management in stored procedures is very common practice, and 
> setting AutoCommit=0 internally may result in serious trouble.
> So, shouldn't AutoCommit come from the connection string,
> and let the developer decide what's the proper value to use?

I guess so.  Time find a way to extend the string with arbitrary
key/value pairs.

Currently we have use:

    [ <username> [ ":" <password> ] "@" ] <dbi source spec>

it would be tempting to change that into:

    <dbi source spec> ( ";" <key> "=" <value> )*

but as the <dbi source spec> could actually use any chars it is hard
to know the there the spec string ends.  The safe solution to that
would be to introduce escape sequences for writing literal ";" in the
source spec.

We could perhaps also get away with ignoring escaping and something
like:

   <dbi source spec> [ ";" ( ";" <key> "=" <value> )+ ]

i.e. that everything after ";;" are extra parameters to the connect
call. E.g.:

   dbi:Oracle:host=foo;sid=42;;username=foo;AutoCommit=0

Other ideas?

Regards,
Gisle