I have pyodbc installed and I wrote a simple SELECT a few weeks ago to "kick the tires". That worked fine. Today I tried to convert an existing script to use pyodbc. It does an INSERT that runs fine on Windows but on IBMi it fails with:
Error('01004', '[01004] [IBM][System i Access ODBC Driver]String data right truncation. (30016) (SQLExecDirectW)')
If it's valid on Windows, I can't see why it fails on IBMi.
-----Original Message-----
From: John Yeung [mailto:gallium.arsenide@xxxxxxxxx]
Sent: Thursday, August 01, 2019 9:36 AM
To: IBMi Open Source Roundtable <opensource@xxxxxxxxxxxxxxxxxx>
Subject: [IBMiOSS] Trouble with numeric columns in pyodbc
I am having severe issues with pyodbc in PASE.
Let's say TESTFILE is a table with four columns, TFLD01, TFLD02, TFLD03, and TFLD04, all numeric. I've tried the following from both my PC and PASE:
# begin snippet
import pyodbc
conn = pyodbc.connect(<my connection stuff here>)
c1 = conn.cursor()
c1.execute('''
insert into testfile values (?, ?, ?, ?)
''', (1, 2, 3, 4))
c1.execute('''
insert into testfile values (?, ?, ?, ?)
''', ('2', '4', '6', '8'))
# end snippet
From my PC, both rows are successfully inserted. The second row is
implicitly converted to numeric values.
From PASE, it's a disaster. The first row is silently inserted as all
zeros (the columns are not null-capable; had they been, I suppose the row might have consisted of nulls), and the second row raises the following exception:
pyodbc.DataError: ('22018', '[22018] [IBM][System i Access ODBC
Driver][DB2 for i5/OS]SQL0406 - Conversion error on assignment to column TFLD01. (-406) (SQLExecDirectW)')
Is anyone else experiencing this? In its current state, at least on my machine, obviously pyodbc is unusable from PASE. :(
John Y.
midrange.com
