Description

The SQLFileInsertReceiver creates a file with standard ANSI SQL insert statements.


In This Article


Receiver Parameters

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

  • path* - Defines the directory path where to store the output file.
  • tableName* - Defines the name of the SQL table as it is defined in the database.
  • fileName* - Defines the name of the file the SQL statement will output to.
  • batchCount* - Defines the number of insert statements written to file in each batch.
  • appendToFile* - Determines whether the file will open in append mode. If true, SQL statements will be appended to the file; otherwise, the file will be overridden.
  • uniqueAttribute - Defines a Domain Attribute the Receiver will use to identify a unique value that has already been inserted into the file and prevents it from being inserted twice.
  • statementSeparator* - Defines when a SQL statement has ended.
  • rowDelimiter* - Defines the characters to delimit each row.
  • booleanValue* - Format a Boolean object with the selected mask. 
  • nullValue* - Represents a Null value.
  • subDir - Defines an optional sub-directory where to store the output file.
  • quoteTextData* - Defines the character to use when quoting text data.
  • escapeCharacter - Used to define an escape sequence for all the special characters. The default value is a backslash (\).
     


Row Delimiter

There are two rowDelimiter types:

  • UNIX/Linux/Mac-LF - Used for Mac, Unix & Linux machines.
  • Windows-CR/LF - Used for Windows machines.


Receiver Attribute Property Keys

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

  • columnName - Defines the actual column name used in the database table (e.g., first_name, last_name, phone_number).
  • include - Determines if the Attribute will be included in each SQL statement.
  • useQuotes - Determines if a value should be quoted. Sometimes, a numeric value should be quoted (e.g., SSN).


Column Name

Make sure to pay attention to the column name for each Attribute. They need to match the column name as it is defined in the database table.



File and Directory Config Tabs

The File and Directory Config Tabs are used to configure what event will trigger file/directory creation and the naming configuration for generated files/directories. Please click here for more information on how to use the File and Directory Config Tabs. 



Example Output

The following is an example output of SQL inserts statements into a user table.


insert into user(id,first_name,last_name,middle_initial,emailAddress) values ('1','Penelope','Spaeth','F','user@email.com');
insert into user(id,first_name,last_name,middle_initial,emailAddress) values ('2','Carla','Elledge','U','user@email.com');
insert into user(id,first_name,last_name,middle_initial,emailAddress) values ('3','Tori','Adamson','R','user@email.com');


Use Case 1 - Domain Attributes Generating Both Null and Quoted Values

Sometimes, a Domain contains Attributes (i.e., fields) that generate quoted and null values. Users can ensure that null values appear properly in the generated output and do not have quotes by using the nullValue parameter.


Project Setup

A Project contains the default Project Version and a User Domain. The User Domain has four Attributes:

  • id
  • firstName
  • lastName
  • nullValue


The firstName and lastName Attributes should be generated as quoted values; however, the nullValue Attribute should generate a NULL value, not a quoted value.



SQLFileInsertReceiver Parameters

The following parameters need to be configured to handle quoted and null values. 

  • nullValue = NULL
  • quoteTextData = Double (values surrounded by double quotes unless null)



Sample Output

The output will appear as below for this example: