FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

On Mon, 9 Sep 2013 20:57:09 +0000
Marc Abramowitz <marca AT surveymonkey.com> wrote:

> 
> https://gist.github.com/msabramo/6501240
> 
> So this would indicate that the problem is with pymssql and not
> FreeTDS.
> 
> Out of curiosity, does anyone know why the old code worked with older
> versions of FreeTDS? As far as I can tell, dbnumcols should normally
> return a positive integer with the number of columns in the result.
> The old code is looking for dbnumcols to return a value <= 0.

http://code.google.com/p/pymssql/source/browse/mssqldbmodule.c?name=1.1.0

It looks like the current code reflects your patch?  

dbnumcols returns the number of columns in the resultset.  That would
be zero if there are no columns.  It never returns an error or a
negative number.  It's been a long time since there's been any need to
touch it.  

The pymssql code looks a little confused by my lights, perhaps because
the ODBC (and probably Python DBI) definition of "results" doesn't
match that of db-lib.  

The comment in the code says ""DECLARE @a INT; SELECT 1" returns two
result sets: one with zero columns, followed by one with one column. "
Strictly speaking that's not true: the server returns only one:

$ TDSDUMP=stdout bsqldb -q -S$S <<< 'declare @a int select 1 as a' |
sed -Ene '/packet/,/^$/p' ...
net.c:740:Sending packet
0000 01 01 00 42 00 00 01 00-64 00 65 00 63 00 6c 00 |...B.... d.e.c.l.|
0010 61 00 72 00 65 00 20 00-40 00 61 00 20 00 69 00 |a.r.e. . @.a. .i.|
0020 6e 00 74 00 20 00 73 00-65 00 6c 00 65 00 63 00 |n.t. .s. e.l.e.c.|
0030 74 00 20 00 31 00 20 00-61 00 73 00 20 00 61 00 |t. .1. . a.s. .a.|
0040 0a 00                  -                        |..|

net.c:621:Received packet
0000 04 01 00 21 00 35 01 00-81 01 00 00 00 20 00 38 |...!.5.. ..... .8|
0010 01 61 00 d1 01 00 00 00-fd 10 00 c1 00 01 00 00 |.a...... ........|
0020 00                     -                        |.|

That's one D1(ROW) and one FD(DONE) packet.  

In db-lib, things like output variables and result status from stored
procedures are are fetched with special API functions, so you do things
like 

        while dbresults
                dbnumcols, dbbind
                while dbnextrow
                        for each column ....
                                or 
                        for each dbnumalts /* compute rows */
                dbcount
        for each dbnumrets /* output variables */
        dbretstat

That's a little messy and because not every DBMS has a notion of
compute rows, output variables, and return status, ODBC folds them into
"results".  IOW, with ODBC you land at the top of the results loop more
often for each query executed.  

At a glance, it looks like the pymssql code is emulating that sort of
thing.  The last_results variable gets flung around a lot, and
dbresults is called from more than one place.  That's an invitation to
error: easy to get wrong and hard to get right.  

Not a FreeTDS version issue, though.  Should work equally well with the
current release as with 0.82.  

--jkl