search

wso2 / micro-integrator

The cloud-native configuration driven runtime that helps developers implement composite microservices.
https://wso2.com/integration/
Apache License 2.0
198 stars 215 forks source link

Unable to insert NULL date on Micro Integrator 1.2.0 with Oracle DB, MySQL #3204

Open jayasithu opened 2 years ago

jayasithu commented 2 years ago

Description

Unable to pass a null value to a DATE column using a dataservice in WSO2 Micro Integrator 1.2.0. We tested this with the sample provided in documentation[1] and tested out this scenario in an Oracle 11g database and MySQL database and we were able to observe an error when passing a NULL to a date column via the dataservice.

following is the dataservice that was used to test the scenario,

<data enableBatchRequests="true" name="RDBMSDataService" serviceGroup="" serviceNamespace="">
    <description/>
    <query id="GetEmployeeDetails" useConfig="MysqlConJNDI1">
        <sql>select EmployeeDate, EmployeeNumber, FirstName, LastName from Employees where EmployeeNumber=:EmployeeNumber</sql>
        <param name="EmployeeNumber" paramType="SCALAR" sqlType="STRING"/>
        <result element="Employees" rowName="Employee">
            <param name="EmployeeDate" paramType="SCALAR" sqlType="DATE"/>
            <element column="EmployeeNumber" name="EmployeeNumber" xsdType="xs:string"/>
            <element column="FirstName" name="FirstName" xsdType="xs:string"/>
            <element column="LastName" name="LastName" xsdType="xs:string"/>
        </result>
    </query>
    <config id="MysqlConJNDI1">
        <property name="carbon_datasource_name">MysqlConJNDI1</property>
    </config>
    <query id="AddEmployeeDetails" useConfig="MysqlConJNDI1">
        <sql>insert into Employees (EmployeeDate, EmployeeNumber, FirstName, LastName) values(:EmployeeDate,:EmployeeNumber,:FirstName,:LastName)</sql>
        <param name="EmployeeDate" paramType="SCALAR" sqlType="DATE" defaultValue="#{SYSTEM_DATE}"/>
        <param name="EmployeeNumber" paramType="SCALAR" sqlType="STRING"/>
        <param name="FirstName" paramType="SCALAR" sqlType="STRING"/>
        <param name="LastName" paramType="SCALAR" sqlType="STRING"/>
    </query>
    <query id="UpdateEmployeeDetails" useConfig="MysqlConJNDI1">
        <param name="EmployeeNumber" paramType="SCALAR" sqlType="STRING"/>
        <sql>update Employees set EmployeeDate=:EmployeeDate, FirstName=:FirstName, LastName=:LastName where EmployeeNumber=:EmployeeNumber</sql>
        <param name="EmployeeDate" paramType="SCALAR" sqlType="DATE"/>
        <param name="FirstName" paramType="SCALAR" sqlType="STRING"/>
        <param name="LastName" paramType="SCALAR" sqlType="STRING"/>
    </query>
    <resource method="GET" path="Employee/{EmployeeNumber}">
        <call-query href="GetEmployeeDetails">
            <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
        </call-query>
    </resource>
    <resource method="POST" path="Employee">
        <call-query href="AddEmployeeDetails">
            <with-param name="EmployeeDate" query-param="EmployeeDate"/>    
            <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
            <with-param name="FirstName" query-param="FirstName"/>
            <with-param name="LastName" query-param="LastName"/>
        </call-query>
    </resource>
    <resource method="PUT" path="Employee">
        <call-query href="UpdateEmployeeDetails">
            <with-param name="EmployeeDate" query-param="EmployeeDate"/>
            <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
            <with-param name="FirstName" query-param="FirstName"/>
            <with-param name="LastName" query-param="LastName"/>
        </call-query>
    </resource>
</data>

Following is the datasource that was used to connect to the MySQL and Oracle database

MySQL

<datasource>
    <name>MysqlConJNDI1</name>
    <description>MySQL Connection</description>
    <jndiConfig useDataSourceFactory="false">
        <name>MysqlConJNDI1</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName>
            <url>jdbc:mysql://localhost:3306/dss</url>
            <username>root</username>
            <password>root</password>
        </configuration>
    </definition>
</datasource>

**Oracle** 

<datasource>
    <name>MysqlConJNDI1</name>
    <description>MySQL Connection</description>
    <jndiConfig useDataSourceFactory="false">
        <name>MysqlConJNDI1</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
            <url>jdbc:oracle:thin:@localhost:1521/XE</url>
            <username>regadmin</username>
            <password>regadmin</password>
        </configuration>
    </definition>
