crash on close() without rollback/commit

[davidalbertonogueira]

Hi, just to report that when closing Win SQL Server connections, even just using cursor to perform retrieval Selects, the following error is generated:

"...\generating_dataset.py", line 228, in get_doc_text
    connection.close()
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 2697, in close
    check_success(self, ret)
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 1009, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 975, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('25000', '[25000] [Microsoft][ODBC Driver Manager] Invalid transaction state')
Exception ignored in: <bound method Connection.__del__ of <pypyodbc.Connection object at 0x00000167673D4518>>
Traceback (most recent call last):
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 2682, in __del__
    self.close()
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 2697, in close
    check_success(self, ret)
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 1009, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "...\Anaconda3\lib\site-packages\pypyodbc.py", line 975, in ctrl_err
    raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('25000', '[25000] [Microsoft][ODBC Driver Manager] Invalid transaction state')
Press any key to continue . . .

To fix it, a rollback() is necessary before closing the connection. Perhaps this fix should be harcoded inside the close method is no "editing" operation was perfomed:

cursor.close()
connection.rollback()
connection.close()

[braian87b]

cursor must always be closed...

[davidalbertonogueira]

@braian87b , I was closing the cursor and the connection. But it is not enough, the connection must have a rollback/commit call before close.

[braian87b]

Please post a small snippet to test and replicate your problem, thanks.

[davidalbertonogueira]

def create_SQL_connection(server, Database, trusted_connection=True, uid="", pwd=""):
  for i in range(10):
    try:
      connection = pypyodbc.connect('Driver={SQL Server};'
                                        'Server=' + server + ';'
                                        'Database=' + Database + ';'
                                        'Trusted_Connection=yes;' if trusted_connection == True else 'uid=' + uid + ";pwd=" + pwd + ';')
      except pypyodbc.Error as ex:
        sqlstate = ex.args[1]
        print(sqlstate) 
        time.sleep(.1 * pow(2,i))
        continue
      return connection   
  return None

def close_SQL_connection(connection):
    connection.close()

connection = create_SQL_connection("http:SomeSQLServerURL", "SomeTableName")

SQLWhatAndFrom = "SELECT * FROM SomeTableName WITH (NOLOCK) "
SQLWhereData = "WHERE  Data between '" + date_begin + \
                                 "' and '" + date_end + "' "
SQLConstrains = (" and FieldX = 'Y' "
                       "ORDER BY Data ;")
SQLCommand = SQLWhatAndFrom + SQLWhereData + SQLConstrains
cursor = connection.cursor()           
cursor.execute(SQLCommand) 
for row in cursor.fetchall():
                if not row :
                    cursor.close()
                    break
                else:
                   ....
cursor.close()
close_SQL_connection(connection) 

Previous definition of close_SQL_connection is not enough when closing Win SQL Server connections. It must be like this:

def close_SQL_connection(connection):
    connection.rollback()
    connection.close()