I was getting the ADO error below when I ran an update statement and then tried access the eof attribute of the supposed returned record set.
The answer is to to use the execute method of your connection object instead. Here’s how I now do it in Python:
def update_query(sql,dsn=GM_DSN):
conn = win32com.client.Dispatch(r’ADODB.Connection’)
conn.Open(dsn)
rows_affected=conn.Execute(sql)[1]
conn.Close()
return rows_affected
And for reference, here’s how I run select queries (i.e. when I do expect results):
def query(sql,dsn=GM_DSN):
conn = win32com.client.Dispatch(r’ADODB.Connection’)
conn.Open(dsn)
rs = win32com.client.Dispatch(r’ADODB.Recordset’)
rs.Open(sql, conn, 1, 3)#The 1 and the 3 are constants for adOpenKeyset and adLockOptimistic
flds=[]
for i in range(rs.Fields.Count):
flds.append(rs.Fields.Item(i).Name)
rows=[]
while not rs.eof:
row=[rs.Fields(fld).value for fld in flds]
rows.append(row)
rs.MoveNext()
conn.Close()
return flds,rows
If you want to run with ADO and Python, then read this wonderful guide. If I had read that, then you wouldn’t be reading about my error now
And here’s a good resource for getting to know ADO in general.
Here is the error I was getting when running an update statement though the second query function:
while not rs.eof:
File “C:\Python24\lib\site-packages\win32com\client\dynamic.py”, line 484, in __getattr__
raise pythoncom.com_error, details
com_error: (-2147352567, ‘Exception occurred.’, (0, ‘ADODB.Recordset’, ‘Operation is not allowed when the object is closed.’, ‘C:\\WINDOWS\\HELP\\ADO270.CHM’, 1240653, -2146824584), None)
[tags]ADO, Python, ADODB, win32com, ADODB.Recordset, ADODB.Connection, ActiveX Data Objects, SQL, Queries[/tags]
Posted by Greg Pinero (Primary Searcher) on Apr 19th, 2006 and is filed under win32.
Both comments and pings are currently closed.
Add this post to Del.icio.us -Digg
3 Responses to “ADO Confusion”
1. Robert Brewersays: April 19th, 2006 at 6:28 pm Tip: I found MoveNext() and Item() to be about 3x slower than GetRows(). See http://projects.amor.org/dejavu/browser/trunk/storage/storeado.py
2. rohit says: September 22nd, 2006 at 1:19 am select employees name,hiredate and day from employees table.generate a query order by day “monday”.
what is the suitable query for it!
3. Greg Pinero (Primary Searcher)says: September 22nd, 2006 at 9:40 am Thanks for the tip, Robert. I’m going to try that out.
Rohit, I’m not sure what you mean by order by “Monday”? Otherwise it sounds like a simple query.