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

Calling stored procedure

SOLVED
arunkumar1
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

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions

Re: Calling stored procedure

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

Re: Calling stored procedure

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.

Re: Calling stored procedure

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

Re: Calling stored procedure

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.

Re: Calling stored procedure

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.

Re: Calling stored procedure

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?

Re: Calling stored procedure

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

Announcements

Check out the upcoming Expert Session: Understanding ThingWorx Navigate Licensing in Community "Customer Events" section.