Why insert blank using pyodbc in Linux environment?
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
file / 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 am using 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 - empty value NOT WRONG.
self.cursor.execute("{call insert_name('123')}")
result: column of names in MSSQL2012 is 123.
name is nvarchar (50). I think this is an encoding / encoding issue.
Linux is cenos6.5 and $ LANG is ko_KR.UTF-8.
But I don't know the problem.
Additional Information
coding problem? setting up mssql?
I am writing the 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]
+3
source to share
2 answers
I did some testing with pyodbc and FreeTDS on my Xububtu 14.04 block and found that my code worked correctly if I used a parameterized query, but it didn't work with a literal query. That is, with the same FreeTDS / ODBC configuration as yours, this approach failed (inserted different symbols):
# -*- 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()
Likewise, this one (and their variants) didn't work
cursor.execute(u"{call insert_name(N'안녕')}")
but this happened:
sql = "{call insert_name(?)}"
parameters = [u"안녕"]
cursor.execute(sql, parameters)
+2
source to share