Using Anypoint Code Builder to Configure Database Connector - Mule 4

Before You Begin

Before creating an integration project, you must:

Set up and access the Anypoint Code Builder web or desktop IDE
Have credentials to access the connector’s API
Ensure that the Visual Studio Code Extension Pack for Java is installed.

To use this connector, you must be familiar with:

The connector’s API
Anypoint Connectors
Mule runtime engine (Mule)

Creating a New Integration Project

To create a new integration project:

In the activity bar of the IDE, click the (Anypoint Code Builder) icon.
From Quick Actions, click Develop an Integration:

Complete the following in the Develop an Integration form:

Attribute Value

Attribute	Value
Project Name	Unique name for your project. This name is used as the title and name of the integration project file. For example, if the project name is "Salesforce Integration," the project file name is `salesforce-integration`.
Project Location	Your home directory or another directory you create. Click Browse to select a different directory for the integration project. Don’t create the project within another project directory. See Adding Folders to Your Home Directory.

Project Name

Unique name for your project.

This name is used as the title and name of the integration project file. For example, if the project name is "Salesforce Integration," the project file name is salesforce-integration.

Project Location

Your home directory or another directory you create. Click Browse to select a different directory for the integration project.

Don’t create the project within another project directory.

See Adding Folders to Your Home Directory.

Select the type of project to create by selecting either the Empty Project or Template or Example card.
Select the Mule runtime and Java Version for your app.
Click Create Project.

Adding the Connector to Your Integration Project

Anypoint Connectors provide operations for retrieving, modifying, and sending data to and from systems.

In addition to the built-in connectors that Anypoint Code Builder provides, you can download many other connectors from Anypoint Exchange.

To import a connector from Exchange and add it to your configuration:

In the Explorer view, open the configuration XML file for your project, such as my-project-name.xml.
Click the (Show Mule graphical mode) icon in the activity bar to open the canvas UI if it doesn’t open automatically.
Add the connector the same way you added other components from the canvas UI:
1. In the canvas UI, click the (Add component) icon.
2. In the Add Component panel, click Connectors.
3. Click the connector name and then click the operation to add, such as Publish:
  
  If the connector is not available locally, click the (Search in Exchange) toggle:
  
  1 Search locally
  
  2 Search in Exchange
4. Select the connector to add to your project.
5. Select the operation from the Add Component panel.

Adding a connector to an integration project does not make that connector available to other projects in your Anypoint Code Builder workspace.

Configuring the Trigger For the Flow

A trigger (source) initiates a flow when a specified condition is met. You can configure one of these triggers to use with Database Connector:

Database > On Table Row
Initiates a flow by selecting from a table at a regular interval and generates one message per obtained row
HTTP > Listener
Initiates a flow each time it receives a request on the configured host and port
Scheduler
Initiates a flow when a time-based condition is met.

See Scheduler (<scheduler/>).

For example, to configure the On Table Row source:

Add the On Table Row component to the canvas.
Click the On Table Row component on the canvas.

In the General tab, configure the following attributes:

UI Field	Description	Required
Table	Name of the database table to select from.	Yes
Watermark column	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.	No
Id column	Used in conjunction with Watermark column, which is the name of the column to consider as the row ID. If provided, this component ensures that the same row is not processed twice by concurrent polls.	No
Scheduling strategy	Triggers execution of the flow based on a time-based condition. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Configuring the Scheduling Strategy in XML.	Yes

UI Field

Description

Required

Table

Name of the database table to select from.

Yes

Watermark column

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.

No

Id column

Used in conjunction with Watermark column, which is the name of the column to consider as the row ID. If provided, this component ensures that the same row is not processed twice by concurrent polls.

No

Scheduling strategy

Triggers execution of the flow based on a time-based condition. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Configuring the Scheduling Strategy in XML.

Yes

Select the Advanced tab to configure the following attributes:

UI Field Description Required

UI Field	Description	Required
Transactional action	Type of joining action that operations can use in transactions: NONE (Default) ALWAYS_BEGIN	No
Transaction type	The type of transaction to create (XA or Local). Availability depends on the Mule runtime version. Local is the default.	No
Primary node only	Select this option to execute the On Table Row trigger only on the primary node when running in a cluster.	No
Redelivery policy	This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Redelivery Policy.	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Reconnection strategy	This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

