Skip to main content

Comments

5 comments

  • Prajakta

     

    There is open ticket #9244 in Live Forms v4.1.x for the fact that http.put and http.post do not work when used in rules

     

    As a work around you can create a stored procedure to update/insert the values into the database table and you can call this stored procedure in the <retrieve> tag of your configuration.xml query. Then you can use http.get statement in your business rule to call this query which will execute the stored procedure and update/insert data to your table.

     

    For example:

    You can have a stored procedure like:

    ALTER PROCEDURE [dbo].[IncrementID_SP]
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @ExistingID varchar(7)
    DECLARE @NewID varchar(7)
    DECLARE @NewIDNUM int
    SELECT @ExistingID = IncrementID from Purchase_ID;
    SET @NewIDNUM = CONVERT(int,@ExistingID) + 1;
    SET @NewID = CONVERT(varchar, @NewIDNUM);
    UPDATE Purchase_ID set Next_Purchase_ID = @NewID;
    SELECT * from Purchase_ID
    END

     

         Note the SELECT statement in this stored procedure. This statement is required in the stored procedure for the database connector, as it expects some resultset from the query inside the <retrieve> tag. You may not use the resultset, but it is necessary for the stored procedure to work from the DB connector. You can use any select statement that returns some result.

     

    In your configuration.xml, the query to call above stored procedure would look like:

    <query name=" getIncrementID">
      <retrieve>
         <statement>
           exec IncrementID_SP
         </statement>
      </retrieve>
    </query>

     

    Then you would call this query from your rule like this:

    eval('x=' + http.get('http://localhost:8082/database/myqueryset/getIncrementID')) ; 

     

    Now when the rule executes it will ultimately execute the stored procedure to update the record in your database table.

    0
  • Nancy Esposito

    If you need to pass form field data to your stored procedure, see the samples in the documentation here.

    0
  • Permanently deleted user

    For SQL Server, it might also help if you have your SELECT statement before updating the database table. This is to ensure that some result is returned.

    For instance, following is another sample stored procedure. Note the usage of SELECT statement here.

     

    USE [SBS]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[WriteToTempTable]
    @ID int,
    @txtProductCodeTBD varchar(50),
    @qtyQTY int,
    @txtDescriptionTBD varchar(50)
    AS
    BEGIN
    Select 'true' as myID
    UPDATE [dbo].[TempTable]
    SET [txtProductCodeTBD] = @txtProductCodeTBD, [qtyQTY] = @qtyQTY, [txtDescriptionTBD] = @txtDescriptionTBD
    WHERE [ID] = @ID
    END

    0
  • Abhishek Joshi

    Hi @Prajakta,

     

    The workaround is working perfectly. We have a similar stored proc, configuration XML and business rule which we have set up a trigger button and when clicking it saves the data into the database.

    However, the same stored proc called using the DOC URI Post/ Put method, we are receiving  "Error: Invalid response status 501. Expecting 200"

    Are we doing something wrong? Please let me know if you require more details.

    Kind regards,

    Abhishek

    0
  • Prajakta

    Hi Abhishek,

    The SQL statements to add records into database via Doc URI POST must go inside <create></create> tags of your query in configuration.xml. And SQL statements to update records into database via Doc URI PUTmust go inside <update></update> tags of your query.

    Please see the table in this documentation which outlines the type of SQL statement that is executed in corresponding Doc URI method execution.

    -Prajakta

    0

Please sign in to leave a comment.