I'm trying to insert data I scraped from the web into an SQL server db, and the relevant columns of my db table are set up as nvarchar(max) type.
Testing this in a python shell:
dbargs={'DATABASE': '<mydbname>',
'DRIVER': '{FreeTDS}',
'PORT': '1433',
'PWD': '<mypass>',
'SERVER': '<server>',
'UID': '<myusername>'}
import pyodbc
cnxn = pyodbc.connect(**dbargs)
cursor = cnxn.cursor()
insert_cmd="INSERT INTO c_master (run_id, product_name) VALUES (?,?)"
Then
cursor.execute(insert_cmd, (274, u'test naméâôóòöë'))
works fine, however
cursor.execute(insert_cmd, (274, u'test \u2019d'))
causes the error
ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near ','. (102) (SQLExecDirectW)")
I find that I am scraping data of the latter type and generating errors when I try to insert it as above into the db in my writer pipeline.
What is the correct way to handle such data?
(I'm using FreeTDS, unixodbc, MSSQL Server, pyodbc)
FreeTDS and unixodbc conf:
/etc/odbc.init:
[myserver]
Driver = FreeTDS
Description =Myserver MSSQL database
# Servername corresponds to the section in freetds.conf
Servername=myserver
Database = mydbname
TDS_Version = 7.0
/etc/odbcinst.ini:
[ODBC]
Trace = Yes
TraceFile = /tmp/odbcsql.log
ForceTrace = Yes
Pooling = Yes
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout = 120
CPReuse =
/etc/freetds/freetds.conf:
[global]
tds version = 7.0
dump file = /var/log/freetds.log
debug flags = 0xffff
# timeout = 10
# connect timeout = 10
text size = 64512
[myserver]
host = <myserverip>
port = 1433
tds version = 7.0
dump_file = /var/log/freetds.log
client charset = UTF-8
I also added TDSDUMP="/var/log/freetds.log" to /etc/environment and /etc/profile. Although weirdly, I'm still not seeing the freetds log.
You need to pass the TDS_Version as part of the connection string. You're connecting with a full connection string, which bypasses the DSN if you pass the full server name as SERVER in the connect() declaration. If you want to connect with a DSN, you need to provide a DSN (and UID / PWD) rather than SERVER and PORT.
I've issued a pull request to better explain TDS Versions to FreeTDS here: https://github.com/FreeTDS/freetds/pull/71
The default version of TDS used if it isn't provided doesn't support Unicode (as you saw!). Assuming you're using SQL Server 2005 or newer, if you're using:
Good luck!