String
Database Connector Reference 1.12 - Mule 4
Anypoint Connector for Database (Database Connector) enables you to connect to relational databases through the JDBC API.
Release Notes: Database Connector Release Notes
Configurations
Default Configuration
Use these parameters to configure the default configuration.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Name |
The name for this configuration. Connectors reference the configuration with this name. |
x |
||
Connection |
The connection types to provide to this configuration. |
x |
||
Expiration Policy |
Configures the minimum amount of time that a dynamic configuration instance can remain idle before Mule considers it eligible for expiration. This does not mean that the platform expires the instance at the exact moment that it becomes eligible. Mule purges the instances as appropriate. |
Connection Types
Data Source Reference Connection
Configure the connection provider implementation that creates database connections from a referenced data source.
When you use a provider’s custom type in a Data Source Reference Connection, define the type inside the Column Types form of the Advanced section in the Database config.
As an example, for Oracle DB checkout their Constant Field Values page (section oracle.jdbc.OracleTypes) for the correct id/type name combination.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Pooling Profile |
Provides a way to configure database connection pooling |
|||
Column Types |
Array of Column Type |
Specifies non-standard column types |
||
Data Source Ref |
Any |
Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided. |
x |
|
Reconnection |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
Derby Connection
Use these parameters to create connections to a Derby database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Pooling Profile |
Provides a way to configure database connection pooling |
|||
Column Types |
Array of Column Type |
Specifies non-standard column types |
||
Transaction Isolation |
Enumeration, one of:
|
The transaction isolation level to set on the driver when connecting the database |
|
|
Use XA Transactions |
Boolean |
Indicates whether or not the created data source must support XA transactions |
|
|
Database |
String |
Name of the database |
||
Subsub Protocol |
String |
Specifies where Derby should look for the database. The available options are:
|
|
|
Create |
Boolean |
Indicates whether to create the database if it does not already exist |
|
|
Connection Properties |
Object |
Specifies a list of custom key-value connection properties for the configuration |
||
Reconnection |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
Generic Connection
Configure these parameters for the connection provider that creates connections for any kind of database using a JDBC URL and the required JDBC driver class.
When you use a provider’s custom type in a Generic Connection, define the type inside the Column Types form of the Advanced section in the Database config.
As an example, for Oracle DB checkout their Constant Field Values page (section oracle.jdbc.OracleTypes) for the correct id/type name combination.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Pooling Profile |
Provides a way to configure database connection pooling |
|||
Column Types |
Array of Column Type |
Specifies non-standard column types |
||
Transaction Isolation |
Enumeration, one of:
|
The transaction isolation level to set on the driver when connecting the database |
|
|
Use XA Transactions |
Boolean |
Indicates whether or not the created datasource must support XA transactions |
|
|
URL |
String |
JDBC URL to use to connect to the database |
x |
|
Driver Class Name |
String |
Fully-qualified name of the database driver class |
x |
|
User |
String |
Database username |
||
Password |
String |
Database password |
||
Reconnection |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
Microsoft SQL Server Connection
Configure these parameters for the database connection provider implementation for Microsoft SQL Server databases.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Pooling Profile |
Provides a way to configure database connection pooling |
|||
Column Types |
Array of Column Type |
Specifies non-standard column types |
||
Transaction Isolation |
Enumeration, one of:
|
The transaction isolation level to set on the driver when connecting the database |
|
|
Use XA Transactions |
Boolean |
Indicates whether or not the created datasource must support XA transactions |
|
|
Host |
String |
Configures the host of the database |
x |
|
Instance Name |
String |
Configures the name of the SQL server instance where the database is located |
||
Port |
Number |
Configures the port of the database |
||
User |
String |
The user to use for authentication against the database |
||
Password |
String |
The password to use for authentication against the database |
||
Database Name |
String |
Name of the default database to work with |
||
Connection Properties |
Object |
Specifies a list of custom key-value connection properties for the configuration |
||
Reconnection |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
MySQL Connection
Configure these parameters to connect to a MySQL database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Pooling Profile |
Provides a way to configure database connection pooling |
|||
Column Types |
Array of Column Type |
Specifies non-standard column types |
||
Transaction Isolation |
Enumeration, one of:
|
The transaction isolation level to set on the driver when connecting the database |
|
|
Use XA Transactions |
Boolean |
Indicates whether or not the created datasource must support XA transactions |
|
|
Host |
String |
Configures the host of the database |
x |
|
Port |
Number |
Configures the port of the database |
x |
|
User |
String |
The user to use for authentication against the database |
||
Password |
String |
The password to use for authentication against the database |
||
Database |
String |
The name of the database |
||
Connection Properties |
Object |
Specifies a list of custom key-value connectionProperties for the configuration |
||
Reconnection |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
Oracle Connection
Configure these parameters to create connections to an Oracle database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Pooling Profile |
Provides a way to configure database connection pooling |
|||
Column Types |
Array of Column Type |
Specifies non-standard column types |
||
Transaction Isolation |
Enumeration, one of:
|
The transaction isolation level to set on the driver when connecting to the database |
NOT_CONFIGURED |
|
Use XA Transactions |
Boolean |
Indicates whether or not the created datasource must support XA transactions |
|
|
Host |
String |
Configures the host of the database |
x |
|
Port |
Number |
Configures the port of the database |
|
|
User |
String |
The user to use for authentication against the database |
||
Password |
String |
The password to use for authentication against the database |
||
Instance |
String |
The name of the database instance |
||
Service Name |
String |
The name of the database service name |
||
Reconnection |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
Associated Sources
Bulk Delete
<db:bulk-delete>
This operation allows delete operations to execute at various times using different parameter bindings and a single database statement. This improves performance compared to executing a single delete operation at various times.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Input Parameters |
Array of Object |
Specifies a list of maps, which contain the parameter names as keys and the value the parameter is bound to, and in which every list item represents a row to insert. |
|
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions |
|
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is true, in which case a default value of |
||
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
This parameter allows you to optionally specify the type of one or more of the parameters in the query. If a value is provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Bulk Insert
<db:bulk-insert>
This operation allows inserts to execute at various times using different parameter bindings and a single database statement. This improves performance compared to executing a single insert operation at various times.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Input Parameters |
Array of Object |
A list of maps in which every list item represents a row to be inserted, and the map contains the parameter names as keys and the value the parameter is bound to. |
|
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions. |
JOIN_IF_POSSIBLE |
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. No timeout is used by default. |
0 |
|
Query Timeout Unit |
Enumeration, one of:
|
A TimeUnit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a resultSet. This property is required when streaming is true; in that case a default value (10) is used. |
||
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows you to optionally specify the type of one or more of the parameters in the query. If provided, you’re not required to reference all of the parameters, but you cannot reference a parameter not present in the input values |
||
Target Variable |
String |
The name of a variable to store the operation’s output. |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors. |
Bulk Update
<db:bulk-update>
This operation allows updates to execute at various times using different parameter bindings and a single database statement. This improves performance compared to executing one single update operation at various times.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Input Parameters |
Array of Object |
Specifies a list of maps, which contain the parameter names as keys and the value the parameter is bound to, and in which every list item represents a row to insert. |
|
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions. |
JOIN_IF_POSSIBLE |
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows you to optionally specify the type of one or more of the parameters in the query. If provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Delete
<db:delete>
This operation deletes data in a database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions |
|
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows you to optionally specify the type of one or more of the parameters in the query. If a value is provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Input Parameters |
Object |
A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (for example, |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Execute DDL
<db:execute-ddl>
This operation allows execution of DDL queries against a database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions |
|
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Execute Script
<db:execute-script>
This operation executes an SQL script in a single database statement. The script is executed as provided by the user, without any parameter binding.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take for transactions. |
|
|
SQL Query Text |
String |
The text of the SQL query to execute |
||
Script Path |
String |
Specifies the location of a file to load. The file can point to a resource on the classpath, or on a disk. |
||
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Insert
<db:insert>
This operation inserts data into a database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions |
|
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows you to optionally specify the type of one or more of the parameters in the query. If provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Input Parameters |
Object |
A map in which keys are the name of an input parameter to be set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (E.g: where id = :myParamName)). The map’s values contain the actual assignation for each parameter. |
||
Auto Generate Keys |
Boolean |
Indicates when to make auto-generated keys available for retrieval. |
|
|
Auto Generated Keys Column Indexes |
Array of Number |
List of column indexes that indicates which auto-generated keys to make available for retrieval |
||
Auto Generated Keys Column Names |
Array of String |
List of column names that indicates which auto-generated keys to make available for retrieval |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Select
<db:select>
This operation queries data from a database. To prevent loading all the results at once, which can lead to performance and memory issues, results are automatically streamed. This means that pages of fetchSize rows are loaded when needed. If this operation is performed inside a transaction (that is, within a Try scope component) and that transaction is closed before consuming the data, accessing the results that haven’t been loaded will fail.
Some drivers, however, load the entire result upon query execution, which means streaming does not work. For example, sometimes Postgres drivers load the entire result into memory.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions |
|
|
Streaming Strategy |
|
Configure to use repeatable streams |
||
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows you to optionally specify the type of one or more of the parameters in the query. If provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Input Parameters |
Object |
A map in which keys are the name of an input parameter to be set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (for example: where id = :myParamName)). The map’s values will contain the actual assignation for each parameter. |
||
Target Variable |
String |
The name of a variable to store the operation’s output. |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors. |
Working with Pooling Profiles
When working with pooling profiles and the Select operation, the connection remains open until one of the following occurs:
-
The flow execution ends
-
The content of the streams are consumed completely
-
The connection is the transaction key.
Because LOBs are treated as streams, the connection remains open until the flow execution ends, or until the content is consumed before the flow completes, in which case the best approach is taken to close the related connection. |
This behavior occurs because the result set the operation generates can have a stream or be part of an ongoing transaction.
Query Single
<db:query-single>
This operation selects a single data record from a database. If you provide an SQL query that returns more than one row, then only the first record is processed and returned. This operation does not use streaming, which means that immediately after performing the Query Single operation, the complete content of the selected record is loaded into memory.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of join action that operations can take regarding transactions |
|
|
Streaming Strategy |
|
Configure to use repeatable streams |
||
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
The maximum number of rows that any ResultSet object generated by this message processor can contain. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Enables you to optionally specify the type of one or more of the parameters in the query. If provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Input Parameters |
Object |
A map in which keys are the name of an input parameter to be set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (for example: where id = :myParamName)). The map’s values will contain the actual assignation for each parameter. |
||
Target Variable |
String |
Name of the variable in which to store the operation’s output |
||
Target Value |
String |
Expression that evaluates the operation’s output. The expression outcome is stored in the target variable. |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors. |
Working with Pooling Profiles
When working with pooling profiles and the Query Single operation, the connection returns to the pool immediately after the operation is performed.
Stored Procedure
<db:stored-procedure>
Invokes a stored procedure on the database. When the stored procedure returns one or more ResultSet instances, results are not read all at once. Instead, results are automatically streamed to prevent performance and memory issues. This behavior means that pages of fetchSize
rows are loaded lazily when needed. If the Stored procedure operation is performed inside a transaction (for example, in a Try scope component), and that transaction is closed before consuming the data, accessing the results that haven’t been loaded will fail.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use. |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take regarding transactions. |
JOIN_IF_POSSIBLE |
|
Streaming Strategy |
|
Configure to use repeatable streams |
||
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. No timeout is used by default. |
0 |
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a resultSet. This property is required when streaming is true; in that case a default value (10) is used. |
||
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows to optionally specify the type of one or more of the parameters in the query. If provided, you’re not even required to reference all of the parameters, but you cannot reference a parameter not present in the input values |
||
Input Parameters |
Object |
A map in which keys are the name of an input parameter to be set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (for example: where id = :myParamName)). The map’s values will contain the actual assignation for each parameter. |
||
Input - Output Parameters |
Object |
A map in which keys are the name of a parameter to be set on the JDBC prepared statement which is both input and output. Each parameter should be referenced in the SQL text using a colon prefix (for example: where id = :myParamName)). The map’s values will contain the actual assignation for each parameter. |
||
Output Parameters |
Array of Output Parameter |
A list of output parameters to be set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (for example: call multiply(:value, :result)) |
||
Auto Generate Keys |
Boolean |
Indicates when to make auto-generated keys available for retrieval. |
|
|
Auto Generated Keys Column Indexes |
Array of Number |
List of column indexes that indicates which auto-generated keys to make available for retrieval. |
||
Auto Generated Keys Column Names |
Array of String |
List of column names that indicates which auto-generated keys should be made available for retrieval. |
||
Target Variable |
String |
The name of a variable to store the operation’s output. |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Working with Pooling Profiles
When working with pooling profiles and the Stored procedure operation, the connection remains open until the flow execution ends or the content of the streams are consumed completely, or if the connection is the transaction key. This behavior occurs because the resultset the operation generates can have a stream or be part of an ongoing transaction.
Starting with Database Connector 1.8.3, the connections on the Stored procedure operation are released if they are not part of a stream or transaction.
Update
<db:update>
Updates data in a database.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Transactional Action |
Enumeration, one of:
|
The type of joining action that operations can take for transactions |
|
|
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
SQL Query Text |
String |
The text of the SQL query to execute |
x |
|
Parameter Types |
Array of Parameter Type |
Allows you to optionally specify the type of one or more of the parameters in the query. If provided, you’re not required to reference all of the parameters; but you cannot reference a parameter that is not present in the input values. |
||
Input Parameters |
Object |
A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter should be referenced in the SQL text using a colon prefix (for example: where id = :myParamName)). The map’s values contain the actual assignation for each parameter. |
||
Auto Generate Keys |
Boolean |
Indicates when to make auto-generated keys available for retrieval |
|
|
Auto Generated Keys Column Indexes |
Array of Number |
List of column indexes that indicates which auto-generated keys to make available for retrieval |
||
Auto Generated Keys Column Names |
Array of String |
List of column names that indicates which auto-generated keys should be made available for retrieval |
||
Target Variable |
String |
The name of a variable to store the operation’s output |
||
Target Value |
String |
An expression to evaluate against the operation’s output and store the expression outcome in the target variable |
|
|
Reconnection Strategy |
A retry strategy in case of connectivity errors |
Sources
On Table Row
<db:listener>
This operation selects from a table at a regular interval and generates one message per obtained row. Optionally, you can provide watermark and ID columns. If a watermark column is provided, the values taken from that column are used to filter the contents of the next poll, so that only rows with a greater watermark value are returned. If an ID column is provided, this component automatically verifies that the same row is not picked twice by concurrent polls.
This operation does not support streaming, meaning that there is no need to perform additional transformations to the payload in order to access the operation results. This behavior is identical to the Query Single operation released in version 1.9.
Parameters
Name | Type | Description | Default Value | Required |
---|---|---|---|---|
Configuration |
String |
The name of the configuration to use |
x |
|
Table |
String |
The name of the table to select from |
x |
|
Watermark Column |
String |
The name of the column to use for a watermark. Values taken from this column are used to filter the contents of the next poll, so that only rows with a greater watermark value are processed. |
||
Id Column |
String |
The name of the column to consider as the row ID. If provided, this component makes sure that the same row is not processed twice by concurrent polls. |
||
Transactional Action |
Enumeration, one of:
|
The type of beginning action that sources can take regarding transactions |
|
|
Transaction Type |
Enumeration, one of:
|
The type of transaction to create. Availability depends on the runtime version. |
|
|
Primary Node Only |
Boolean |
Whether this source should be executed only on the primary node when running in a cluster |
||
Scheduling Strategy |
scheduling-strategy |
Configures the scheduler that triggers the polling |
x |
|
Redelivery Policy |
Defines a policy for processing the redelivery of the same message |
|||
Query Timeout |
Number |
Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout is used. |
|
|
Query Timeout Unit |
Enumeration, one of:
|
A time unit that qualifies the |
|
|
Fetch Size |
Number |
Indicates how many rows to fetch from the database when rows are read from a ResultSet. |
10 |
|
Max Rows |
Number |
Sets the limit for the maximum number of rows that any ResultSet object generated by this message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped. |
||
Reconnection Strategy |
A retry strategy in case of connectivity errors. |
Types
Pooling Profile
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Max Pool Size |
Number |
Maximum number of connections a pool maintains at any given time |
|
|
Min Pool Size |
Number |
Minimum number of connections a pool maintains at any given time |
|
|
Acquire Increment |
Number |
Determines how many connections at a time to try to acquire when the pool is exhausted |
|
|
Prepared Statement Cache Size |
Number |
Determines how many statements are cached per pooled connection. Setting this to zero disables statement caching. |
|
|
Max Wait |
Number |
The amount of time a client trying to obtain a connection waits for it to be acquired when the pool is exhausted. Setting this value to zero (default) means wait indefinitely. This is equivalent to |
|
|
Max Wait Unit |
Enumeration, one of:
|
A #maxWait. |
SECONDS |
|
Max Idle Time |
Number |
Determines how many seconds a connection can remain pooled but unused before being discarded. Setting this value to zero (default) means idle connections never expire. |
|
|
Additional Properties |
Object |
A map in which keys are the name of a pooling profile configuration property. Does not support the use of expressions. These properties cannot be used to override any of the previously specified properties (like Max Pool Size or Min Pool Size), the main property prevails if an attempt is made to override it. The map’s values contain the actual assignation for each parameter. |
||
Max Statement |
Number |
Defines the total number PreparedStatements a DataSource will cache. The pool destroys the least-recently-used PreparedStatement when it reaches the specified limit. When set to |
||
Test connection on checkout |
Boolean |
Disables connection testing on checkout to improve performance. If set to |
|
Column Type
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Id |
Number |
Type identifier used by the JDBC driver |
x |
|
Type Name |
String |
Name of the data type used by the JDBC driver |
x |
|
Class Name |
String |
Indicates which Java class must be used to map the database type |
Reconnection
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Fails Deployment |
Boolean |
When the application is deployed, a connectivity test is performed on all connectors. If set to |
||
Reconnection Strategy |
The reconnection strategy to use |
Reconnect
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Frequency |
Number |
How often to reconnect (in milliseconds) |
||
Count |
Number |
The number of reconnection attempts to make |
||
blocking |
Boolean |
If set to |
|
Reconnect Forever
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Frequency |
Number |
How often in milliseconds to reconnect |
||
blocking |
Boolean |
If set to |
|
Tls
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Enabled Protocols |
String |
A comma-separated list of protocols enabled for this context. |
||
Enabled Cipher Suites |
String |
A comma-separated list of cipher suites enabled for this context. |
||
Trust Store |
||||
Key Store |
||||
Revocation Check |
Trust Store
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Path |
String |
The location (which will be resolved relative to the current classpath and file system, if possible) of the trust store. |
||
Password |
String |
The password used to protect the trust store. |
||
Type |
String |
The type of store used. |
||
Algorithm |
String |
The algorithm used by the trust store. |
||
Insecure |
Boolean |
If true, no certificate validations will be performed, rendering connections vulnerable to attacks. Use at your own risk. |
Key Store
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Path |
String |
The location (which will be resolved relative to the current classpath and file system, if possible) of the key store. |
||
Type |
String |
The type of store used. |
||
Alias |
String |
When the key store contains many private keys, this attribute indicates the alias of the key that should be used. If not defined, the first key in the file will be used by default. |
||
Key Password |
String |
The password used to protect the private key. |
||
Password |
String |
The password used to protect the key store. |
||
Algorithm |
String |
The algorithm used by the key store. |
Standard Revocation Check
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Only End Entities |
Boolean |
Only verify the last element of the certificate chain. |
||
Prefer Crls |
Boolean |
Try CRL instead of OCSP first. |
||
No Fallback |
Boolean |
Do not use the secondary checking method (the one not selected before). |
||
Soft Fail |
Boolean |
Avoid verification failure when the revocation server can not be reached or is busy. |
Custom Ocsp Responder
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Url |
String |
The URL of the OCSP responder. |
||
Cert Alias |
String |
Alias of the signing certificate for the OCSP response (must be in the trust store), if present. |
Expiration Policy
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Max Idle Time |
Number |
A scalar time value for the maximum amount of time a dynamic configuration instance should be allowed to be idle before it’s considered eligible for expiration |
||
Time Unit |
Enumeration, one of:
|
A time unit that qualifies the |
Redelivery Policy
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Max Redelivery Count |
Number |
The maximum number of times a message can be redelivered and processed unsuccessfully before triggering a process-failed-message |
||
Use Secure Hash |
Boolean |
Whether to use a secure hash algorithm to identify a redelivered message. |
||
Message Digest Algorithm |
String |
The secure hashing algorithm to use. If this is not set, the default is SHA-256. |
|
|
Id Expression |
String |
Defines one or more expressions to use to determine when a message has been redelivered. This property can be set only if Use secure hash is set to |
||
Object Store |
Object Store |
The object store where the redelivery counter for each message is stored |
Parameter Type
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Key |
String |
The name of the input parameter |
x |
|
Type Classifier |
x |
Type Classifier
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Type |
Enumeration, one of:
|
|||
Custom Type |
String |
Statement Result
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Affected Rows |
Number |
|||
Generated Keys |
Object |
Repeatable In Memory Iterable
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Initial Buffer Size |
Number |
The number of instances that are initially allowed to be kept in memory to consume the stream and provide random access to it. If the stream contains more data than can fit into this buffer, then the buffer expands according to the Buffer size increment attribute, with an upper limit of Max in memory size. The default value is 100 instances. |
|
|
Buffer Size Increment |
Number |
Specifies by how much the buffer size expands if it exceeds its initial size. Setting a value of zero or lower means that the buffer should not expand, in which case a |
|
|
Max Buffer Size |
Number |
The maximum amount of memory to use. If more than the specified maximum amount of memory is used, then a `STREAM_MAXIMUM_SIZE_EXCEEDE`D error is raised. A value lower than, or equal to, zero means no limit. |
Repeatable File Store Iterable
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
In Memory Objects |
Number |
The maximum number of instances to keep in memory. If more than the specified maximum is required, then content starts to buffer on disk. |
||
Buffer Unit |
Enumeration, one of:
|
The unit in which maxInMemorySize is expressed |
Repeatable In Memory Stream
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
Initial Buffer Size |
Number |
The number of instances that are initially allowed to be kept in memory to consume the stream and provide random access to it. If the stream contains more data than can fit into this buffer, then the buffer expands according to the Buffer size increment attribute, with an upper limit of Max in memory size |
||
Buffer Size Increment |
Number |
Specifies by how much the buffer size expands if it exceeds its initial size. Setting a value of zero or lower means that the buffer should not expand, in which case a |
||
Max Buffer Size |
Number |
The maximum amount of memory to use. If more than the specified maximum amount of memory is used, then a |
||
Buffer Unit |
Enumeration, one of: BYTE KB MB GB |
The unit in which all these attributes are expressed |
Repeatable File Store Stream
Field | Type | Description | Default Value | Required |
---|---|---|---|---|
In Memory Size |
Number |
Defines the maximum memory that the stream should use to keep data in memory. If more than that is consumed content on the disk is buffered. |
||
Buffer Unit |
Enumeration, one of:
|
The unit in which Max in memory size is expressed |