INSERT & SELECT data in same recordset

[Eric1977]

I started with a procedure, with INSERT data and SELECT inserted data_id, and it didn't work with Labview.

---

No, you tried to combine two SQL statements into the Execute Query VI. That is not allowed. If you had separated them into two different statements with two seperate Execute Query VIs, it would have worked.

If you tried it in SSMS first, it would work because SSMS is smart enough to know they are seperate statements. The Execute Query VI does not.

---

INSERT INTO [dbo].[USERS] ([User_Name])
OUTPUT INSERTED.[USER.ID]
VALUES (@User)

---

Glad you found a solution and I also learned something. You got a kudo from me as well.

[DavidBoyd]

Yes, SQL Server has the convenient OUTPUT INSERTED, OUTPUT UPDATED, and OUTPUT DELETED clauses.  Other flavors of SQL may support them too but I couldn't say.

I'm of the opinion that no production code should have SQL language built-on-the-fly living on LabVIEW block diagrams; take the time to create stored procedures and then call them via the "Create Parameterized Query" support in the DB toolkit, providing any parameters, and processing output and return params, and returned recordsets, as needed.

Stored procedures document well, insulate the caller from the table schema, execute faster because they are compiled (once) into a query plan by SQL Server rather than interpreted on each call, manage datatypes natively (rather than turn everything into strings), and are immune to SQL injection.  

Anything short of that effort (IMHO) is a hack that will likely bite you someday.  In a controlled environment you shouldn't have direct access to tables anyway.

If you're interested, several past posts of mine discuss the limits of the DB toolkit (one actual bug with a workaround), and a proper way to manage SP calls.

Dave

[rolfk]

Definitely agree with David. Direct table access in a production system is a hack! It couples your application very tightly with your database. A single renaming of an element can be enough to make your statements suddenly fail.

Stored Procedures can insulate the application from this. As long as the SP  interface stays the same, you csn totally restructure your database and it still works. And the improved performance since any sorting, filtering and whatever happens natively on the server is also nice.

The only drawback of SPs is that they have syntactical SQL differences between servers. But even for basic SQL statements you quickly run into differences that make swapping out SQL servers without query modifications impossible, for anything but very basic databases.

As to the original problem it actually would work but the query generates two separate recordsets. One for the INSERT statement and one for the SELECT statement. But the Database Vis don’t iterate themselves over recordsets they simply return the first empty one that resulted from the INSERT. And that recordset isn’t completely empty but contains the result from that query which also indicates if it was successful.

it’s still very inefficient even if you would modify the Database VIs to iterate over all available recordsets.

[RamonG]

I almost forgot I made this:

It works with SQL Server, but the previous record set reference probably needs to be closed when switching to the next.

[DavidBoyd]

I'm going to leave two links here to prior discussions involving queries which return more than one recordset (i.e., contain more than one SELECT, or some other multi-statement block), because I'm tired of hearing "LabVIEW can't do that".  Yes, of course it can.  Or at least, the DB toolkit contains a VI to support this process.  Unfortunately, as shipped (since at least the first release ca. 2006!), it contains a logical flaw.  If you want to follow the details (and accept two modified toolkit VIs from me for the fix), read into this thread (and perhaps follow the link in its text for more background):

Solved: Re: SQL query does not return results - NI Community

Also, if you want a fairly lengthy description of how to call a stored procedure, capture any output and return params, and any recordsets it returns (using the "fixed" toolkit VIs referenced above), follow this link too:

Re: Database Store Procedure as Query to Get Return Value - NI Community

Dave

[Discrusio]

Thx for all the respons.

I have a lot to learn in this first SQL assignment.

My goal is to create a Labview program to import some old testreports with measurements, and store them in a SQL-db.

My plan os to make two procedures on the SQL-db.

The first with UUT-SN,ProductID,User,Status,Time and so on input, and a record_id output (for use in the below procedure).

The second to insert a single measurement with all that necessary data, inclusive a FK_ID to the above procedures entry.

[Eric1977]

Pretty neat project/assignment you have there. I have no doubt you'll succeed.

Lean on us for things you get suck on. We are here to help but put some effort into trying first before asking. Also, make every intention of uploading your VI, not just an image. Your VI will tell us so much more than just a snippet will.

Just a few helpful reminders that will help us.