Connector for connecting to relation Databases through the JDBC API

Configurations

Config

Connector for connecting to relation Databases through the JDBC API

Parameters

Name Type Description Default Value Required

Name

String

The name of this configuration. With this name can be later referenced.

x 

Connection

The connection types that can be provided to this configuration.

x 

Connection Types

Data Source Reference Connection

ConnectionProvider implementation which creates DB connections from a referenced

Parameters
Name Type Description Default Value Required

Pooling Profile

Provides a way to configure database connection pooling.

 

Custom Data Types

Array of Custom Data Type

Specifies non-standard custom data 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 Strategy

A retry strategy in case of connectivity errors

 
Generic Connection

ConnectionProvider that creates connections for any kind of database using a JDBC URL and the required JDBC Driver Class

Parameters
Name Type Description Default Value Required

Pooling Profile

Provides a way to configure database connection pooling.

 

Custom Data Types

Array of Custom Data Type

Specifies non-standard custom data types

 

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

The transaction isolation level to set on the driver when connecting the database.

NOT_CONFIGURED

 

Use XA Transactions

Boolean

Indicates whether or not the created datasource has to support XA transactions. Default is false.

false

 

URL

String

JDBC URL to be used to connect to the database.

x 

Driver Class Name

String

Fully-qualified name of the database driver class.

x 

Reconnection Strategy

A retry strategy in case of connectivity errors

 
Derby Connection

Creates connections to a Derby database

Parameters
Name Type Description Default Value Required

Pooling Profile

Provides a way to configure database connection pooling.

 

Custom Data Types

Array of Custom Data Type

Specifies non-standard custom data types

 

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

The transaction isolation level to set on the driver when connecting the database.

NOT_CONFIGURED

 

Use XA Transactions

Boolean

Indicates whether or not the created datasource has to support XA transactions. Default is false.

false

 

Database

String

Name of the database

 

Subsub Protocol

String

Specifies the type of SubsubProtocol to be used by Derby. The available options are: 'directory', 'memory', 'classpath' and 'jar'.

directory

 

Create

Boolean

Indicates if the database should be created if it this not exist.

false

 

Connection Properties

Object

Specifies a list of custom key-value connectionProperties for the config.

 

Reconnection Strategy

A retry strategy in case of connectivity errors

 
MySQL Connection

Creates connections to a MySQL database.

Parameters
Name Type Description Default Value Required

Pooling Profile

Provides a way to configure database connection pooling.

 

Custom Data Types

Array of Custom Data Type

Specifies non-standard custom data types

 

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

The transaction isolation level to set on the driver when connecting the database.

NOT_CONFIGURED

 

Use XA Transactions

Boolean

Indicates whether or not the created datasource has to support XA transactions. Default is false.

false

 

Host

String

Configures the host of the database

x 

Port

Number

Configures the port of the database

x 

User

String

The user that is used for authentication against the database

 

Password

String

The password that is used 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 config.

 

Reconnection Strategy

A retry strategy in case of connectivity errors

 
Oracle Connection

Creates connections to a Oracle database

Parameters
Name Type Description Default Value Required

Pooling Profile

Provides a way to configure database connection pooling.

 

Custom Data Types

Array of Custom Data Type

Specifies non-standard custom data types

 

Transaction Isolation

Enumeration, one of:

  • NONE

  • READ_COMMITTED

  • READ_UNCOMMITTED

  • REPEATABLE_READ

  • SERIALIZABLE

  • NOT_CONFIGURED

The transaction isolation level to set on the driver when connecting the database.

NOT_CONFIGURED

 

Use XA Transactions

Boolean

Indicates whether or not the created datasource has to support XA transactions. Default is false.

false

 

Host

String

Configures the host of the database

x 

Port

Number

Configures the port of the database

1521

 

User

String

The user that is used for authentication against the database

 

Password

String

The password that is used for authentication against the database

 

Instance

String

The name of the database instance

 

Reconnection Strategy

A retry strategy in case of connectivity errors

 

