Menu
FreeTDS-unixODBC-MSSQL2012
below conf :
/etc/odbcinst.ini
[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/local/freetds/lib/libtdsodbc.so
Setup = /usr/lib64/libtdsS.so.2
FileUsage = 1
CPTimeout = 5
CRReuse = 5
/etc/odbc.ini
[MSSQLTEST]
driver=FreeTDS
server=10.10.1.16
port=1433
database=ACCOUNT
client_charset = UTF-8
tds_version = 8.0
/etc/freetds.conf
[MSSQLTEST]
host = 10.10.1.16
port = 1433
tds version = 8.0
And I use pyodbc to connect MSSQL2012
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import pyodbc
conn = pyodbc.connect("DSN=MSSQLTEST;UID=sa;PWD=xxxxx;database=PAYON;CHARSET=UTF8")
cursor = conn.cursor()
self.cursor.execute("{call insert_name('안녕')}")
result : name column in MSSQL2012 is empty value NOT WRONG value.
self.cursor.execute("{call insert_name('123')}")
result : name column in MSSQL2012 is 123.
name is nvarchar(50). I think that it is charset/encoding problem.
Linux is cenos6.5 and $LANG is ko_KR.UTF-8.
But I do not know the problem point.
encoding problem? mssql setting?
I write code below:
# -*- coding:utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import pyodbc
conn = pyodbc.connect("DSN=MSSQLTEST;UID=sa;PWD=xxxxx;database=PAYON")
cursor = conn.cursor()
cursor.execute("insert into tpayon_test (name) values(N'안녕')")
cursor.commit()
conn.close()
and unixODBC LOG:
[ODBC][28783][1414141316.359552][__handles.c][450]
Exit:[SQL_SUCCESS]
Environment = 0x1987c70
[ODBC][28783][1414141316.359682][SQLSetEnvAttr.c][182]
Entry:
Environment = 0x1987c70
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 4
[ODBC][28783][1414141316.359740][SQLSetEnvAttr.c][349]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.359792][SQLAllocHandle.c][364]
Entry:
Handle Type = 2
Input Handle = 0x1987c70
[ODBC][28783][1414141316.359865][SQLAllocHandle.c][482]
Exit:[SQL_SUCCESS]
Output Handle = 0x19848f0
[ODBC][28783][1414141316.359930][SQLDriverConnectW.c][286]
Entry:
Connection = 0x19848f0
Window Hdl = (nil)
Str In = [DSN=MSSQLTEST;UID=sa;PWD=xxxxx;database=PAYON][length = 51]
Str Out = (nil)
Str Out Max = 0
Str Out Ptr = (nil)
Completion = 0
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
[ODBC][28783][1414141316.368017][SQLDriverConnectW.c][842]
Exit:[SQL_SUCCESS]
Connection Out [[NULL]]
[ODBC][28783][1414141316.368172][SQLSetConnectAttr.c][321]
Entry:
Connection = 0x19848f0
Attribute = SQL_ATTR_AUTOCOMMIT
Value = (nil)
StrLen = -5
[ODBC][28783][1414141316.368872][SQLSetConnectAttr.c][675]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.368977][SQLGetInfo.c][546]
Entry:
Connection = 0x19848f0
Info Type = SQL_DRIVER_ODBC_VER (77)
Info Value = 0x7fffe24b61c0
Buffer Length = 20
StrLen = 0x7fffe24b61be
[ODBC][28783][1414141316.369032][SQLGetInfo.c][608]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.369121][SQLGetInfo.c][546]
Entry:
Connection = 0x19848f0
Info Type = SQL_DESCRIBE_PARAMETER (10002)
Info Value = 0x7fffe24b61a0
Buffer Length = 2
StrLen = 0x7fffe24b61be
[ODBC][28783][1414141316.369204][SQLGetInfo.c][608]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.369255][SQLGetInfo.c][546]
Entry:
Connection = 0x19848f0
Info Type = SQL_NEED_LONG_DATA_LEN (111)
Info Value = 0x7fffe24b61a0
Buffer Length = 2
StrLen = 0x7fffe24b61be
[ODBC][28783][1414141316.369304][SQLGetInfo.c][608]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.369347][SQLAllocHandle.c][529]
Entry:
Handle Type = 3
Input Handle = 0x19848f0
[ODBC][28783][1414141316.369411][SQLAllocHandle.c][1064]
Exit:[SQL_SUCCESS]
Output Handle = 0x19db6d0
[ODBC][28783][1414141316.369463][SQLGetTypeInfo.c][164]
Entry:
Statement = 0x19db6d0
Data Type = SQL_TYPE_TIMESTAMP
[ODBC][28783][1414141316.391748][SQLGetTypeInfo.c][314]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.391810][SQLFetch.c][158]
Entry:
Statement = 0x19db6d0
[ODBC][28783][1414141316.391864][SQLFetch.c][340]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.391910][SQLGetData.c][233]
Entry:
Statement = 0x19db6d0
Column Number = 3
Target Type = 4 SQL_INTEGER
Buffer Length = 4
Target Value = 0x7fffe24b61b8
StrLen Or Ind = (nil)
[ODBC][28783][1414141316.391970][SQLGetData.c][497]
Exit:[SQL_SUCCESS]
Buffer = [23]
Strlen Or Ind = NULLPTR
[ODBC][28783][1414141316.392015][SQLGetTypeInfo.c][164]
Entry:
Statement = 0x19db6d0
Data Type = SQL_VARCHAR
[ODBC][28783][1414141316.392056][SQLGetTypeInfo.c][186]Error: 24000
[ODBC][28783][1414141316.392109][SQLGetTypeInfo.c][164]
Entry:
Statement = 0x19db6d0
Data Type = Unknown(-9)
[ODBC][28783][1414141316.392196][SQLGetTypeInfo.c][186]Error: 24000
[ODBC][28783][1414141316.392275][SQLGetTypeInfo.c][164]
Entry:
Statement = 0x19db6d0
Data Type = SQL_BINARY
[ODBC][28783][1414141316.392326][SQLGetTypeInfo.c][186]Error: 24000
[ODBC][28783][1414141316.392386][SQLFreeStmt.c][140]
Entry:
Statement = 0x19db6d0
Option = 0
[ODBC][28783][1414141316.392542][SQLFreeStmt.c][246]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.392610][SQLAllocHandle.c][529]
Entry:
Handle Type = 3
Input Handle = 0x19848f0
[ODBC][28783][1414141316.392665][SQLAllocHandle.c][1064]
Exit:[SQL_SUCCESS]
Output Handle = 0x1a057f0
[ODBC][28783][1414141316.392722][SQLFreeStmt.c][140]
Entry:
Statement = 0x1a057f0
Option = 0
[ODBC][28783][1414141316.392767][SQLFreeStmt.c][246]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.392814][SQLExecDirect.c][236]
Entry:
Statement = 0x1a057f0
SQL = [insert into tpayon_test (name) values(N'안녕')][length = 48 (SQL_NTS)]
[ODBC][28783][1414141316.394015][SQLExecDirect.c][499]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.394075][SQLRowCount.c][169]
Entry:
Statement = 0x1a057f0
Row Count = 0x7fffe24b6300
[ODBC][28783][1414141316.394122][SQLRowCount.c][240]
Exit:[SQL_SUCCESS]
Row Count = 0x7fffe24b6300 -> 1
[ODBC][28783][1414141316.394169][SQLNumResultCols.c][152]
Entry:
Statement = 0x1a057f0
Column Count = 0x7fffe24b631e
[ODBC][28783][1414141316.394214][SQLNumResultCols.c][244]
Exit:[SQL_SUCCESS]
Count = 0x7fffe24b631e -> 0
[ODBC][28783][1414141316.394265][SQLEndTran.c][318]
Entry:
Connection = 0x19848f0
Completion Type = 0
[ODBC][28783][1414141316.394971][SQLGetInfo.c][546]
Entry:
Connection = 0x19848f0
Info Type = SQL_CURSOR_COMMIT_BEHAVIOR (23)
Info Value = 0x1985d60
Buffer Length = 2
StrLen = 0x7fffe24b621e
[ODBC][28783][1414141316.395160][SQLGetInfo.c][608]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.395211][SQLGetInfo.c][546]
Entry:
Connection = 0x19848f0
Info Type = SQL_CURSOR_ROLLBACK_BEHAVIOR (24)
Info Value = 0x1985d62
Buffer Length = 2
StrLen = 0x7fffe24b621e
[ODBC][28783][1414141316.395260][SQLGetInfo.c][608]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.395302][SQLEndTran.c][504]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.395349][SQLEndTran.c][318]
Entry:
Connection = 0x19848f0
Completion Type = 1
[ODBC][28783][1414141316.408656][SQLEndTran.c][504]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.408716][SQLDisconnect.c][204]
Entry:
Connection = 0x19848f0
[ODBC][28783][1414141316.408893][SQLDisconnect.c][341]
Exit:[SQL_SUCCESS]
[ODBC][28783][1414141316.409055][SQLFreeHandle.c][279]
Entry:
Handle Type = 2
Input Handle = 0x19848f0
[ODBC][28783][1414141316.409117][SQLFreeHandle.c][330]
Exit:[SQL_SUCCESS]
539
I did some testing with pyodbc and FreeTDS on my Xububtu 14.04 box and found that my code worked correctly if I used a parameterized query, but it did not work with a literal query. That is, with the same FreeTDS/ODBC configuration as yours, this approach failed (inserted different characters):
# -*- coding:utf-8 -*-
import pyodbc
conn = pyodbc.connect("DSN=MSSQLTEST;UID=sa;PWD=whatever;database=myDb")
cursor = conn.cursor()
cursor.execute(u"insert into tpayon_test (name) values (N'안녕')")
cursor.commit()
conn.close()
while this approach worked fine:
# -*- coding:utf-8 -*-
import pyodbc
conn = pyodbc.connect("DSN=MSSQLTEST;UID=sa;PWD=whatever;database=myDb")
cursor = conn.cursor()
sql = "insert into tpayon_test (name) values (?)"
parameters = [u"안녕"]
cursor.execute(sql, parameters)
cursor.commit()
conn.close()
Similarly, this (and variations thereof) did not work
cursor.execute(u"{call insert_name(N'안녕')}")
but this did:
sql = "{call insert_name(?)}"
parameters = [u"안녕"]
cursor.execute(sql, parameters)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
