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

Discussion in 'Python' started by Felix McAllister, Sep 15, 2003.

  1. 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()
     
    Felix McAllister, Sep 15, 2003
    #1
    1. Advertising

  2. Felix McAllister

    Bob Gailer Guest

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

    At 07:53 AM 9/15/2003, Felix McAllister wrote:
    >[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

    303 442 2625


    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.506 / Virus Database: 303 - Release Date: 8/1/2003
     
    Bob Gailer, Sep 15, 2003
    #2
    1. Advertising

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

    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.
     
    Felix McAllister, Sep 15, 2003
    #3
  4. "Felix McAllister" <> wrote in message news:<tvj9b.31786$>...
    > 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.
     
    logistix at cathoderaymission.net, Sep 15, 2003
    #4
  5. Felix McAllister

    Graham Breed Guest

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

    logistix at cathoderaymission.net wrote:

    > 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
     
    Graham Breed, Sep 15, 2003
    #5
  6. Felix McAllister

    Giles Brown Guest

    "Felix McAllister" <> wrote in message news:<tvj9b.31786$>...
    > 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
     
    Giles Brown, Sep 15, 2003
    #6
  7. Re: Win32 Com + ADO: How to compare the result of a recordset to'nothing'

    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.
    > 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()
     
    Felix McAllister, Sep 16, 2003
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Developer
    Replies:
    0
    Views:
    485
    Developer
    Aug 11, 2003
  2. nita
    Replies:
    1
    Views:
    880
    Saravana
    Nov 20, 2004
  3. Hung Huynh
    Replies:
    8
    Views:
    313
    Bob Barrows
    Sep 24, 2003
  4. Michael Tan
    Replies:
    32
    Views:
    999
    Ara.T.Howard
    Jul 21, 2005
  5. Devin Jeanpierre
    Replies:
    2
    Views:
    470
    Devin Jeanpierre
    Feb 14, 2012
Loading...

Share This Page