1

I am having a difficult time trying to connect to a SQL Server DB on Linux, using pyodbc. I have a ODCINI file entry created. I started with this:

import pyodbc

conn = pyodbc.connect('DSN=DSN;Database=DB;UID=UID;PWD=PWD')

cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM dbo.tableA')
for row in cursor.fetchall():
    print(row)

which throws this error:

RuntimeError: Unable to set SQL_ATTR_CONNECTION_POOLING attribute.

I googled that error and added this line after reading some recommendations:

pyodbc.pooling=False

So script changed to this:

import pyodbc

pyodbc.pooling=False

conn = pyodbc.connect('DSN=DSN;Database=DB;UID=UID;PWD=PWD')

cursor = conn.cursor()
cursor.execute('SELECT count(*) FROM dbo.tableA')
for row in cursor.fetchall():
    print(row)

Which resulted in this:

pyodbc.InterfaceError: ('IM003', '[IM003] 䑛瑡䑡物捥嵴佛䉄⁃楬嵢匠数楣楦摥搠楲敶\u2072潣汵\u2064潮⁴敢氠慯敤d\uffff\uffff㢸ꔻ罱\x00\ue5b8鮫罱\x00㳰ꔻ罱\x00\uffff\uffff罱\x00\x00\x00\x00\x00鳭ꕞ罱\x00塰ꕉ罱 (0) (SQLDriverConnect)')

At the suggestion of a coworker I added these 2 lines AFTER the pyodbc.connect line:

conn.setdecoding(pyodbc.SQL_CHAR, encoding='latin1', to=str)
conn.setencoding(str, encoding='latin1')

I tried that with both latin1 and utf-8. Neither work, still throws the same interface error with Chinese characters.

Any ideas?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
JD2775
  • 3,658
  • 7
  • 30
  • 52
  • 1
    The first part of the error message converted from "Chinese" UTF-16LE to Windows-1252 comes out as "[DataDirect][ODBC lib] Specified driver could not be loaded". First, verify that your Linux environment is in the "Operating System Support" list [here](https://www.progress.com/odbc/microsoft-sql-server#technical-specifications). If so, then try following the steps in the tutorial [here](https://www.progress.com/tutorials/odbc/sql-server-odbc-driver-for-linux-quick-start-guide). – Gord Thompson Apr 18 '19 at 20:59
  • Thanks @GordThompson. I think you hit the nail on the head, it seems to be a driver issue. Specifically a data direct driver issue. – JD2775 Apr 18 '19 at 21:50

2 Answers2

1

I had the similar issue with same description RuntimeError: Unable to set SQL_ATTR_CONNECTION_POOLING attribute. I had no clue what is happening and why is happening. After lot of debugging i was able to figure it out why.

Simple answer is :
Reinstall the unixODBC drivers or/and SQL drivers.

Reason why :
When install the ODBC Drivers first and then SQL related drivers, sometimes it override the symlinks in Unix system. You can find out more info on this from pyodbc official GitHub issue#847 .

BobMorane
  • 3,870
  • 3
  • 20
  • 42
Ram
  • 11
  • 1
0

you can simply uninstall and then do:

conda install unixodbc
hp_elite
  • 158
  • 1
  • 6