Description

The GenericSQLInsertV2Receiver is responsible for directly inserting a Domain's generated data into a database table through a JDBC connection. This receiver is ideal for quickly inserting up to 10,000 rows of data into a table with 25 columns or fewer. However, for larger volumes of data (e.g., 50K to 10M) and/or when the table contains more than 25 columns, it is recommended to use the GenericSQLInsertThreadReceiver. This receiver is relatively faster in larger data insertion scenarios. To learn more about the speed comparisons between the two receivers, please refer to the document titled 'GenericSQLInsertThreadReceiver vs GenericSQLInsertV2Receiver'.


Receiver Parameters

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

  • resourcePath* - Defines the path where the resource file for JDBC connection properties exists.
  • resourceSubDir - Defines an optional subdirectory under the resourcePath where the resource file for JDBC connection properties exists.
  • resourceName* - Defines the name of the resource that holds database connection information on a user's local machine.
  • databaseName* - Defines the name of the database to access.
  • tableName* - Defines the name of the database table to insert data into.
  • uniqueAttribute - Defines a Domain Attribute that the Receiver uses to identify a unique value that has already been inserted into the table and prevents it from being inserted twice.
  • truncate* - Defines whether a table will be truncated or deleted prior to data insertion.
    • WARNING! The truncate parameter allows users to truncate or delete all data from the table to be populated prior to data generation and insertion. Use this option very carefully; data truncation or deletion can not be reversed once it has been executed. The options are the following:
      • notApplicable (default) - no rows are deleted from a database table.
      • truncate - Deletes all rows from a table. Deletion is fast, does not use a where clause, no transaction, and no rollback.
      • delete - Deletes one or more rows from a table, with optional where clause or transaction.
  • statementSeparator* - Defines when a SQL statement has ended.
  • rowDelimiter* - Defines the bytes that delimit each row.
  • booleanValue* - Defines the format for a Boolean object with the selected mask.
  • nullValue* - Defies the constant that represents the Null value for a given database.
  • quoteTextData* - Defines the character to use when quoting text data.
  • generatedKeysFileName - Defines the file name in which generated keys will be stored.
  • escapeCharForIndentifiers - Defines the escape character used to wrap identifiers (e.g., schema name, table name, and column name) containing special characters (e.g., ?, ', ", etc.).
  • ignoreDBName - By default, the insert query appends the database name unless ignoreDBName is set to True.


Receiver Attribute Property Keys

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

  • columnName - Defines the name of the column as it is in the actual database table (e.g., first_name, last_name).
  • columnType - Defines the column data type (click here for a detailed help article).
  • include - Defines if the column should be included or excluded from the insert statement.


JDBC Configuration File

This Receiver utilizes a configuration file stored locally on the user's computer to obtain the necessary information for establishing a JDBC connection with the user's local database. For detailed information, refer to 'What is a JDBC Config File?'


PostgreSQL Data Insertion Considerations

There are special considerations when inserting data into a PostgreSQL database table. For detailed information, refer to 'Why Are There Errors When Inserting Data into PostgreSQL?'


Escape Character Cases

Case 1

Parameter Value: '

Example: 'db name'.'table name'.'column name'


Case 2

Parameter Value: []

Example: [db name].[table name].[column name]


Case 3

Parameter Value: #$#

Example: #$db nam#'.#$table name#.#$column name#


Note: Escape character for identifiers depends on database configuration. Please check with your DBA to find out the appropriate escape character for your database, if required.