Type of joining action that operations can use in transactions:

NONE (Default)
ALWAYS_BEGIN

No

Transaction type

The type of transaction to create (XA or Local). Availability depends on the Mule runtime version. Local is the default.

No

Primary node only

Select this option to execute the On Table Row trigger only on the primary node when running in a cluster.

No

Redelivery policy

This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Redelivery Policy.

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Reconnection strategy

This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring a Connector in XML

Although you can manually code a Mule app in XML, it is more efficient to use Anypoint Code Builder:

If you manually code a Mule runtime engine (Mule) app in XML from the Anypoint Code Builder XML editor, you can access the connector from your app by adding reference statements to both your XML Mule flow and the Apache Maven pom.xml file.
If you add the connector in Anypoint Code Builder, Studio automatically populates the XML code with the connector’s namespace and schema location and it also adds a dependency to the pom.xml file.

This example shows how the namespace statements are placed in the <mule> XML block:

<mule xmlns:&lt;value&gt;="http://www.mulesoft.org/schema/mule/&lt;value&gt;"
	xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
	xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns:file="http://www.mulesoft.org/schema/mule/file"
	xmlns="http://www.mulesoft.org/schema/mule/core"
	xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core
	http://www.mulesoft.org/schema/mule/core/current/mule.xsd
	http://www.mulesoft.org/schema/mule/file
	http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
	http://www.mulesoft.org/schema/mule/http
	http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
	http://www.mulesoft.org/schema/mule/ee/core
	http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd
	http://www.mulesoft.org/schema/mule/&lt;value&gt;
	http://www.mulesoft.org/schema/mule/&lt;value&gt;/current/mule-&lt;value&gt;.xsd">

The Apache Maven pom.xml file generated by Anypoint Code Builder contains dependencies for a Mule app. If you code a Mule app manually, include this XML snippet in your pom.xml file to enable access to this connector:

<dependency>
  <groupId>{group-id-exchange}</groupId>
  <artifactId>{artifact-id-exchange}</artifactId>
  <version>x.x.x</version>
  <classifier>mule-plugin</classifier>
</dependency>

Replace x.x.x with the version that corresponds to the connector you are using.

To obtain the most up-to-date pom.xml file information:

Go to Anypoint Exchange.
In Exchange, click Login and supply your Anypoint Platform username and password.
In Exchange, search for the connector.
Select the connector.
Click Dependency Snippets.

Configuring a Database Connection

Database (Database Connector) can connect to any database for which a JDBC driver is available. The following XML examples show how to connect to the most popular databases such as Derby, Microsoft SQL Server, MySQL, Oracle, and a generic database.

Configuring a Derby Database Connection

In the XML editor, the configuration looks like this:

<db:config name="Database_Config" >
	<db:derby-connection database="derbyDB" />
</db:config>

Configuring a Generic Connection

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
    <db:generic-connection driverClassName="org.h2.Driver"
    url="jdbc:h2:file:/tmp/datasenseDB"/>
</db:config>

Configuring a Microsoft SQL Server Connection

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
    <db:mssql-connection
    host="0.0.0.0"
    port="553"
    user="Max"
    password="password"
    databaseName="microsoftDB" />
</db:config>

Configuring a MySQL Database Connection

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
  <db:my-sql-connection
  host="0.0.0.0"
  port="3306"
  user="Max"
  password="POWERS"
  database="mysqlDB"/>
</db:config>

Configuring an Oracle Connection

In the XML editor, the configuration looks like this:

<db:config name="Database_Config">
  <db:oracle-connection
  host="192.168.99.100"
  port="1521" instance="xe"
  user="system"
  password="oracle" />
</db:config>

Configuring the JDBC Driver

After you configure the connections, add the JDBC driver. The following example shows how to add a driver for MySQL Database:

Add a Maven dependency to your project’s pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.48</version>
</dependency>

Configure your pom.xml file so that Mule exposes the driver dependency to Database Connector:

<build>
    <plugins>
        <!-- Only used to declare the shared libraries-->
        <plugin>
            <groupId>org.mule.tools.maven</groupId>
            <artifactId>mule-maven-plugin</artifactId>
            <version>1.0.0</version>
            <configuration>
                <sharedLibraries>
                    <sharedLibrary>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                    </sharedLibrary>
                </sharedLibraries>
            </configuration>
        </plugin>
    </plugins>
