2

I cannot get parametrized query working with pyodbc and 4D. Here an example of simple and basic code (working on mySQL database). The table B has two columns without any constraint.

import pyodbc

con2 = pyodbc.connect('dsn=odbc_source');
cur2 = con2.cursor();

KeyMap = {
    1: ('Aaaahaa',datetime.date(1990,1,1)),
    2: ('Bcdefgh',datetime.date(1990,2,1)),
    3: ("Aaaah'aa",datetime.date(1990,3,1))
    };

for k in KeyMap:
    v = (k,KeyMap[k][0]);
    cur2.execute("INSERT INTO B(Num,Text) VALUES(?,?);",v);

I always get this error message:

Traceback (most recent call last):
File "D:\jlandercy\src\python\cripi\transfert\000_TestInsertParam.py", line 33, in <module>
cur2.execute("INSERT INTO B(Num,Text) VALUES(?,?);",v);
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')

Or, that the connection failed, but if I issue a SELECT it works. What am I doing wrong.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
jlandercy
  • 7,183
  • 1
  • 39
  • 57
  • What version of 4d server are you using? And what version of the odbc driver? – Tim Penner Feb 27 '16 at 03:38
  • Did you ever resolve this? I too am getting the surprising "contains 1 parameter markers" when my code clearly contains 2. It seems to be an issue with processing the query and not binding parameters per se. – WAF Jan 30 '19 at 09:38
  • 1
    @WAF, I don't know if new ODBC connectors also have this issues (it is also limited by your 4D version). Anyway we solved it by formatting the SQL statements before submitting them to ODBC driver. This works but requires to carefully cast and convert to string all fields to submit, not very flexible and leading to a poor design (it might be prone to injection). You also need to take good care of NULL values which are really different among SQL standard and 4D point of view. I have not tested with 4D over ODBC but it may be cleaner way: https://stackoverflow.com/a/23744051/3067485 – jlandercy Jan 30 '19 at 11:04

1 Answers1

0

Have you tried enumerating the mapping instead of making the () object?

for k in KeyMap:
    cur2.execute("INSERT INTO B(Num,Text) VALUES(?,?);",k,KeyMap[k][0]);
James
  • 119
  • 1
  • 8