</datasource>

And this is the sample payload.xml that was used to Insert the data into the database,

<_postemployee>
    <EmployeeDate>2022-06-25</EmployeeDate>
    <EmployeeNumber>12</EmployeeNumber>
    <FirstName>Harry</FirstName>
    <LastName>Potter</LastName>
</_postemployee>

curl -X POST -H 'Accept: application/xml' -H 'Content-Type: application/xml' --data "@employee-payload.xml" http://localhost:8290/services/RDBMSDataService/employee

Scenario 01

With this setup at first a NULL value was passed to the payload.xml filw as the sample below and executed the curl command

<_postemployee>
    <EmployeeDate>NULL</EmployeeDate>
    <EmployeeNumber>12</EmployeeNumber>
    <FirstName>Harry</FirstName>
    <LastName>Potter</LastName>
</_postemployee>

Then we were able to observe the following errors

CURL Response:

[2022-06-16 17:44:33,725] INFO {LogMediator} - {proxy:dssCallMediatorInlineSingleRequestProxy} To: /services/dssCallMediatorInlineSingleRequestProxy, MessageID: urn:uuid:2114c169-cb05-4f35-8c48-5c8b0f62c628, correlation_id: 2114c169-cb05-4f35-8c48-5c8b0f62c628, Direction: request, MESSAGE = Executing default 'fault' sequence, ERROR_CODE = 0, ERROR_MESSAGE = DataService exception occurred while accessing the dataservice to do the operation, Envelope: <?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body/></soapenv:Envelope>

MI Log:

[2022-06-16 17:44:33,685] ERROR {DataServiceCallMediator} - {proxy:dssCallMediatorInlineSingleRequestProxy} DataService exception occurred while accessing the dataservice to do the operation DS Fault Message: Error in DS non result invoke.
DS Code: INCOMPATIBLE_PARAMETERS_ERROR
Nested Exception:-
javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': date string can not be less than 19 characters
DS Code: INCOMPATIBLE_PARAMETERS_ERROR
Source Data Service:-
Name: DSSCallMediatorTest
Location: /home/jayasithu/Desktop/Tickets/TMGDCSUB-241/wso2mi-4.0.0/tmp/carbonapps/-1234/1655380186370NullValueCompositeExporter_1.0.0-SNAPSHOT.car/DSSCallMediatorTest_1.0.0/DSSCallMediatorTest-1.0.0.dbs
Description: N/A
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: addEmployee
Current Params: {lastName=Parker, firstname=Ann, Date=NULL, employeeNumber=101}
Nested Exception:-
java.lang.NumberFormatException: date string can not be less than 19 characters
 at org.wso2.micro.integrator.dataservices.core.dispatch.SingleDataServiceRequest.processSingleRequest(SingleDataServiceRequest.java:117)
 at org.wso2.micro.integrator.dataservices.core.dispatch.SingleDataServiceRequest.processRequest(SingleDataServiceRequest.java:66)
 at org.wso2.micro.integrator.dataservices.core.dispatch.DataServiceRequest.dispatch(DataServiceRequest.java:358)
 at org.wso2.micro.integrator.dataservices.core.dispatch.BatchDataServiceRequest.processRequest(BatchDataServiceRequest.java:106)
 at org.wso2.micro.integrator.dataservices.core.dispatch.DataServiceRequest.dispatch(DataServiceRequest.java:358)
 at org.wso2.micro.integrator.dataservices.core.DataServiceProcessor.dispatch(DataServiceProcessor.java:40)
 at org.wso2.micro.integrator.mediator.dataservice.DataServiceCallMediator.dispatchToService(DataServiceCallMediator.java:215)
 at org.wso2.micro.integrator.mediator.dataservice.DataServiceCallMediator.mediate(DataServiceCallMediator.java:110)
 at org.apache.synapse.mediators.AbstractListMediator.mediate(AbstractListMediator.java:109)
 at org.apache.synapse.mediators.AbstractListMediator.mediate(AbstractListMediator.java:71)
 at org.apache.synapse.mediators.base.SequenceMediator.mediate(SequenceMediator.java:158)
 at org.apache.synapse.core.axis2.ProxyServiceMessageReceiver.receive(ProxyServiceMessageReceiver.java:228)
 at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180)
 at org.apache.synapse.transport.passthru.ServerWorker.processNonEntityEnclosingRESTHandler(ServerWorker.java:375)
 at org.apache.synapse.transport.passthru.ServerWorker.run(ServerWorker.java:189)
 at org.apache.axis2.transport.base.threads.NativeWorkerPool$1.run(NativeWorkerPool.java:172)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 at java.lang.Thread.run(Thread.java:748)
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': date string can not be less than 19 characters

