Win32 Com + ADO: How to compare the result of a recordset to 'nothing'

[Felix McAllister]

Hi,
When using win32com.client, how do you test for a 'nothing' com object as you can in VB? I have an example here when using ADO to loop over multiple recordsets returned from a query. I get the following error:

Traceback (most recent call last):
File "C:\dev\python\MySamples\dbtest.py", line 14, in ?
rs.MoveFirst()
File "C:\Python23\lib\site-packages\win32com\client\dynamic.py", line 460, in __getattr__
raise AttributeError, "%s.%s" % (self._username_, attr)
AttributeError: <unknown>.MoveFirst

I'm assuming that after the result of rs.NextRecordSet is invalid somehow (it shouldn't be, BTW, as a similar loop runs fine in VB).

In Perl, I'd just use : if (defined($rs)....

Any help appreciated,

Felix.

The code is as follows:
###########################3
import win32com.client
conn = win32com.client.Dispatch("ADODB.Connection")
conn.ConnectionString = "Driver={SQL Server};Server=(local);Database=Test;Trusted_Connection=yes;"
conn.Open()
rs = conn.Execute("TestSPXML")[0]
xmlString = ""
while rs != None:
rs.MoveFirst() # FAILS HERE ON THE SECOND ITERATION OF THE LOOP
while not rs.EOF:
xmlString = xmlString + rs.Fields[0].Value
rs.MoveNext()
rs = rs.NextRecordSet()
print xmlString
conn.Close()

 

[Bob Gailer]

[snip]
The code is as follows:
###########################3
import win32com.client
conn = win32com.client.Dispatch("ADODB.Connection")
conn.ConnectionString = "Driver={SQL
Server};Server=(local);Database=Test;Trusted_Connection=yes;"
conn.Open()
rs = conn.Execute("TestSPXML")[0]
xmlString = ""
while rs != None:
rs.MoveFirst() # FAILS HERE ON THE SECOND ITERATION OF THE LOOP
while not rs.EOF:
xmlString = xmlString + rs.Fields[0].Value
rs.MoveNext()
rs = rs.NextRecordSet()
print xmlString
conn.Close()

I am learning how to use ADODB with SQL Server, so your example is very
timely. I know that I have to change the ConnectionString, as I get this
error when running your example: "'Microsoft OLE DB Provider for ODBC
Drivers', '[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server
does not exist or access denied.'"

What do I have to change?

Bob Gailer
(e-mail address removed)
303 442 2625

 

[Felix McAllister]

Bob,
You need to change the (local) part of the connection string to a SQL Server instance on your machine or network. You might also have to change the Trusted Connection=yes part to use a SQL Server login name and password, if that instance isn't set up for trusted connections.
Felix.

 

[logistix at cathoderaymission.net]

Hi,
When using win32com.client, how do you test for a 'nothing' com object as you can in VB? I have an example here when using ADO to loop over multiple recordsets returned from a query. I get the following error:

Traceback (most recent call last):
File "C:\dev\python\MySamples\dbtest.py", line 14, in ?
rs.MoveFirst()
File "C:\Python23\lib\site-packages\win32com\client\dynamic.py", line 460, in __getattr__
raise AttributeError, "%s.%s" % (self._username_, attr)
AttributeError: <unknown>.MoveFirst

I'm assuming that after the result of rs.NextRecordSet is invalid somehow (it shouldn't be, BTW, as a similar loop runs fine in VB).

In Perl, I'd just use : if (defined($rs)....

Any help appreciated,

Felix.

The code is as follows:
###########################3
import win32com.client
conn = win32com.client.Dispatch("ADODB.Connection")
conn.ConnectionString = "Driver={SQL Server};Server=(local);Database=Test;Trusted_Connection=yes;"
conn.Open()
rs = conn.Execute("TestSPXML")[0]
xmlString = ""
while rs != None:
rs.MoveFirst() # FAILS HERE ON THE SECOND ITERATION OF THE LOOP
while not rs.EOF:
xmlString = xmlString + rs.Fields[0].Value
rs.MoveNext()
rs = rs.NextRecordSet()
print xmlString
conn.Close()

Recordsets have a .BOF property that is similar to .EOF, but indicates
that your cursor is before the first record. If both .BOF and .EOF
are true, you have a null recordset. So something like:

if not(rst.BOF and rst.EOF):
rst.MoveFirst()

should work.

 

[Graham Breed]

Recordsets have a .BOF property that is similar to .EOF, but indicates
that your cursor is before the first record. If both .BOF and .EOF
are true, you have a null recordset. So something like:

if not(rst.BOF and rst.EOF):
rst.MoveFirst()

should work.

Maybe should, but frequently doesn't. Although the books tell you to do
this, I've never found it to be any use. SQL Server, at any rate,
always sets the cursor to the first record, so rst.EOF is enough to test
for no records. The problem comes when nothing is returned, which is
different to no records being returned. (In Query Analyser, you see
nothing, instead of column names with nothing underneath.) In that
case, I find checking for rst.State==1 does the trick. I think BOF
fails the same way as EOF in this case (if the record set's closed, it's
an error to even ask where the cursor is).

From the error message, I don't think this is the OP's problem, though.
You would see something telling you not to do that on a closed record
set. I suggest poking the object in an interpreter, and looking in
pywintypes, if nobody has any better ideas.


Graham

 

[Giles Brown]

Hi,
When using win32com.client, how do you test for a 'nothing' com object as you can in VB? I have an example here when using ADO to loop over multiple recordsets returned from a query. I get the following error:

Traceback (most recent call last):
File "C:\dev\python\MySamples\dbtest.py", line 14, in ?
rs.MoveFirst()
File "C:\Python23\lib\site-packages\win32com\client\dynamic.py", line 460, in __getattr__
raise AttributeError, "%s.%s" % (self._username_, attr)
AttributeError: <unknown>.MoveFirst

I'm assuming that after the result of rs.NextRecordSet is invalid somehow (it shouldn't be, BTW, as a similar loop runs fine in VB).

In Perl, I'd just use : if (defined($rs)....

Any help appreciated,

This is just a guess, but is it anything to do with NextRecordset
returning a tuple (recordset, records affected)?

(Don't think that should end up being wrapped in a
win32com.client.dynamic
wrapper though?)

You could try putting some print statements into
win32com.client.dynamic
to see exactly what you're getting back.

Good luck,
Giles Brown

 

[Felix McAllister]

Thanks to all who replied to my posting.

There were a number of things wrong with my code.

1. Giles Brown was correct in stating that the NextRecordset method returns a tuple. I should have seen this in the debugger when I printed the value out:
[Dbg]>>> rs.NextRecordset()
(<COMObject NextRecordset>, -1)

2. There was a typo in the call to NextRecordset - I had "NextRecordSet" [capital S]. I didn't know that case mattered.

The correct loop code is as follows:
while rs != None:
rs.MoveFirst()
while not rs.EOF:
xmlString = xmlString + rs.Fields[0].Value
rs.MoveNext()
rs = rs.NextRecordset()[0]


Felix.