Nav
You are viewing an older version of this section. Click here to navigate to the latest version.

Database Connector Reference

This page summarizes the configuration parameters of the Database Connector.

Note: In Mule 3.7 and newer, selected fields support MEL expressions. For more information, see the Database Connector guide.

Select

Selects data from a database.

Attributes of <select…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

streaming

boolean

no

false

Indicates if result sets must be returned as an iterator or as a list of maps.

fetchSize

integer

no

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

integer

no

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.

Child Elements of <select…​>

Name Cardinality Description

dynamic-query

0..1

template-query-ref

0..1

in-param

0..*

parameterized-query

0..1

in-param

0..1

abstractQueryResultSetHandler

0..1

Update

Updates data in a database.

Attributes of <update…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

bulkMode

boolean

no

false

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.

Child Elements of <update…​>

Name Cardinality Description

dynamic-query

0..1

template-query-ref

0..1

in-param

0..*

parameterized-query

0..1

in-param

0..1

Delete

Deletes data from a database.

Attributes of <delete…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

bulkMode

boolean

no

false

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.

Child Elements of <delete…​>

Name Cardinality Description

dynamic-query

0..1

template-query-ref

0..1

in-param

0..*

parameterized-query

0..1

in-param

0..1

Insert

Inserts data into a database.

Attributes of <insert…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

bulkMode

boolean

no

false

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.

autoGeneratedKeys

boolean

no

false

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

autoGeneratedKeysColumnIndexes

string

no

Comma-separated list of column indexes that indicates which auto-generated keys to make available for retrieval.

autoGeneratedKeysColumnNames

string

no

Comma separated list of column names that indicates which auto-generated keys should be made available for retrieval.

Child Elements of <insert…​>

Name Cardinality Description

dynamic-query

0..1

template-query-ref

0..1

in-param

0..*

parameterized-query

0..1

in-param

0..1

Execute ddl

Enables execution of DDL queries against a database.

Attributes of <execute-ddl…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

Child Elements of <execute-ddl…​>

Name

Cardinality

Description

dynamic-query

1..1

Bulk execute

Updates data in a database.

Attributes of <bulk-execute…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

file

string

no

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

Stored procedure

Executes a SQL statement in a database.

Attributes of <stored-procedure…​>

Name Type Required Default Description

config-ref

string

no

Defines the configuration parameters for the JDBC connection.

source

string

no

The expression used to obtain the value used to calculate parameters. Default is empty, so parameters are calculated from the payload.

target

string

no

#[payload]

The enricher expression used to enrich the current message with the result of the SQL processing.

transactionalAction

transactionalActionType

no

JOIN_IF_POSSIBLE

Indicates how the message processor handles transactions.

streaming

boolean

no

false

Indicates if result sets must be returned as an iterator or as list of maps.

fetchSize

integer

no

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

integer

no

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

boolean

no

false

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

autoGeneratedKeysColumnIndexes

string

no

Comma-separated list of column indexes that indicates which auto-generated keys to make available for retrieval.

autoGeneratedKeysColumnNames

string

no

Comma separated list of column names that indicates which auto-generated keys should be made available for retrieval.

Child Elements of <stored-procedure…​>

Name Cardinality Description

dynamic-query

0..1

template-query-ref

0..1

in-param

0..*

parameterized-query

0..1

in-param

0..1

out-param

0..1

inout-param

0..1

Template query

Attributes of <template-query…​>

Name Type Required Default Description

name

name (no spaces)

yes

Identifies the query so that other elements can reference it.

Child Elements of <template-query…​>

name Cardinality Description

dynamic-query

1..1

parameterized-query

1..1

in-param

0..*

template-query-ref

1..1

in-param

1..*

Connection properties

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

Child Elements of <connection-properties…​>

Name Cardinality Description

property

1..*

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. Note 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


          
       
1
2
3
4
5
6
<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

Attributes of <data-type…​>

Name Description

name

