Passing parameters to a saved query in MS Access

Question

Ok, I tried every possible situation from SO, and none of it did the job.

I have a problem calling a saved query which requires 4 parameters to be passed (talking about access database), and I have a simple code :

params = ()
sql_saldo = "{CALL KUP_BalansKupDosp(204701,0,#3/5/2020#,0)}"
saldo = cursor.execute(sql_saldo)
for row in saldo.fetchall():
    print(row)

This works!

But since I need those parameters to be dynamic, when I try :

params = ('204701','0,#3/5/2020#','0')
sql_saldo = "{CALL KUP_BalansKupDosp(?,?,?,?)}"
saldo = cursor.execute(sql_saldo, params)
for row in saldo.fetchall():
    print(row)

I get an error Data type mismatch in criteria expression

This is original parameter definition PARAMETERS InKupID Long, InSekID Long, InDatum DateTime, InToler Currency = 1; So it takes, string, string, datetime, and int i suppose. So when I try with :

today_row = date.today()
today = today_row.strftime("%d/%m/%Y")
params = (204701,0,today,0)
sql_saldo = "{CALL KUP_BalansKupDosp(?,?,?,?)}"
saldo = cursor.execute(sql_saldo, params)
for row in saldo.fetchall():
    print(row)

I also get the same error, i even tried with every possible combo and I just cant figure it out.

PS. My connection is working great since I have 10 more queries besides this one.

Someone had a similar problem?

Answer 1

I manage to solve it with this :

params = (204701, 0, today, 0)
sql_saldo = "{CALL KUP_BalansKupDosp(%s,%s,%s,%s)}" % params
saldo = cursor.execute(sql_saldo)
for row in saldo.fetchall():
    print(row)

Answer 2

This is original parameter definition PARAMETERS InKupID Long, InSekID Long, InDatum DateTime, InToler Currency = 1; So it takes, string, string, datetime, and int i suppose.

No. In Access DDL, Long is "Long Integer" and Currency is a numeric value similar to Decimal(18, 4).

To avoid the SQL injection vulnerabilities present in the other answer, we need to use a parameterized query and pass the appropriate types.

params = (204701, 0, datetime.datetime(2020, 3, 5), Decimal("19.95"))
sql_saldo = "{CALL KUP_BalansKupDosp(?,?,?,?)}"
cursor.execute(sql_saldo, params)
for row in cursor.fetchall():
    print(row)