Function sequence error in PYODBC

Question

I am using pyodbc to connect to a database and extract certain data from it.

Here is my code:

con = pyodbc.connect("driver={SQL Server};server= MyServer;database= MyDatabase;trusted_connection=true") 

cursor = con.cursor()

SQL_command = """
                      SELECT RowID = ISNULL
                      (
                          (
                              SELECT TOP 1 RowID
                              FROM [MyDatabase].[admin].[MyTable] 
                              WHERE [queue] = ? and processed IS NULL
                          )
                          ,-1
                      )
                  """

cursor.execute(SQL_command, queueNumber)

cursor.commit()

con.commit()

result_set = cursor.fetchall()

And I got following error after I run above code:

pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error (0) (SQLFetch)')

May I know what caused such problem, and how can I fix it?

Thanks.

Answer 1

I believe your problem is the strange commit statements. You only need to commit when inserting or updating records not selecting.

cursor.execute(SQL_command, queueNumber)
result_set = cursor.fetchall()

Also, in the future when using commit, both cursor.commit and con.commit do the same thing, you only need one.

Finally, I'd get used to calling execute with the second arguement as a tuple:

cursor.execute(SQL_command, (queueNumber,))

The way you have it works for pyodbc but is not DB API standard.