Using scope_identity()

[Mike C]

Hello everyone. I am using the following SQL query to get the primary key ID following a record insert but nothing is returned:

Insert into tblname (field_1, filed_2) Values ('value1', 'value2')

select scope_identity()

This query works as expected in the SQl server Management studio but not in LabVIEW.

I am using the LabVIEW database connectivity kit with LabVIEW 8.5.

Does anyone have a solution to this problem?

Thanks,

Michael

[xtal]

Do any of the VIs return errors? What database management system do you use? What connection type? (ODBC, UDL file, other). What is the structure of your tables/fields?

[PaulG.]

I had some problems with termination characters following the query. It would work in SQL but the same query in LV would not. Play around with that.

[orko]

QUOTE (Mike C @ Apr 25 2008, 09:05 AM)

I am using the following SQL query to get the primary key ID following a record insert but nothing is returned:

Insert into tblname (field_1, filed_2) Values ('value1', 'value2')

select scope_identity()

This query works as expected in the SQl server Management studio but not in LabVIEW.

I agree about termination characters being a major bane. Also, two other things come to mind.

1) you say, "query", not "queries" so I'm assuming that the text above is going in as one query? That probably will not work...I would assume that you would have to do an insert query and then a select query seperately. I haven't played with it in in the LabVIEW DBC toolkit to be sure.

2) If combined read/write is allowed in the DBCT in one SQL query, the above still will not work since there is nothing separating the two queries but a line break. A semi-colon would need to be after each query to signify the end. Consider this, which is a valid SQL statement (and how I usually form my queries programatically since the fields/values can be generated easily):

Insert into tblname(field_1, filed_2)Values ('value1', 'value2');select scope_identity();

[Mike C]

Thanks for the quick responses. I'll try and provide some answers to your questions:

1. There are no error messages

2. Using a termination character has no effect

3. The insert and select scope_identity() need to be in the same command. The scope_identity() is supposed to return the table primary key ID the insert had

4. Database: SQL Server 2005, ODBC connection

[orko]

QUOTE (Mike C @ Apr 25 2008, 01:07 PM)

3. The insert and select scope_identity() need to be in the same command.

Not true. They are actually two seperate queries that are run one right after the other. The SQL 2005 management studio is smarter than the LabVIEW DBC Toolkit, in that it knows every command that the SQL server is capable of processing, and what syntax they require. So it doesn't care if you put a semicolon at the end of the statement or not.

The LabVIEW DBC Toolkit however would probably need to know where each query ends. I would suggest placing a semicolon in between the two queries and trying it out. It still may not work, I'm not sure if the DBCT is capable of executing multiple queries from the same SQL statement. But I gaurantee you that if your application sends the INSERT and SELECT statements as two different queries that it should work.

There are actually alternative ways of getting a "last ID used" return with one INSERT/UPDATE/DELETE query using an OUTPUT syntax that you would use if you were worried about other processes inserting in-between your last insert and your scope_identity() query...I just can't seem to find a link to give you for that...

oh wait, here's a http://weblogs.asp.net/esanchez/archive/2006/04/24/443757.aspx' rel='nofollow' target="_blank">Blog article that describes it.

[Mike C]

I have tried using a semicolon to indicate where each query ends but it did not work. I suppose the LabVIEW toolkit cannot execute more than one query per SQL statement. I did take a quick look at the blog and it seems that there is still a more than one query per SQL statement so it will probably not work. I'll give it a try though.

QUOTE (orko @ Apr 25 2008, 04:06 PM)

Not true. They are actually two seperate queries that are run one right after the other. The SQL 2005 management studio is smarter than the LabVIEW DBC Toolkit, in that it knows every command that the SQL server is capable of processing, and what syntax they require. So it doesn't care if you put a semicolon at the end of the statement or not.

The LabVIEW DBC Toolkit however would probably need to know where each query ends. I would suggest placing a semicolon in between the two queries and trying it out. It still may not work, I'm not sure if the DBCT is capable of executing multiple queries from the same SQL statement. But I gaurantee you that if your application sends the INSERT and SELECT statements as two different queries that it should work.

There are actually alternative ways of getting a "last ID used" return with one INSERT/UPDATE/DELETE query using an OUTPUT syntax that you would use if you were worried about other processes inserting in-between your last insert and your scope_identity() query...I just can't seem to find a link to give you for that...

oh wait, here's a http://weblogs.asp.net/esanchez/archive/2006/04/24/443757.aspx' rel='nofollow' target="_blank">Blog article that describes it.

[xtal]

QUOTE (Mike C @ Apr 28 2008, 03:02 PM)

I have tried using a semicolon to indicate where each query ends but it did not work. I suppose the LabVIEW toolkit cannot execute more than one query per SQL statement. I did take a quick look at the blog and it seems that there is still a more than one query per SQL statement so it will probably not work. I'll give it a try though.

The toolkit can definitely send more than one query at a time. It doesn't do anything magical under the hood...it just passes the string directly to the ODBC driver in this case. Since you don't get any errors from the ODBC driver that indicate problems with the connection, I can only guess that there is a syntax problem.

Another option is to use the UDL file to connect and use the SQL Server driver directly. I've found ODBC to be difficult to work with and using the SQL Server driver is usually faster and has more functionality and supports more datatypes.

Also, can you run each statement separately for testing purposes? Starting with a simple query and making it progressively harder until it fails will give us a better idea of what part is causing the problem.

[Mike C]

Well, I figured out a way to do it. I had to create a stored procedure to do the insert and include the "select scope_identity()" as part of it. The funny part to this is that if you do execute the stored procedure from a query the insert happens correctly but the scope_identity is not returned.

What does work is a parameterized query where you can set parameters (what you need to insert) as well as read parameters (the scope_identity). It took some looking around to find out the correct syntax to do this (the examples in LabVIEW do not explain this well).

I am listing below the stored procedure and the code. This was just a test so please excuse any cosmetic sloppiness.

Stored Procedure:

CREATE PROCEDURE [dbo].[uSER_INS] (@userFullNM varchar(100),@username varchar(50),@adminpass varchar(50), @roleid int, @isactive int, @userid int output)

AS

BEGIN

SET @USERID = NULL

SELECT @USERID = [uSER_ID] FROM [uSER] WHERE USER_FULL_NM = @userFullNM

IF @USERID IS NULL

BEGIN

INSERT INTO [user]

(user_full_nm, username, admin_password, user_role_id, is_active, create_dtm, last_update_dtm)

VALUES(@userFullNM, @username, @adminpass, @roleid, @isactive, GetDate(),GetDate())

SELECT @userid=SCOPE_IDENTITY()

END

ELSE

BEGIN

UPDATE [uSER]

SET username = @username,

admin_password = @adminpass,

user_role_id=@roleid,

is_active=@isactive,

last_update_dtm = GetDate()

WHERE [uSER_ID] = @USERID

END

END