Mule 4: Database Connector (Part 2)

Dynamic Queries:

To get rid of SQL Injection, we need to parameterize the "where" clause in our SQL query. What do we need to do when we don't only need to parameterize the "where" clause but also parts of the query? In Mule 3, we can't achieve this as in Mule 4 from the DB connector drop down. We need to select that we are using Dynamic query or parameterized. You have to choose between having a dynamic query and parameterized. In Mule 4 DB connector, we can use parameterized "where" clause and parts of the query simultaneously. In this example, you can see how a full expression is used to produce the query by building a string in which the table depends on a variable. An important thing to notice is that although the query text is dynamic, it is still using input parameters:

<set-variable variableName="table" value="EMPLOYEE"/>
<db:select config-ref="dbConfig">
 <db:sql>#["SELECT * FROM $(vars.table) WHERE name = :name"]</db:sql>
 <db:input-parameters>
   #[{'name' : payload}]
 </db:input-parameters>
</db:select>

Here comes the question, "Can we use dynamic queries here even though we can also declare table name as Input Parameter?" The answer is no. Input Parameters can only be applied to parameters in a "where" clause. To modify any other part of the query, you need to use DW’s operator.

Streaming Large Results:

Database results can be huge. One single query might return millions of records. Streaming is a recommended solution for this. What does streaming mean? Suppose you have a query that returns 20K rows. Attempting to fetch all those rows at once will result in the following:

• Performance degradation, since that’s a big pull from the network.
• A risk of causing out of memory, since all that information needs to be loaded into application memory.

Streaming means that the connector will not fetch the 20K rows at once; instead, it will fetch a smaller chunk, and once that chunk has been consumed, it will go fetch the rest. That way, you can reduce enhance the performance of your flow.

Streaming in Mule 4 vs Streaming in Mule 3:

In Mule 3, this was something you had to specifically enable because it was disabled by default. In Mule 4, this is enabled by default, so you don’t have to worry about it anymore. You can simply trust that the feature is already there.

Insert, Update, and Delete Operations :

The insert, update, and delete operations were also upgraded in Mule 4 DB connector. You can use DataWeave parameters and get dynamic queries as well:

<db:insert config-ref="dbConfig">
  <db:sql>
    INSERT INTO EMPLOYEE(EMPLOYEE_ID, NAME, DESCRIPTION) VALUES (777, 'Pryank', :description)
  </db:sql>
  <db:input-parameters>
    #[
    {'description' : payload}
    ]
  </db:input-parameters>
</db:insert>

<db:update config-ref="dbConfig">
  <db:sql>
    UPDATE EMPLOYEE SET DESCRIPTION = :description where EMPLOYEE_ID = :employeeId
  </db:sql>
  <db:input-parameters>
  #[
    {'description' : payload,
    'employeeId' : 7,
    }
  ]
  </db:input-parameters>
</db:update>

<db:delete config-ref="dbConfig">
  <db:sql>
    DELETE FROM EMPLOYEE where EMPLOYEE_ID = :employeeId
  </db:sql>
  <db:input-parameters>
  #[
    {'employeeId' : 7}
  ]
  </db:input-parameters>
</db:delete>

Hope this helps! Keep learning!