Description

The GenericSQLInsertReceiver is used to insert a Domain's generated data directly into a database table via a JDBC connection.  


In This Article


Receiver Parameters

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

  • resourceName* - Defines the name of the resource that contains the 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 rows into.
  • uniqueAttribute - Defines a Domain Attribute the Receiver will use to identify a unique value that has already been inserted into the table to prevent duplicate data entry where a unique constraint is required.
  • truncate* - Truncates the table before inserting rows by executing a 'delete from table' statement.
  • statementSeparator* - Defines when a SQL statement has ended.
  • booleanValue* - Format a Boolean object with the selected mask.
  • rowDelimiter* - Defines the bytes that delimit each row.
  • nullValue* - Represents Null value.
  • quoteTextData* - Defines the character to use when quoting text data.
  • generatedKeysFilePath - Defines the path where the file having generated keys will be stored.
  • generatedKeysFileName - Defines the file name in which generated keys will be stored.
  • escapeCharForIndentifiers - Escape character is used to wrap identifiers: schema name, table name, and column name. The parameter is useful when identifiers contain special characters like ?, ', ", etc.
  • ignoreDBName - By default, ignoreDBName is set to False which appends the database name in the insert query. In order to not to have database name appended in the insert query, set the ignoreDBName to True. 


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.


Column Data Types

This Receiver uses a set of standard data types to populate columns within most database tables; these column data types should successfully work with most databases that allow for data insertion via JDBC.  The column data types are the following:  

  • String - gets translated to a VARCHAR
  • Boolean - native data type
  • BigDecimal - gets translated to a DECIMAL
  • Date - native data type
  • Double - native data type
  • Float - native data type
  • Integer - native data type
  • Long - gets translated to a BIGINT
  • Time - native data type
  • Timestamp - native data type


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 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.
  • include - Defines if the column should be included or excluded from the insert statement.


Example Setting of Receiver Property Key Values

The example image below shows the property key view for the set of Attributes of a Domain using the GenericSQLInsertReceiver. 



Special Note - Error Inserting Data into PostgreSQL

The following error(s) may be received when attempting to insert test data into PostgreSQL:

column "columnName" of relation "relation name" does not exist
column "columnName" does not exist


PostgreSQL follows SQL standards, and identifiers such as table names/column names are forced to lowercase unless surrounded by double quotes. 


Example 1 (Without Quotes)

APP_USER becomes app_user


Example 2 (With Quotes)

"APP_USER" becomes APP_USER


To resolve this issue, users can try with double quotes or change all column names within the Receiver's Attribute Property Keys to lowercase (shown below).