Updating Database Value Using Agent

Last 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.

Example:

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.

agentcreatedandinstalled

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.

remoteconnectionsoff

Also, in this example we are going to work with Customers table in Northwind database on our client machine.

northwindtableontheclient

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.

createdatabaseconnection

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.

testconnection

If connection Status: Success, we click Ok to save our Database Integration.

statussuccess

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.

createflow

Then, we Name the Flow and click Ok to proceed to the Flow Designer.

nameflow

In the Flow Designer we are going to use a simple Form for User interactions. We add Show Form step from Favorite Steps category.

addshowform

Then, we Name the Form and click Create to proceed to the Form Designer.

nameform

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.

formdesign

Next, on the outcome from our Show Form step in the Flow we add RAW SQL Step from Integrations > My Integrations > Database > Advanced category.

addrawsqlstep

Then, we configure our RAW SQL Step as following. In the Connection Settings we pick Use External Database from the Connection dropdown list.

useexternaldb

Next, using External Database picker we select our Database Connection that we created previously in this tutorial using Agent.

pickdb

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.

sqlcommand

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.

flowcompleted

Back in the Designer Folder we locate a thumbnail for our Flow and click Action > Run Flow from the thumbnail’s menu.

runflow

Our Form pops up. We fill it out and click Create to continue.

fillouttheform

Flow executes to the end. If we switch to our client machine, we can monitor our SQL Statement going through in SQL Profiler.

sqlprofiler

And our database table is updated with a new Record.

valueinserted

 

 

Additional Resources