Help on mssql query service

[luwenya]

I have a ms sql table called "site". to keep the question simply, say the table contains one identity column called site_id (primary key), and another column called site_name. when a record is inserted, the site_id automatically increases. I need to get the returned new site_id and then do an insert using the site_id into a few other tables in a single action. I tried to create 3 services, one is sql command using insert statement. the second one is a sql query with return type as infotable and sql statement is "select @@IDENTITY as 'Identity'"; the third service is a localscript with return type as number looks like following:

var params = {

site_name: 'abc' /* STRING */

};

// result: NUMBER

me.InsertSiteTable(params);

// result: INFOTABLE dataShape: "ProfileIdentityData"

var result = me.SelectIdentity();

however, I got the result of 1, but the actual ID is 1012. I think the reason is in order to get the new site_id value, the insert and select should be in one session. but I am not sure how to construct it in Thingworx. can you help?

Thanks,

-LuWen

[abalousek]

LuWen,

The most efficient way of executing what you need would be for you (or your DBAs) to create a stored procedure in the database that handles all the needed transactions and then simply call that using a SQL Command handler. In your attempt, the select for the identity is not processed in the same transaction and therefore will not return the identity value from your original transaction. Without the stored procedure, you'll need to do an intermediate query of the table you're inserting into for the value(s) you just inserted to get the site_id, then use that result for your subsequent table inserts.

[luwenya]

Thank you for the timely reply! I can go with the stored procedure approach to solve one part of my problem which is insert to other tables using the site_id. however, there is another problem which I am trying to resolve by getting the new site_id back in an insert statement. Here is my problem: I am creating a new form for the user to fill out as well as allow the user to edit the form, but I have no way to tell if it is a new insert or an update. so I have a very small popup which allows the user to create a title of the form, then click "Create". The create button ties to a create service event and then I want to display the full form using the new ID which is created. I am not sure how to do that. any suggestion on that? 

[luwenya]

Is it possible to run following sql query which returns what I am looking for, but I am getting an error:

In mssql service, run following sql query:

INSERT INTO MySite (Site_Name) VALUES ('ABC');

SELECT Site_ID, Site_Name FROM MySite WHERE Site_ID = SCOPE_IDENTITY();

I am getting: Unable to Invoke Service GetSiteID on MsSQLServices : The statement did not return a result set.

[paic]

Hi LuWen,

You will have to run this as a stored procedure, and use an SQL Query to execute it.