Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Function sequence error in PYODBC

Tags:

python

sql

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.

like image 389
ChangeMyName Avatar asked Sep 10 '14 15:09

ChangeMyName


People also ask

What is a function sequence error?

The "Function sequence error" may occur when you execute a SQL statement in the wrong context, such as when a SQL result set has not been completely read by the client. A common cause of this error is executing a SELECT statement without processing the result set before executing another SQL statement.

What driver do I use for Pyodbc?

pyODBC uses the Microsoft ODBC driver for SQL Server.


1 Answers

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.

like image 70
Mark Avatar answered Sep 30 '22 03:09

Mark