Teradata
Returning generated keys from parameterized INSERT query fails, identical non-parameterized query succeeds
To reproduce, first set up a connection and table like this:
conn = udaExec.connect(..., ReturnGeneratedKeys="C")
curs = conn.cursor()
curs.execute('''CREATE TABLE testautokeyreturn (tablekey BIGINT GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE -999999999999999999
MAXVALUE 999999999999999999
NO CYCLE),
some_int BIGINT NOT NULL)''')
Then observe that this works:
curs.execute('INSERT INTO testautokeyreturn VALUES (, -2)')
print curs.fetchone()
...printing: Row 1: [1]
And finally, try this, which is the same query except paramterized:
curs.execute('INSERT INTO testautokeyreturn VALUES (, ?)', [-2])
print curs.fetchone()
...fails with traceback:
Traceback (most recent call last):
File "<input>", line 1, in <module>
File "C:\PythonCAT\x64\2.7\lib\site-packages\teradata\udaexec.py", line 618, in fetchone
return self.cursor.fetchone()
File "C:\PythonCAT\x64\2.7\lib\site-packages\teradata\util.py", line 94, in fetchone
return next(self, None)
File "C:\PythonCAT\x64\2.7\lib\site-packages\teradata\util.py", line 142, in next
return self.__next__()
File "C:\PythonCAT\x64\2.7\lib\site-packages\teradata\util.py", line 133, in __next__
values = next(self.iterator)
File "C:\PythonCAT\x64\2.7\lib\site-packages\teradata\tdodbc.py", line 624, in rowIterator
for col in range(1, len(cursor.description) + 1):
TypeError: object of type 'NoneType' has no len()
The ODBC driver isn't returning a result set in this situation. I will follow up with ODBC support.
I know this is an ODBC driver bug, but just off the top of your head is there any known workaround for this? I tried setting up a "User DSN" for my Teradata server via the Windows ODBC Data Source Administrator and setting its configuration to return the generated keys. This didn't work, so it either has nothing to do with modifying the connection from the Teradata python module or the bug even persists after modifying this option. If I need to, I guess I could de-parameterize the query by formatting the query string with the insert values embedded in the string.
Using a non-parameterized insert is the only known workaround for this issue. The ODBC team has investigated and confirmed it's a bug with the ODBC driver. I will leave this issue open until an ODBC fix is available.
I posted a comment on the JIRA asking for an ETA. Got back a response that the issue is not actually being worked at the moment and does not have an ETA. I referenced this POST again in the JIRA so hopefully since there is interest will get a higher prioritization.
Still experiencing this issue on 15.10.0.21. Can we get another status update / poke to the team that owns it? I've de-parameterized my queries as a workaround, but this is not ideal.
My Teradata ODBC driver is 15.10.0.0.