Mule 4 - Mock Oracle database insert using H2 in memory database how to override "to_date"

Question

Am trying to write Munit for a simple Database insert operation. Mule is 4.4 and using Oracle database.
However the database insert is using to_date

insert into emp_owner.emp (emp_id,start_date ) 
values (:empId,to_date(:startDate, 'YYYY-MM-DD')

In My Munit I am using MUnit Db Server Config and am using H2 in memory database to mock while unit testing

Here is the flow which I want to test:

<db:config name="Emp_Database_Config" doc:name="Database Config" doc:id="e9ad13a0-c3bd-4f08-a384-88c027344c83" >
    <db:oracle-connection 
        host="${emp.host}" 
        port="${emp.port}" 
        serviceName="${emp.serviceName}" 
        user="${emp.user}" 
        password="${emp.password}">
        <reconnection failsDeployment="true" >
            <reconnect />
        </reconnection>
        <db:pooling-profile maxPoolSize="7"/>
    </db:oracle-connection>
</db:config>

<sub-flow name="empSub_Flow" doc:id="ed349aec-45fb-43a5-9182-97613f666a78" >
    
    <logger level="INFO" doc:name="Start" doc:id="7603db9c-6322-47f3-8816-cece900b18e6" message="start"/>
    

<db:insert doc:name="Insert emp data" doc:id="626a7405-0afb-40c9-bfdc-c1732c9dc61d" config-ref="${emp.dbName}">
            <db:sql><![CDATA[insert into emp_owner.emp (emp_id,start_date ) 
values (:empId,to_date(:startDate, 'YYYY-MM-DD'))]]></db:sql>
            <db:input-parameters><![CDATA[#[{
   'empId' : payload.empId, 
   'startDate' : payload.start_date
}]]]></db:input-parameters>
        </db:insert>
        <logger level="INFO" doc:name="End" doc:id="0fd5c833-810e-4389-944d-b027403b5833" message="End"/>
        
    </sub-flow>

Here is the code for munit :

<munit:config name="emp-test-suite.xml" />
    
    
    <dbserver:config name="MUnit_DB_Server_Config_Emp" doc:name="MUnit DB Server Config" doc:id="96f4b81b-524f-4e4c-96a8-e9d71fda7a5a" >
        <dbserver:connection csv="emp_owner.csv" database="DATABASE_EMP" connectionStringParameters="MODE=Oracle" />
    </dbserver:config>
    
    <db:config name="Emp_Database_Config_Test">
        <db:generic-connection url="jdbc:h2:tcp://localhost/mem:DATABASE_EMP" driverClassName="org.h2.Driver" />
    </db:config>
    

    
    
    
    <munit:before-test name="emp-test-suiteBefore_Test" doc:id="11f4f175-6654-4b25-8957-8e8cf4c7e3f6" >
        <dbserver:execute doc:name="Execute" doc:id="f71d8154-5c1c-45f4-a73a-12212b03ea5f" config-ref="MUnit_DB_Server_Config_Emp" sql='#["CREATE SCHEMA IF NOT EXISTS emp_owner; SET SCHEMA emp_owner; "]'/>
    </munit:before-test>
    <munit:test name="emp-test-suite-empSub_FlowTest" doc:id="2e7fb1c2-1548-4d2b-a902-e9c15777a944" description="Test">
        <munit:behavior >
            <dbserver:execute doc:name="Create Emp table" doc:id="4b60d5b7-5a39-40d8-a65b-582115bc7dcd" 
                config-ref="MUnit_DB_Server_Config_Emp" sql="CREATE TABLE EMP ( Emp_Id NUMBER  NOT NULL , START_DATE TIMESTAMP (6) NOT NULL  ) " />
        </munit:behavior>
        <munit:execution >
            <flow-ref doc:name="Flow-ref to empSub_Flow" doc:id="61b55b0b-2351-49f1-b69a-d65fe40e3241" name="empSub_Flow"/>
        </munit:execution>
        <munit:validation >
            <dbserver:execute-query doc:name="Execute query" doc:id="0338ef43-4dc1-4823-b8d6-0160d1b5598d" config-ref="MUnit_DB_Server_Config_Emp" sql='#["select * from emp_owner.emp where emp_id = 10"]' />
            <munit-tools:assert-equals doc:name="Assert equals" doc:id="f1abc645-1730-4cc2-beef-9fed6b7690ca" actual="#[!isEmpty(payload)]" expected="#[true]" message="We should see data returned from database" />
        </munit:validation>
    </munit:test>

while running munit Oracle database is getting mocked with H2 , however the problem is with to_date function in Oracle which is not supported in H2.

Did do a google search as well as within SO and found solutions related to alias :

drop ALIAS if exists TO_DATE;

CREATE ALIAS TO_DATE as ' import java.text.*; @CODE java.util.Date toDate(String s, String dateFormat) throws Exception { if("YYYY-MM-DD".equals(dateFormat)) { dateFormat = "yyyy-MM-dd" ;
}
if("DD/MON/YY".equals(dateFormat)) { dateFormat = "dd/MMM/YY" ;
}

return new SimpleDateFormat(dateFormat).parse(s);
}
';

However I am not sure in case of Mule how I can include in My Munit config and use it ?

Answer 1

TO_DATE function is supported by H2 in the Oracle compatibility mode, you need to add ;MODE=Oracle to JDBC URL.