Mule Convert payload string to CLOB for Oracle DB Insert

Question

The basic gist of the problem is how to take a string payload and convert it to CLOB for an oracle <db:insert ... />

I'll need to write and read CLOB data to and from an oracle database. The typical approach for converting String to CLOB is to do something like db.getConnection().createClob() and then set the data into this created CLOB. It doesn't appear that within MULE, this is a legitimate option as I won't (or at least non-trivially) have access to the connection object. (perhaps i can do some hacky stuff with creating a new connection and converting in a transformer?)

Looking back through documentation and prior versions, there appears to be an old property of <jdbc:connector .../> as of V3.5.x that could handle individual rows from the database and provide a custom handler for these rows. This jdbc connector has been deprecated and I expect it will be removed in future versions.

So how does this work? How does mule intend to do this? How do i convert a String to CLOB for a <db:insert .../>

Below is a sample flow that will reproduce ths problem.

<db:oracle-config name="Oracle" host="localhost" port="1521" instance="testIns" user="myUser" password="myPass" doc:name="Oracle Configuration" />
<flow name="databaseInsertFlow">
    <file:inbound-endpoint path="C:\test\input" responseTimeout="10000" doc:name="File"/>
    <file:file-to-string-transformer doc:name="File to String"/>
    <db:insert config-ref="Oracle_Configuration" doc:name="Database">
        <!-- create table tblTest (cdata clob) -->
        <db:parameterized-query><![CDATA[insert into tblTest (cdata) values (#[payload])]]></db:parameterized-query>
    </db:insert>
</flow>

UPDATE: Further review of the problem has shown that the issues lies with using the ojdbc7.jar driver. Reverting to ojdbc6.jar resolved the problem using the above flow.

Beta033 Beta033 · Accepted Answer · 2017-03-02T22:54:05

I did find a solution for insert that works* but is a terrible and ugly hack.

This solution is to ignore the mule-provided database connector and roll your own.

below is the configuration needed:

<spring:beans>
  <spring:bean id="dbInserter" scope="prototype" class="kansas.InsertPayloadClob">
    <spring:property name="dbConnection">
        <spring:ref local="Oracle_Configuration"/>
    </spring:property>
  </spring:bean>
</spring:beans>
<flow name="databaseInsertFlow">
       <file:inbound-endpoint path="c:\test\input" responseTimeout="10000" doc:name="File"/>
       <file:file-to-string-transformer doc:name="File to String"/>
       <component doc:name="Java">
           <spring-object bean="dbInserter"/>
    </component>
   </flow>

and a simple class to actually perform the database insert

public class InsertPayloadClob implements Callable { 

private StaticDbConfigResolver dbConnection;

@Override
public Object onCall(MuleEventContext eventContext) throws Exception {
    String src = eventContext.getMessageAsString();
    DbConfig config = dbConnection.resolve(null);
    try {
        DbConnection conn = config.getConnectionFactory().createConnection(TransactionalAction.JOIN_IF_POSSIBLE);
        Clob clob = conn.createClob();
        clob.setString(1, src);
        try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO tblClobTest (cdata) values(?)")) {
            stmt.setClob(1, clob);
            stmt.executeUpdate();
          }
        return 1; 
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return 0;
}

public void setDbConnection(StaticDbConfigResolver o){
    this.dbConnection = o;
}

Mule Convert payload string to CLOB for Oracle DB Insert

2 Answers