Re: [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
[EMAIL PROTECTED]

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

_______________________________________________
DB-SIG maillist  -  DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig