Skip to main content
A
arunkumar11-Visitor
1-Visitor
April 4, 2016
Solved

Calling stored procedure

  • April 4, 2016
  • 2 replies
  • 5729 views

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

Best answer by Tudor

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

2 replies

Q
qngo1-Visitor
1-Visitor
April 4, 2016

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.

A
arunkumar11-VisitorAuthor
1-Visitor
April 4, 2016

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

T
Tudor14-AlexandriteAnswer
14-Alexandrite
February 10, 2017

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

    Terms & ConditionsAccessibility statement