Errors on insert with parameter markers

[data-henrik]

I am trying to run an INSERT statement that has string, integer, and date values. The statement works when passing the values as literals, but fails when trying to use parameter markers (?).

Could such a scenario be added to the Db2 unit tests, both for QA and serving as example?

[nakagami]

The test around here https://github.com/nakagami/pydrda/blob/master/test_db2.py#L130-L137 confirms the operation of select and update. I think insert will probably work as well.

[data-henrik]

That is one of the errors:

  File "/home/user/.local/lib/python3.11/site-packages/drda/connection.py", line 367, in _query
    rows, _, _ = self._parse_response()
                 ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/user/.local/lib/python3.11/site-packages/drda/connection.py", line 102, in _parse_response
    raise err
drda.OperationalError: -180:b'22007':b''

[data-henrik]

another error when just using two string parameter markers on INSERT

  File "/home/hloeser/.local/lib/python3.11/site-packages/drda/connection.py", line 102, in _parse_response
    raise err
drda.OperationalError: -30020:b'58009':b'0x220E"("0133")"\xff'

[nakagami]

I would appreciate it if you could provide an example of an insert statement for the table definition to be reproduced.

I see here that you are getting errors with date, datetime, and timestamp. https://stackoverflow.com/questions/14959872/insert-throws-sqlcode-180-sqlstate-22007-on-ibm-db2

How about something like

insert into foo (column) values (timestam(?))

[data-henrik]

SQL:

create table drdatest(id int, s1 varchar(30), s2 varchar(30), d date)
insert into drdatest(id,s1,s2,d) values(1,'hello','world', '2023-11-08')
select * from drdatest

code snippet:

    myvalues=[121,'hello', 'world again', datetime.date.fromisoformat('2023-11-08')]
    cur.execute("select * from final table(insert into drdatest(id,s1,s2,d) values(?,?,?,?))", myvalues)

based on tests, it seems to happen only when there are more columns present.

[nakagami]

Thank you. I was able to write a test that produces the error https://github.com/nakagami/pydrda/commit/50d5ff93f48194c51541c51783d2a9df598327aa

Hmmm, I don't know the cause, but it looks like there is something wrong with the encoding when passing string parameters.

Maybe it's something around here https://github.com/nakagami/pydrda/blob/master/drda/ddm.py#L420 I don't know how to fix it yet.

[data-henrik]

Thank you for looking into it. I clicked on the line you pointed to. I don't know details about DRDA, but am curious why the string is UTF-16, but everything else is using UTF-8.

[nakagami]

@data-henrik I have fixed the bug that seems to be it. Can you install pydrda from github repository and check if it works?

If it seems ok, I will upload it to pypi with the newer version.

[data-henrik]

@nakagami Thank you! I plan to test it today, Monday latest and let you know.

[data-henrik]

:tada: I applied the single line patch (https://github.com/nakagami/pydrda/commit/d4be12637817fc151fabd11620b83f02631976a6) to ddm.py and now it works. :tada: :+1:

[nakagami]

Thanks, fixed version has been released as 0.4.8