Updating Database Value Using Agent
  • Updated on 27 Mar 2019
  • 3 minutes to read
  • Print
  • Dark
    Light

Updating Database Value Using Agent

  • Print
  • Dark
    Light

Learn how to Update a Database Valueusing an Agent in Decisions. This example demonstrates how to integrate with an external database using an Agent and will also demonstrate how to insert a new record into an already integrated database. For more information about how to Set up an Agent in Decisions see the following link: Setting up an Agent.

Below is an example of an Agent that has already been registered and setup in Decisions.
agentCreatedAndInstalled.png

For this example remote connections are restricted to SQL Server on the client machine to ensure that our Decisions Server communicates with the database via the Agent.
remoteConnectionsOff.png

This example will connect with the Customers table in the Northwind database on the client machine.
northwindTableOnTheClient.png

Begin by creating a connection to the database. In the Portal navigate to System > Integration > Databases and click Create Connection on Folder Actions Menu .

createconnection.jpg

Next on the Database Integration editor, provide a Connection Name. Then, pick MSSQL Database Type and check Use Agent check-box. Next, pick the Agent from the drop down menu. In the Microsoft SQL Server section we provideServer Name and Database Name.

  • Note: Talk to the client SQL Server as it is installed on the local system because Agent is being used. .*Finally, uncheck Windows Authentication check-box and provide credentials to the SQL Server.
    To complete select Test to try the database connection.

testConnection1.png

If the connection is successful, Status: Success, select Ok to save the Database Integration.
statusSuccess.png

Now, the Decisions Server is ready to communicate with SQL Server on the client machine. In the Designer Folder on Start Designer View select Create Flow.
createFlow2.png

Name the Flow, and select Create to proceed to the Flow designer.

nameFlow.png

In the Flow Designer use a simple Form for User interactions. Add Show Form step from Favorite Steps category.
addShowForm1.png

Then, Name the Form and select Create to proceed to the Form Designer.
nameForm1.png

The Form is designed as follows: The form has a Text Box, Controls for user inputs and a Create Button control for User to be able to submit the Form . When finished, we can save the Form and close Form Designer.
formDesign1.png

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

Next configure the RAW SQL Step as following. In the Connection Settings pick Use External Database from the Connection* drop down list.
useExternalDb.png

Next, using External Database picker we select the Database Connection that was created previously in this tutorial using Agent.
pickDb.png

Then, in the Inputs section for SQL Commandinput choose Text Merge.Plain Mapping Type and select Show Editor. In Merge Text Editor define the SQL Command using Flow Data and insert Values. Select okay to complete and save the Merge Text Editor.
sqlCommand.png

Finally, connect the Done pathway and the Failed pathway from the RAW SQL Step to the End Step in the Flow. This completes theFlow. Save and close in theFlow Designer.

flowCompleted.png

Back in the Designer Folder locate a thumbnail for the Flow and select Action > Run Flow from the thumbnail’s menu.
runFlow3.png

Fill out and the Form and select Create to continue.
fillOutTheForm.png

If the Flow executes to the end, then switch to the client machine, you can monitor the SQL Statement going through in SQL Profiler.
sqlProfiler.png

And the database table is updated with a new Record.
valueInserted.png

Was this article helpful?