4.0.22 MSSQL Exceptions: Restricted data type attribute violation and Data Truncation

[brewneaux]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be
sure to specify 32-bit Python or 64-bit:

• Python: 2.7.12 64bit
• pyodbc: 4.0.22
• OS: Windows 7
• DB: MS Sql Server 2012
• driver: SQL Server Native Client 11.0

Issue

There are multiple errors while trying to insert data using cursor.execute that appeared in 4.0.22. This repro script should help test: https://gist.github.com/brewneaux/4f8c52865b9f2ad4f273cd3da000a575

**** First Input:

INSERT INTO test_insert (id,id2,id3,id4,val,id5,id6,id7,id8) VALUES (?,?,?,?,?,?,?,?,?)
(1, 1, 2005, 1, 1, u'2005-01-01 00:00:00', 7, 1, 1)
**** First Exception:


('07006', u'[07006] [Microsoft][SQL Server Native Client 11.0]Restricted data type attribute violation (0) (SQLBindParameter)')
Traceback (most recent call last):
  File "test_pyodbc.py", line 43, in <module>
    curs.execute(stmt, data)
Error: ('07006', u'[07006] [Microsoft][SQL Server Native Client 11.0]Restricted data type attribute violation (0) (SQLBindParameter)')



**** End First Exception


**** Second Input:

INSERT INTO test_insert (id8,val,id6,id7,id4,id5,id2,id3,id) VALUES (?,?,?,?,?,?,?,?,?)
[2005, u'2005-01-01 00:00:00', 1, 1, 1, 7, 1, 1, 1]
**** Second Exception:


Traceback (most recent call last):
  File "test_pyodbc.py", line 81, in <module>
    curs.execute(stmt, row.values())
DataError: ('String data, right truncation: length 38 buffer 10', '22001')

('String data, right truncation: length 38 buffer 10', '22001')


**** End Second Exception

[gordthompson]

re: your first example

Your parameter values are in the wrong order. In your gist you define your table as

    curs.execute('''CREATE TABLE test_insert (
        id smallint not null,
        val smalldatetime not null,
        id2 smallint not null,
        id3 [smallint] NOT NULL,
        id4 [smallint] NOT NULL,
        id5 [tinyint] NOT NULL,
        id6 [tinyint] NOT NULL,
        id7 [tinyint] NOT NULL,
        id8 [smallint] NOT NULL
    )''')

and in your first example

INSERT INTO test_insert (id,id2,id3,id4,val,id5,id6,id7,id8) VALUES (?,?,?,?,?,?,?,?,?)
(1, 1, 2005, 1, 1, u'2005-01-01 00:00:00', 7, 1, 1)

you are trying to insert u'2005-01-01 00:00:00' (the 6th parameter value) into id5 (the 6th column in the list). id5 is a tinyint, not smalldatetime.

re: your second example

I cannot reproduce the issue under pyodbc 4.0.22 using this code:

cnxn = pyodbc.connect(conn_str, autocommit=True)
crsr = cnxn.cursor()

crsr.execute('''CREATE TABLE #test_insert (
        id smallint not null,
        val smalldatetime not null,
        id2 smallint not null,
        id3 [smallint] NOT NULL,
        id4 [smallint] NOT NULL,
        id5 [tinyint] NOT NULL,
        id6 [tinyint] NOT NULL,
        id7 [tinyint] NOT NULL,
        id8 [smallint] NOT NULL
    )''')

stmt = 'INSERT INTO #test_insert (id8,val,id6,id7,id4,id5,id2,id3,id) VALUES (?,?,?,?,?,?,?,?,?)'
data = [2005, u'2005-01-01 00:00:00', 1, 1, 1, 7, 1, 1, 1]

crsr.execute(stmt, data)

[brewneaux]

Here is an updated gist that definitely fails on my PC
https://gist.github.com/brewneaux/d39ee76063f2d58e959d041b73d8cee4

