Byte string too long PyPyOdbc

Question

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?

Post your code. We also need to know the SQL table definition. — Mike, Sep 09 '17 at 20:12
Executed same command directly from SQL Management Studio and it worked out. The column is, nvarchar(MAX). Im now searching why FreeTDS keeps using version 4.2 when running the following command: tsql -C — user0187409, Sep 09 '17 at 20:16
@user0187409 - Try adding `TDS_Version=7.3` to your connection string (or DSN entry in odbc.ini) and see if that helps (ref: [here](http://www.freetds.org/userguide/odbcconnattr.htm)). — Gord Thompson, Sep 09 '17 at 23:54
interesting... now it gives me a different error: 'Unicode data in Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.' — user0187409, Sep 10 '17 at 02:32
Tried also TDS_Version=8.0 in connection string but stills give same 'string too long' error. If using TDS_Version=7.4 gave me same 'Unicode data in Unicode-only...' error. — user0187409, Sep 10 '17 at 02:35
Please [edit] your question to show us the results of `tsql -C` and `odbcinst -j`. — Gord Thompson, Sep 10 '17 at 16:41
I was able to reproduce your most recent issue ("Unicode data in Unicode-only collation ...") under FreeTDS v0.91 with both pypyodbc and pyodbc using `TDS_Version=7.3`, but `TDS_Version=7.2` seemed to work. Upgrading to FreeTDS v1.00.55 also helped avoid some (but not all) strangeness with pypyodbc under Python3. — Gord Thompson, Sep 10 '17 at 20:46
Upgraded to FreeTDS 1.00.55, how did you make pypyodbc to look for the freetds.conf file? Mine is fixed looking at /etc/freetds/freetds.conf, but the installation created the file at /usr/local/etc/freetds.conf — user0187409, Sep 10 '17 at 21:08

score 1 · Answer 1 · answered Sep 11 '17 at 00:23

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.

This Stack Overflow question ended up being the solution for me when I was getting this error: https://stackoverflow.com/questions/50056878/byte-string-too-long-error-in-python-using-pypyodbc-1-3-4 — Jed, Jan 30 '19 at 14:27

Byte string too long PyPyOdbc

1 Answers1

Linked