Associated Operations

Operations

Bulk Delete

http://www.mulesoft.org/schema/mule/db:bulk-delete

Allows executing one delete statement various times using different parameter bindings. This happens using one single Database statement, which has performance advantages compared to executing one single delete operation various times. contains the parameter names as keys and the value the parameter is bound to. according to the order in which commands were added to the batch.

Parameters

Name Type Description Default Value Required

Configuration

String

The name of the configuration to use.

x 

Parameter Values

Array of Object

A List of Maps in which every list item represents a row to be inserted, and the map

#[payload]

 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Template

A reference to a globally defined query to be used as a template

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Array of Number

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Bulk Insert

http://www.mulesoft.org/schema/mule/db:bulk-insert

Allows executing one insert statement various times using different parameter bindings. This happens using one single Database statement, which has performance advantages compared to executing one single update operation various times. contains the parameter names as keys and the value the parameter is bound to. according to the order in which commands were added to the batch.

Parameters

Name Type Description Default Value Required

Configuration

String

The name of the configuration to use.

x 

Parameter Values

Array of Object

A List of Maps in which every list item represents a row to be inserted, and the map

#[payload]

 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Template

A reference to a globally defined query to be used as a template

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Array of Number

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Bulk Update

http://www.mulesoft.org/schema/mule/db:bulk-update

Allows executing one update statement various times using different parameter bindings. This happens using one single Database statement, which has performance advantages compared to executing one single update operation various times. contains the parameter names as keys and the value the parameter is bound to. according to the order in which commands were added to the batch.

Parameters

Name Type Description Default Value Required

Configuration

String

The name of the configuration to use.

x 

Parameter Values

Array of Object

A List of Maps in which every list item represents a row to be inserted, and the map

#[payload]

 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Template

A reference to a globally defined query to be used as a template

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Array of Number

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Delete

http://www.mulesoft.org/schema/mule/db:delete

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:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Input Parameters

Object

The map's values will contain the actual assignation for each parameter.

#[{}]

 

Template

A reference to a globally defined query to be used as a template

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Number

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Execute DDL

http://www.mulesoft.org/schema/mule/db:execute-ddl

Enables 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 be executed

x 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

the given number. If the limit is exceeded, the excess rows are silently dropped.

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Number

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Execute Script

http://www.mulesoft.org/schema/mule/db:execute-script

Executes a SQL script in one single Database statement. The script is executed as provided by the user, without any parameter binding. according to the order in which commands were added to the batch.

Parameters

Name Type Description Default Value Required

Configuration

String

The name of the configuration to use.

x 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

SQL Query Text

String

The text of the SQL query to be executed

 

Script Path

String

The location of a file to load. The file can point to a resource on the classpath or on a disk.

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

the given number. If the limit is exceeded, the excess rows are silently dropped.

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Array of Number

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Insert

http://www.mulesoft.org/schema/mule/db:insert

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:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Input Parameters

Object

The map's values will contain the actual assignation for each parameter.

#[{}]

 

Template

A reference to a globally defined query to be used as a template

 

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

 

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 on which the operation's output will be placed

 

Output

Type

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Select

http://www.mulesoft.org/schema/mule/db:select

Selects data from a database This works in tandem with the fetch size parameter

Parameters

Name Type Description Default Value Required

Configuration

String

The name of the configuration to use.

x 

Stream Response

Boolean

if enabled retrieves the result set in blocks so that memory is not exhausted in case of large data sets.

false

 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Input Parameters

Object

The map's values will contain the actual assignation for each parameter.

#[{}]

 

Template

A reference to a globally defined query to be used as a template

 

Target Variable

String

The name of a variable on which the operation's output will be placed

 

Output

Type

Any

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Stored Procedure

http://www.mulesoft.org/schema/mule/db:stored-procedure

Invokes a Stored Procedure on the database. This works in tandem with the fetch size parameter

Parameters

Name Type Description Default Value Required

Configuration

String

The name of the configuration to use.

x 

Streaming

Boolean

