2

To anyone that can help, thank you in advance.
I am running 64bit windows 10, 64bit office and 64bit python 3.7.2

Using pyodbc, I am trying to execute a Select statement such as this:

"SELECT * FROM EconVars WHERE Year(ValueDate) = 1999"

In order to do this, my code is as follows:

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""        
params = (1999,)
cursor.execute(existquery,params)

When I run this, I get the following error pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')

I have read this might be because pyodbc has issues passing integer values, therefore I have attempted to convert to float, as this seemed to work for some people. Therefore, my code becomes:

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = (float(1999),)
cursor.execute(existquery,params)

When running this code, the program simply stops on the cursor.execute line with no error.

I have also tried using the pypyodbc module instead and when running the exact same code above

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = [1999,]
cursor.execute(existquery,params)

I get the following error

OSError: exception: access violation reading 0x0000000000000000

I have confirmed that there is nothing wrong with the cursor connection because the same cursor successfully runs an INSERT command.

If anyone has any ideas they would be greatly appreciated.

Update 1

Thanks for responding, I have downgraded to 4.0.24 however the error still remains, the code below will still crash out with no error

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = (1999,)
cursor.execute(existquery,params)
existData = cursor.fetchall()

Also, passing a string, doesnt work either

existquery = """SELECT * FROM EconVars WHERE Year(ValueDate) = ?"""
params = ('1999',)
cursor.execute(existquery,params)
existData = cursor.fetchall()

I have, however got it working by passing a string date such as 

existquery = """SELECT * FROM EconVars WHERE ValueDate = ?"""
params = ('#01/04/1999#',)
cursor.execute(existquery,params)
existData = cursor.fetchall()

However, clearly this doesnt help when looking for datapoints from a specific year. It also leads to UK / US date problems as the date above is actually interpreted as the 4th January.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
user78913
  • 73
  • 7
  • Wouldn't a simple string concat to build the query do? i.e. `existquery = "SELECT * FROM EconVars WHERE Year(ValueDate) = " + str(1999)` – shahkalpesh Jan 16 '19 at 16:12
  • This also just stops the code and exits with no error message – user78913 Jan 16 '19 at 17:56
  • What does `pyodbc.version` return? – Gord Thompson Jan 16 '19 at 17:56
  • Hi, It is version 4.0.25 – user78913 Jan 16 '19 at 17:57
  • I have found that running the statement `existquery = """SELECT * FROM EconVars WHERE ValueDate = #01/05/1999#"""` will not stall on `cursor.execute(existquery)` `cursor.fetchall` however the cursor contains -1 rows which is not correct. Is the row count the correct way to determine if the cursor has records? – user78913 Jan 16 '19 at 18:02
  • "Is the row count the correct way to determine if the cursor has records?" - No, the row count only applies to DML statements (INSERT, UPDATE, DELETE). – Gord Thompson Jan 16 '19 at 19:50
  • Thanks, I got what I needed here by using len(cursor.fetchall) – user78913 Jan 17 '19 at 08:52

1 Answers1

3

I was able to reproduce your issue. A change introduced in pyodbc 4.0.25 appears to cause problems with queries against an Access database when those queries use integer parameters.

The issue has been reported on GitHub here. In the meantime, downgrade to pyodbc 4.0.24.

Update

This issue was fixed in pyodbc 4.0.27.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418