</build>

Adding Components to Your Project

Add components to your integration project to build a flow:

In the Explorer view, open the XML file for your project, such as my-project-name.xml.
Select Build a Flow from the start card to create an empty flow:
Change the name of the flow:
1. Click Flow.
2. Click the edit icon.
3. Enter the flow name, and click the checkmark.
4. In the canvas UI, click the (Add component) icon.
5. In the Add Component panel, search for and select your component from the results. The following example shows the Listener component from the HTTP search results:
  
  The configuration XML file now includes the XML for the HTTP Listener into the <flow/> element, for example:
  <flow name="getFlights" > <http:listener path="" config-ref="" doc:name="Listener" doc:id="ojzfry" /> </flow>

Configuring Component Attributes

After you add components to your integration project, configure the attributes in the component UI, or configure the attributes directly in the XML code editor.

Connector XML tags start with a namespace followed by an operation, following the syntax namespace:operation, such as <db:select/> for a Select operation in Anypoint Connector for Database (Database Connector).

You can use autocomplete from an expression (fx) field for a component to select DataWeave functions from the Core module and other values accepted in this field.

Configuring the Bulk Delete Operation

This operation enables delete operations to execute at various times using different parameter bindings and a single database statement.

Add the Bulk delete operation to the canvas.
Click the Bulk delete component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

UI Field Description Required

UI Field	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Configuring the Bulk Insert Operation

This operation enables 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.

Add the Bulk insert operation to the canvas.
Click the Bulk insert component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Bulk Update Operation

This operation enables 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.

Add the Bulk update operation to the canvas.
Click the Bulk update component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Delete Operation

This operation deletes data in a database.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Execute DDL Operation

This operation allows execution of DDL queries against a database.

Add the Execute DDL operation to the canvas.
Click the Execute DDL component on the canvas.
In the General tab, in SQL Query Text, add text for the SQL query to execute.

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Execute Script Operation

This operation executes a SQL script in a single database statement. The script is executed as you provide it, without any parameter binding.

Add the Execute Script operation to the canvas.
Click the Execute Script component on the canvas.

In the General tab, configure the following attributes:

UI Field	Description	Required
SQL Query Text	Text for the SQL query to execute.	No
Script path	Specifies the location of a file to load. The file can point to a resource on the classpath or on a disk.	No

UI Field

Description

Required

SQL Query Text

Text for the SQL query to execute.

No

Script path

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

No

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Insert Operation

This operation inserts data into a database.

Add the Insert operation to the canvas.
Click the Insert component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Auto Generate Keys	Indicates when to make auto-generated keys available for retrieval.	No
Auto Generated Keys Column Indexes	List of column indexes that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.	No
Auto Generated Keys Column Names	List of column names that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Auto Generate Keys

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

No

Auto Generated Keys Column Indexes

List of column indexes that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.

No

Auto Generated Keys Column Names

List of column names that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Query Single Operation

This operation selects a single data record from a database. If you provide a SQL query that returns more than one row, only the first record is processed and returned. This operation doesn’t use streaming, which means that immediately after performing the Query Single operation, the complete contents of the selected record is loaded into memory.

Add the Query Single operation to the canvas.
Click the Query Single component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Select Operation

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 streamed automatically. 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 fails.

Add the Select operation to the canvas.
Click the Select component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Streaming Strategy	This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See include::connectors::partial$acb-xml-examples.adoc[tag="streaming-strategy"].	No
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Streaming Strategy

This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See include::connectors::partial$acb-xml-examples.adoc[tag="streaming-strategy"].

No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Stored Procedure Operation

This operation 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 fails.

Add the Stored Procedure operation to the canvas.
Click the Stored Procedure component on the canvas.

In the General tab, configure the following attributes:

UI Field Description Required

UI Field	Description	Required
SQL Query Text	Text of the SQL query to execute.	Yes
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
Input-Output Parameters	This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Stored Procedure.	No
Output Parameters	This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Stored Procedure.	No

SQL Query Text

Text of the SQL query to execute.

Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

Input-Output Parameters

This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Stored Procedure.

No

Output Parameters

This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Stored Procedure.

No

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Auto Generate Keys	Indicates when to make auto-generated keys available for retrieval.	No
Auto Generated Keys Column Indexes	List of column indexes that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.	No
Auto Generated Keys Column Names	List of column names that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Auto Generate Keys

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

