SQL toolbox fetch and fetch all show different behaviour

[Dan_55]

Hi,

I have been working witht SQL toolkit in Labview 2009 and have come across an oddity where commands return a different value when called with fetch (DB tools fetch element data) or with fetch all (DB tools fetch recordset data). Specifically, I am trying to get the current value of an identity column from the existing database. When I use the fetch all, I get a variant array of the correct size but it is blank. When using the fetch command for the first element, it correctly returns the value. This does not seem to happen on any other select statements I have used. I have included the code snippet below. I would like to know if anyone knows what other SQL statements may be subject to this difference. I was also wondering what is the best practice with regards to the connection reference, in my application I open a connection reference once and pass this into a shift register and reuse this reference until the application closes. Each individual recordset reference is opened and then closed for each query. Is this the best method of handling the references?

[rsw]

Hi Dan,

I believe you need to use the "Database Variant to Data" VI that converts this generic data type to the one you need to read, from this you should get an array of (in your case) strings with all the information enclosed.

Kind Regards, 

[Dan_55]

Hi Rob,

I tried using the SQL variant to data but the problem is that the variant is empty. I have attached a shot of the returned variant array to illustrate. I have been looking at a number of SQL command strings and some work equally with fetch element and fetch all whilst others perform differently. For example,

"Select IDENT_CURRENT('ID_Column')" only works with fetch single while

"Declare @myID int

set @myID=IDENT_CURRENT('ID_Column')

Select @myID" works equally well with both of them.

[ktian]

Can you please give your connect string and database structure to reproduce this problem.

It would be better if you can attach your test VI too, thanks.

[ktian]

I suggest you convert the element of variant array to string, you will see the variant is not actual "empty".

I attached an example VI to show it.

BTW, convert a whole array of variant to data directly will be supported in LV2010.

[Yamaeda]

Remember that Fetch All always return a 2D array. So it should be the same result if you insert a Index Array 0,0 after the Fetch All.

To avoid timing issues (atleast now while debugging) you can try to place the single Fetch after the Fetch all, so it's not a parallell reading causing it.

I also reuse the connection ref through all of my program and open and close querys as much as possible.

/Y

Message Edited by Yamaeda on 02-04-2010 02:14 PM