No data returned from MSSQL server

[Alan Bell]

[Impact]

 * Due to an issue with PyMSSQL 1.0.x and the version of FreeTDX in the repositories, PyMSSQL no longer returns any information from MSSQL database queries. This means you cannot use fetchone() or fetchall() or similar methods to get data back from a database query.

[Test Case]

 * Install python-pymssql to Ubuntu.

 * Inside a 'python' console on the Ubuntu server, use the following python code, replacing values accordingly for your MSSQL servers. The "print" function will return 'None' in the current code base. When fixed, you will get either one value, or a tuple, depending on the results (but you will NOT get 'None'):

import pymssql
dbconn = pymssql.connect(host='FQDN_FOR_DBSERVER', user='DBUSER', password='DBPASSWORD', database='DBNAME')
dbcur = dbconn.cursor()
dbcur.execute('ANY_VALID_SQL_QUERY')
dbrs = dbcur.fetchone()
dbcur.close()
print dbrs

[Regression Potential]

 * The patch in the debdiffs is from pymssql upstream. We are on a very old pymssql, but this patch was designed to fix the issue.

[Other Info]

 * Test builds in https://launchpad.net/~teward/+archive/ubuntu/pymssql/+packages; these packages in that PPA are used for testing and were used to test the upstream patch.

[Original Description]

this works great in oneiric, but in precise it silently returns empty results from SQL server. The upstream project has relocated and provided a new release, I downloaded and built it and the same code I had started returning results.
http://code.google.com/p/pymssql/downloads/list

ProblemType: Bug
DistroRelease: Ubuntu 12.04
Package: python-pymssql 1.0.2+dfsg-1build1
ProcVersionSignature: Ubuntu 3.2.0-9.16-generic 3.2.1
Uname: Linux 3.2.0-9-generic x86_64
ApportVersion: 1.91-0ubuntu1
Architecture: amd64
Date: Thu Jan 19 20:24:31 2012
EcryptfsInUse: Yes
InstallationMedia: Ubuntu 10.10 "Maverick Meerkat" - Release amd64 (20101007)
SourcePackage: pymssql
UpgradeStatus: Upgraded to precise on 2012-01-16 (2 days ago)

[Alan Bell (alanbell)]

trivial python to show the problem

#!/usr/bin/env python
import _mssql
conn=_mssql.connect(server='server',user='test',password='password',database='test')
conn.execute_query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS")
for row in conn:
    print row

if it is working it should return a bunch of data about columns, in precise it just returns nothing.

[Fred (rischfre)]

Have got the same issue, code working properly on 11.04 but no way to get it work on 12.04

#!/usr/bin/env python

import _mssql

conn = _mssql.connect(server='192.168.1.xxx:1433', user='linux', password='linux', database='MYDB')
result = conn.execute_scalar("SELECT MediaLabel FROM T_MediaStatus")
print result

conn.close()

[jtscsousa (jtscsousa)]

I had to install python-pymssql_1.9.910-1~oneiric1_amd64.deb from

https://launchpad.net/~ukplc-team/+archive/ppa/+build/2856607

to solve this problem in 12.04.

[torh (tor-h)]

Since this still isn't working. This is also possible to make it work in 12.04:
apt-get uninstall python-pymssql; pip install pymssql

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Ken Robbins (kenneth-robbins)]

I had this same problem when I upgraded to 12.04. I discovered the problem to be an apparent incompatibility between FreeTDS 0.91 and pymssql 1.0.2. When I downgraded to FreeTDS 0.84RC4 (installed from source), pymssql 1.0.2 works fine. When I upgraded to pymssql 1.9.908 or 2.0.0, pymssql works fine. I decided to downgrade FreeTDS because of this comment: http://groups.google.com/group/pymssql/browse_thread/thread/2de6418ae7c2da56

[Ken Robbins (kenneth-robbins)]

Sorry, that should be FreeTDS 0.82RC4.

[MrTux (mrtux)]

I had upgrade pymssql to 1.9.912 and using FreeTDS 0.91
pymssql works fine but if I using _mssql it isn't working.

[Michael Bewley (michael-bewley)]

Uninstalling the default package, and installing via pip fixed the problem for me (Ubuntu 12.04-1)

[Richard Ulrich (richi-paraeasy)]

The problem is still the same with Ubuntu 12.10

[Richard Ulrich (richi-paraeasy)]

Neither installing from pip nor from this ppa https://launchpad.net/~ukplc-team/+archive/ppa/+packages
can get it to work on quantal.
Did anybody succeed on Ubuntu 12.10?

[Remigijus Jarmalavičius (reekenx)]

I confirm what Michael Bewley (michael-bewley) wrote on 2012-10-19. This helped for me:

$ apt-get purge python-pymssql
$ pip install Cython
$ pip install pymssql

Version info:

$ dpkg -l freetds-common
ii freetds-common 0.82-7

Debian 6.0 with.

[Andrea Cometa (scigghia)]

I confirm Michael Bewley and Remigijus Jarmalavičius

$ apt-get purge python-pymssql
$ apt-get install freetds-dev
$ pip install Cython
$ pip install pymssql

this solve the problem on Debian Wheezy.

[alfonso olavarria (alfonsojn15)]

in ubuntu 10.04LTS

>>import pymssql
>>conn=pymssql.connect(host='ip', user='user', password='pass', datababse='database')
>>cur=conn.cursor()
>>row=cur.execute('query.....')
>>print row
None
>>|

..........returns no data from SQL server................

[alfonso olavarria (alfonsojn15)]

apology. is in ubuntu 12.04

[Steger Jozsef (steger)]

