cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X

Calling stored procedure

arunkumar1
1-Newbie

Calling stored procedure

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tudor
12-Amethyst
(To:arunkumar1)

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:

2-10-2017 2-35-27 PM.png

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

2-10-2017 2-33-12 PM.png

View solution in original post

6 REPLIES 6
qngo
5-Regular Member
(To:arunkumar1)

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.

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

qngo
5-Regular Member
(To: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.

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.

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
12-Amethyst
(To:arunkumar1)

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:

2-10-2017 2-35-27 PM.png

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

2-10-2017 2-33-12 PM.png

Top Tags