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
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.
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.