if enabled retrieves the result set in blocks so that memory is not exhausted in case of large data sets.

false

 

Transactional Action

Enumeration, one of:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Input Parameters

Object

The map's values will contain the actual assignation for each parameter.

#[{}]

 

Input - Output Parameters

Object

The map's values will contain the actual assignation for each parameter.

 

Output Parameters

Array of Output Parameter

text using a semicolon prefix (E.g: {@code call multiply(:value, :result)})

 

Template

A reference to a globally defined call to be used as a template

 

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

 

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 on which the operation's output will be placed

 

Output

Type

Object

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Update

http://www.mulesoft.org/schema/mule/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:

  • ALWAYS_JOIN

  • JOIN_IF_POSSIBLE

  • NOT_SUPPORTED

The type of joining action that operations can take regarding transactions.

JOIN_IF_POSSIBLE

 

Query Timeout

Number

default.

0

 

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

A {@link TimeUnit} which qualifies the {@link #queryTimeout}

SECONDS

 

Fetch Size

Number

streaming is {@code true}; in that case a default value (10) is used.

 

Max Rows

Number

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 be executed

 

Parameter Types

Array of Parameter Type

reference all of the parameters, but you cannot reference a parameter not present in the input values

 

Input Parameters

Object

The map's values will contain the actual assignation for each parameter.

#[{}]

 

Template

A reference to a globally defined query to be used as a template

 

Auto Generate Keys

Boolean

Indicates when to make auto-generated keys available for retrieval.

false

 

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 on which the operation's output will be placed

 

Output

Type

For Configurations.

Throws

  • DB:CONNECTIVITY  

  • DB:RETRY_EXHAUSTED  

Types

Pooling Profile

Field Type

Max Pool Size

Number

Min Pool Size

Number

Acquire Increment

Number

Prepared Statement Cache Size

Number

Max Wait

Number

Max Wait Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Custom Data Type

Field Type

Id

Number

Type Name

String

Class Name

String

Reconnect

Field Type

Frequency

Number

Count

Number

Blocking

Boolean

Reconnect Forever

Field Type

Frequency

Number

Parameter Type

Field Type

Key

String

Type Classifier

Type Classifier

Field Type

Type

Enumeration, one of:

  • BIT

  • TINYINT

  • SMALLINT

  • INTEGER

  • BIGINT

  • FLOAT

  • REAL

  • DOUBLE

  • NUMERIC

  • DECIMAL

  • CHAR

  • VARCHAR

  • LONGVARCHAR

  • DATE

  • TIME

  • TIMESTAMP

  • BINARY

  • VARBINARY

  • LONGVARBINARY

  • NULL

  • OTHER

  • JAVA_OBJECT

  • DISTINCT

  • STRUCT

  • ARRAY

  • BLOB

  • CLOB

  • REF

  • DATALINK

  • BOOLEAN

  • ROWID

  • NCHAR

  • NVARCHAR

  • LONGNVARCHAR

  • NCLOB

  • SQLXML

  • UNKNOWN

Custom Type

String

Bulk Query Definition

Field Type

Template

Sql

String

Parameter Types

Array of Parameter Type

Query Timeout

Number

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Fetch Size

Number

Max Rows

Number

Query

Field Type

Template

Input Parameters

Object

Sql

String

Parameter Types

Array of Parameter Type

Query Timeout

Number

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Fetch Size

Number

Max Rows

Number

Statement Result

Field Type

Affected Rows

Number

Generated Keys

Object

Output Parameter

Field Type

Key

String

Type Classifier

Stored Procedure Call

Field Type

In Out Parameters

Object

Output Parameters

Array of Output Parameter

Template

Input Parameters

Object

Sql

String

Parameter Types

Array of Parameter Type

Query Timeout

Number

Query Timeout Unit

Enumeration, one of:

  • NANOSECONDS

  • MICROSECONDS

  • MILLISECONDS

  • SECONDS

  • MINUTES

  • HOURS

  • DAYS

Fetch Size

Number

Max Rows

Number