salesforce Dreamforce On-Demand
Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Runtime (3.4)
  3. Developing
  4. Message Sources
  5. Studio Endpoints

  6. Database (JDBC) Endpoint Reference

Database (JBDC) Endpoint Reference

The Database Endpoint, which is sometimes referred to as the "JDBC Endpoint", implements a transport channel to connect your Mule application to any JDBC-compatible database. You can implement the Database endpoint as an inbound endpoint with a one-way exchange pattern, or as an outbound endpoint configured for either a one-way or request-response exchange pattern.

Use the Database Endpoint to perform, create, read, update, and delete operations (often referred to by the acronym CRUD) on data base record. If you are using an EE version, it can also invoke a procedure stored in a database, such as creating new tables dynamically.

Configuration

Database Endpoint configuration consists of three stages:

  1. Decide where you want to place the Database endpoint within your Mule flow, then drag it from the Palette to the appropriate place in the sequence of building blocks that make up your Mule flow.

    1. IF you place the Database endpoint at the beginning of the flow, it will act as a polling inbound endpoint (i.e., message source), triggering the flow by executing a query at intervals specified through the polling frequency field. This necessarily corresponds to a one-way exchange pattern for Inbound Endpoints.

      Database (JDBC) Inbound Endpoints can only be set to a one-way exchange pattern. Database (JDBC) Outbound Endpoints can be set to either one-way or request-response exchange pattern. For details about exchange patterns, see Mule Studio Essentials.

    2. If you place JDBC building block in the middle of or at the end of the flow, it will serve as an outbound endpoint, which is to say, as a processor that executes SQL code. Different SQL statements return different data types. For example, queries return a list of maps, Store Procedures (available only for EE) return a map, while other update statements return nothing.

  2. Configure the Database endpoint by providing values for the field on the various tabs on the Properties pane, which you open by double-clicking on the JDBC icon on the Message Flow canvas.

The Database Endpoint Properties pane contains five tabs, as detailed below.

General Tab

general_tab
Property Description

Display Name

Defaults to the generic endpoint name, which in this case is JDBC. Change the display name (which must be alpha-numeric) to reflect the endpoint’s specific role, i.e. Order Entry Endpoint.

Exchange-Pattern

If set to one-way (the default) for either an inbound or outbound endpoint, the Database endpoint sends incoming data to the next building block in the flow. If set to request-response, which is only possible for outbound Database endpoint, the endpoint waits for a responese before sending the data.

Query Key

Specifies which query to use when accessing the database.

SQL Statement

See SQL Statement for details.

Transaction Type

Select from a drop-down list the way database resources will be accessed. The available options are No Transactions, XA Transactions, and JBDC Transactions.

Transaction Action

Select from a drop-down list how the transaction will access the database.

Transaction Timeout

Enter a timeout in ms to wait for the Action to complete.

Interact with External

Check the box to allow Mule to interact with a transaction begun outside of Mule. As an example, if an external transaction is active, then BEGIN_OR_JOIN will join in, and ALWAY_BEGIN will cause an exception to be thrown.

SQL Statement

This panel (below) replaces the Query panel on the General Tab only when the Mule Enterprise Edition runtime has been specified during flow configuration.

sql_statement

This panel facilitates the specification of SQL queries as well as stored procedures. To select a stored procedure, complete the flowing steps:

  1. Click the green "plus" icon to the right of the Key text box in the SQL Statement panel to the General tab on the Properties pane of the Database (JDBC) Endpoint.

  2. After the Query dialog box (below) pops up, type the key of the stored procedure you want to identify into the text box labeled Query Key.

  3. In the large text box labeled Query, type the SQL statement you want to execute.

  4. (Optional) In the text box marked Query Reference, type ???

  5. When you are satisfied with your entries in the fields, click Finish to commit them.

    query

Advanced Tab

advanced_tab
Property Description

Address

Enter the address for this endpoint, i.e., jdbc://getTest?type=1.

Response Timeout

Specify how long the endpoint must wait for a response (in ms). The default is 1000 ms.

Encoding

Choose from a drop-down list the character set used for message data. (i.e. UTF-8).

Disabling Transport Transformer

Check this box if you do not want to use the endpoint’s default response transport.

MIME Type

Select from the drop-down list one of the formats this endpoint supports.

Query Timeout

(Applies to outbound Database endpoint only). Specify how long (in ms) the Database endpoint waits for the SQL statemetnt to return a response.

Polling Frequency

(Applies to inbound Database endpoint only). Specify how often to check for incoming messages. Default value is 1000 ms.

Reference Tab

references
Property Description

Endpoint Reference

Use the drop-down list to select a previously configured global endpoint reference. If you have not created a global element for this type of endpoint, you can do so from this window by clicking Add. Click Edit to modify a previously created global element.

Connector Reference

Use the drop-down list to select a previously configured connector for this endpoint. If you have not created a connector for this type of endpoint, you can do so from this window by clicking Add. Click Edit to modify a previously created global element. The following list the available types of global database connectors:

  • DB2 Data Source

  • Derby Data Source

  • MS SQL Data Source

  • MySQL Data Source

  • Oracle Data Source

  • PostgreSQL Data Source

Request Transformer References

Enter a list of synchronous transformer that will be applied to the request before it is sent to the transport.

Response Transformer References

Enter a list of synchronous transformer that will be applied to the response before it is returned from the transport.

This connector has one additional property that is currently only configurable at the global level in Studio, when you configure a Connector Reference:

Property Default Value Description

handleOutputResultSets

false

If set to true, returns a result set from calls to stored procedures.

To set this property to true, create or edit your Connector Reference, open the Advanced tab of the Global Element Configuration, and check the box next to Handle Output Result Sets.

Queries Tab

queries_tab

You can enter SQL queries for this endpoint, which consists of the following:

  • a key

  • a value

  • a value reference (optional)

Tips

  • DataMapper and iterative execution of SQL Statement: If you use a DataMapper to feed an ArrayList into a JDBC endpoint in your application, note that Mule executes your JDBC statement once for every item in the ArrayList that emerged from the DataMapper. This is expected behavior: when the payload is a list and the SQL statement contains parameters, Mule assumes the list contains the values to be inserted and employs the BatchUpdateSqlStatementStrategy. To more closely examine this behavior, access the following classes:

    • com.mulesoft.mule.transport.jdbc.sqlstrategy.EESqlStatementStrategyFactory (creates a SqlStatementStrategy based on the type of SQL and the payload)

    • com.mulesoft.mule.transport.jdbc.sqlstrategy.BatchUpdateSqlStatementStrategy

See Also

For details on setting the properties for a Database endpoint using an XML editor, consult the JDBC Transport Reference