Scenario 2

Next we passed an empty string to the payload.xml file and executed the curl command. Then the following error was observed,

CURL Response:

<axis2ns7:DataServiceFault xmlns:axis2ns7="http://ws.wso2.org/dataservice">
   <axis2ns7:current_params>{EmployeeDate=, FirstName=Nikitha, EmployeeNumber=12, LastName=Pathi}</axis2ns7:current_params>
   <axis2ns7:source_data_service>
      <axis2ns7:data_service_name>RDBMSDataService</axis2ns7:data_service_name>
      <axis2ns7:description />
      <axis2ns7:location>/home/jayasithu/Desktop/Tickets/TMGDCSUB-241/wso2mi-1.2.0/tmp/carbonapps/-1234/1655881305552OracleDBTestCompositeExporter_1.0.0-SNAPSHOT.car/RDBMSDataService_1.0.0/RDBMSDataService-1.0.0.dbs</axis2ns7:location>
      <axis2ns7:default_namespace>http://ws.wso2.org/dataservice</axis2ns7:default_namespace>
   </axis2ns7:source_data_service>
   <axis2ns7:ds_code>DATABASE_ERROR</axis2ns7:ds_code>
   <axis2ns7:nested_exception>DS Fault Message: Empty string or null value was found as date.
DS Code: UNKNOWN_ERROR</axis2ns7:nested_exception>
   <axis2ns7:current_request_name>_postemployee</axis2ns7:current_request_name>
</axis2ns7:DataServiceFault>

MI Log:

DS Code: DATABASE_ERROR
Source Data Service:-
Name: RDBMSDataService
Location: /home/jayasithu/Desktop/Tickets/TMGDCSUB-241/wso2mi-1.2.0/tmp/carbonapps/-1234/1655880974595OracleDBTestCompositeExporter_1.0.0-SNAPSHOT.car/RDBMSDataService_1.0.0/RDBMSDataService-1.0.0.dbs
Description: 
Default Namespace: http://ws.wso2.org/dataservice
Current Request Name: _postemployee
Current Params: {EmployeeDate=, FirstName=Nikitha, EmployeeNumber=12, LastName=Pathi}
Nested Exception:-
DS Fault Message: Empty string or null value was found as date.
DS Code: UNKNOWN_ERROR
    at org.wso2.micro.integrator.dataservices.core.engine.DSOMDataSource.execute(DSOMDataSource.java:100)
    at org.wso2.micro.integrator.dataservices.core.engine.DSOMDataSource.serialize(DSOMDataSource.java:105)
    at org.wso2.micro.integrator.dataservices.core.engine.DSOMDataSource.executeInOnly(DSOMDataSource.java:78)
    at org.wso2.micro.integrator.dataservices.core.dispatch.SingleDataServiceRequest.processSingleRequest(SingleDataServiceRequest.java:115)
    at org.wso2.micro.integrator.dataservices.core.dispatch.SingleDataServiceRequest.processRequest(SingleDataServiceRequest.java:66)
    at org.wso2.micro.integrator.dataservices.core.dispatch.DataServiceRequest.dispatch(DataServiceRequest.java:358)
    at org.wso2.micro.integrator.dataservices.core.DataServiceProcessor.dispatch(DataServiceProcessor.java:40)
    at org.wso2.micro.integrator.dataservices.core.DBInOnlyMessageReceiver.invokeBusinessLogic(DBInOnlyMessageReceiver.java:52)
    ... 9 more
Caused by: DS Fault Message: Error in 'SQLQuery.processPreNormalQuery': DS Fault Message: Empty string or null value was found as date.
DS Code: UNKNOWN_ERROR

The above tests was carried out in MI 1.2.0 latest updated pack and also we could see the same error for the DSS call mediator in Micro Integrator 4.0.0 as well. To test this in MI 4.0.0 documentation[2] was used to setup the project.

[1]https://ei.docs.wso2.com/en/7.2.0/micro-integrator/use-cases/examples/data_integration/rdbms-data-service/ [2]https://apim.docs.wso2.com/en/latest/reference/mediators/dss-mediator/

Steps to Reproduce

Please follow the steps given below to setup the test project and reproduce the issue. Please refer to documentation[1] for more information on how RDBMS data (stored in a MySQL database) can be exposed as a data service.

