win32all odbc/MS SQLServer/bigint

[Bjorn Pettersen]

> From: M.-A. Lemburg [mailto:mal at lemburg.com] 
> 
> Bjorn Pettersen wrote:
[...]

> The win32all odbc module fetches BIGINTs as floats. That's
> where you probably get your corruption from...

That's what I thought from reading the code, but I didn't want to imply
it publically in case I was wrong. I'm sure there are areas where loss
of precision is acceptable, but in business applications there is a
rather strong reaction when this happens, especially without
documentation, or warning, and the fact that the Python type in the
result set is a long not a float...

> > I looked at the mxODBC documentation, and it says it's doing 
> > a conversion [automatic] to string for bigint data... (again 
> > too slow). I'm pretty flummoxed, the ODBC C calls for 
> > bigint/__int64 are pretty straight forward(?)
> 
> They are ? __int64 is not exactly portable across platforms...

AIX, IRIX, SGI, Linux, SUN, HP, z/OS, OS/390, and Win* all support a 64
bit integer type (I did the research at my last company, and only for
the platforms we supported... -- I'm sure there are exceptions, but ODBC
isn't a lowest common denominator specification by design).

> BTW, conversion from strings to Python longs is done in C,
> so it's not as slow as you may think.

I haven't looked at the Py_LongFromLONGLONG(sp?) algorithm, so you may
be right. It was too slow when I wrote our odbc wrapper library in
C++...

> > As an added bonus, someone had the pleasure of showing me 
> > that a simple select/fetchall (i.e. all C code for Python), 
> > was over three times faster in C#..., and the win32all odbc 
> > module has crashed python somewhere deep down in sql three 
> > times in the last two hours (we're expecting this app to 
> > run for ~4 days...). Looks like it's back to C++ (*sigh*).
> 
> Have you tried mxODBC on this ? We have many customers running
> mxODBC under Zope on heavily loaded sites for weeks... so far
> without any complaints about its stability.

I will definitely try it later today (your reputation precedes you, but
upper management is cheap, especially when something doesn't come from
MS <groan>). My other options are wrapping our db lib as a Python
extension (mostly done, but not DB API compliant), or recoding in C++
(should be fast, I've got a working prototype :-)

Btw., are any of your customers running with 3Tb of collections
("someone didn't pay their bill") data, with throughput requirements and
hard deadlines ("if you're not done by noon you must pay us back
$50K"/hr) <wink>? (sorry if I come across too harsh, not my intention,
but after going through the joy of formally proving my program could do
no harm to either the data or the throughput of the server [oh, and
being called at 3am on Sunday], my sense of humor seems to have gone
into hiding -- maybe next week I'll have a beer and laugh about it :-)

-- bjorn

ps: In case you were wondering, we're not doing this on live production
data because we don't know what we're doing...