Updating Database Value Using AgentLast Updated: 03/28/2018 Introduced in Version: 4.0
This tutorial demonstrates how to Update Database Value using Agent in Decisions.
To learn what the Agent is and how to set it up in Decisions please follow this document: How To Set Up An Agent.
In this example we are going to demonstrate how to integrate with external database using Agent and how to insert new record into integrated database.
Note: This example assumes the Northwind database has been integrated with the Portal. For your own purposes, you can use your default Decisions database or you can integrate with another database. For more information on integrating a database, see Integrating an External Database with a Flow.
Note: for this example the Agent is already set and configured based on How To Set Up An Agent document.
For this example we restrict remote connections to SQL Server on the client machine to ensure that our Decisions Server communicates with the database via the Agent.
Also, in this example we are going to work with Customers table in Northwind database on our client machine.
We begin with creating connection to our database. In the Portal we navigate to System > Integration > Databases and click Create Connection on Folder Actions Menu.
In the resulting Database Definition window we provide Connection Name. Then, we pick MSSQL Database Type and check Use Agent check-box. Next, we pick our Agent from the dropdown menu. In the Microsoft SQL Server section we provide Server Name and Database Name.
Note: we can talk to our client SQL Server as it is installed on our local system because we are using Agent. Finally, we uncheck Windows Authentication check-box and provide credentials to our SQL Server. When finished, we click Test to try our database connection.
If connection Status: Success, we click Ok to save our Database Integration.
Now, our Decisions Server is ready to communicate with SQL Server on the client machine. In the Designer Folder on Start Designer View we click Create New Flow.
Then, we Name the Flow and click Ok to proceed to the Flow Designer.
In the Flow Designer we are going to use a simple Form for User interactions. We add Show Form step from Favorite Steps category.
Then, we Name the Form and click Create to proceed to the Form Designer.
Our Form is designed as following… It has Text Box controls for User inputs and ‘Create’ Button control for User to be able to submit the Form. When finished, we can save the Form and close Form Designer.
Next, on the outcome from our Show Form step in the Flow we add RAW SQL Step from Integrations > My Integrations > Database > Advanced category.
Then, we configure our RAW SQL Step as following. In the Connection Settings we pick Use External Database from the Connection dropdown list.
Next, using External Database picker we select our Database Connection that we created previously in this tutorial using Agent.
Then, in the Inputs section for SQL Command input we choose Text Merge.Plain Mapping Type and click Show Editor. In Merge Text Editor we define our SQL Command using Flow Data as insert Values. When finished, we click Ok to save and close Merge Text Editor.
Finally, we connect Done and Failed outcomes from our RAW SQL Step to the End Step in our Flow. This completes our Flow. We can save and close Flow Designer.
Back in the Designer Folder we locate a thumbnail for our Flow and click Action > Run Flow from the thumbnail’s menu.
Our Form pops up. We fill it out and click Create to continue.
Flow executes to the end. If we switch to our client machine, we can monitor our SQL Statement going through in SQL Profiler.
And our database table is updated with a new Record.