Setting up the database,

1.Create a database named Employees in MySQL.

CREATE DATABASE Employees;

  1. Create the Employee table inside the Employees database:
USE Employees;
CREATE TABLE Employees(
EmployeeDate DATE,
EmployeeNumber varchar(255),
FirstName varchar(255),
LastName varchar(255)
);

Setting up the integration project.

  1. Create a Integration project on Integration Studio.
  2. Create a data source under that project and add the following configurations. 
    <datasource>
<name>MysqlConJNDI1</name>
<description>MySQL Connection</description>
<jndiConfig useDataSourceFactory="false">
    <name>MysqlConJNDI1</name>
</jndiConfig>
<definition type="RDBMS">
    <configuration>
        <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName>
        <url>jdbc:mysql://localhost:3306/dss</url>
        <username>root</username>
        <password>root</password>
    </configuration>
</definition>
</datasource>
  3. Then create the data service with the following configurations. 
    <data enableBatchRequests="true" name="RDBMSDataService" serviceGroup="" serviceNamespace="">
<description/>
<query id="GetEmployeeDetails" useConfig="MysqlConJNDI1">
    <sql>select EmployeeDate, EmployeeNumber, FirstName, LastName from Employees where EmployeeNumber=:EmployeeNumber</sql>
    <param name="EmployeeNumber" paramType="SCALAR" sqlType="STRING"/>
    <result element="Employees" rowName="Employee">
        <param name="EmployeeDate" paramType="SCALAR" sqlType="DATE"/>
        <element column="EmployeeNumber" name="EmployeeNumber" xsdType="xs:string"/>
        <element column="FirstName" name="FirstName" xsdType="xs:string"/>
        <element column="LastName" name="LastName" xsdType="xs:string"/>
    </result>
</query>
<config id="MysqlConJNDI1">
    <property name="carbon_datasource_name">MysqlConJNDI1</property>
</config>
<query id="AddEmployeeDetails" useConfig="MysqlConJNDI1">
    <sql>insert into Employees (EmployeeDate, EmployeeNumber, FirstName, LastName) values(:EmployeeDate,:EmployeeNumber,:FirstName,:LastName)</sql>
    <param name="EmployeeDate" paramType="SCALAR" sqlType="DATE" defaultValue="#{SYSTEM_DATE}"/>
    <param name="EmployeeNumber" paramType="SCALAR" sqlType="STRING"/>
    <param name="FirstName" paramType="SCALAR" sqlType="STRING"/>
    <param name="LastName" paramType="SCALAR" sqlType="STRING"/>
</query>
<query id="UpdateEmployeeDetails" useConfig="MysqlConJNDI1">
    <param name="EmployeeNumber" paramType="SCALAR" sqlType="STRING"/>
    <sql>update Employees set EmployeeDate=:EmployeeDate, FirstName=:FirstName, LastName=:LastName where EmployeeNumber=:EmployeeNumber</sql>
    <param name="EmployeeDate" paramType="SCALAR" sqlType="DATE"/>
    <param name="FirstName" paramType="SCALAR" sqlType="STRING"/>
    <param name="LastName" paramType="SCALAR" sqlType="STRING"/>
</query>
<resource method="GET" path="Employee/{EmployeeNumber}">
    <call-query href="GetEmployeeDetails">
        <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
    </call-query>
</resource>
<resource method="POST" path="Employee">
    <call-query href="AddEmployeeDetails">
        <with-param name="EmployeeDate" query-param="EmployeeDate"/>    
        <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
        <with-param name="FirstName" query-param="FirstName"/>
        <with-param name="LastName" query-param="LastName"/>
    </call-query>
</resource>
<resource method="PUT" path="Employee">
    <call-query href="UpdateEmployeeDetails">
        <with-param name="EmployeeDate" query-param="EmployeeDate"/>
        <with-param name="EmployeeNumber" query-param="EmployeeNumber"/>
        <with-param name="FirstName" query-param="FirstName"/>
        <with-param name="LastName" query-param="LastName"/>
    </call-query>
</resource>
</data>
  4. Save the changes and Export the CAR file and Add the CAR file to the /repository/deployment/server/carbonapps directory.
  5. Then create the payload.xml file in a different location with the relevant details to be passed to the dataservice 
    <_postemployee>
