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]
_______________________________________________
DB-SIG maillist - [email protected]
http://mail.python.org/mailman/listinfo/db-sig
