FreeTDS Development Group

[Marc Abramowitz <marca AT surveymonkey.com>]

Hi James,

Again, way more info than I could have reasonably expected, so thank you
very much for taking the time!

There is a lot of info here and it's probably going to take me a bit of
time to process, but I wanted to make sure to thank you and answer your
easy questions.

> My bread and water these days comes from Continuum Analytics, so I have
> great interest in things db pythonic.

Apologies for making the assumption that you would only be interested in
FreeTDS-proper and not in stuff that uses it, like pymssql. Folks at CA
are probably extremely comfortable with Cython -- that means that you guys
would be fantastic at reviewing the pymssql 2.x code :-)


> I'm afraid I don't follow.  There's a 2.x version not in "production"?
> If I'm using garden-variety Python, which pymssql should I have?

OK, let me clarify. pymssql 1.x is written mostly in C with a Python
wrapper and if you look on PyPI, it's the latest release:

https://pypi.python.org/pypi/pymssql/1.0.2


This version has been around since 2009 and this is the version that my
company (SurveyMonkey) is using in production, because it works fine for
our needs. This version has the buggy code I posted earlier which causes
it to not get any results if you use it with newer versions of FreeTDS,
but it works fine with FreeTDS 0.82 (I don't understand why but it does).

However, if you create a virtualenv and do `pip install pymssql` (not
asking specifically for 1.0.2), you'll actually get a 2.X development
snapshot (because pip currently follows links to our Google Code repo --
my understanding is that new pip versions will stop doing this soonish and
will only download from PyPI, but I digress):

marca@marca-mac:~$ virtualenv /tmp/foobar
Š
marca@marca-mac:~$ source /tmp/foobar/bin/activate
(foobar)marca@marca-mac:~$ pip install pymssql
Š

(foobar)marca@marca-mac:~$ pip freeze | grep pymssql
Warning: cannot find svn location for pymssql==2.0.0b1-dev-20130403
pymssql==2.0.0b1-dev-20130403


This is a tarball of the 2.X version that I uploaded to Google Code in
April. This is the 100% Cython code. I use this personally for development
and it works very well for me. It works with every version of FreeTDS that
I've tried including FreeTDS that I've built straight from Gitorious and
FreeTDS 0.82 (I think it even works with FreeTDS < 0.7 in some older Linux
distros and FreeBSD 8.1). It also appears to be significantly faster than
the 1.X code, probably because it all compiles down to C and then machine
code and doesn't have any interpreted Python code. It has features that
the 1.X code doesn't have, though not features that everyone needs,
especially if going through an ORM or abstraction layer like SQLAlchemy.
It also has a branch for "python3" which works well -- at some point soon,
I'd like to merge that into the mainline and do a release with it. We
don't use this version in production at SurveyMonkey, because we don't
have a compelling reason to risk upgrading. I don't know if other folks
use this in production or not. I'd guess that a lot of folks would shy
away from something that is not officially on PyPI, but I could be wrong.


If folks are doing new development with pymssql, I'd steer them towards
the 2.x version, as it has more features, better performance, a test
suite, Python 3 compatibility coming soon, and it's been getting commits.

> Wouldn't this work better as...

Going to review this later when I have more time for open-source hacking.
My guess is that what you suggested is probably better because you know
db-lib intimately well :-)

Speaking of db-lib, what is the best place to read to get advice about how
to use the API -- i.e.: the kind of info that you wrote about (e.g.:
"You're supposed to call dbnumcols() only after dbresults returns
SUCCEED."). I've glanced at the FreeTDS manual in the past and I've
sometimes looked at some of the docs on individual functions from
Microsoft and Sybase, but I don't know if I've ever seen as much detail
about the overall process as you mentioned in your email. A canonical
example of result processing would be handy (though perhaps I have that
now from your email :-))

Thanks again for the boat loads of info and for looking at the pymssql
code and making suggestions!

Cheers,
Marc




On 9/11/13 8:37 PM, "James K. Lowden" <jklowden AT freetds.org> wrote:

