1

I have this query working through ODBC to a 4D database. It returns records as expected.

with pyodbc.connect('dsn=datasource') as db4dcon:  
    print(db4dcon)
    db4dcur = db4dcon.cursor()
    print(db4dcur)
    db4dcur.execute("SELECT * FROM Sampling WHERE Id='2017-043';")
    print(db4dcur.rowcount)

<pyodbc.Connection object at 0x029CC068>
<pyodbc.Cursor object at 0x0292B058>
3

I would like to change it to a parameterized version, but I could not succeed. I have read the doc, and none of those example work for me.

Neither:

db4dcur.execute("SELECT * FROM Sampling WHERE Id=?;", '2017-043')

Nor:

db4dcur.execute("SELECT * FROM Sampling WHERE Id=?;", ('2017-043',))

Nor:

db4dcur.execute("SELECT * FROM Sampling WHERE Id=?;", ['2017-043']))

Work. They all lead to the following error:

<pyodbc.Connection object at 0x02C7C068>
<pyodbc.Cursor object at 0x028DB058>
Traceback (most recent call last):
  File "test.py", line 251, in <module>
    main(sys.argv)
  File "test.py", line 221, in main
    db4dcur.execute("SELECT * FROM Sampling WHERE Id=?;", '2017-043')
pyodbc.Error: ('08004', '[08004] Server rejected the connection:\nFailed to execute statement.\r (1110) (SQLExecDirectW)')

I have checked my ODBC connection, it seems server rejects a malformed query when replacing wildcard, but I cannot check what it is sent because of ODBC mechanism. Does anyone have an idea about what is going wrong with this parameterized query?

Community
  • 1
  • 1
chdegrave
  • 11
  • 2
  • Your parameterized queries certainly look like they should work. Could you try using pypyodbc instead of pyodbc to see if it behaves any differently? – Gord Thompson Apr 04 '17 at 13:10
  • Thank you! Indeed, I tried with pypyodbc and it works for each case. – chdegrave Apr 04 '17 at 14:17
  • It is interesting that pypyodbc works while pyodbc doesn't work - i would have suggested updating the ODBC driver or checking the ODBC TRACE LOG but i assume you are using the same query and same driver for both tests... Another option could be [P4D](https://github.com/ibrewster/p4d) which is a Python database module for the 4D database system using the native [SQLlib_4D](https://github.com/4D/SQLlib_4D) protocol. – Tim Penner Apr 04 '17 at 17:12

0 Answers0