[DB-SIG] ADO database connection seems to be closing

[Chris Curvey]

Robert Brewer wrote:

>Chris Curvey wrote:
>  
>
>>I'm trying to get to an MS-SQL datbase via the 
>>win32com.client library 
>>and the ADO library, but I seem to lose the context of the connection 
>>whenever I execute a statement.  For example, the first 
>>"assert" below 
>>is OK, but the second one is not.  I'm also losing the value 
>>of @@identity.
>>
>>Is this a known issue?  I was hoping for a DB-API 2.0 interface to 
>>MS-SQL, but I can't get my clients to pay for an mxODBC license.
>>
>>import unittest
>>from win32com.client import Dispatch
>>
>>class TransactionTest(unittest.TestCase):
>>    def testConnection(self):
>>	conn = Dispatch('ADODB.Connection')
>>	conn.Open("Driver={SQL
>>Server};Server=mxdev;UID=chris_dev;PWD=chris;DATABASE=tempdb")
>>
>>	# start a transaction 
>>	conn.execute("begin tran")
>>
>>	result = conn.execute("select @@trancount")
>>
>>	while not result.EOF:
>>	    # this test passes
>>	    assert result.Fields.Item(0).Value == 1
>>	    result.MoveNext()
>>
>>	result = conn.execute("select @@trancount")
>>	while not result.EOF:
>>	    #this test fails
>>	    assert result.Fields.Item(0).Value == 1
>>	    result.MoveNext()
>>
>>
>>##############################################################
>>##########
>>if __name__ == "__main__":
>>    unittest.main()
>>    
>>
>
>Two ideas:
>
>1. Have you tried result.Close() before the second conn.execute()?
>
>2. I use a new ADODB.Recordset object, which works for me:
>
>res = win32com.client.Dispatch(r'ADODB.Recordset')
>res.Open(query, conn, adOpenForwardOnly, adLockReadOnly)
>data = []
>if not(res.BOF and res.EOF):
>    data = res.GetRows()
>    # Convert cols x rows -> rows x cols
>    data = zip(*data)
>res.Close()
>
>
>Hope that helps!
>
>
>Robert Brewer
>MIS
>Amor Ministries
>fumanchu at amor.org
>  
>

Yup, adding "result.Close()" made it all better!  thanks!