<EmployeeDate>2022-06-23</EmployeeDate>
<EmployeeNumber>12</EmployeeNumber>
<FirstName>Harry</FirstName>
<LastName>Potter</LastName>
</_postemployee>
  6. Execute the following curl command from the location where payload.xml is saved. curl -X POST -H 'Accept: application/xml' -H 'Content-Type: application/xml' --data "@employee-payload.xml" http://localhost:8290/services/RDBMSDataService/employee
  7. Then you'll be able to successfully insert the values in the payload.xml file to the Employee database.

Please follow documentation[1] since I used the example provided in the documentation to set this up. But the sample datasource provided in the documentation caused an error when having the datasource within the dataservice and that I why I created a seperate datasource and refered it from the dataservice.

Furthermore I have attached the entire project I used in Integration studio for your reference and the CAR file that was used to test in MySQL, please edit the datasource accordingly.

CAR File: OracleDBTestCompositeExporter_1.0.0-SNAPSHOT .car.zip Complete Project: OracleDBTest.zip

[1]https://ei.docs.wso2.com/en/7.2.0/micro-integrator/use-cases/examples/data_integration/rdbms-data-service/

Affected Component

MI

Version

1.2.0

Environment Details (with versions)

No response

Relevant Log Output

No response

Related Issues

No response

Suggested Labels

No response

github-actions[bot] commented 2 years ago

This issue is NOT closed with a proper Resolution/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Resolution/Cannot Reproduce - Issue cannot be reproduced.
      - Resolution/Duplicate - Issue is already reported before.
      - Resolution/Fixed - Issue has already been fixed.
      - Resolution/Answered - Issue has already been answered.
      - Resolution/Invalid - Issue is invalid.
      - Resolution/Not a bug - Issue is not a bug.
      - Resolution/Postponed - Issue is postponed.
      - Resolution/Won’t Fix - Issue won't be fixed.

github-actions[bot] commented 2 years ago

This issue is NOT closed with a proper Resolution/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Resolution/Cannot Reproduce - Issue cannot be reproduced.
      - Resolution/Duplicate - Issue is already reported before.
      - Resolution/Fixed - Issue has already been fixed.
      - Resolution/Answered - Issue has already been answered.
      - Resolution/Invalid - Issue is invalid.
      - Resolution/Not a bug - Issue is not a bug.
      - Resolution/Postponed - Issue is postponed.
      - Resolution/Won’t Fix - Issue won't be fixed.

github-actions[bot] commented 2 years ago

This issue is NOT closed with a proper Resolution/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Resolution/Cannot Reproduce - Issue cannot be reproduced.
      - Resolution/Duplicate - Issue is already reported before.
      - Resolution/Fixed - Issue has already been fixed.
      - Resolution/Answered - Issue has already been answered.
      - Resolution/Invalid - Issue is invalid.
      - Resolution/Not a bug - Issue is not a bug.
      - Resolution/Postponed - Issue is postponed.
      - Resolution/Won’t Fix - Issue won't be fixed.

github-actions[bot] commented 2 years ago

This issue is NOT closed with a proper Resolution/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Resolution/Cannot Reproduce - Issue cannot be reproduced.
      - Resolution/Duplicate - Issue is already reported before.
      - Resolution/Fixed - Issue has already been fixed.
      - Resolution/Answered - Issue has already been answered.
      - Resolution/Invalid - Issue is invalid.
      - Resolution/Not a bug - Issue is not a bug.
      - Resolution/Postponed - Issue is postponed.
      - Resolution/Won’t Fix - Issue won't be fixed.

github-actions[bot] commented 2 years ago

This issue is NOT closed with a proper Resolution/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Resolution/Cannot Reproduce - Issue cannot be reproduced.
      - Resolution/Duplicate - Issue is already reported before.
      - Resolution/Fixed - Issue has already been fixed.
      - Resolution/Answered - Issue has already been answered.
      - Resolution/Invalid - Issue is invalid.
      - Resolution/Not a bug - Issue is not a bug.
      - Resolution/Postponed - Issue is postponed.
      - Resolution/Won’t Fix - Issue won't be fixed.

github-actions[bot] commented 2 years ago

This issue is NOT closed with a proper Resolution/ label. Make sure to add proper reason label before closing. Please add or leave a comment with the proper reason label now.

      - Resolution/Cannot Reproduce - Issue cannot be reproduced.
      - Resolution/Duplicate - Issue is already reported before.
      - Resolution/Fixed - Issue has already been fixed.
      - Resolution/Answered - Issue has already been answered.
      - Resolution/Invalid - Issue is invalid.
      - Resolution/Not a bug - Issue is not a bug.
      - Resolution/Postponed - Issue is postponed.
      - Resolution/Won’t Fix - Issue won't be fixed.