Calling stored procedure

[arunkumar1]

Hi,

How to create a SQLCommand service, that invoke my stored procedure and return a result?

I'm using MySQL database. I've created a simple stored procedure that inserts a row and returns the last id.

CREATE PROCEDURE TestProcedure(OUT RecordId INT)

BEGIN

INSERT INTO TestTable (Name) VALUES ("Hello");

SET RecordId = LAST_INSERT_ID();

END

Now, I added a new Service and choose SQL Command type, and wrote following line in it

CALL TestProcedure(@result)

Still, I'm not seeing the ID when I execute the service.

Is this the correct way to set the result? Should I do something else?

Regards

Arunkumar D

[qngo]

Hi,

Even what you wanted to do could be done with normal SQL Command service, I always wanted to know if it's possible to call a procedure with ThingWorx.

[arunkumar1]

Could you please let me know, how to do it with SQL Command service?

[qngo]

In fact, I would do that with 1 SQL Command (INSERT ...), 1 SQL Query (Select LastInsertID from ...) and one Local Javascript which calls the two others SQL services and return the result of the SQL Query.

[arunkumar1]

But in a highly concurrent environment, this approach doesn't guarantee that the second SQL Query returns the ID of the first SQL command.

That has to be done within a single transaction, and hence my approach shown in initial question.

[jgorsline]

Arunkumar.

Any update on this? Was Tudor Costache's post helpful? If so, could you click on the "correct answer" or "mark as helpful" button and let us know?

[Tudor · Accepted solution]

It should be possible to capture the return of a stored procedure.  The trick, however, is to flip the invocation of the SP from an SQL command to an SQL query.  As an example, here is my stored procedure that inserts a row and returns the ID:

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    INSERT INTO Technicians (FirstName, LastName) values (@LastName, @FirstName);

    Select SCOPE_IDENTITY();

END

In Thingworx, I set up a service that invokes the SP as a Query instead of a Command. The result is an infotable, which can be later formatted and parsed via a datashape:

Testing the service inserts a row and returns the ID as expected: