Has anyone ever succeeded to use the scope_identity() from the LabVIEW DB toolkit ?

[aSquared]

Hi,

Using LabVIEW 2014 DS, and MSSQL 2008R2.

I have configured a table with identity, so the indexed will be incremented automatically.

I am trying to get the ID of the last Insert command that I am using.

I am trying to SELECT SCOPE_IDENTITY() but with no success.

I read about trying to so it with a procedure or transaction, but I just could not succeed myself.

Has anyone ever succeeded to use the scope_identity() from the LabVIEW DB toolkit ?

If yes, can you please elaborate more or even share the code ?

Thanks, 

Amitai.

[tst]

I haven't used scope_identity, because I usually prefer stored procedures for something like this, as they're generally safer when you have multiple connections. In the SP you can do a SELECT at the end to return the data you want. To call the SP from LV you can execute a query like: "SP param1=A,param2=B", where SP is the procedure name, paramN is the parameter name and A is the parameter value.

There are also certain things which don't work from outside SQL Server. It's possible that scope_identity is one of them. I expect a search should reveal an answer.

[Sam_Sharp]

Yes I have...let me dig out some code...

I do my INSERT query (with a semicolon at the end) and in the same query string do a "SELECT SCOPE_IDENTITY() as inserted_id;" - essentially what you're doing here is running two queries in one - one to insert your data and the second to retrieve the ID. From what I remember of when I did this (last year?), I think scope_identity() is valid for the same connection so as you're doing both queries in the same connection it should always be valid.

I then do this to get the ID (pardon the crappy VI snippet):

Essentially I execute the query and the result is a 2D array that contains one row/column which is your last inserted ID.

[aSquared]

Hi Sam and tst, thanks for your reply.

Sam, I have tried your suggestion with no success, it seems that the first query is getting the select reference.

(try running 2 select in one labview sql query and see that only the first one is shown)

What I get is an empty array. (I start with an Insert and then a Select)

tst, I found several posts that claimed to succeed using Procedures or Transactions, but I haven't succeeded to reproduce it.

I am starting to question myself if it is possible to use SCOPE_IDENTITY(); from the LabVIEW DB toolkit.

[mikeporter]

What exactly is the SQL that you're executing? Are you getting any errors?

Mike...

[Sam_Sharp]

Can you show the query/code that you're using? It definitely worked for us so I'm not sure what might be causing you issues.

[ssjunge]

If you perform an INSERT query and add SELECT SCOPE_IDENTITY() you are actually performing two queries. This results in two recordsets with the last recordset containing the ID you ask for. You will need to use Fetch Next Recordset to retrieve the recordset with the ID.

[The_Seeker]

Hi Mike,

I am also seeing issues with the scope_identity() capability. (This particular project is LV 2018. The database is MS-SQL.) Like the original posting, I am getting no parameters returned.

To test it in the simplest form, I tried scope_identity() on a LabVIEW sample. In this case, I am getting an error from the LV DB Toolkit. (See attached image.)

It appears that some people have gotten this to work, while other (like me) are missing something. Can you provide any guidance?

Thanks, 

-- Dave

[DavidBoyd]

I have zero doubt that this is possible, and I'd like to help you get the result you're looking for.  First question, though:  you say you're using MS SQL Server, but your error message implies you're using MS-Jet, which is the driver for an Access DB.

Which is it?  Capabilities aren't the same.

Dave

[The_Seeker]

Hi Dave,

Yes, sorry for the confusion. 

The application is MS-SQL, and this was the first attempt. Since this was part of a larger application, I decided to simplify it as much as possible my using the built-in LabVIEW examples as the basic framework for the example I shared. 

So, to clarify, in MS-SQL, I don't get this error message, but I do get an empty result back from the query (array size: 0,0). When I tried it with the built-in example, I get the error shown in the .png image.

So, 0 for 2 with two different databases. (The query for both works fine without the scope_identity() appended, and the INSERT works as expected with the MS-SQL -- the recorded is generated. However, nothing is returned.)

One of our Network guys suggested it could be a DB Server configuration issue... Does that sound like a possibility?

Thanks Dave!

-- Dave