6 Replies Latest reply on Mar 3, 2017 2:06 PM by jgorsline RSS
    arunkumar Explorer

    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

      • Re: Calling stored procedure
        qngo Creator

        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
          tcostache Explorer

          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