Geek Answers Handbook

Python with MS SQL - truncated output

I am trying to connect to MSSQL DB using python from Linux ( Python 2.7 , Ubuntu 11.04 ). The output I get is truncated to 500 characters. Please see the script and configs below. How can this be solved? The problem I suspect in or near the ODBC driver.

Code (pyodbc, pymssql):

conn = pymssql.connect(host='my_remote_host', user='ro_user', password='password', database='current', as_dict=True) cur = conn.cursor() cur.execute(sql) for i in cur: print i conn.close() cnxn = pyodbc.connect(driver='FreeTDS', server='my_remote_host', database='current', uid='ro_user', pwd='password') cursor = cnxn.cursor() cursor.execute(sql) rows = cursor.fetchall() ... cnxn.close()

I do not have write access to MS SQL DB, it is actually a remote server that does not belong to our system.

SQL:

 sql = ''' SELECT Req.ID, ShReq.Summary AS [Short Name], ShReq.ALM_SharedText AS [Text], Req.ContainedBy, Req.DocumentID FROM CurMKS..ALM_Requirement Req JOIN CurMKS..ALM_SharedRequirement ShReq ON Req.[References] = ShReq.ID WHERE DocumentID = 1111111'''

The problem is the ShReq.ALM_SharedText field. It is truncated to 255 characters, but using transformations such as convert(text,ShReq.ALM_SharedText) AS TEXT and CAST(ShReq.ALM_SharedText AS TEXT) , I increase the truncation to 500 characters. However, there are fields with longer text than 500 characters, and they are truncated.

ODBC Settings:

/etc/odbc.ini :

 [MKS] #Driver=FreeTDS Driver=/usr/lib/odbc/libtdsodbc.so Description=Database Trace=No Server=my_remote_host Port=1433 Database=current UID=ro_user PWD=password TDS Version=8.0

/etc/odbcinst.ini :

 [FreeTDS] Description=FreeTDS Driver=/usr/lib/odbc/libtdsodbc.so UsageCount=1

/etc/freetds/freetds.conf :

 [global] tds version = 8.0 ; dump file = /tmp/freetds.log ; debug flags = 0xffff ; timeout = 10 ; connect timeout = 10 ; text size = 2097152 [mksserver] host = my_remote_host port = 1433 tds version = 8.0 client charset = UTF-8

Any thoughts on how this can be resolved?

+4
source share
2 answers

Change the text size section in global freetds.conf to the maximum (4294967295 bytes):

 [global] tds version = 8.0 text size = 4294967295

You also need to set TEXTSIZE to SQL maximum (2147483647 bytes):

 sql = """ SET TEXTSIZE 2147483647; SELECT Req.ID, ShReq.Summary AS [Short Name], ShReq.ALM_SharedText AS [Text], Req.ContainedBy, Req.DocumentID FROM CurMKS..ALM_Requirement Req JOIN CurMKS..ALM_SharedRequirement ShReq ON Req.[References] = ShReq.ID WHERE DocumentID = 111111; """
+4
source

If you are using an older version of pymssql (1.0.2), there are certain limitations.

varchar and nvarchar data are limited to 255 characters, and longer lines are silent. This is a known limitation of the TDS protocol. The workaround is CAST or CONVERT for this string or expression for a text data type that can return 4000 characters.

source: http://pymssql.sourceforge.net/limitations.php

+1
source

More articles:


All Articles