CA API Gateway 9.1

9.4 9.1

Insert JDBC Data in Bulk Assertion

The Insert JDBC Data in Bulk assertion is designed to add a large amount of data into a JDBC data source. It parses data from (by default) a comma-separated value (CSV) file and then inserts them in bulk into a specified database using a JDBC connection. The CSV file is obtained from the source message. For information on how to set the message source, see .
gateway91
The
Insert JDBC Data in Bulk
 assertion is designed to add a large amount of data into a JDBC data source. It parses data from (by default) a comma-separated value (CSV) file and then inserts them in bulk into a specified database using a JDBC connection. The CSV file is obtained from the source message. For information on how to set the message source, see Select a Target Message.
Throughout this topic, "CSV" is used to denote a comma-separated value file. However, comma separators are only the default. Any character may be used as the field delimiter.
For inserting large amounts of data, this assertion offers better performance than using the Perform JDBC Query Assertion, which is best suited for inserting smaller amounts of data.
Context Variables
None.
Cluster Properties
None.
Properties
Setting
What you should know...
JDBC Connection
Select a preconfigured connection from the drop-down list or specify a context variable.
Table Name
Enter the name of the database table to update. You may specify context variables.
Record Delimiter
Select the file record delimiter used in the CSV file. The default is
CRLF
.
Field Delimiter
Optionally specify the field record delimiter used in the CSV file. Must be a single character. You may specify context variables. The default is
,
 (comma).
Quoted?
Select the check box if the CSV file contains quotes. Otherwise, the document is parsed without enforcing the quotes.
Quote Char
If quotes are used, optionally specify the quote character. You may specify context variables. The default is
"
 (double quotes).
Escape Char
If quotes are used, optionally specify the escape character. You may specify context variables.The default is empty.
Data mapping table
This table describes how to map the data from the CSV file to the database table. Note that the CSV field number starts from 0.
See "table_mapping" below for more information
Decompression
Specify the compression type used to compress the data.
Batch Size
Specify the batch size for data to be inserted into the database. The default is
100
.
Table Mapping Properties
Setting
What you should know...
Table Column
Specify the database table column where the values will be inserted.
CSV Field
Specify the field number (starting from '0') in the CSV file from which to get the value.
Transformation
Select the transformation to be performed on the value. The transformations currently supported:
  • String:
     A free-form string that is copied directly to the database table. Requires no parameter.
  • Regex2Bool:
     Places 'true' into the database table if the CSV field matches the regular expression, otherwise places 'false'. Requires a regular expression as a parameter.
  • Regex2Int:
     Places '1' into the database table if the CSV field matches the regular expression, otherwise places '0'. Requires a regular expression as a parameter.
  • Subtract:
     Takes the current value from the CSV field and subtracts the value of the field specified in the parameter. Treats value types as long; example.:
    col(3) - col(6)
  • Add:
     Takes the current value from the CSV field and adds the value of the field specified in the parameter.
    Treats value types as long; example:
    col(3) + col(6)
  • ConstantInt:
     Sets the table column to the integer value of the parameter.
  • ConstantString:
     Sets the table column to the string value of the parameter.
  • UUID:
     Sets the table column to the newly generated UUID value.
Parameter
If the selected transformation accepts a parameter, enter it here.
Frequently Asked Questions
Question
Answer
How can I tell if data was inserted correctly into the database?
Look for these audit entries: -4, -3.
How can I tell if this assertion failed?
Look for these audit entries: 2, -5.
Where is the CSV file located?
The CSV data is obtained from the target message (request, response, or context variable). It can also be added via a REST API call.

Content feedback and comments