Parameterized Queries (1 Viewer)

[tmyers]

Not sure which sub-form to post in, so general it is!

I am connecting to a small (only 3 table) access database (2016, .accdb) using an ODBC driver from Python. I am trying to run an parameterized query and am getting an error when I try. From what I have been able to find, it is saying that the ODBC driver does not allow parameterized queries. Can someone confirm that per chance?

Its not VBA, but here is my code for those interested:

import pyodbc
import os

def db_connection():
    access_file_path = #chopped out for posting
    connection = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + access_file_path)
    cursor = connection.cursor()

    return connection, cursor

def db_order_parameters(selected_order):
    connection, cursor = db_connection()
    cursor.execute(
        '''SELECT tblParameters.DroppedRows, tblParameters.DroppedStatus, tblParameters.Tracking
        FROM tblOrders INNER JOIN tblParameters ON tblOrders.ID = tblParameters.ID_Order
        WHERE tblOrders.OrderNumber = ?
        ''', (selected_order, )
    )
    parameters = cursor.fetchone()
    cursor.close()
    connection.close()
    print(parameters)
    return parameters

There error in question is on cursor.execute and is:

Exception has occurred: Error
('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented  (106) (SQLBindParameter)')

Anyone happen to have any idea?

 

[MarkK]

Since you are constructing the SQL string anyway, you can avoid the parameter query and do...

cursor.execute(
        '''SELECT tblParameters.DroppedRows, tblParameters.DroppedStatus, tblParameters.Tracking
        FROM tblOrders INNER JOIN tblParameters ON tblOrders.ID = tblParameters.ID_Order
        WHERE tblOrders.OrderNumber = ''' + selected_order
    )

...or whatever the proper python syntax would be to concatenate the 'selected_order' value at the end of the SQL string.

 

[Mike Krailo]

This might be a better question to ask on a python forum. Since you are going into uncharted waters for me I couldn't answer this, but I would do some more experiments to see what you can and cannot do. If something doesn't work as is the case with your current query, then back off to something more simple and keep testing till you determine what does work.

quote from someone on stackoverflow:

You cannot use pyodbc and Access ODBC to run queries that involve user-defined functions. However, if you have the full Microsoft Access application installed then your Python app can use COM automation to spin up an instance of Access and run the query that way.

 

[tmyers]

Since you are constructing the SQL string anyway, you can avoid the parameter query and do...

cursor.execute(
        '''SELECT tblParameters.DroppedRows, tblParameters.DroppedStatus, tblParameters.Tracking
        FROM tblOrders INNER JOIN tblParameters ON tblOrders.ID = tblParameters.ID_Order
        WHERE tblOrders.OrderNumber = ''' + selected_order
    )

...or whatever the proper python syntax would be to concatenate the 'selected_order' value at the end of the SQL string.

That was going to be my next go around but was mostly curious if anyone knew for a fact that the ODBC driver simply didnt allow parameterized queries as it would possibly effect future queries I am constructing as well.

 

[Pat Hartman]

Can someone confirm that per chance?

Where is "Access" getting the variable? That should help you figure this out. How is Access going to display a prompt on your web page? It can't so that should be your answer.

As @MarkK suggested, build the entire string and send that.

 

[tmyers]

Now I am even more confused as I moved on to make a different query and this one worked. It however is only returning a single column rather than multiple.

def db_orders():
    connection, cursor = db_connection()
    current_user = os.environ.get("USERNAME")
    cursor.execute(
    '''SELECT tblOrders.OrderNumber, tblOrders.JobName
    FROM tblUser INNER JOIN tblOrders ON tblUser.PMID = tblOrders.ID_PM
    WHERE tblUser.WindowsID = ? AND tblOrders.Active = True
    ''', (current_user,))
    rows = cursor.fetchall()
    orders = ["{} - {}".format(row[0], row[1]) for row in rows]
    cursor.close()
    connection.close()
    
    return orders

I am going to go with that it is a problem with my code rather than the query itself. As Mike said, I am going to have to take this to a python forum it seems.

 

[monheimx9]

I don't think this is a python issue
It looks more like a SQL issue

If I'm not wrong, Access SQL does not implement bind parameters, and also, I think the question mark is used in MySQL and not in Access SQL or SQL Server but I'm not 100% sure

You might use a format string instead

cursor.execute(
    'SELECT tblOrders.OrderNumber, tblOrders.JobName'
    'FROM tblUser INNER JOIN tblOrders ON tblUser.PMID = tblOrders.ID_PM'
f'WHERE tblUser.WindowsID = {current_user} AND tblOrders.Active = True')