And the sql trace
https://gist.github.com/brewneaux/2317021183b0708418e1d8e4e401d96d

[gordthompson]

I can confirm that this is an issue with pyodbc 4.0.22. My repro code:

import pyodbc
import sys
print('Python version ' + sys.version.replace('\n', ' '))
# Python version 2.7.14 (v2.7.14:84471935ed, Sep 16 2017, 20:25:58) [MSC v.1500 64 bit (AMD64)]
print('pyodbc version ' + pyodbc.version)
conn = pyodbc.connect(
    r'DRIVER=ODBC Driver 11 for SQL Server;'
    r'SERVER=.\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'Trusted_Connection=yes;'
)
curs = conn.cursor()
table_name = 'test_insert'
curs.execute("SELECT name FROM sys.tables where name = ?", table_name)
if curs.fetchone():
    curs.execute('DROP TABLE ' + table_name)
    print('(Table [{}] dropped.)'.format(table_name))
curs.execute('''CREATE TABLE {} (
    id smallint not null,
    val smalldatetime not null
)'''.format(table_name))
curs.commit()
print('(Table [{}] created.)'.format(table_name))
stmt = 'INSERT INTO {} (val, id) VALUES (?,?)'.format(table_name)
print('stmt: ' + repr(stmt))
data = (u'2005-01-01 00:00:00', 1)
print('data: ' + repr(data))
curs.execute(stmt, data)

The above works fine under 4.0.21 but fails under 4.0.22 with the error

Traceback (most recent call last):
  File "C:/Users/Gord/PycharmProjects/py2pyodbc_demo/main.py", line 27, in <module>
    curs.execute(stmt, data)
pyodbc.DataError: ('String data, right truncation: length 38 buffer 32', '22001')

Interestingly, under 4.0.22 table_name = '#test_insert' works, but table_name = 'test_insert' and table_name = '##test_insert' do not. Under 4.0.21 all three variations work.

[v-chojas]

That trace looks odd --- I do not see any execution of the queries, nor do any of the functions called return SQL_ERROR. The first time it does SQLPrepare, SQLNumParams, and SQLDescribeParameter for all the parameters (and the returned types are correct), but then instead of binding it immediately frees the statement. The second time it prepares, describes, and binds, but frees the statement without executing it.

[gordthompson]

@v-chojas I agree that the ODBC traces look odd. The trace for my repro code is attached. It shows the same thing: SQLPrepare succeeds, as does SQLNumParams, SQLDescribeParam is called twice with result SQL_SUCCESS both times, and then SQLFreeStmt is called.

SQL.LOG

[WilliamZimmermann]

Hello. I'm with the same problem. Anyone have a suggestion?

[gordthompson]

@WilliamZimmermann You could revert to pyodbc 4.0.21 for the time being.

[WilliamZimmermann]

Hello @gordthompson . Thank you. I did it and now, with 4.0.21 it's working again. Thank you very much.

[gordthompson]

@v-chojas - The 4.0.22 issue(s) also affect parameterized queries trying to insert strings > 255 characters into an Access "Long Text" (a.k.a. "Memo") column as described in the Stack Overflow question here. I have confirmed that 4.0.22 fails while 4.0.21 succeeds. If you'd prefer that I create a separate issue for this please let me know.

[mkleehammer]

Please upgrade to 4.0.23 which reverts the problematic commits from 4.0.22.

[slarrain]

I have 4.0.23 and have the same issue @mkleehammer

[ravichandrae]

I was using 4.0.22, and facing this error. After upgrading to 4.0.23, this error is gone.

[binhnguyenduc]

I fixed my issue by replacing all np.nan values to standard python None value. MSSQL doesn't seem to understand np.nan, hence the data type violation.

See this for how to replace np.nan with None.
https://stackoverflow.com/questions/14162723/replacing-pandas-or-numpy-nan-with-a-none-to-use-with-mysqldb

[etav]

Could be an unrelated issue but changing the driver to a more modern one did the trick for me.