No

Auto Generated Keys Column Indexes

List of column indexes that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.

No

Auto Generated Keys Column Names

List of column names that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

Configuring the Update Operation

This operation updates data in a database.

Add the Update operation to the canvas.
Click the Update component on the canvas.

In the General tab, configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Input Parameters	A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.	No
SQL Query Text	Text of the SQL query to execute.	Yes

Input Parameters

A map in which keys are the name of an input parameter to set on the JDBC prepared statement. Each parameter must 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.

No

SQL Query Text

Text of the SQL query to execute.

Yes

Select the Advanced tab to configure the following attributes:

Attribute Description Required

Attribute	Description	Required
Transactional action	The type of joining action that operations can take for transactions: ALWAYS_JOIN JOIN_IF_POSSIBLE (Default) NOT_SUPPORTED	No
Query timeout	Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (`0`) is used.	No
Query timeout unit	Select the unit of time to use. The default is SECONDS.	No
Fetch size	Indicates how many rows to fetch from the database when rows are read from a `ResultSet`. This property is required when streaming is set to `true`, in which case a default value of `10` is used.	No
Max rows	Sets the limit for the maximum number of rows that any `ResultSet` object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.	No
Parameter types	Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.	No
Auto Generate Keys	Indicates when to make auto-generated keys available for retrieval.	No
Auto Generated Keys Column Indexes	List of column indexes that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.	No
Auto Generated Keys Column Names	List of column names that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.	No
Target Variable	Name of the variable that stores the operation’s output.	No
Target Value	Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.	No
Error Mappings	Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.	No
Reconnection Strategy	When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.	No

Transactional action

The type of joining action that operations can take for transactions:

ALWAYS_JOIN
JOIN_IF_POSSIBLE (Default)
NOT_SUPPORTED

No

Query timeout

Indicates the minimum amount of time before the JDBC driver attempts to cancel a running statement. By default, no timeout (0) is used.

No

Query timeout unit

Select the unit of time to use. The default is SECONDS.

No

Fetch size

Indicates how many rows to fetch from the database when rows are read from a ResultSet. This property is required when streaming is set to true, in which case a default value of 10 is used.

No

Max rows

Sets the limit for the maximum number of rows that any ResultSet object generated by the message processor can contain for the given number. If the limit is exceeded, the excess rows are silently dropped.

No

Parameter types

Array of Parameter Type. Use this parameter 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. You can’t reference a parameter that is not present in the input values.

No

Auto Generate Keys

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

No

Auto Generated Keys Column Indexes

List of column indexes that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.

No

Auto Generated Keys Column Names

List of column names that indicates which auto-generated keys to make available for retrieval. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Insert.

No

Target Variable

Name of the variable that stores the operation’s output.

No

Target Value

Expression that evaluates the operation’s output. The expression outcome is stored in the target variable.

No

Error Mappings

Creates custom error types that you map to Mule error types. The mappings can help you further differentiate and group errors in your app. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Mule Errors.

No

Reconnection Strategy

When an operation in a Mule application fails to connect to an external server, the default behavior is for the operation to fail immediately and return a connectivity error. You can modify the default behavior by configuring the reconnection strategy for the operation. This attribute is not currently enabled in the configuration UI. Configure it in the XML editor. See Reconnect and Reconnect Forever, and Configuring the Reconnection Strategy in XML.

No

XML Examples

Configuring the Reconnection Strategy in XML

The following is an XML example of a standard reconnection strategy setting the count of reconnection attempts before failing to 5 and setting failsDeployment to true, which causes the application deployment to fail if the connection test fails. Because a frequency is not specified, the retry interval is the default of every 2000 ms:

<db:config name="Database_Config" doc:name="Database Config" doc:id="79ae8d0f-e0af-4901-961d-b80befbf3f86" >
	<db:data-source-connection >
		<reconnection failsDeployment="true" >
           <reconnect count="5"/>
        </reconnection>
	</db:data-source-connection>
</db:config>

The following is an XML example of a reconnect forever strategy where the connector attempts to reconnect every 4000 ms for as long as the application runs:

<db:config name="Database_Config1" doc:name="Database Config" doc:id="c0aaf1dd-ab6e-49ee-9e4d-8b5799945cc9">
    <db:data-source-connection>
        <reconnection failsDeployment="true">
            <reconnection>
                <reconnect-forever frequency="4000"/>
            </reconnection>
        </reconnection>
    </db:data-source-connection>