Reference to the JDBC type

  • Type: string

  • Required: yes

  • Default: none

id

identifier for the data type as specified in the class for JDBC data types java.sql.Types

  • Type: int

  • Required: yes

  • Default: none

Pooling profile

Provides a way to configure database connection pooling.

Attributes of <pooling-profile…​>

Name Type Required Default Description

maxPoolSize

integer

no

Maximum number of connections a pool maintains at any given time.

minPoolSize

integer

no

Minimum number of connections a pool maintains at any given time.

acquireIncrement

integer

no

Determines how many connections at a time to try to acquire when the pool is exhausted.

preparedStatementCacheSize

integer

no

5

Determines how many statements are cached per pooled connection. Defaults to 0, meaning statement caching is disabled.

maxWaitMillis

string

no

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 Type Required Default Description

name

name (no spaces)

yes

Identifies the database configuration so other elements can reference it.

dataSource-ref

string

no

Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.

url

string

no

URL used to connect to the database. Supports MEL expressions.

useXaTransactions

boolean

no

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

driverClassName

string

no

Fully-qualified name of the database driver class. Supports MEL expressions.

connectionTimeout

int

no

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

enumeration

no

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 Type Required Default Description

name

name (no spaces)

yes

Identifies the database configuration so other elements can reference it.

dataSource-ref

string

no

Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.

url

string

no

URL used to connect to the database. Supports MEL expressions.

useXaTransactions

boolean

no

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

driverClassName

string

no

Fully-qualified name of the database driver class. Supports MEL expressions.

connectionTimeout

int

no

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

enumeration

no

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

user

string

no

The user that is used for authentication against the database. Supports MEL expressions.

password

string

no

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 Type Required Default Description

name

name (no spaces)

yes

Identifies the database configuration so other elements can reference it.

dataSource-ref

string

no

Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.

url

string

no

URL used to connect to the database. Supports MEL expressions.

useXaTransactions

boolean

no

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

driverClassName

string

no

Fully-qualified name of the database driver class. Supports MEL expressions.

connectionTimeout

int

no

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

enumeration

no

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

user

string

no

The user that is used for authentication against the database. Supports MEL expressions.

password

string

no

The password that is used for authentication against the database. Supports MEL expressions.

host

string

no

Configures just the host part of the JDBC URL (and leaves the rest of the default JDBC URL untouched). Supports MEL expressions.

port

integer

no

Configures just the port part of the JDBC URL (and leaves the rest of the default JDBC URL untouched).

instance

string

no

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 Type Required Default Description

name

name (no spaces)

yes

Identifies the database configuration so other elements can reference it.

dataSource-ref

string

no

Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided.

url

string

no

URL used to connect to the database. Supports MEL expressions.

useXaTransactions

boolean

no

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

driverClassName

string

no

Fully-qualified name of the database driver class. Supports MEL expressions.

loginTimeout

int

no

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

enumeration

no

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

user

string

no

The user that is used for authentication against the database. Supports MEL expressions.

password

string

no

The password that is used for authentication against the database. Supports MEL expressions.

database

string

no

The name of the database. Must be configured unless a full JDBC URL is configured. Supports MEL expressions.

host

string

no

Configures just the host part of the JDBC URL (and leaves the rest of the default JDBC URL untouched). Supports MEL expressions.

port

integer

no

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

 

In Param

Attributes of <in-param…​>

Name Type Required Default Description

name

string

yes

The name of the input parameter.

value

string

yes

The value of the parameter.

type

ExtendedJdbcDataTypes

no

Parameter type name.

Out Param

Attributes of <out-param…​>

Name Type Required Default Description

name

string

yes

The name of the output parameter.

type

ExtendedJdbcDataTypes

no

Parameter type name.

Inout Param

Attributes of <inout-param…​>

Name Type Required Default Description

name

string

yes

The name of the output parameter.

value

string

yes

The value of the parameter.

type

ExtendedJdbcDataTypes

no

Parameter type name.

See Also