Can I retrieve data from a multiple select query?

[SciManStev]

I recently have been able to consolidate many queries into one large one, or into large compound queries. I found an instance where I am using a select query in a for loop that may execute up to 400 times. I tried building a huge compound query, and using DB Tools Execute Query vi, followed by the DB Tools Fetch Recordset Data vi. The query executes at the database without an error, but the Fetch Recordset Data vi only returns the first instance. Is there a way to retrieve all the data without having to send up to 400 separate select queries?

[sarahk]

Hi, SciManStev.

Can you tell me a little more about the query and what kind of data you're expecting? I haven't encountered this issue with the RFetch Recordset Data VI, so I'd like to first determine what sort of data we're expecting vs. what sort of data we're receiving.  What query are you making?

Thanks! Have a nice evening.

[SciManStev]

Sorry I didn't replt earlier, I was on vacation. The query I am using is to check serial numbers, and determine if they are all valid. The programs purpose is to define a serial number to a pre-existing part number. Our company makes inclinometers and accelerometers, and this entire series of LabVIEW programs is designed to automate the calibration and testing of these units. The part number definitions can contain 3 or 4 hundred parameters, so the database itself consistes of 44 tables with potentially several hundred columns per table. It is designed to not only provide definitions to every part number, but also to store all potential raw unit data to be calculated and formed into a report at any time. The logistics of getting that much data in and out of the database have forced me to do things more effeciently. The actual query in question is to take each serial number either manually entered, or automatically picked, and see if they already exist with the part number they are being defined as. If there are any duplicates, then the program will alert the operator that serial numbers x, y, and z for instance have already been asigned as the part number in question. Currently I run a simple query once for each serial number. This works, but there may be 200 serial numbers assigned. Also the serial numbers can contain upper or lower case letters. By making all the serial number letters into capitals, then into lower case, it could mean up to 400 individual queries going out over the LAN. This is a bandwidth hog, and time consuming. I started experimenting with compound queries. The actual query used is below.

SELECT SERIALNO FROM "maintable" WHERE PARTNO = '475196-001' AND SERIALNO = '3000005';SELECT SERIALNO FROM "maintable" WHERE PARTNO = '475196-001' AND SERIALNO = '3000006';SELECT SERIALNO FROM "maintable" WHERE PARTNO = '475196-001' AND SERIALNO = '3000007';SELECT SERIALNO FROM "maintable" WHERE PARTNO = '475196-001' AND SERIALNO = '3000008';SELECT SERIALNO FROM "maintable" WHERE PARTNO = '475196-001' AND SERIALNO = '3000009'

When I execute this query, SQL Server 2000 has no problem with it, but the DB Tools Fetch Recordset Data vi only returns the first match. I think my answer may lie with OR statements. Rather than sending what amounts to potentially dozens of individual queries, I should be able to chain them into one query with a lot of OR statements. As long as the OR statement is not an exclusive OR statement, I think it should work. I haven't tried it yet, and it may take some time to get the syntax right. The query is built in a for loop with the number of iterations equal to the number of serial numbers being defined. Once I get this working I will alter it to include both upper and lower case letters that can be included in the query. Any suggestiona of how the query should be structured would be most helpful, or another way to achieve what I am trying to accomplish.

SciManStev

[SciManStev]

I was able to build a query that solved my situation. It will work regardless of how big the query is, or at least within the limits of SQL Server 2000.

SELECT SERIALNO FROM "maintable" WHERE PARTNO = '475196-001' AND SERIALNO = '3000000' OR PARTNO = '475196-001' AND SERIALNO = '3000001' OR PARTNO = '475196-001' AND SERIALNO = '3000002' OR PARTNO = '475196-001' AND SERIALNO = '123321' OR PARTNO = '475196-001' AND SERIALNO = '456654'

All matching data is retrieved, and my efficiency increased over 1000%. In searching for this solution I uncovered more programmimg that needed updating. I wrote the bulk of these programs 4 years ago, and have learned a lot since. It seems that there is always a more efficient way to do just about anything.

SciManStev

[sarahk]

Hi, SciManStev.

I'm glad you got it working. And thanks for posting the solution -- I had just begun doing some research into this myself, so I appreciate you letting me know you found an answer. (And it will help others who may encounter the issue!)

Have a nice afternoon!

[Lynwood]

Hello!

Pardon me for reactivating an old thread, but my problem is (I believe) highly related to the original question in this thread.  I have searched for a couple of hours so far and not found a good way to do something that I believe must be a very common problem.

I am using LabVIEW 2009 and SQL Server 2008. 

I want to insert a new row into a table, then get the primary key value (id) of the new row, which was created automatically as an auto incrementing integer.  I want to get it SAFELY.  I do not want to execute an insert, then execute a select to get the largest id in the table in two separate LabVIEW operations, because in the time between the two statements being executed another DB user could have added another row to the table.  If this happened, I would get the wrong id when I ran my select statement.  In other words, getting the id in this manner creates a race condition.

What I REALLY want to do is use DB Tools Execute Query to issue the INSERT statement, then use DB Tools Fetch Recordset to get a handle to the newly inserted row.  Alas, I found that the recordset returned after execution of the INSERT statement is empty, even though the INSERT command executed correctly on the DB side.

So I decided to try a compound SQL statement like so:  "insert into table1 (field1) values (42); select top 1 id from table1 order by id desc".  The above syntax works perfectly when executed in SQL Server Management Studio.  It performs the insert, then displays the largest ID in the table.

This statement is less dangerous than running these statements separately from LabVIEW because the time between their execution is nearly zero, so doing it this way would be acceptable.  However, when I use DB Tools Execute Query to issue this statement, I get the empty recordset again, just like when I used it to issue the INSERT statement by itself.  However, if I just run the SELECT statement by itself without the INSERT, I get a good recordset back.

Which brings me back to the topic of this thread.  I am using DB Tools Execute Query to execute a compound SQL statement that the DB understands and processes correctly.  But the result set only contains what was returned by the first part of the compound SQL statement.

It seems to me it would make a lot more sense for the result set of the LAST statement executed to be returned, rather than the first.  It looks like a bug to me.  I know that I can work around this problem by writing a stored procedure, but that's pretty gross for doing something so simple.

Does anyone have any ideas on other ways to work around this problem?

Thanks,

Lynwood Hines