CODEWITHSUNDEEP
pythonsqlpyodbc
ChangeMyName
ChangeMyName

Reputation: 7418

Function sequence error in PYODBC

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.

Upvotes: 18

Views: 20697

Answers (2)

Alexander Kvist
Alexander Kvist

Reputation: 589

I was recieving the two following errors more or less interchangeably:

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

pyodbc.Error: ('HY007', '[HY007] [Microsoft][ODBC Driver 17 for SQL Server]Associated statement is not prepared (0) (SQLNumResultCols)')

My problem was, that two threads were using the same connection, which led to weird states of the prepared statements. I fixed it by creating a new connection for each thread.

Upvotes: 1

Mark
Mark

Reputation: 108567

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.

Upvotes: 37

Related Questions