Updating Database Value Using Agent

Overview

The following document and example demonstrate how to Update a Database Value via an Agent in Decisions by inserting a new record into a preexisting integrated Database.
 

Example

1. From the File System of the Client machine, open SQL Server Managment Studio and connect to the Server. 
2. On the left, right-click the Server and select Properties; from the Properties window, under Connections, enable Allow remote connections to server. 
3. From the Decisions Studio navigate to System > Integrations and right-click the Databases Folder and select Create Connection. 
4. From the Database Integration screen, provide a Connection Name, toggle Use Agent, then select the Agent from the dropdown list. Then, under MICROSFOT SQL SERVER, provide the Server Name, Database Name.  Uncheck Windows Authentication and provide the login credentials for the Server. 
5. Click TEST to ensure the connection is functioning, then click OK to save the configuration. 
6. Navigate to a Designer Project in the Studio and click CREATE FLOW on the Global Action Bar. Then, select Flow, provide a Name, and click CREATE. 
7. From the Flow Designer, attach a Show Form step from the FAVORITE STEPS category of the Toolbox tab. 
8. Navigate to the Show Form step's Properties tab and click PICK OR CREATE FORM, then CREATE a new Form.
9. Design a Form using TextBoxes, Labels, and a "Create" Button component from the FAVORITES category of the Toolbox. Save and close the Form Designer. 
   Design this Form to correspond with values on the integrated Database table. For this example, the Zipcodes table involves a City, Latitude, Longitude, State, State abbreviation, and Zipcode value. 

   
   

10. From the Flow Designer navigate back to the Show Form step's Properties > INPUTS and set mapping to Ignore. 

11. Attach a Raw SQL Step from Toolbox > INTEGRATION > DATABASE > ADVANCED, to the Show Form step. From the Raw SQL Step's Properties, under CONNECTION SETTINGS > Connection, select Use External Database from the dropdown, then PICK the previously created Database connection. 

12. Under INPUTS > SQL Command, change the mapping to Merge Plain Text then click SHOW EDITOR. 

13. From the Merge Text Editor, define a SQL Command by inputting text and utilizing the Data Tree on the left. Click SAVE to save the Message and exit. 

    The Message is defined by as follows
    
    [server name].[dbo].[databasename]
    then
    Place the names of each part of the table within parentheses, separating each value with a comma and a space. For example: (City, Latittude, Longitude, State, State_Abbreviation, Zipcode)
    
    After the close parentheses add a space and the word Values, followed by an additional space, and an open parentheses. Then type a single quote, and add data from the left, followed by a closing single quotation mark; separate each value with a comma then close the final value with a parenthesis and a semicolon.

    
    For example:
    Values ('City','Latitude','Longitude') etc...
    
    See screenshot for additional aid.

    
    

    
    

14. Connect both outcome paths from the Raw SQL Step to the End step. Save the Flow, then if desired closeout via X.
    

Debug

1. From the Flow Designer, select the Debug link from the top action bar. 
2. Click START DEBUGGING. 
3. Upon arriving to the Form, fill it out with the appropriate input, then click Create.
4. After the Flow completes, switch to the Client machine, and monitor the SQL Statement as it goes through the SQL Profiler. Then, verify that the Database Table was updated with the new record.