>On Tue, 10 Sep 2013 14:59:23 +0000
>Marc Abramowitz <marca AT surveymonkey.com> wrote:
>
>> More info then you probably care about pymssql
>
>My bread and water these days comes from Continuum Analytics, so I have
>great interest in things db pythonic.
>
>> And then folks stopped maintaining the 1.x code (written in C) and
>> started a whole new 2.X version, that is written with Cython. That
>> code doesn't seem to have this problem either, and that code is more
>> actively maintained now (mostly by me), but it's
>> never been officially released, so it isn't as proven in production.
>
>I'm afraid I don't follow.  There's a 2.x version not in "production"?
>If I'm using garden-variety Python, which pymssql should I have?
>
>> https://github.com/pymssql/pymssql/blob/master/_mssql.pyx#L1036
>
>            # Since python doesn't have a do/while loop do it this way
>            while True:
>                with nogil:
>                    self.last_dbresults = dbresults(self.dbproc)
>                self.num_columns = dbnumcols(self.dbproc)
>                if self.last_dbresults != SUCCEED or self.num_columns
>>0: 
>                       break
>            check_cancel_and_raise(self.last_dbresults, self)
>
>You're supposed to call dbnumcols() only after dbresults returns
>SUCCEED.  
>
>Wouldn't this work better as
>
>        self.clear_metadata()
>       while (self.last_dbresults = dbresults(self.dbproc)) == SUCCEED:
>            self._rows_affected = dbcount(self.dbproc)
>            if (self.num_columns = dbnumcols(self.dbproc)) > 0:
>                   column_names = list()
>                   column_types = list()
>                   for col in xrange(1, self.num_columns + 1):
>                       column_names.append(dbcolname(self.dbproc, col))
>                       coltype = dbcoltype(self.dbproc, col)
>                       column_types.append(get_api_coltype(coltype))
>                   self.column_names = tuple(column_names)
>                   self.column_types = tuple(column_types)
>                   return True 
>           if (mumble = dbnumalts(self.dbproc)) > 0
>               # do the needful and
>               return True
>
>       if self.last_dbresults == NO_MORE_RESULTS or \
>          self.last_dbresults == NO_MORE_RPC_RESULTS:
>               self.clear_metadata()
>               if dbhasretstat(self.dbproc):
>                       self.retstatus = dbretstatus(self.dbproc)
>               # handle output parameters
>               # (not currently supported)
>               return False
>
>       check_cancel_and_raise(self.last_dbresults, self)
>       return False
>       
>That way, each db-lib function is called just once, and every return
>value of dbresults is handled.
>
>Studying this turned up a little bug in FreeTDS.  Consider
>
>       create table #t( t int )
>       insert into #t values (1)
>       insert into #t values (2)
>
>TDS has a variety of DONE packets, and DONE handling is one of the more
>difficult parts to get right.  The problem is partly that the protocol
>has changed over the years, partly in anticipating when the server
>has stopped sending data, and partly (as I alluded to in my last
>message) because libtds supports different APIs with different notions
>of "results".   
>
>Be that as it may, DONE packets carry the "rows affected" information
>that dbcount returns.  It's one of the murkier corners of db-lib,
>because there's no correspondence between the application's
>opportunities to call dbcount and the number of DONE packets that the
>server sends.  
>
>When the above SQL is submitted as a single batch, the server returns 3
>DONE packets, one for each statement.  dbresults returns SUCCEED after
>the *first* done packet, the one resulting from the CREATE TABLE
>statement, for which no count is valid.  (A bit in the DONE packet
>signifies whether the count value can be trusted, cf. dbiscount.)
>
>So the poor user calls dbresults, gets SUCCEED, calls dbiscount, gets
>FALSE.  WTF?  
>
>When dbresults is called a second time, the library returns to the
>TDS stream and parses out the next two DONE packets, sets the count to
>1 (twice, once per packet), and then returns NO_MORE_RESULTS.  At that
>point dbiscount might return TRUE, but the user isn't supposed to call
>dbcount after unless dbresults returned SUCCEED.  :-(
>
>This is what happens when the folks writing the library are learning as
>they go.  
>
>The corrective is both simple and difficult.  Simply, every TDS packet
>carries a "more results" bit, telling the client whether or not the
>server is finished sending.  For DONE tokens, db-lib should continue
>reading the stream until either that bit is 0 -- indicating SUCCESS,
>but no rows returned -- or another kind of packet arrives, say a
>row-format packet.
>
>The difficult part is that, as I said, this is one of the gnarlier
>parts of the library.  For ODBC, each DONE packet constitutes a
>result.  For db-lib, only the last one does.  So the client library
>passes into libtds a "read until" parameter, meaning that the libraries
>have shared state.  It's enough to make you want to take up knitting
>instead.  
>
>The complexity is entirely unnecessary; it's an accidental outgrowth
>the organic way in which libtds evolved.  And I have a very nice design
>for libtds2.  What it needs is someone with time.
>
>--jkl
>_______________________________________________
>FreeTDS mailing list
>FreeTDS AT lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds