Byte string too long PyPyOdbc

2019-07-10 04:53发布

问题:

Using FreeTDS and Python3.5 library PyPyodbc I am getting a 'Byte string too long' when trying to insert a row in the SQL Server database (residing on Windows). Under FreeTDS conf file there is a vairable 'text size' that I changed to:

[global]
text size = 4294967295

Here is the configuration files

/etc/freetds/freetds.conf
[global]
      tds version = 4.2
      text size = 4294967295

[sqlserver]
      host = 192.168.0.3
      port = 1433
      tds version = 8.0

/etc/odbc.ini
[sqlserverdatasource]
Driver = freetds
Description = SQL Server
Servername = sqlserver
Database = MyDB
TDS_Version = 8.0

/etc/odbcinst.ini    
[freetds]
Description = MS SQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
Trace = Yes
TraceFile = /tmp/freetds.log
FileUsage = 1
UsageCount = 1

Executing 'tsql -C':

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

Executing '/usr/bin/tsql -S 192.168.0.3 -U user':

Password: 
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"

Executing 'odbcinst -j':

unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/metheUser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Python PyPyodbc connection string:

connStr = 'DSN=sqlserverdatasource;Database=MyDB;uid=user;pwd=password'

But the error keeps appearing, what I am missing?

回答1:

I finally found the problem, other than set in TDS_Version = 8.0, the allocation for a character buffer in PyPyodbc is not enough. My query has more than 3100 characters (at a minimum). Updating the PyPyodbc directly to double the amount was sufficient for now. Not sure if quadrupling it will be necessary.

Why this not had been added to the library? For more info: https://github.com/jiangwen365/pypyodbc/issues/27

Thanks to @GordThompson to help me out with the possibilities and test it on his own environment.