I am developing a data syncing script for a project using python 3.3.2 and pyodbc. The data that I need is coming from a sql server 2008 instance, but so far I have not been able to make any queries successfully.
I am able to establish a connection to the database server with pyodbc:
cnxn = pyodbc.connect('DSN=<DSN>; UID=<uid>; PWD=<pwd>')
cursor = cnxn.cursor()
The freetds and iodbc logs show a successful connection to the database server (logs included blow). However, when I try to execute a query:
cursor.execute('select * from <TABLE>')
I get this error:
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near '�'. (102) (SQLExecDirectW)")
The iodbc and FreeTDS logs show that '??????????????????????????????' is being passed as the query. This looks to me like a unicode/encoding problem. The same pyodbc code works in a python 2.7 using the same versions of iODBC and FreeTDS, so I think this problem has something to do with the interaction between python 3.3.2 and pyodbc.
I found this bug report, but have yet to find any sort of fix or workaround. Any help would be greatly appreciated. I've been trying to figure this one out for the past two days without much luck.
If there is any information that I have not included that would be helpful, let me know, I'll provide what I can. I do not have access to the SQL Server logs.
Environment:
Mac OSX 10.8.5
Python 3.3.2
pyodbc 3.0.7
iODBC 3.52.6 (I can't figure out how to convince pyodbc to use unixodbc on Mac)
FreeTDS 0.91
iODBC log:
[000000.000070]
python 7FFF7876C180 ENTER SQLAllocHandle
SQLSMALLINT 1 (SQL_HANDLE_ENV)
SQLHANDLE 0x0 (SQL_NULL_HANDLE)
SQLHANDLE * 0x1007b5970
[000000.000091]
python 7FFF7876C180 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 (SQL_HANDLE_ENV)
SQLHANDLE 0x0 (SQL_NULL_HANDLE)
SQLHANDLE * 0x1007b5970 (0x10049e350)
[000000.000110]
python 7FFF7876C180 ENTER SQLSetEnvAttr
SQLHENV 0x10049e350
SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION)
SQLPOINTER 0x3
SQLINTEGER * 4
[000000.000508]
python 7FFF7876C180 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x10049e350
SQLINTEGER 200 (SQL_ATTR_ODBC_VERSION)
SQLPOINTER 0x3
SQLINTEGER * 4
[000000.000527]
python 7FFF7876C180 ENTER SQLAllocHandle
SQLSMALLINT 2 (SQL_HANDLE_DBC)
SQLHANDLE 0x10049e350
SQLHANDLE * 0x7fff5fbff080
[000000.000545]
python 7FFF7876C180 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 (SQL_HANDLE_DBC)
SQLHANDLE 0x10049e350
SQLHANDLE * 0x7fff5fbff080 (0x1004c3bf0)
[000000.000569]
python 7FFF7876C180 ENTER SQLDriverConnectW
SQLHDBC 0x1004c3bf0
SQLPOINTER 0x0
SQLWCHAR * 0x1004c3cf0
| ??????????????????????????????????? |
SQLSMALLINT 39
SQLWCHAR * 0x0
SQLSMALLINT 0
SQLSMALLINT * 0x0
SQLUSMALLINT 0 (SQL_DRIVER_NOPROMPT)
[000000.000709]
python 7FFF7876C180 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)
SQLHDBC 0x1004c3bf0
SQLPOINTER 0x0
SQLWCHAR * 0x1004c3cf0
SQLSMALLINT 39
SQLWCHAR * 0x0
SQLSMALLINT 0
SQLSMALLINT * 0x0
SQLUSMALLINT 0 (SQL_DRIVER_NOPROMPT)
[000000.000740]
python 7FFF7876C180 ENTER SQLDriverConnect
SQLHDBC 0x1004c3bf0
SQLPOINTER 0x0
SQLCHAR * 0x7fff5fbff0a0
| DSN=<dsn>;UID=<uid>t;PWD=<pwd> |
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x0
SQLSMALLINT 0
SQLSMALLINT * 0x0
SQLUSMALLINT 0 (SQL_DRIVER_NOPROMPT)
[000000.071429]
python 7FFF7876C180 EXIT SQLDriverConnect with return code 0 (SQL_SUCCESS)
SQLHDBC 0x1004c3bf0
SQLPOINTER 0x0
SQLCHAR * 0x7fff5fbff0a0
SQLSMALLINT -3 (SQL_NTS)
SQLCHAR * 0x0
SQLSMALLINT 0
SQLSMALLINT * 0x0
SQLUSMALLINT 0 (SQL_DRIVER_NOPROMPT)
[000000.071482]
python 7FFF7876C180 ENTER SQLSetConnectAttr
SQLHDBC 0x1004c3bf0
SQLINTEGER 102 (SQL_ATTR_AUTOCOMMIT)
SQLPOINTER 0x0
SQLINTEGER * 4294967291 (SQL_IS_UINTEGER)
[000000.089380]
python 7FFF7876C180 EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS)
SQLHDBC 0x1004c3bf0
SQLINTEGER 102 (SQL_ATTR_AUTOCOMMIT)
SQLPOINTER 0x0
SQLINTEGER * 4294967291 (SQL_IS_UINTEGER)
[000000.089468]
python 7FFF7876C180 ENTER SQLGetInfo
SQLHDBC 0x1004c3bf0
SQLUSMALLINT 77 (SQL_DRIVER_ODBC_VER)
SQLPOINTER 0x7fff5fbff000
SQLSMALLINT 20
SQLSMALLINT * 0x7fff5fbfeffe
[000000.089502]
python 7FFF7876C180 EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)
SQLHDBC 0x1004c3bf0
SQLUSMALLINT 77 (SQL_DRIVER_ODBC_VER)
SQLPOINTER 0x7fff5fbff000
| 03.50 |
SQLSMALLINT 20
SQLSMALLINT * 0x7fff5fbfeffe (5)
[000000.089529]
python 7FFF7876C180 ENTER SQLGetInfo
SQLHDBC 0x1004c3bf0
SQLUSMALLINT 10002 (SQL_DESCRIBE_PARAMETER)
SQLPOINTER 0x7fff5fbfeffc
SQLSMALLINT 2
SQLSMALLINT * 0x7fff5fbfeffe
[000000.089550]
python 7FFF7876C180 EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)
SQLHDBC 0x1004c3bf0
SQLUSMALLINT 10002 (SQL_DESCRIBE_PARAMETER)
SQLPOINTER 0x7fff5fbfeffc
| N |
SQLSMALLINT 2
SQLSMALLINT * 0x7fff5fbfeffe (1)
[000000.089593]
python 7FFF7876C180 ENTER SQLGetInfo
SQLHDBC 0x1004c3bf0
SQLUSMALLINT 111 (SQL_NEED_LONG_DATA_LEN)
SQLPOINTER 0x7fff5fbfeffc
SQLSMALLINT 2
SQLSMALLINT * 0x7fff5fbfeffe
[000000.089615]
python 7FFF7876C180 EXIT SQLGetInfo with return code 0 (SQL_SUCCESS)
SQLHDBC 0x1004c3bf0
SQLUSMALLINT 111 (SQL_NEED_LONG_DATA_LEN)
SQLPOINTER 0x7fff5fbfeffc
| Y |
SQLSMALLINT 2
SQLSMALLINT * 0x7fff5fbfeffe (1)
[000000.089638]
python 7FFF7876C180 ENTER SQLAllocHandle
SQLSMALLINT 3 (SQL_HANDLE_STMT)
SQLHANDLE 0x1004c3bf0
SQLHANDLE * 0x7fff5fbfeff0
[000000.089669]
python 7FFF7876C180 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 (SQL_HANDLE_STMT)
SQLHANDLE 0x1004c3bf0
SQLHANDLE * 0x7fff5fbfeff0 (0x1004cabb0)
[000000.089686]
python 7FFF7876C180 ENTER SQLGetTypeInfo
SQLHSTMT 0x1004cabb0
SQLSMALLINT 93 (SQL_TYPE_TIMESTAMP)
[000000.111645]
python 7FFF7876C180 EXIT SQLGetTypeInfo with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x1004cabb0
SQLSMALLINT 93 (SQL_TYPE_TIMESTAMP)
[000000.111684]
python 7FFF7876C180 ENTER SQLFetch
SQLHSTMT 0x1004cabb0
[000000.111711]
python 7FFF7876C180 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x1004cabb0
[000000.111724]
python 7FFF7876C180 ENTER SQLGetData
SQLHSTMT 0x1004cabb0
SQLUSMALLINT 3
SQLSMALLINT 4 (SQL_C_LONG)
SQLPOINTER 0x7fff5fbfefec
SQLLEN 4
SQLLEN * 0x0
[000000.111759]
python 7FFF7876C180 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x1004cabb0
SQLUSMALLINT 3
SQLSMALLINT 4 (SQL_C_LONG)
SQLPOINTER 0x7fff5fbfefec
| 21580870632603671 |
SQLLEN 4
SQLLEN * 0x0
[000000.111788]
python 7FFF7876C180 ENTER SQLGetTypeInfo
SQLHSTMT 0x1004cabb0
SQLSMALLINT 12 (SQL_VARCHAR)
[000000.111801]
python 7FFF7876C180 EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR)
SQLHSTMT 0x1004cabb0
SQLSMALLINT 12 (SQL_VARCHAR)
[000000.111815]
python 7FFF7876C180 ENTER SQLGetTypeInfo
SQLHSTMT 0x1004cabb0
SQLSMALLINT -9 (SQL_WVARCHAR)
[000000.111828]
python 7FFF7876C180 EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR)
SQLHSTMT 0x1004cabb0
SQLSMALLINT -9 (SQL_WVARCHAR)
[000000.111842]
python 7FFF7876C180 ENTER SQLGetTypeInfo
SQLHSTMT 0x1004cabb0
SQLSMALLINT -2 (SQL_BINARY)
[000000.111854]
python 7FFF7876C180 EXIT SQLGetTypeInfo with return code -1 (SQL_ERROR)
SQLHSTMT 0x1004cabb0
SQLSMALLINT -2 (SQL_BINARY)
[000000.111869]
python 7FFF7876C180 ENTER SQLFreeStmt
SQLHSTMT 0x1004cabb0
SQLUSMALLINT 0 (SQL_CLOSE)
[000000.132529]
python 7FFF7876C180 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x1004cabb0
SQLUSMALLINT 0 (SQL_CLOSE)
[000000.132590]
python 7FFF7876C180 ENTER SQLAllocHandle
SQLSMALLINT 3 (SQL_HANDLE_STMT)
SQLHANDLE 0x1004c3bf0
SQLHANDLE * 0x101373ee8
[000000.132629]
python 7FFF7876C180 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 (SQL_HANDLE_STMT)
SQLHANDLE 0x1004c3bf0
SQLHANDLE * 0x101373ee8 (0x10045c0b0)
[000123.339614]
python 7FFF7876C180 ENTER SQLFreeStmt
SQLHSTMT 0x10045c0b0
SQLUSMALLINT 0 (SQL_CLOSE)
[000123.339667]
python 7FFF7876C180 EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x10045c0b0
SQLUSMALLINT 0 (SQL_CLOSE)
[000123.339718]
python 7FFF7876C180 ENTER SQLExecDirectW
SQLHSTMT 0x10045c0b0
SQLWCHAR * 0x10047a540
| ?????????????????????????????? |
SQLINTEGER -3 (SQL_NTS)
[000123.360756]
python 7FFF7876C180 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
SQLHSTMT 0x10045c0b0
SQLWCHAR * 0x10047a540
SQLINTEGER -3 (SQL_NTS)
[000123.361764]
python 7FFF7876C180 ENTER SQLGetDiagRec
SQLSMALLINT 3 (SQL_HANDLE_STMT)
SQLHSTMT 0x10045c0b0
SQLSMALLINT 1
SQLCHAR * 0x7fff5fbfeebc
SQLINTEGER * 0x7fff5fbfeec4
SQLCHAR * 0x7fff5fbfeed0
SQLSMALLINT 1023
SQLSMALLINT * 0x7fff5fbfeec2
[000123.361849]
python 7FFF7876C180 EXIT SQLGetDiagRec with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 (SQL_HANDLE_STMT)
SQLHSTMT 0x10045c0b0
SQLSMALLINT 1
SQLCHAR * 0x7fff5fbfeebc
| 42000 |
SQLINTEGER * 0x7fff5fbfeec4 (102)
SQLCHAR * 0x7fff5fbfeed0
| [FreeTDS][SQL Server]Incorrect syntax ne |
| ar '�'. |
SQLSMALLINT 1023
SQLSMALLINT * 0x7fff5fbfeec2 (49)
FreeTDS log:
token.c:2355:tds_process_end() state set to TDS_IDLE
util.c:156:Changed query state from READING to IDLE
token.c:2370: rows_affected = 0
odbc.c:1454:SQLAllocHandle(3, 0x1004caad0, 0x10045c1c8)
odbc.c:1615:_SQLAllocStmt(0x1004caad0, 0x10045c1c8)
odbc.c:4388:SQLGetStmtAttr(0x1004cb550, 10010, 0x1004ab428, 0, 0x0)
odbc.c:4388:SQLGetStmtAttr(0x1004cb550, 10011, 0x1004ca118, 0, 0x0)
odbc.c:4388:SQLGetStmtAttr(0x1004cb550, 10012, 0x1004ca158, 0, 0x0)
odbc.c:4388:SQLGetStmtAttr(0x1004cb550, 10013, 0x1004cb6b8, 0, 0x0)
odbc.c:4154:SQLFreeStmt(0x1004cb550, 0)
odbc.c:4075:_SQLFreeStmt(0x1004cb550, 0, 0)
odbc.c:3463:SQLExecDirect(0x1004cb550, 0x100434950, -3)
prepare_query.c:203:parsing 0 parameters
odbc.c:3211:_SQLExecute(0x1004cb550)
odbc.c:3216:_SQLExecute() starting with state 0
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
write.c:140:tds_put_string converting 30 bytes of "??????????????????????????????"
write.c:168:tds_put_string wrote 12 bytes
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 01 01 00 14 00 00 01 00-fd ff fd ff fd ff fd ff |........ ????????|
0010 fd ff fd ff - |????|
token.c:540:tds_process_tokens(0x1004b7c90, 0x7fff5fbff1b4, 0x7fff5fbff1b0, 0x6914)
util.c:156:Changed query state from PENDING to READING
net.c:555:Received header
0000 04 01 00 6a 00 42 01 00- |...j.B..|
net.c:609:Received packet
0000 04 01 00 6a 00 42 01 00-aa 56 00 66 00 00 00 01 |...j.B.. ?V.f....|
0010 0f 1a 00 49 00 6e 00 63-00 6f 00 72 00 72 00 65 |...I.n.c .o.r.r.e|
0020 00 63 00 74 00 20 00 73-00 79 00 6e 00 74 00 61 |.c.t. .s .y.n.t.a|
0030 00 78 00 20 00 6e 00 65-00 61 00 72 00 20 00 27 |.x. .n.e .a.r. .'|
0040 00 fd ff 27 00 2e 00 0b-43 00 4f 00 4c 00 4f 00 |.??'.... C.O.L.O.|
0050 2d 00 53 00 51 00 4c 00-2d 00 30 00 32 00 00 01 |-.S.Q.L. -.0.2...|
0060 00 fd 02 00 fd 00 00 00-00 00 |.?..?... ..|
token.c:555:processing result tokens. marker is aa(ERROR)
token.c:122:tds_process_default_tokens() marker is aa(ERROR)
token.c:2588:tds_process_msg() reading message 102 from server
token.c:2661:tds_process_msg() calling client msg handler
odbc.c:2270:msgno 102 20003
token.c:2674:tds_process_msg() returning TDS_SUCCEED
token.c:555:processing result tokens. marker is fd(DONE)
token.c:2339:tds_process_end: more_results = 0
was_cancelled = 0
error = 1
done_count_valid = 0
token.c:2355:tds_process_end() state set to TDS_IDLE
util.c:156:Changed query state from READING to IDLE
token.c:2370: rows_affected = 0
util.c:104:logic error: cannot change query state from IDLE to PENDING
odbc.c:3534:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3535: result_type=4052, TDS_DONE_COUNT=0, TDS_DONE_ERROR=2
odbc.c:3575:odbc_process_tokens: row_count=-1
odbc.c:3374:_SQLExecute: odbc_process_tokens returned result_type 4052
error.c:517:SQLGetDiagRec(3, 0x1004cb550, 1, 0x7fff5fbfee8c, 0x7fff5fbfee94, 0x7fff5fbfeea0, 1023, 0x7fff5fbfee92)
error.c:566:SQLGetDiagRec: "[FreeTDS][SQL Server]Incorrect syntax near '�'."
In cases where pyodbc is being difficult (for whatever reason) you might give pypyodbc a try. It is a pure Python implementation that is similar to pyodbc in its usage. However, unfortunately, it no longer appears to be actively maintained (as of July 2019).
pypyodbc (at pypi.org)