MSSQL result sets failing due to early commit #119

Open
patrickmdnet opened this Issue Nov 26, 2011 · 3 comments

Projects

None yet

3 participants

OS is Ubuntu Linux LTS 10.04.
Arch is x86_64
FreeTDS is version 0.82-7 (from the Ubuntu Lucid repo)
pymssql-2.0.0b1_dev_20111019-py2.6-linux-x86_64
MS SQL Server is version 8.0 SP4 (SQL Server 2000).

Freetds.conf looks like this:
[global]
tds version = 8.0
text size = 64512

This code fails to return any results:

import web

mssql_conn = web.database(dbn='mssql', ....)
out = mssql_conn.select("select uid from dbo.sysusers")
print(out.list())

but this code works:

trans = mssql_conn.transaction()
out = mssql_conn.select("select uid from dbo.sysusers")
print(out.list())
trans.commit()

The issue is that the DB.query() method (in db.py) performs a commit() at the end of the method. This destroys the result cursor. The workaround is to create a transaction object, dump the result cursor into a list, and then do the commit().

It's not clear to me what the best way is to work around this in db.py.

I tried this with FreeTDS 0.91 and got the same results.

marsmxm commented Jun 21, 2012

I encontered this problem on Windows XP, too.
After commenting out these codes(line 660 in db.py)

if not self.ctx.transactions: 
            self.ctx.commit()

It just worked well.
Is it should be:

if self.ctx.transactions:
            self.ctx.commit()

Sorry if I misunderstood the concept.

DiJu519 commented Jan 27, 2015

Just wanted to say thanks.

I didn't end up using the trans.commit however.

I made the code change marsmxm suggested, and then did the following:

builds = db.select("BuildDates").list()
releases = db.select("ReleaseDates").list()
return render.index(builds,releases)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment