Database Connector Reference
This page covers the Database connector configuration parameters.
Select
Selects data from a database.
Attributes of <select…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
streaming |
Indicates if result sets must be returned as an iterator or as a list of maps.
|
fetchSize |
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.
|
maxRows |
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.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector will attempt to cancel a running query. No timeout is used by default.
|
Update
Updates data in a database.
Attributes of <update…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
bulkMode |
Indicates whether or not a bulk update is requested. When true, payload is required to be a collection and a bulk update executes for each item in the collection. Note: This attribute cannot be set dynamically through a MEL expression.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector attempts to cancel a running query. No timeout is used by default.
|
Delete
Deletes data from a database.
Attributes of <delete…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
bulkMode |
Indicates whether or not a bulk update is requested. When true, payload is required to be a collection and a bulk update executes for each item in the collection. Note: This attribute cannot be set dynamically through a MEL expression.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector will attempt to cancel a running query. No timeout is used by default.
|
Insert
Inserts data into a database.
Attributes of <insert…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
bulkMode |
Indicates whether or not a bulk update is requested. When true, payload is required to be a collection and a bulk update executes for each item in the collection. Note: This attribute cannot be set dynamically through a MEL expression.
|
autoGeneratedKeys |
Indicates when to make auto-generated keys available for retrieval.
|
autoGeneratedKeysColumnIndexes |
Comma-separated list of column indexes that indicates which auto-generated keys to make available for retrieval.
|
autoGeneratedKeysColumnNames |
Comma separated list of column names that indicates which auto-generated keys should be made available for retrieval.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector will attempt to cancel a running query. No timeout is used by default.
|
Execute DDL
Enables execution of DDL queries against a database.
Attributes of <execute-ddl…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector will attempt to cancel a running query. No timeout is used by default.
|
Bulk Execute
Updates data in a database.
Attributes of <bulk-execute…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
file |
The location of a file to load. The file can point to a resource on the classpath or on a disk.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector will attempt to cancel a running query. No timeout is used by default.
|
Stored Procedure
Executes a SQL statement in a database.
Attributes of <stored-procedure…>
| Name | Description |
|---|---|
config-ref |
Defines the configuration parameters for the JDBC connection.
|
source |
The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.
|
target |
The enricher expression used to enrich the current message with the result of the SQL processing.
|
transactionalAction |
Indicates how the message processor handles transactions.
|
streaming |
Indicates if result sets must be returned as an iterator or as list of maps.
|
fetchSize |
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.
|
maxRows |
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.
|
autoGeneratedKeys |
Indicates when to make auto-generated keys available for retrieval.
|
autoGeneratedKeysColumnIndexes |
Comma-separated list of column indexes that indicates which auto-generated keys to make available for retrieval.
|
autoGeneratedKeysColumnNames |
Comma separated list of column names that indicates which auto-generated keys should be made available for retrieval.
|
queryTimeout |
Indicates the minimum amount of time (in seconds) before the connector will attempt to cancel a running query. No timeout is used by default.
|
Template Query
JDBC User-Defined Data Types (UDT)
Specify a user-defined JDBC data type to be used when passing one to a stored procedure call. If you need to use a UDT in a stored procedure, specify the data type in the database configuration in a child element <db:data-type> of the parent element <db:data-types>. The user-defined data type parameter name (name) and type id (id) must be specified as in the example below, and as a parameter of the stored procedure you call.
JDBC type STRUCT is cast to a mapped Java class. There is no cast for a Java array to an SQL ARRAY.
|
Child Elements of <data-types…>
| Name | Cardinality | Description |
|---|---|---|
data-type |
1..* |
Requires a name that is a user-defined reference to a JDBC data type, and the integer specifier id for the data type. |
<data-types> Example
<db:oracle-config name="Oracle_Configuration" url="jdbc:oracle:thin:@54.175.245.218:1581:xe" user="user" password="4321" >
<db:data-types>
<db:data-type name="T_DEMO_OBJECTS" id="2003"/>
<db:data-type name="T_DEMO_OBJECT" id="2002" />
</db:data-types>
</db:oracle-config>| JDBC Data Type Code | id |
|---|---|
ARRAY |
2003 |
BIGINT |
-5 |
BINARY |
-2 |
BIT |
-7 |
BLOB |
2004 |
BOOLEAN |
16 |
CHAR |
1 |
CLOB |
2005 |
DATALINK |
70 |
DATE |
91 |
DECIMAL |
3 |
DISTINCT |
2001 |
DOUBLE |
8 |
FLOAT |
6 |
INTEGER |
4 |
JAVA_OBJECT |
2000 |
LONGNVARCHAR |
-16 |
LONGVARBINARY |
-4 |
LONGVARCHAR |
-1 |
NCHAR |
-15 |
NCLOB |
2011 |
NULL |
0 |
NUMERIC |
2 |
NVARCHAR |
-9 |
OTHER |
1111 |
REAL |
7 |
REF |
2006 |
REF_CURSOR |
2012 |
ROWID |
-8 |
SMALLINT |
5 |
SQLXML |
2009 |
STRUCT |
2002 |
TIME |
92 |
TIME_WITH_TIMEZONE |
2013 |
TIMESTAMP |
93 |
TIMESTAMP_WITH_TIMEZONE |
2014 |
TINYINT |
-6 |
VARBINARY |
-3 |
VARCHAR |
12 |
Pooling Profile
Provides a way to configure database connection pooling.
Attributes of <pooling-profile…>
| Name | Description |
|---|---|
maxPoolSize |
Maximum number of connections a pool maintains at any given time.
|
minPoolSize |
Minimum number of connections a pool maintains at any given time.
|
acquireIncrement |
Determines how many connections at a time to try to acquire when the pool is exhausted.
|
preparedStatementCacheSize |
Determines how many statements are cached per pooled connection. If changed to 0, statement caching is disabled.
|
maxWaitMillis |
The number of milliseconds a client calling getConnection() waits for a connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely.
|
Generic Config
Provides a way to define a JDBC configuration for any DB vendor.
Attributes of <generic-config…>
| Name | Description |
|---|---|
name |
Identifies the database configuration so other elements can reference it.
|
dataSource-ref |
Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.
|
url |
URL used to connect to the database. Supports MEL expressions.
|
useXaTransactions |
Indicates whether or not the created datasource has to support XA transactions. Default is false.
|
driverClassName |
Fully-qualified name of the database driver class. Supports MEL expressions.
|
connectionTimeout |
Maximum time in seconds that this data source will wait while attempting to connect to a database. A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout.
|
transactionIsolation |
The transaction isolation level to set on the driver when connecting the database.
|
Child Elements of <generic-config…>
| Name | Cardinality | Description |
|---|---|---|
pooling-profile |
0..1 |
Provides a way to configure database connection pooling. |
connection-properties |
0..1 |
Specifies a list of custom key-value connectionProperties for the config. Supports MEL expressions. |
data-types |
0..1 |
Specifies non-standard custom data types. |
mule:abstract-reconnection-strategy |
0..1 |
Derby Config
Attributes of <derby-config…>
| Name | Description |
|---|---|
name |
Identifies the database configuration so other elements can reference it.
|
dataSource-ref |
Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.
|
url |
URL used to connect to the database. Supports MEL expressions.
|
useXaTransactions |
Indicates whether or not the created datasource has to support XA transactions. Default is false.
|
driverClassName |
Fully-qualified name of the database driver class. Supports MEL expressions.
|
connectionTimeout |
Maximum time in seconds that this data source will wait while attempting to connect to a database. A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout.
|
transactionIsolation |
The transaction isolation level to set on the driver when connecting the database.
|
user |
The user that is used for authentication against the database. Supports MEL expressions.
|
password |
The password that is used for authentication against the database. Supports MEL expressions.
|
Child Elements of <derby-config…>
| Name | Cardinality | Description |
|---|---|---|
pooling-profile |
0..1 |
Provides a way to configure database connection pooling. |
connection-properties |
0..1 |
Specifies a list of custom key-value connectionProperties for the config. Supports MEL expressions. |
data-types |
0..1 |
Specifies non-standard custom data types. |
mule:abstract-reconnection-strategy |
0..1 |
Oracle Config
Attributes of <oracle-config…>
| Name | Description |
|---|---|
name |
Identifies the database configuration so other elements can reference it.
|
dataSource-ref |
Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.
|
url |
URL used to connect to the database. Supports MEL expressions.
|
useXaTransactions |
Indicates whether or not the created datasource has to support XA transactions. Default is false.
|
driverClassName |
Fully-qualified name of the database driver class. Supports MEL expressions.
|
connectionTimeout |
Maximum time in seconds that this data source will wait while attempting to connect to a database. A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout.
|
transactionIsolation |
The transaction isolation level to set on the driver when connecting the database.
|
user |
The user that is used for authentication against the database. Supports MEL expressions.
|
password |
The password that is used for authentication against the database. Supports MEL expressions.
|
host |
Configures just the host part of the JDBC URL (and leaves the rest of the default JDBC URL untouched). Supports MEL expressions.
|
port |
Configures just the port part of the JDBC URL (and leaves the rest of the default JDBC URL untouched).
|
instance |
Configures just the instance part of the JDBC URL (and leaves the rest of the default JDBC URL untouched). Supports MEL expressions.
|
Child Elements of <oracle-config…>
| Name | Cardinality | Description |
|---|---|---|
pooling-profile |
0..1 |
Provides a way to configure database connection pooling. |
connection-properties |
0..1 |
Specifies a list of custom key-value connectionProperties for the config. Supports MEL expressions. |
data-types |
0..1 |
Specifies non-standard custom data types. |
mule:abstract-reconnection-strategy |
0..1 |
MySQL Config
Attributes of <mysql-config…>
| Name | Description |
|---|---|
name |
Identifies the database configuration so other elements can reference it.
|
dataSource-ref |
Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.
|
url |
URL used to connect to the database. Supports MEL expressions.
|
useXaTransactions |
Indicates whether or not the created datasource has to support XA transactions. Default is false.
|
driverClassName |
Fully-qualified name of the database driver class. Supports MEL expressions.
|
loginTimeout |
Maximum time in seconds that this data source will wait while attempting to connect to a database. A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout.
|
transactionIsolation |
The transaction isolation level to set on the driver when connecting the database.
|
user |
The user that is used for authentication against the database. Supports MEL expressions.
|
password |
The password that is used for authentication against the database. Supports MEL expressions.
|
database |
The name of the database. Must be configured unless a full JDBC URL is configured. Supports MEL expressions.
|
host |
Configures just the host part of the JDBC URL (and leaves the rest of the default JDBC URL untouched). Supports MEL expressions.
|
port |
Configures just the port part of the JDBC URL (and leaves the rest of the default JDBC URL untouched).
|
Child Elements of <mysql-config…>
| Name | Cardinality | Description |
|---|---|---|
pooling-profile |
0..1 |
Provides a way to configure database connection pooling. |
connection-properties |
0..1 |
Specifies a list of custom key-value connectionProperties for the config. Supports MEL expressions. |
data-types |
0..1 |
Specifies non-standard custom data types. |
mule:abstract-reconnection-strategy |
0..1 |
SQL Server Config
To configure access to SQL Server Configuration use the Generic Configuration of the Database Connector. See the Generic Config for configuring Transaction properties and Connection Pooling properties.
Download the Microsoft JDBC driver if you do not have it.
Attributes of <generic-config…>
| Name | Type | Required | Default | Description |
|---|---|---|---|---|
name |
name (no spaces) |
yes |
Identifies the database configuration so other elements can reference it. |
|
url |
string |
no |
URL used to connect to the database. Supports MEL expressions. Example: jdbc:sqlserver://${db.host};database=${db.database};user=${db.user};password=${db.password} |
|
driverClassName |
string |
no |
Fully-qualified name of the database driver class. Supports MEL expressions. Example: com.microsoft.sqlserver.jdbc.SQLServerDriver |
Using Integrated Security
If you want to make use of the integrated security login feature when using the Database Connector with your Microsoft SQL instance, you need to perform some additional steps:
-
Configure the native libraries location inside the JDBC driver (sqljdbc4.jar).
-
Right click on your project name in Package Explorer
-
Click Build Path
-
Click Configure Build Path
-
Select sqljdbc4.jar
-
Select the child element Native Library Location (None)
-
Press the Edit… button
-
Select the folder that contains the sqljdbc_auth.dll native library.
-
Click Ok to confirm the changes.
-
-
You also need to add integratedSecurity=true to your connection URL. The line should look like the following string:
-
jdbc:sqlserver://${db.host};database=${db.database};integratedSecurity=true
-
See Also
-
Access the Database Connector Overview for an overview, user guide, and examples.