Creating SQL Update Data Transformations

Creating SQL Update Data Transformations

Overview

The standard SQL Data Transformation used in a DIM Project is an Insert SQL transaction. SQL Update transactions are also supported for cases when a company needs to update existing information in their SQL Database. This feature is only available by editing the Project (.XML) file directly with a Text Editor.

Adding a SQL Update Data Transformation

In order to add a SQL Update Data Transformation, an entire block of code is added to the Project file to execute a SQL statement. If the Project already has a SQL Insert Data Transformation, the SQL Update code can be just added to that block. If not, the SQL Connection Information needs to be added as well. If a User adds a SQL Update Data Transformation to a Project that has no pre-existing SQL in it, it is recommended to create a new SQL Insert Data Transform using the Project Manager first to verify the Database connection. When the Project file is edited, the original Insert Statement can be removed.

The Block shown below is a basic SQL Update including SQL Connection Information:

  1. <sql provider="OleDb" connectionString="provider=sqloledb; data source=(local); initial catalog=Test; user id=demo; password=test" transaction="true">
  2.    <execCommand>
  3.       <commandText>update f2b.bar set delta=? where gamma=3</commandText>
  4.       <parameter>{Page1@BatchId}</parameter>
  5.    </execCommand>
  6. </sql>

The <sql> block is the SQL Connection Information section. The <execCommand> block is the SQL Update text. The <parameter> block is the actual value of the Parameter from the Project’s Form. The ‘?’ represents each Parameter in the block and are evaluated in order. Parameters do have the option to use a “name” attribute for use in the Update command, but most Transactions will not use this format.


Using Conditions

SQL Updates can use conditions and only requires the “condition” attribute to be set.
In the sample below, the SQL Update will only run if a value is entered in the {Page1BatchId} Region in the Form.
  1. <execCommand condition="{Page1@BatchId}">
  2.    <commandText>update f2b.bar set delta=? where gamma=3</commandText>
  3.    <parameter>{Page1@BatchId}</parameter>
  4. </execCommand>

How to Set Up a SQL Update Data Transformation in a Project with NO existing SQL Transactions

1. Open the .xml Project File (typically located at C:\Program Files\F2B Data Integration Module\Jobs) with a Text Editor.
2. Find the <rfiSvc> block (the SQL Update will be added below this block).
3. Add the following block:
  1. <sql provider="OleDb" connectionString="provider=sqloledb; data source=(local); initial catalog=Test; user id=demo; password=test" transaction="true">
  2.    <execCommand>
  3.       <commandText>update f2b.bar set delta=? where gamma=3</commandText>
  4.       <parameter>{Page1@BatchId}</parameter>
  5.    </execCommand>
  6. </sql>
4. Save the Project File.
5. Exit the Text Editor.
6. Open the Data Integration Module Administrator.
7. Start (or Restart) the Service to now Process Jobs with the SQL Update in place.

When a SQL Update is processed in a Job, additional entries will be shown in the Logs. However, the Logs only show that the SQL statement was executed; it does not show if the Transaction was successful. Here is a sample of what a SQL Update would look like in the Logs:

09/16 11:35:41.92 AM (tid:16156) Info       : Executing sql command

09/16 11:35:42.06 AM (tid:16156) Info       : Command affected 1 row(s)


Transaction Option

The transaction option can only be changed directly in the Project file and has a True or False value. This transaction option was created to allow the SQL Transactions in the Project to be linked in a single Transaction block that will roll back if any errors occur. The default value is set to True to link all Transactions into a single block.
  1. True (default) - If there are processing errors in SQL Statements after a block has been executed, any transaction set to True IS rolled back in the database.
  2. False - If there are processing errors in SQL Statements after a block has been executed, any transaction set to False is NOT rolled back in the database.
Note: If there are any processing errors for the Job prior to the SQL Statement being run, no transaction will even occur. 

    • Popular Articles

    • Forms Designer Quick Start Guide

      Overview Field2Base Forms Designer is the proprietary software application that allows your existing paper forms to be quickly converted to a smart E-form available to your end users via our mobile and web-based Mobile Forms applications. This ...

    • Portal 11.28.2023 Release Notes

      Overview Our release notes offer brief descriptions of product enhancements and bug fixes. We include links to the current articles for any affected features. Those articles will be updated shortly after the Portal release to include new ...

    • Integration Service Configuration Guide

      How To Configure Integration Services To Allow Read/Write Access on a Network Path All of our Integration Products, including the DIM, DUU, and EDM have respective Windows Services responsible for communicating with our server. Occassionally, ...

    • Data Integration Module (DIM) Migration Guide

      Overview This article provides the information necessary to migrate the Field2Base Data Integration Module (DIM) over from one server to another. Please refer to the DIM Install Guide for the initial installation of the Field2Base DIM. Once that's ...

    • How to Check the Version of Integration Products Running on a Windows 10 Machine

      Right-click on the Start menu button. Click on Apps & Features. In the Apps & Features search bar type in the Integration Product you are looking for, eg. F2B Data Integration Module, F2B Data Upload, or F2B Enterprise Dispatch Module. Click to ...