</db:config>

Configuring the Streaming Strategy in XML

You can configure how Mule handles streams with streaming strategies.

File-Stored Repeatable Stream

The following is an XML example of reading a file that is configured with a 1 MB buffer:

<file:read path="bigFile.json">
  <repeatable-file-store-stream
    inMemorySize="1"
    bufferUnit="MB"/>
</file:read>

If you consistently process a file that’s no larger than 10 KB, you can save memory by reducing the inMemorySize attribute:

<file:read path="smallFile.json">
  <repeatable-file-store-stream
    inMemorySize="10"
    bufferUnit="KB"/>
 </file:read>

In-Memory Repeatable Stream

The following XML example shows an in-memory repeatable stream with a 512 KB initial size, which grows at a rate of 256 KB and allows up to 2000 KB (2 MB) of content in memory:

<file:read path="exampleFile.json">
  <repeatable-in-memory-stream
    initialBufferSize="512"
    bufferSizeIncrement="256"
    maxInMemorySize="2000"
    bufferUnit="KB"/>
</file:read>

Configuring the Scheduling Strategy in XML

Fixed Frequency

This is an example of the fixed frequency scheduling strategy XML using the default values:

<flow name="db-connector-acbFlow" doc:id="1c863ef7-d9e6-48ab-be8e-f1465d2b619d" >
	<db:listener doc:name="On Table Row" doc:id="81d4cf34-bb0d-497c-bc3f-316f4e79ad85" >
		<scheduling-strategy >
			<fixed-frequency frequency="1000" startDelay="0" timeUnit="MILLISECONDS"/>
		</scheduling-strategy>
	</db:listener>
</flow>

Cron

For more complex scheduling strategies, you can use a cron expression:

<flow name="db-connector-acbFlow" doc:id="1c863ef7-d9e6-48ab-be8e-f1465d2b619d" >
	<db:listener doc:name="On Table Row" doc:id="81d4cf34-bb0d-497c-bc3f-316f4e79ad85" >
    <scheduling-strategy>
      <cron expression="0 0 12 * * ?" timeZone="America/Los_Angeles"/>
    </scheduling-strategy>
  </db:listener>
</flow>

Creating a New Configuration XML File

Anypoint Code Builder creates the initial Mule configuration XML file in src/main/mule within the Mule project. This file contains global configurations and flow structures.

You can create additional configuration files, for example, to store global configurations.

To create a new XML configuration file:

In the Explorer view, right-click the mule folder (src/main/mule) and select New File.

Alternatively, use the menu.
Show me how
Select the mule folder, and then:
- In the desktop IDE, select File > New Mule Configuration File.
- In the cloud IDE, click the (menu) icon, and select File > New File.
Enter a name for the new file with the .xml extension.
In the Editor view for the new file, press Ctrl+Space to display available options.

Select Empty mule configuration:

New Mule configuration option in auto-correct

This command adds the following code to the file:

<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
    xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
    http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd">

</mule>

1	Search locally
2	Search in Exchange

Using Anypoint Code Builder to Configure Database Connector - Mule 4

Before You Begin

Creating a New Integration Project

Adding the Connector to Your Integration Project

Configuring the Trigger For the Flow

Configuring a Connector in XML

Configuring a Database Connection

Configuring a Derby Database Connection

Configuring a Generic Connection

Configuring a Microsoft SQL Server Connection

Configuring a MySQL Database Connection

Configuring an Oracle Connection

Configuring the JDBC Driver

Adding Components to Your Project

Configuring Component Attributes

Configuring the Bulk Delete Operation

Configuring the Bulk Insert Operation

Configuring the Bulk Update Operation

Configuring the Delete Operation

Configuring the Execute DDL Operation

Configuring the Execute Script Operation

Configuring the Insert Operation

Configuring the Query Single Operation

Configuring the Select Operation

Configuring the Stored Procedure Operation

Configuring the Update Operation

XML Examples

Configuring the Reconnection Strategy in XML

Configuring the Streaming Strategy in XML

File-Stored Repeatable Stream

In-Memory Repeatable Stream

Configuring the Scheduling Strategy in XML

Fixed Frequency

Cron

Creating a New Configuration XML File

See Also