The following SQL query is a simplified test to use a temporary table.
When I ran this query from the 'SQL management studio', I could get the correct results.
However when I ran it in LabView, no results were returned.
If I took the temporary table out of the query, it worked fine in LabView.
Anybody has idea what I did wrong in the first query with temporary table? I appreciate your thoughts and help.
Solved! Go to Solution.
Spelling error? Was there an error message?
In your pictures you have.. "Select .. into #termp" then "Select * from #temp".
Both valid statements, but not likely to work as intended.
Thanks for pointing the spelling error out. It is absolutely a spelling error.
I corrected the spelling error and ran the query again. It runs without any error, but does not return any data. Feels to me that this is a LabView database toolkit issue.
Temporary tables aren't supported according to..
But you can use stored procedures..
Hope that helps.
This is applies to MS SQL server, but may apply to others in a similar way, too.
If you execute two SELECTs in a single query, you will get two results, each followed by some statistics about the query, which also mark the end of the current result.
See for example this screenshot from my DB client "DBeaver" :
(OK, this is boring, two times the same query and the same result, but yet, two results)
The point is, when the first query does not return anything, there is still an empty result plus statistics sent back to the client.
Most clients suppress empty results automatically, and only display non-empty results.
Labview... does not. Instead, it stops reading the result when it recognizes the first statistics information.
The solution is to disable those statistics by sending SET NOCOUNT ON; at the beginning of the query:
I read that webpage about 'temp table not supported in LabView'.
On the other end, 'Set NOCOUNT ON' solved the issue that I had.
so temp table is now supported in LabView??
If you read those links carefully, you'll read many complaints that such queries simply don't work, and somewere someone writes that NI has somehow confimed that it is somehow not working.
Finally, people say "that's not supported".
Now, the query text is sent to the DB. Labview has no idea what this text does, and it does not need to. It also has no idea what the result should look like, and simply waits for a table coming back. So, how should LV know that the query contains temp tables and decide not to process that? The only reason is that the data returned from the DB contains something unusual.
I'd even say it's not a bug, it is more of a missing feature that LV can not read multible results or ignore emty ones. Yet, one should know that there is an issue...
There actually is support in the DB toolkit for queries/stored procedures which return multiple recordsets, including in the general case where those recordsets may 1) have different column counts and 2) some recordsets may be empty.
Under the "Advanced" subpalette, look for the polymorphic VI "DB Tools Fetch Next Recordset.vi". It's intended to be used after a "Fetch All" call (you call the execute VI only once, before the first fetch, and only call the free VI at the end).
Unfortunately, the "Fetch Next Recordset" VI has a fatal flaw as shipped - when there are no more recordsets, it returns an error AND leaks the recordset refnum. This makes it nearly useless unless you know in advance that you're going to get exactly N recordsets and only call Fetch Next Recordset N-1 times. If you're writing a more generalized routine, you must modify the toolkit VI.
Read my reply in this other thread for more info: Fetch Next Recordset flaw and discussion
I ended up modifying the toolkit VI, which I am loath to do, but this really needs to be fixed. Since the code is polymorphic, I fixed it for the Recordset (R) and the Command-Recordset (CR) instances (one calls the other). In addition to preserving the ADO recordset refnum in the GOOP repository, the method also now returns an "EOR" boolean which is essential for iterative use. I've attached my modified files.
My code notes for how to intelligently call a stored procedure and accept any number of recordsets (and, bonus, get their column names!), plus returned parameters, are pasted below:
Call DB Tools Create Parameterized Query, with flags to indicate SP, and autodetecting parameters
Iterate/filter the return param list to build a list of output params (and possibly an SP return value)
Set the parameter values
Execute the query
Test for nonzero column count on the returned recordset
While column count is nonzero
Fetch the recordset column names
Fetch all the data (as 2-D OLE variant array)
Move to the next recordset (exit while loop if EOR flag TRUE)
Free the command-recordset reference
Read return parameters (if any) as variant and assign their names
Free the command reference
Hope someone finds this useful.
Thanks very much for sharing all this info.
That's why we come here. One engineer's struggle might save another engineer's day. Thank you.