Description

The SQLUpdateV2Receiver connects to a database and performs batched updates. This Receiver is useful for data replacement of values within a database table (i.e., in-place masking) to guarantee data security. It works for any Relational Database that has a JDBC driver available (e.g., PostgreSQL, Oracle, DB2, MSSQL, MySQL, Sybase, etc.). 


The SQLUpdateV2Receiver can be used for: 

  • In-Place Masking - This receiver can be used to perform in-place masking for one or more tables in a relational database. Click here to see an example. 

  • Create Table - This Receiver can also create a database table for any kind of database. To see an example, click here. 


In This Article


Receiver Parameters

The following parameters can be defined for the SQLUpdateV2Receiver. Items with an asterisk (*) are required. 

  • resourcePath* - Defines the directory path where the Resource file is located.
  • resourceSubDir - Defines an optional subdirectory under the resourcePath where the Resource file exists.
  • resourceName* - Defines the name of the resource that contains the database connection information on a user's local machine.


JDBC Configuration File

This Receiver utilizes a configuration file stored locally on the user's computer or a shared test server to obtain the necessary information to connect to a specific database via JDBC. For detailed information, refer to 'What is a JDBC Config File?'


Receiver Attribute Property Keys

The Receiver defines one property key that can be modified on any of its associated Domain Attributes:

  • variable - Determines if the Attribute will be used as a variable within the update statement. Up to 20 attributes may be assigned to a variable (var1 - var20). The default is 'noVar', meaning the Attribute will not be used.



Defining an SQL Update statement

The update statement is defined on the Receiver's Data Tab. This information has been defined for the following Domain and Attributes: 



Example: SQL Update Statement

Attributes whose values will be used in the update statement are assigned a variable from var1 to var20.



Defining a Create Table Statement

The create table statement is defined on the Receiver's Data Tab. This information has been defined for the following Domain and Attributes:


The example below creates a MySQL database table. For a step-by-step example, please click here. 



IMPORTANT: Make sure the correct syntax is used for the database table that is being created. 


Oracle Database - Query for Deleting Two Tables

To delete two tables in an Oracle database, please add the query below to the Receiver and execute the scenario.


BEGIN
    EXECUTE IMMEDIATE 'delete from <table name>';
    EXECUTE IMMEDIATE 'delete from <table name>';
END;

IMPORTANT: Please be careful when you delete a table. Ensure the proper table name(s) have been included, as this action cannot be undone.

Text