0

I am trying to get the ID of a newly inserted row by using OUTPUT. However, I am getting exception: fetch() on closed connection or empty query set.

I am using adodbapi for SQL Server connectivity.

Code:

import adodbapi
Query = '''
        DECLARE @revisions_id table(revisions_id bigint);
        INSERT INTO [dbo].[revisions]
                   ([submitted_date]
                   ,[submission_received_date]
                   ,[form_number]
                   ,[ProcessTryCount])
        OUTPUT INSERTED.revisions_id INTO @revisions_id     
             VALUES
                   (GETDATE()
                   ,GETDATE()
                   ,?
                   ,0 )

        SELECT revisions_id FROM @revisions_id 
    '''
values = [2] 
connsql = a.sql_connection()
cursor = connsql.cursor()
cursor.execute(Query, values)
revisionsID = cursor.fetchone()
connsql.commit()
cursor.close()
connsql.close()

Exception:

  File "TMSWorkflow.py", line 43, in CreateRevisions
    revisionsID = cursor.fetchone()
  File "C:\Users\adminuser2\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\adodbapi.py", line 922, in fetchone
    result = self._fetch(1)
  File "C:\Users\adminuser2\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\adodbapi.py", line 898, in _fetch
    self._raiseCursorError(api.FetchFailedError, 'fetch() on closed connection or empty query set')
  File "C:\Users\adminuser2\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\adodbapi.py", line 562, in _raiseCursorError
    eh(self.connection, self, errorclass, errorvalue)
  File "C:\Users\adminuser2\AppData\Local\Programs\Python\Python35\lib\site-packages\adodbapi\apibase.py", line 57, in standardErrorHandler
    raise errorclass(errorvalue)
adodbapi.apibase.FetchFailedError: fetch() on closed connection or empty query set
user2961127
  • 963
  • 2
  • 17
  • 29
  • did you check if you're able to connect to DB from Python? and also if the query is returning any results directly in the DB? – django-unchained Mar 20 '20 at 00:43
  • yes, the query does gives results in DB. Also, i am able to connect to db using above code if it's just basic insert operation. – user2961127 Mar 20 '20 at 00:51

0 Answers0