Updating/Inserting into a SQL Table Using A Business Rule
What's the correct syntax in a business rule to post data to update a database?
-
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
ENDNote 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 -
If you need to pass form field data to your stored procedure, see the samples in the documentation here.
0 -
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
END0 -
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 -
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.
Comments
5 comments