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
- Escape Character Cases
- Column Data Types
- JDBC Configuration File
- Example JDBC Configuration File
- Receiver Attribute Property Keys
- Example Setting of Receiver Property Key Values
- Special Note - Error Inserting Data into PostgreSQL
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 uses a configuration file, located on the user's local computer, as a resource to attain the necessary information for connecting to a user's local database via JDBC. The required properties to connect to a database via JDBC are the following:
- driver - the path and file name of the JDBC library for the database to connect to.
- user - the name used to connect as a user to the database.
- password - the password used to connect as the given user to the database.
- url - the JDBC universal resource locator required to connect to the given database.
- batchCount - defines the number of rows of generated data that are batched together before writing to the database.
Example JDBC Configuration File
Below is an example of a JDBC configuration file defined to connect to a MySQL database called, acme:
driver=com.mysql.jdbc.Driver user=root password=openSaysMe url=jdbc:mysql://localhost:3306/acme?rewriteBatchedStatements=true batchCount=1000
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.
- 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).