--- schema_version: "1" id: dd2e0804b43ef08742fbd304ac6cb81a6fac5ae913d1514eaea4ced71599268f title: Migrating the Database Connector collection: mulesoft version: current locale: en-us portal_url: https://docs.salesforce.com/mulesoft/current/en-us/mule-runtime/4.3/migration-connectors-database.md canonical_url: https://docs.mulesoft.com/mule-runtime/4.3/migration-connectors-database source_site: docs.mulesoft.com content_type: documentation content_hash: e8e526c89f8c6d3d738c9e90f692cf26013bcdc08130671b8f017e26d4c986f2 description: MuleSoft Documentation Site release: "4.3" latest: false --- --- product: Mule Runtime version: 4.3 --- # Migrating the Database Connector > For the full documentation index, see: https://docs.mulesoft.com/llms.txt The main changes to the Database connector from Mule 3 to Mule 4 include: - All database operations (such as Select, Insert, Update, Delete, and others) have been refactored into separate operations. There are also new, dedicated bulk operations for Mule 4. - Use of DataWeave: Database operations, such as Insert and Update, support DataWeave version 2 transformations. You can construct the data sets that insert or update the database without message side effects, and you no longer use message enrichers. - You can perform dynamic and static queries in similar ways. - The Mule streaming framework is automatically configured for your operations. You can execute a select statement and process the results asynchronously without worrying about leaking connections. ## What’s Covered Here? - [Database Connection Configurations](#database_configuration) - [Data Source Reference](#data_source_ref) - [Examples: Detailed Connector Configurations](#connector_config_detail) - [Migrating Dynamic Queries](#database_dynamic_queries) - [Streaming Large Results](#database_streaming) - [Migrating Bulk Operations](#database_operation_bulk) - [Migrating the Bulk Execute Operation](#database_bulk_execute) - [Transactional Actions](#tx_actions) - [Output Target](#output_target) ## Database Connection Configurations The Database connectors for Mule 3 and Mule 4 support Derby, MySQL, Oracle, and generic database configurations. For Mule 4, Microsoft SQL Server and Data Source Reference connections are also supported. The XML for these connection configurations has changed in Mule 4: - Mule 3: Database elements such as `` are the top-level elements. - Mule 4: `` is the top-level element for all database connection configurations in Mule 4, and specific database configurations are nested under separate `` elements: - Derby: `` - Microsoft SQL Server: `` - MySQL: `` - Oracle: `` - Generic: `` - Data Source Reference Connection: `` Mule 3 Example: Derby Connection Configuration ```xml ``` Notice that `` in the Mule 3 example above changes to `` in the Mule 4 example below, and the attribute for the URL to the database changes from `url` to `database` in Mule 4. The `` child element is new in Mule 4. Mule 4 Examples: Derby Connection Configuration ```xml ``` Also note that in Mule 4, additional `` settings (XML attributes) are available: - `create="true|false"`: Whether to create the database if it does not exist. - `subsubProtocol="directory"`: Type of SubsubProtocol to be used by Derby. The available options are `directory`, `memory`, `classpath` and `jar`. The next section describes connection configurations for other databases. ### Data Source Reference These topics provide Mule 4 XML examples for other database connections and include connector-specific settings: - [Mule 4 Example: Data Source Reference](#example_data_source_db) - [Mule 4 Example: Generic](#example_generic_db) - [Mule 4 Example: Microsoft SQL Server](#example_mssql_db) - [Mule 4 Example: MySQL](#example_mysql_db) - [Mule 4 Example: Oracle JDBC Database](#example_oracle_db) - [Mule 4 Example: Microsoft SQL Server](#example_mssql_db) - [Example: Maven dependency setting for a Microsoft SQL Server driver](#example_driver) Note that the Mule 3 example for Derby in the previous section shows most of the main differences between Mule 3 and Mule 4 for database connection configurations, including My SQL, Oracle, and so on. It also shows the main migration pattern from Mule 3 to Mule 4 database connections: - Derby: [Database Connection Configurations](#database_configuration) #### Mule 4 Example: Data Source Reference To use a Data Source Reference you need to: 1 - Create a XML Spring Beans file creating the Spring Bean representing the `DataSource` datasource-spring-beans.xml ```xml ``` > [!WARNING] Is required to configure the Maven dependency which contains the DataSource > implementation. For this example, `org.enhydra.jdbc.standard.StandardDataSource`, the required > dependency is: > > `` > `com.experlog` > `xapool` > `1.5.0` > `` 2 - Create Data Source Connection
1Import the datasource-spring-beans.xml file using the Mule Spring Module [link to Spring Module]
2Create the DB Config with the DataSource Connection, and add as dataSourceRef the Spring Bean ID of the DataSource Bean created in datasource-spring-beans.xml.
```xml (1) (2) ``` This setting is unique to Data Source Reference: - `dataSourceRef="my_ref_here"`: Reference to a JDBC DataSource object. This object is typically created using Spring. When using XA transactions, an XADataSource object must be provided. #### Mule 4 Example: Generic ```xml ``` These setting are unique to a Generic database: - `url="url_here"` for the JDBC URL to be used to connect to the database. - `driverClassName="driver_class_name_here"` for the fully-qualified name of the database driver class. > [!WARNING] The Maven dependency which contains the Class referenced in the `driverClassName` > parameter should be configured in the Mule Application. > Also, due to new Mule 4 ClassLoading mechanism, this dependency must be declared as a Shared > Library to be exported to the DB Connector. See also, [Database Transactions](#db_transactions). #### Mule 4 Example: Microsoft SQL Server ```xml ``` See also, [Database Transactions](#db_transactions). #### Mule 4 Example: MySQL ```xml ``` See also, [Database Transactions](#db_transactions). #### Mule 4 Example: Oracle JDBC Database ```xml ``` See also, [Database Transactions](#db_transactions). #### Example: Maven dependency setting for a Microsoft SQL Server driver Microsoft SQL Server, MySQL, Derby, Oracle configurations require a driver. ```xml com.microsoft.sqlserver mssql-jdbc 6.2.2.jre8 ``` > [!WARNING] Because of the new Mule 4 ClassLoading mechanism, this dependency must be declared as a > Shared Library to be exported to the DB Connector. Using Studio, this will be automatically > configured. ### Examples: Detailed Connector Configurations In Mule 4, you can add child elements for these settings under the database connection configurations (such as ``): - [Database Transactions](#db_transactions) - [Transactional Actions](#tx_actions) - [Transaction Isolation](#tx_isolation) - [XA Transactions](#xa_tx) - [Connection Properties](#connection_properties) - [Column Types](#column_types) - [Pooling Profile](#pooling_profile) - [Reconnection Strategies](#reconnection_strategies) #### Database Transactions ##### Transaction Isolation Transactions are for the transaction isolation level to set on the driver when connecting the database. - **NOT\_CONFIGURED** (Default) - **READ\_COMMITTED** (`transactionIsolation="READ_COMMITTED"`) - **READ\_UNCOMMITTED** (`transactionIsolation="READ_UNCOMMITTED"`) - **REPEATABLE\_READ** (`transactionIsolation="REPEATABLE_READ"`) - **SERIALIZABLE** (`transactionIsolation="SERIALIZABLE"`) Mule 3 Example: Transaction Isolation ```xml ``` Mule 4 Example: Transaction Isolation ```xml ``` ##### XA Transactions Connections can be configured to work in XA Transactions, in both **Mule 3** and **Mule 4** this can be configured in the connection configuration setting `useXaTransactions="true"` Mule 3 Example: XA Transactions ```xml ``` Mule 4 Example: XA Transactions ```xml ``` #### Connection Properties **MySQL** and **Microsoft SQL Server** database configurations provide connection property settings, these properties are injected to the JDBC Connection as additional properties. Mule 3 Example: Connection Properties ```xml ``` Mule 4 Example: Connection Properties ```xml ``` #### Column Types - Column types (``) for non-standard column types. #### Pooling Profile - Pooling profile (``) for JDBC Data Sources capable of connection pooling connections. It is is almost identical to Connection Pooling in Mule 3. #### Reconnection Strategies Reconnection strategy settings are similar in Mule 3 and Mule 4. However, custom reconnection strategies are not available in Mule 4. The examples below show changes to the XML for these settings: - **None**: Default. Same as Do Not Use A Reconnection Strategy in Mule 3. - **Standard**: Same as Standard Reconnection in Mule 3. Both have the same options: Frequency (ms), Reconnection Attempts - **Forever**: Same as Reconnect Forever in Mule 3. Mule 3 Examples: Reconnection Settings ```xml ` ` ``` Mule 4 Examples: Reconnection Settings ```xml ``` ## Database Connector Operations - Query for SQL query text and input parameters - Streaming strategy settings (as shown here in [Streaming Large Results](#database_streaming)) - [Transactional Actions](#tx_actions) - Query settings - Error mapping for errors, including DB:BAD\_SQL\_SYNTAX, DB:QUERY\_EXECUTION - Mule 4 input and output metadata for attributes, payload, and variables ### Migrating Parameterized Queries In Both **Mule 3** and **Mule 4** input parameters are supplied as key-value pairs. Those keys are used with the semicolon character (:) to reference a parameter value by name, this means that written SQL parameterized queries in **Mule 3** doesn’t need changes to be used in **Mule 4**. This approach is recommended to avoid SQL injection attacks, and it allows the connector to perform optimizations that improve the overall performance of the Mule app. #### Input Parameters - In **Mule 3** each Input Parameter was defined as a `` element inside the `` operation which had an `name` attribute for the parameter name, and a `value` attribute which holds the parameter value. - In **Mule 4**, the input parameters are defined as Map ``, where the key is the name of the parameter, and the value as the parameter value. This map can only be created using DataWeave. Mule 3 Example: Parameterized SELECT Query ```xml SELECT * FROM employees WHERE last_name = :last_name ``` Mule 4 Example: Parameterized SELECT Query ```xml SELECT * FROM employees WHERE last_name = :last_name #[ { last_name : vars.lastName } ] ``` ### Migrating Dynamic Queries Sometimes, you not only need to parameterize the WHERE clause, but also parameterize parts of the query itself. Example use cases for this would be queries which need to hit online/historic tables depending on a condition, or complex queries where the project columns need to vary. In Mule 3, the concept of select was split in parameterized and dynamic queries, and you couldn’t use both at the same time. You had to choose between having a dynamic query or having the advantages of using parameters (SQL Injection protection, PreparedStatement optimization, etc.). Furthermore, the syntax to do one or the other was different, so you had to learn two different ways of doing the same thing. But with the new Database Connector in Mule 4, you can now use both methods at the same time by using expressions in the query: Mule 3 Example: Dynamic SELECT Query ```xml SELECT * FROM #[flowVars.table] WHERE name = #[flowVars.name] ``` Mule 4 Example: Dynamic SELECT Query ```xml #['SELECT * FROM $(vars.table) WHERE name = $(vars.name)'] ``` GOTCHA: The Mule 3 example is less performant and vulnerable to SQL Injection attacks since the entire query is being treated as a string instead of using PreparedStatements ## Streaming Large Results Streaming can handle results queries to big database tables, which might return tens of thousands of records, especially for integration use cases. Streaming is a great solution for this. ### Enabling Streaming In **Mule 3**, streaming is disabled by default, so you have to enable it to use it. In **Mule 4**, streaming is always on but you can configure the strategy through which the runtime will achieve repeatable streaming. For more information on this, please refer to the [streaming documentation page](https://docs.mulesoft.com/mule-runtime/4.3/streaming-about). TIP: Note that disabling repeatable streaming will prevent DataWeave and other components to process the a stream many times, even in parallel. ### Migrating Bulk Operations #### Enabling Bulk Mode In **Mule 3**, you set bulk functionality through a `bulkMode="true"` setting on **Insert, Update, and Delete** operations. In **Mule 4**, you use dedicated Bulk Insert (``), Bulk Update (``), and Bulk Delete (``) database, instead. This means that operations no longer change their behavior depending on the received payload. #### Consuming bulk data In **Mule 3**, in a bulk query, the connector worked similar to the `` component, it took the current payload value, expecting to be a collection of items, and executes the `` expressions with each value of the collection. In **Mule 4**, is slightly different, instead of consuming the payload and executing expressions for each element in a collection, is required to inject a collection of entries. ##### Examples: Mule 3 Example: Select with a Parameterized Query ```xml INSERT INTO customers (id, name, lastName) VALUES (:id, :name, :lastName) ``` Mule 4 Example: Using the Bulk Operation to Get Different Values ```xml INSERT INTO customers (id, name, lastName) VALUES (:id, :name, :lastName) #[[{ id: 2, name: 'George', lastName: 'Costanza' }, { id: 3, name: 'Cosmo', lastName: 'Kramer' }]] ``` ### Migrating the Bulk Execute Operation The `` operation from the Mule 3 connector is replaced by the `` operation. It’s important to note that even though the Mule 3 version uses the word `bulk`, it is not an actual bulk operation as in the examples above. Instead, it executes many individual statements using the same connection, but it does not create bulk prepared statements. So, in Mule 4, the operation is named `execute-script` to better describe what it is doing. Mule 4 Example: Using the `execute-script` operation ```xml update PLANET set NAME='Mercury' where POSITION=0; update PLANET set NAME='Mercury' where POSITION=4 ``` You can also extract the statements to an external file: Mule 4 Example: Using the `execute-script` operation with an external file ```xml ``` ### Transactional Actions When database operations are executed inside a transaction, they can determine how to interact with the transaction. Available Transactional Actions: - **ALWAYS\_JOIN** - **JOIN\_IF\_POSSIBLE** - **NOT\_SUPPORTED** Mule 3 Example: Transactional Action ```xml SELECT * FROM people ``` Mule 4 Example: Transactional Action ```xml SELECT * FROM people ``` ### Output Target In **Mule 3** the DB Connector had the capability of save the execution output in a desired target variable. In **Mule 4**, this can still be done, but with a few differences. In **Mule 3** what was considered as output was just the execution result, so when configuring, for example: `target="#[flowVars.targetVar]"` this meant that the query execution will be saved in the flow variable `targetVar`. In **Mule 4** is very similar, you have a `target` parameter which is the name of the target Flow Variable, this can’t be an expression, and the new parameter in **Mule 4** is `targetValue`, this is an expression that is evaluated against the operation result. For example this can be: `#[payload]`, to save the entire payload, or `#[payload[0]]` to just save the first row of a Select Query. Mule 3 Example: Output Target ```xml SELECT * FROM People ``` Mule 4 Example: Output Target ```xml SELECT * from People ``` To use the Database connector, simply add it to your application using the Studio palette or add the following dependency in your `pom.xml` file: ```xml org.mule.connectors mule-db-connector 1.2.0 mule-plugin ``` ## See Also - [Database Connector Overview](https://docs.mulesoft.com/db-connector/latest/) - [Streaming in Mule 4.0](https://docs.mulesoft.com/mule-runtime/4.3/streaming-about).