[DB-SIG] ANN: mxODBC Version 0.3

[M.-A. Lemburg]

Bill Tutt wrote:
> 
> Ugh.. why do we need N different ODBC modules?
> I'd really love to know..

Don't know if we do... I wrote this code up for my own needs.

> >From your webpage:
>         This interface module was created in need of an Python interface to
> the German ADABAS D relational database from Software AG. This database can
> be accessed through the Oracle CLI and or ODBC. After having tried both the
> OracleDB module from Digital Creations and the SolidDB Module (ODBC) from
> g.a.m.s. I decided to code my own version, because both didn't meet my
> requirement for high stability for various reasons.
> 
> What are those reasons?
> Please, we'd like to know, really.. :)

Ok, some explanations: I'm using the Adabas SQL database for a product
I'm currently putting together. Since it will run on Un*x, the Win32
odbc module was no option... I knew there was something like this out
there, but couldn't find any further information about it and didn't
really want to dig into the Win32 Python sources to find it somewhere
within. The next step was to try the two other possibilities at the
time: Adabas can emulate oracle at API level, so I gave the oracle
module from DC a try. That caused several segfaults, either at
connect time or shortly thereafter and the errors didn't come out right
either -- I don't blame this on the Python module, but on the emulation.
Anyway, this was a nogo. Then I considered using the ODBC interface
of the database. The only interface I could find was the Solid module
that Michael wrote. I tried version 0.0.6 (the latest at that time)
and it seemed to work fine except for one thing: LONGs caused regular
segfaults. Since I want to save Python pickles in the DB, this posed
to be a problem. So I sat down and wrote my own little thingie. And
I can tell you, it was fun. Well, until I discovered a bug in the
ODBC driver of my database. A workaround is now in place, so that
doesn't bother me anymore.

High stability: I plan to use the interface for long running
processes, which really have to do lots of db stuff. So there
are two things to watch out for: segfaults (obviously :) and
memory leakage. The second is easy to handle in the normal
operation mode, but becomes somewhat tidious in all the different
error cases.

> Also, whats wrong with just porting the ODBC code from Win32?
> I'm certainly more than willing to answer specific questions about it.
> Unfortunately I don't have the time to test fixes to it, but I'm more than
> happy to answer questions about it.
> 
> Lets see if I can list the differences between your ODBC code, and the Win32
> version..

Before you start: I had a problem finding the "new" DB API mentioned
on the web page, in fact I couldn't find it. That's why there
opbviously are some things that I didn't know of. Maybe you could
point me to the Win32 DB-API ?

> Win32 version has exceptions, where you have essentially ODBC errors..
> Exceptions: The dbi code includes exceptions your ODBC module is supposed to
> throw..
>                    Specfically: noError, opError, progError, integrityError,
> dataError, and internalError
> Where they apply to the following cases:
>         noError is generally ment to be thrown when you have a warning i.e.
> Data truncated that you should know about.
>         opError is an operational error i.e. being disconnected,  data
> source name not found, etc....
>         progError is a programming error i.e. table already exists, table
> not found, etc...
>         integrityError is an integirity error in the database's relational
> integerity i.e. a foreign key check fails
>         dataError is a data error, i.e. Numeric value out of range, division
> by zero, etc...
>         internalError is an interntal Database error, i.e. Invalid cursor
> state, invalid transaction state, etc...
> 
> You have: (sqlstate, sqltype, errortext, lineno) tuples.

The lineno component will eventually go away: it's only meant
for debugging the code.

> You can find the sqlstate->DBI exceptions in odbc.cpp in the Win32 code.

I guess I'll have to download the sources after all ;-)

> Autocommit: You default to off, when ODBC defaults to on..
> This is highly conterintuitive, and violates the principle of least
> surprise.

The DB API gave me the impression that you'll always have to
explicitly call .commit() to get your changes committed. Now what
if someone else also gets this impression and then drops some
tables just for the fun it... I'm sure he'd get into a lot of
trouble, if the module would have autocommitted his requests.
The other way around is less dangerous.

> The Win32 code has a similar function: setautocommit(0 | 1) to turn
> autocommit on or off.

I have exposed the ODBC API setconnectoption() for this to be switched
on/off.

> Whether or not ODBC should default to autocommit or not isn't really the
> question.. if it does, so should we.
> 
> Connection String:
> You have ('host:DBNAME', 'uid', 'pwd')
> Win32 has: 'DSN/uid/pwd'
> What really should be done here is to adopt an ODBC like connection
> string... aka:
> 'property=value;property=value' Where "uid" is always the userid, and "pwd"
> is always the password.

But the ODBC API SQLConnect requests those values in exactly that way...
why put those three values into one string, when passing them
in three seperate strings makes things much clearer.

> fetchxxx() return values:
> The return values are the same as the Win32 ODBC code

Glad to hear that :-) They're not defined in DB API 1.0.

> Ditching DBI:
> That was a particulary silly thing to do, the exceptions listed above exist
> in DBI, and DBI is a useful abstraction mechanism, and is database
> independant.. Why does it need to be tossed?

I didn't "toss" it, I just didn't need it. If you want it, code
it up in Python -- the mxODBC module returns the original and
standardized SQL type codes and also exposes these, so wrapping these
in a few Python classes really isn't a big deal.

The other reason I simply omitted it in the distribution is the
fact that I haven't found the "standard" dbi module everybody is
referring to. Every DB module seems to have its own, resulting in
a unnecessary namespace clash.

> Large Binary/Text data:
> This is one place where your code does the right thing, and the Win32 ODBC
> code falls down.
> Currently the Win32 ODBC code has a 64k limit on binary data, which can be
> easily fixed.
> 
> Dates:
> Well there isn't any difference between what you do, and what the DBI/Win32
> ODBC code does.
> (Well except for handling TIME types but thats just a function of ignoring
> the date part, or indeed just one line in odbc.cpp)
> They're both limited to dates after Jan 1, 1970 00:00:00 GMT.
> What we should do is come up with a sane date type for Python that isn't
> based on time_t.

Hmm, I just thought it would be simple enough interface. Add date and
time value and you get what you want. Or simply pass a time.mktime()
value. But you're right: dates before 1.1.70 aren't handled well,
though they work on Linux:

Python 1.5a4 (#165, Nov 19 1997, 22:43:43)  [GCC 2.7.2.1] on linux2
Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam
i>>> import time
>>> time.localtime(time.time())
(1997, 11, 29, 12, 41, 46, 5, 333, 0)
>>> time.mktime((1921, 11, 29, 12, 41, 46, 5, 333, 0))
-1517573894.0
>>> time.localtime(_)
(1921, 11, 29, 12, 41, 46, 1, 333, 0)
>>> 

> Looping over lists of tuples for non-resultset producing queries:
> Your code does do this, and modifying Win32 ODBC to do this is trivial as
> well. (I have the necessary details btw...)
> 
> ODBC Catalog functions:
> Well your code is ahead of the Win32 code here obviously since the Win32
> code doens't have anything like this.
> Submission of code for this kind of thing is greatly appreciated.
> 
> Wanting only to have one ODBC module,

Hey, I didn't mean to compete against any other module. On the other
hand, a little competition might result in a bright new shining
interface for the benefit of all of us ;-)

-- 
Marc-Andre Lemburg



_______________
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
_______________