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

[Robert Brewer]

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