distribution package python-pymssql 1.0.2+dfsg-1 for amd64 architecture in raring (Ubuntu 13.04) still does the very same.

I have previously compiled the sources downloaded from http://code.google.com/p/pymssql/downloads/detail?name=pymssql-2.0.0b1-dev-20111019.tar.gz&can=2&q= and set PYTHONPATH environment variable to point also to the folders containing the so binaries. By doing so it works for me.

(I just realized there is a newer tarball http://code.google.com/p/pymssql/downloads/detail?name=pymssql-2.0.0b1-dev-20130403.tar.gz&can=2&q= same version number, but have not tested that one yet.)

note also another problem with the pymssql shipped with distro, though you get an empty result, the nextresult() method of the cursor will always return True, so you can easily jump in an endless loop

[alfonso olavarria (alfonsojn15)]

Solved!!

Prepare and execute a database operation (query or command). Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Parameter style for pymssql
 is %-formatting, as in: cur.execute('select * from table where id=%d', id) cur.execute('select * from table where strname=%s', name) Please consult online documentation for more examples and guidelines.

link: http://www.sourcecodebrowser.com/pymssql/1.0.2plus-pdfsg/pymssql_8py_source.html

Python 2.7.3 (default, Aug 1 2012, 05:16:07)
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymssql
>>> conn=pymssql.connect(host='IP', user='user', password='pass', database='bd')
>>> cur=conn.cursor()
>>> cur.execute('select * from tbl_tbl where clave=%d',123456)
>>> cur.fetchall()

here show all the results!

SO: Ubuntu 12.04LTS
python:2.7.3
pymssql: 1.0.2
freetds:0.91

[flashydave (dave-opensourcesolutions)]

Thanks Alfonso - you saved me a lot of work.
Pity they couldn't default the last parameter to being '' or None to retain the old behaviour and thus not break existing code.

Anyway I can confirm your results on 12.04 LTS
Here are my precise component versions.

Server: Ubuntu12.04.2 LTS x86_64
python: 2.7.3-0ubuntu2.2
python-pymssql: 1.0.2+dfsg-1build1
freetds-common: 0.91-1

[Praveen (prajwalpri-shetty)]

I am a beginner to Raspberry Pi wheezy. I want ms sql server connection my application but due to this bug i cant read data from data base any other way to resolve this bug ...(pip command not working )

[Thomas Ward (teward)]

This is due to an incompatibility between freetdx and PyMSSQL 1.0.x.

Workaround is to remove the python-pymssql package, and run `pip install pymssql` or `easy_install --upgrade pymssql` or similar, to get the most up to date pymssql version, greater than or equal to 2.0.1.

ALso confirmed this issue on Xenial and Yakkety, as well as Trusty.

[Thomas Ward (teward)]

It's very likely every version in Ubuntu is impacted by this.

[Thomas Ward (teward)]

Debdiff uploads to follow after my evening commute home... these address the issue.

[Thomas Ward (teward)]

Yakkety DebDiff

[Thomas Ward (teward)]

Guess I'll put the other debdiffs here too.

Xenial debdiff.

[Thomas Ward (teward)]

Wily debdiff.

[Thomas Ward (teward)]

Trusty DebDiff

[Thomas Ward (teward)]

Precise Debdiff

[Thomas Ward (teward)]

Sponsors Team: First, check/sponsor Yakkety. Once that's been verified and such, we can continue on to the other releases.

[Robie Basak (racb)]

Thomas,

Thank you for the debdiffs! Unfortunately pymssql is a version 1.0 source package, so I'm not sure quilt makes sense. I suppose it will work as you have supplied it, since your debdiffs would cause the package to ship with quilt applied. I've not seen it done this way before though, so I'm not comfortable sponsoring it without someone senior telling me it's OK. I would have expected the patch to just be applied without quilt (perhaps with more information in debian/changelog as dep3 wouldn't be available).

[Thomas Ward (teward)]

I've removed the sponsors team for now until I can redo the debdiffs.

[Geoffrey Thomas (geofft)]

Hi Thomas,

A few months ago I pushed a branch updating the python-pymssql packaging to Debian's git repository, but I'm still waiting to hear back from the maintainer. I'll attempt to get sponsorship for a team upload to Debian soon - if so, it's possibly better to just sync the package from Debian.

See https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=648230#15 and https://anonscm.debian.org/cgit/python-modules/packages/pymssql.git/log/?h=2.1.1 . I can update this to 2.2.0. I think your debdiffs were removed from the bug - please do check my work and see if I got all the changes you were going to make.

Among other things I updated the source format to 3.0 (quilt).

[Thomas Ward (teward)]

@Geoffrey:

That sounds great for updating the development release, but a straight sync may not work for the stable release update to the rest of the world.

The debdiffs I had done were to 'nitpick' the upstream fixes and apply them to the older package versions, not to redo the packaging. Those nitpicks were rejected by Sponsors, so I never got to redo them.

We need compatible debdiffs for the older releases, using the upstream changes to fix only that issue, unless a version bump is determined to be preferable.

[Thomas Ward (teward)]

Setting Zesty to Confirmed/High, because I did some testing here on a Zesty container with the MSSQL instance I have here and the same "No data returned" issue cropped up.

There is an updated version in Artful's Proposed repository that might fix this for Artful, though, since it's updated to a release that will resolve most of the issues observed thus far.

[Peter Schwenke (bluetoad)]

I can't believe this is still going on. I no longer work in software and have no use for this but as pointed out on other related pymssql bugs I had this working many years ago and had a working version in my bluetoad PPA. I'll just shake my head and unsubscribe from this!

[Steve Langasek (vorlon)]

The Precise Pangolin has reached end of life, so this bug will not be fixed for that release