DB Tools Fetch Next Recordset

[10Degree]

I am having trouble iterating through the set of recordsets returned from a query that returns multiple tables.  I am using the DB Tools Fetch Next Recordset.vi in a loop to iterate through recordsets until they have all been processed.  One problem I have is that there is no indication that you have iterated past the end of recordsets.  DB Tools Fetch Next Recordset does not produce an eof type indication like you get when iterating through records in a recordset.

My code is attached:

1.  Open a database and execute a query that returns more than 1 table (SELECT * FROM tblA SELECT * FROM tblB)

2.  Fetch the current recorset data and store in array.

3.  Use DB Tools Fetch Next Recordset to move to the next recordset.

Question:  What is the stop condition to test for?

Errors propogate through to the close vi which reports:

Error 97 occurred at Rec Get Recordset Properties (R).vi->Rec Fetch Recordset Data (R).vi->Untitled 1

Possible reason(s):
Unknown System Error in Rec Get Recordset Properties (R).vi->Rec Fetch Recordset Data (R).vi->Untitled 1

Any help or example for navigating recordsets?

Thanks,

-cb

[Captain_Kirby]

Hi,

I believe you can still use the "DB Tools Get Properties" Vi to return either the number of recordsets, or an end of file condition (meaning you have reached the end of your recordsets.) This VI returns different parameters in its output cluster depending on the reference passed to it. Passing it a single recordset refrence, or a group of recordsets, should return the same data elements, and you can poll these elements in a loop to determin the stop condition.

I would still suggest experimenting with this VI, as I have not implemented it myself, and so I dont know exactly what you will get in the properties cluster.

[gperezr]

Did you find the answer?

[mikeporter]

This is a 5 year old thread. Are you having a similar issue? What is the problem you are experiencing?

Mike...

[gperezr]

Hi, 

I have a sql query in labview 2011 and i receive a lot of recordsets from it, i can group all records in arrays but i have the same error at the last cycle (i think is normal because it lost each recordset when i use fetch next recorset so the last cycle is consider like EOF and BOF at the same time) but i have other issues, when i tried to free the connection it appear error code 1045 about Rec Destroy and using the execution trace toolkit there are a lot of References Leak, i think maybe one per cycle.

My results are the expected but i have this problems.

I did a very simple example to reproduce my problems, it can use any sql db connection because it doesnt query colunms or  data and presents  the same things.

Thanks for your time

[mikeporter]

So you are executing a procedure, not performing a SQL query. It would be easier, more efficient and more reliable if you did use standard SQL to fetch your data. The memory leak makes sense. If the DCT isn't closing recordsets you will be leaking memory all over the floor.

It looks like you are doing multiple queries inside the procedure when all you really need to do is one query that will return all the data in one shot.

Mike...

[gperezr]

I tried using a temporal table for join all records, but using it i dont have results in labview

[mikeporter]

Well, why go to all that complication? If simple works, simple is better. Remember, someday someone (perhaps even you) will need to maintain this code. To do that they (you) will need to be able to read and understand it.

Mike...

[Sectoid]

DB Tools Get Properties VI works correct only if Parameterized Query used. Get Properties VI should have command-recordset reference on input. If it have recordset reference on input, it reports an error after last recordset read.

[DavidBoyd]

I know, I know, it is an old thread, but it's still the most appropriate.  For all the years I've used the database toolkit, I'm surprised I haven't run into this earlier.

I execute a parameterized query and the stored procedure returns more than one recordset, so of course they can have different column formats, and must be fetched independently.  Ah, the DBCT has a "DB Tools Fetch Next Recordset" VI which has an instance for a command-recordset object.  But it's fatally flawed - if the caller writes a general loop to iterate all the recordsets, retrieving all the records (and column names!) from each, there's no good way to test for end-of-recordsets.  The ADO help online for the NextRecordset Method says:

As long as there are additional results and the Recordset containing the compound statements is not disconnected or marshaled across process boundaries, the NextRecordset method will continue to return Recordset objects. If a row-returning command executes successfully but returns no records, the returned Recordset object will be open but empty. Test for this case by verifying that the BOF and EOF properties are both True. If a non-row-returning command executes successfully, the returned Recordset object will be closed, which you can verify by testing the State property on the Recordset. When there are no more results, recordset will be set to Nothing.

---

But the code in Rec Fetch Next Recordset (R).vi doesn't evaluate the NextRecordset reference, it just blindly replaces the present reference in the GOOP repository.  Oops, reference leak, and mayhem for anything downstream that attempts to use the recordset.

I had wanted to exit the iteration by testing for the existence of a zero count of fields returned by Get Recordset Properties.  I see that won't work - the damage is done simply by moving past the last valid recordset.

Anyone else have a workaround for this?  My next option is to modify the toolkit (I really hate to do this) to test the returned reference for null/nothing and if so, preserve the existing one and return a "non found" flag in place of a runtime error.

Dave