[Zope-Perl] ZOPE DBI adapter - feedback and patch (please review)

[Gisle Aas]

Joseph Wayne Norton <norton@alum.mit.edu> writes:

> 1) FYI - I'm able to connect to oracle with the following connection
>    string:
> 
>         <user>:<pass>@dbi:Oracle:host=<host>;sid=<sid>
> 
>    where <> is replaced with the actual values.  I had to look at your
>    code to see how the user and pass variables are being passed.

I need to write some Zope/Documentation.  Perhaps I just need to
understand of the Zope help system is supposed to work.

> 2) Please see the following URL:
> 
>    http://www.zope.org/Members/Roug/select_with_multiple
>    
>    The construct <dtml-var sql_delimiter> is intended to allow for
>    multiple queries in one SQL_method.
> 
>    I made a patch (see end of file) to your dbi.py code to support
>    this feature.  I *nearly* mimiced the behavior of the postgres
>    adapator -- but removed the restriction of not allowing selects in
>    a multi-query SQL method -- it seems too restrictive to me.

Looks basically right to me.  I'll incorportate this in the next
release.


> 3) It would be convienent to also install the dbi.py into the python
>    site library during the setup.py execution.  I think it is
>    currently left out.

Yes.  Currently we have two copies of the dbi.py file.  Anybody know
what I need to put into setup.py to get it installed?

> ***************
> *** 128,139 ****
>           self.db = dbi.connect(connection, user, auth,
>                                 RaiseError = 1,
>                                 PrintError = 0,
> !                               AutoCommit = 1,
>                                )
>   
>       def query(self,query_string, max_rows=9999999):
>           dbh=self.db
> !         sth = dbh.prepare(query_string)
> !         rows = sth.execute()
> !         return map(make_item, sth["NAME"], sth["TYPE"], sth["PRECISION"]), \
> !                sth.fetchall_arrayref()
> --- 128,151 ----
>           self.db = dbi.connect(connection, user, auth,
>                                 RaiseError = 1,
>                                 PrintError = 0,
> !                               AutoCommit = 0,  # JWN see below

If we set 'AutoCommit = 0' then MySQL croaks at this point with a:

  perl.PerlError: Transactions not supported by database

so some other approach is needed to deal with transaction-less
databases.

Regards,
Gisle


>       def query(self,query_string, max_rows=9999999):
>           dbh=self.db
> !       try:
> !               # JWN - hard-coded <dtml-var sql_delimiter>
> !               queries=filter(None, map(strip,split(query_string, '\0')))
> !               sth=None
> !               rows=None
> !               for qs in queries:
> !                       sth = dbh.prepare(qs)
> !                       rows = sth.execute()
> !       except:
> !               dbh.rollback()
> !               raise
> !       else:
> !               dbh.commit()
> !       # JWN - only return the results of the last query
> !       return map(make_item, sth["NAME"], sth["TYPE"], sth["PRECISION"]), \
> !              sth.fetchall_arrayref()