EI Data Service error DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value:

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

EI Data Service error DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value:

Dushantha Batuwita

Hi Dev;

 I have written a data service using EI( wso2ei-6.1.1) to expose a legacy backend with soap service.

 
I have used MySQL function as follows in the config files

<data name="CarEvaldbDataService" transports="http https local">
    <config id="carEvalOrderInformation">
        <property name="driverClassName">com.mysql.jdbc.Driver</property>
        <property name="url">jdbc:mysql://localhost:3306/eval</property>
        <property name="username">root</property>
        <property name="password">root</property>
    </config>
    <query id="addCustomerOrder" useConfig="carEvalOrderInformation">
        <sql>select getOrderNumber ('engCap','carmodel1','exteriorColor','interiorColor','interiorMaterial','customerName','optionName','custEmail','tireSize') as ordernumber</sql>
        <result element="orderNumber" rowName="">
            <element column="orderNumber" name="orderNumber" xsdType="INTEGER"/>
        </result>
        <param name="engCap" sqlType="INTEGER"/>
        <param name="carmodel1" sqlType="STRING"/>
        <param name="exteriorColor" sqlType="STRING"/>
        <param name="interiorColor" sqlType="STRING"/>
        <param name="interiorMaterial" sqlType="STRING"/>
        <param name="customerName" sqlType="STRING"/>
        <param name="optionName" sqlType="STRING"/>
        <param name="custEmail" sqlType="STRING"/>
        <param name="tireSize" sqlType="INTEGER"/>
    </query>
    <operation returnRequestStatus="true" name="addCustomerOrder">
        <call-query href="addCustomerOrder">
            <with-param name="engCap" query-param="engCap"/>
            <with-param name="carmodel1" query-param="carmodel1"/>
            <with-param name="exteriorColor" query-param="exteriorColor"/>
            <with-param name="interiorColor" query-param="interiorColor"/>
            <with-param name="interiorMaterial" query-param="interiorMaterial"/>
            <with-param name="customerName" query-param="customerName"/>
            <with-param name="optionName" query-param="optionName"/>
            <with-param name="custEmail" query-param="custEmail"/>
            <with-param name="tireSize" query-param="tireSize"/>
        </call-query>
    </operation>
</data>


The Mysql Function correctly runs and gives the expected result when it  is executed isolate in terminal
sample signature of Mysql function has given below to get an idea of the data types of the passing in parameters.

CREATE DEFINER=`root`@`localhost` FUNCTION `getOrderNumber`(CAPACITY1 int, MODEL1 varchar(20), EXTERIOR_COLOR1 varchar(20), 
INTERIOR_COLOR1 varchar(20), MATERIAL1 varchar(20) ,
CUSTOEMER_NAME varchar(20),OPTION_NAME1 varchar(20),
        CUSTEMAIL varchar(30), TIRE_SIZE1 int ) RETURNS int(11)


Further, the data types of the columns related to CAPACITY1 and TIRE_SIZE is defined to be int.

Question

When I use the "tryIt" feature of the deployed data service please refer to the below image, I get an error which complaints about data type mismatches which I haven't done according to my understanding.

Inline image 1 




the associated error:



Current Params: {interiorColor=maroon, engCap=1800, exteriorColor=silver, interiorMaterial=leather, carmodel1=model3, custEmail=[hidden email], optionName=Navigation, tireSize=12, customerName=Gamlath}
Nested Exception:-
java.sql.SQLException: Incorrect integer value: 'engCap' for column 'CAPACITY1' at row 1

at org.apache.axis2.AxisFault.makeFault(AxisFault.java:430)
at org.apache.axis2.transport.http.SOAPMessageFormatter.writeTo(SOAPMessageFormatter.java:78)
at org.apache.synapse.transport.passthru.PassThroughHttpSender.submitResponse(PassThroughHttpSender.java:573)
at org.apache.synapse.transport.passthru.PassThroughHttpSender.invoke(PassThroughHttpSender.java:264)
at org.apache.axis2.engine.AxisEngine.send(AxisEngine.java:442)
at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.invokeBusinessLogic(AbstractInOutSyncMessageReceiver.java:45)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:110)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180)
at org.apache.synapse.transport.passthru.ServerWorker.processEntityEnclosingRequest(ServerWorker.java:404)
at org.apache.synapse.transport.passthru.ServerWorker.run(ServerWorker.java:151)
at org.apache.axis2.transport.base.threads.NativeWorkerPool$1.run(NativeWorkerPool.java:172)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value: 'engCap' for column 'CAPACITY1' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: CarEvaldbDataService
Location: /home/dushantha/TICKETHANDLING/Evaluation/Evaluation_2/ProjectsetUp/wso2ei-6.1.1/wso2/tmp/carbonapps/-1234/1513259443181carAssemblySystemCompositeApplication_1.0.0.car/CarAssemblyDataService_1.0.0/CarAssemblyDataService-1.0.0.dbs
Description: N/A



Please kindly share your wisdom about the possible root cause.

many thanks






--
Dushantha Nayanajith Chandima Batuwita
Software Engineer Support | WSO2
[hidden email]
mobile : 0094777453010

_______________________________________________
Dev mailing list
[hidden email]
http://wso2.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: EI Data Service error DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value:

Senduran Balasubramaniyam
Hi Dushantha,

Can you please double check your query. May try directly in the MySQL console to validate it 

Regards
Senduran

On Thu, Dec 14, 2017 at 7:54 PM, Dushantha Batuwita <[hidden email]> wrote:

Hi Dev;

 I have written a data service using EI( wso2ei-6.1.1) to expose a legacy backend with soap service.

 
I have used MySQL function as follows in the config files

<data name="CarEvaldbDataService" transports="http https local">
    <config id="carEvalOrderInformation">
        <property name="driverClassName">com.mysql.jdbc.Driver</property>
        <property name="url">jdbc:mysql://localhost:3306/eval</property>
        <property name="username">root</property>
        <property name="password">root</property>
    </config>
    <query id="addCustomerOrder" useConfig="carEvalOrderInformation">
        <sql>select getOrderNumber ('engCap','carmodel1','exteriorColor','interiorColor','interiorMaterial','customerName','optionName','custEmail','tireSize') as ordernumber</sql>
        <result element="orderNumber" rowName="">
            <element column="orderNumber" name="orderNumber" xsdType="INTEGER"/>
        </result>
        <param name="engCap" sqlType="INTEGER"/>
        <param name="carmodel1" sqlType="STRING"/>
        <param name="exteriorColor" sqlType="STRING"/>
        <param name="interiorColor" sqlType="STRING"/>
        <param name="interiorMaterial" sqlType="STRING"/>
        <param name="customerName" sqlType="STRING"/>
        <param name="optionName" sqlType="STRING"/>
        <param name="custEmail" sqlType="STRING"/>
        <param name="tireSize" sqlType="INTEGER"/>
    </query>
    <operation returnRequestStatus="true" name="addCustomerOrder">
        <call-query href="addCustomerOrder">
            <with-param name="engCap" query-param="engCap"/>
            <with-param name="carmodel1" query-param="carmodel1"/>
            <with-param name="exteriorColor" query-param="exteriorColor"/>
            <with-param name="interiorColor" query-param="interiorColor"/>
            <with-param name="interiorMaterial" query-param="interiorMaterial"/>
            <with-param name="customerName" query-param="customerName"/>
            <with-param name="optionName" query-param="optionName"/>
            <with-param name="custEmail" query-param="custEmail"/>
            <with-param name="tireSize" query-param="tireSize"/>
        </call-query>
    </operation>
</data>


The Mysql Function correctly runs and gives the expected result when it  is executed isolate in terminal
sample signature of Mysql function has given below to get an idea of the data types of the passing in parameters.

CREATE DEFINER=`root`@`localhost` FUNCTION `getOrderNumber`(CAPACITY1 int, MODEL1 varchar(20), EXTERIOR_COLOR1 varchar(20), 
INTERIOR_COLOR1 varchar(20), MATERIAL1 varchar(20) ,
CUSTOEMER_NAME varchar(20),OPTION_NAME1 varchar(20),
        CUSTEMAIL varchar(30), TIRE_SIZE1 int ) RETURNS int(11)


Further, the data types of the columns related to CAPACITY1 and TIRE_SIZE is defined to be int.

Question

When I use the "tryIt" feature of the deployed data service please refer to the below image, I get an error which complaints about data type mismatches which I haven't done according to my understanding.

Inline image 1 




the associated error:



Current Params: {interiorColor=maroon, engCap=1800, exteriorColor=silver, interiorMaterial=leather, carmodel1=model3, custEmail=[hidden email], optionName=Navigation, tireSize=12, customerName=Gamlath}
Nested Exception:-
java.sql.SQLException: Incorrect integer value: 'engCap' for column 'CAPACITY1' at row 1

at org.apache.axis2.AxisFault.makeFault(AxisFault.java:430)
at org.apache.axis2.transport.http.SOAPMessageFormatter.writeTo(SOAPMessageFormatter.java:78)
at org.apache.synapse.transport.passthru.PassThroughHttpSender.submitResponse(PassThroughHttpSender.java:573)
at org.apache.synapse.transport.passthru.PassThroughHttpSender.invoke(PassThroughHttpSender.java:264)
at org.apache.axis2.engine.AxisEngine.send(AxisEngine.java:442)
at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.invokeBusinessLogic(AbstractInOutSyncMessageReceiver.java:45)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:110)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180)
at org.apache.synapse.transport.passthru.ServerWorker.processEntityEnclosingRequest(ServerWorker.java:404)
at org.apache.synapse.transport.passthru.ServerWorker.run(ServerWorker.java:151)
at org.apache.axis2.transport.base.threads.NativeWorkerPool$1.run(NativeWorkerPool.java:172)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value: 'engCap' for column 'CAPACITY1' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: CarEvaldbDataService
Location: /home/dushantha/TICKETHANDLING/Evaluation/Evaluation_2/ProjectsetUp/wso2ei-6.1.1/wso2/tmp/carbonapps/-1234/1513259443181carAssemblySystemCompositeApplication_1.0.0.car/CarAssemblyDataService_1.0.0/CarAssemblyDataService-1.0.0.dbs
Description: N/A



Please kindly share your wisdom about the possible root cause.

many thanks






--
Dushantha Nayanajith Chandima Batuwita
Software Engineer Support | WSO2
[hidden email]
mobile : 0094777453010

_______________________________________________
Dev mailing list
[hidden email]
http://wso2.org/cgi-bin/mailman/listinfo/dev




--
Senduran
Senior Software Engineer,
WSO2, Inc.;  http://wso2.com/
Mobile: +94 77 952 6548

_______________________________________________
Dev mailing list
[hidden email]
http://wso2.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: EI Data Service error DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value:

Madhawa Gunasekara-2
Hi Dushantha,

You can't specify input parameters like that. if you are specifying input parameter as a named param you have to append semicolon start of that named param.  Please refer [1]

select getOrderNumber (:engCap,:carmodel1,:exteriorColor,:interiorColor,:interiorMaterial,:customerName,:optionName,:custEmail,:tireSize) as ordernumber
Thanks,
Madhawa

On Fri, Dec 15, 2017 at 10:24 AM, Senduran Balasubramaniyam <[hidden email]> wrote:
Hi Dushantha,

Can you please double check your query. May try directly in the MySQL console to validate it 

Regards
Senduran

On Thu, Dec 14, 2017 at 7:54 PM, Dushantha Batuwita <[hidden email]> wrote:

Hi Dev;

 I have written a data service using EI( wso2ei-6.1.1) to expose a legacy backend with soap service.

 
I have used MySQL function as follows in the config files

<data name="CarEvaldbDataService" transports="http https local">
    <config id="carEvalOrderInformation">
        <property name="driverClassName">com.mysql.jdbc.Driver</property>
        <property name="url">jdbc:mysql://localhost:3306/eval</property>
        <property name="username">root</property>
        <property name="password">root</property>
    </config>
    <query id="addCustomerOrder" useConfig="carEvalOrderInformation">
        <sql>select getOrderNumber ('engCap','carmodel1','exteriorColor','interiorColor','interiorMaterial','customerName','optionName','custEmail','tireSize') as ordernumber</sql>
        <result element="orderNumber" rowName="">
            <element column="orderNumber" name="orderNumber" xsdType="INTEGER"/>
        </result>
        <param name="engCap" sqlType="INTEGER"/>
        <param name="carmodel1" sqlType="STRING"/>
        <param name="exteriorColor" sqlType="STRING"/>
        <param name="interiorColor" sqlType="STRING"/>
        <param name="interiorMaterial" sqlType="STRING"/>
        <param name="customerName" sqlType="STRING"/>
        <param name="optionName" sqlType="STRING"/>
        <param name="custEmail" sqlType="STRING"/>
        <param name="tireSize" sqlType="INTEGER"/>
    </query>
    <operation returnRequestStatus="true" name="addCustomerOrder">
        <call-query href="addCustomerOrder">
            <with-param name="engCap" query-param="engCap"/>
            <with-param name="carmodel1" query-param="carmodel1"/>
            <with-param name="exteriorColor" query-param="exteriorColor"/>
            <with-param name="interiorColor" query-param="interiorColor"/>
            <with-param name="interiorMaterial" query-param="interiorMaterial"/>
            <with-param name="customerName" query-param="customerName"/>
            <with-param name="optionName" query-param="optionName"/>
            <with-param name="custEmail" query-param="custEmail"/>
            <with-param name="tireSize" query-param="tireSize"/>
        </call-query>
    </operation>
</data>


The Mysql Function correctly runs and gives the expected result when it  is executed isolate in terminal
sample signature of Mysql function has given below to get an idea of the data types of the passing in parameters.

CREATE DEFINER=`root`@`localhost` FUNCTION `getOrderNumber`(CAPACITY1 int, MODEL1 varchar(20), EXTERIOR_COLOR1 varchar(20), 
INTERIOR_COLOR1 varchar(20), MATERIAL1 varchar(20) ,
CUSTOEMER_NAME varchar(20),OPTION_NAME1 varchar(20),
        CUSTEMAIL varchar(30), TIRE_SIZE1 int ) RETURNS int(11)


Further, the data types of the columns related to CAPACITY1 and TIRE_SIZE is defined to be int.

Question

When I use the "tryIt" feature of the deployed data service please refer to the below image, I get an error which complaints about data type mismatches which I haven't done according to my understanding.

Inline image 1 




the associated error:



Current Params: {interiorColor=maroon, engCap=1800, exteriorColor=silver, interiorMaterial=leather, carmodel1=model3, custEmail=[hidden email], optionName=Navigation, tireSize=12, customerName=Gamlath}
Nested Exception:-
java.sql.SQLException: Incorrect integer value: 'engCap' for column 'CAPACITY1' at row 1

at org.apache.axis2.AxisFault.makeFault(AxisFault.java:430)
at org.apache.axis2.transport.http.SOAPMessageFormatter.writeTo(SOAPMessageFormatter.java:78)
at org.apache.synapse.transport.passthru.PassThroughHttpSender.submitResponse(PassThroughHttpSender.java:573)
at org.apache.synapse.transport.passthru.PassThroughHttpSender.invoke(PassThroughHttpSender.java:264)
at org.apache.axis2.engine.AxisEngine.send(AxisEngine.java:442)
at org.apache.axis2.receivers.AbstractInOutSyncMessageReceiver.invokeBusinessLogic(AbstractInOutSyncMessageReceiver.java:45)
at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:110)
at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:180)
at org.apache.synapse.transport.passthru.ServerWorker.processEntityEnclosingRequest(ServerWorker.java:404)
at org.apache.synapse.transport.passthru.ServerWorker.run(ServerWorker.java:151)
at org.apache.axis2.transport.base.threads.NativeWorkerPool$1.run(NativeWorkerPool.java:172)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: javax.xml.stream.XMLStreamException: DS Fault Message: Error in 'SQLQuery.processPostNormalQuery': Incorrect integer value: 'engCap' for column 'CAPACITY1' at row 1
DS Code: DATABASE_ERROR
Source Data Service:-
Name: CarEvaldbDataService
Location: /home/dushantha/TICKETHANDLING/Evaluation/Evaluation_2/ProjectsetUp/wso2ei-6.1.1/wso2/tmp/carbonapps/-1234/1513259443181carAssemblySystemCompositeApplication_1.0.0.car/CarAssemblyDataService_1.0.0/CarAssemblyDataService-1.0.0.dbs
Description: N/A



Please kindly share your wisdom about the possible root cause.

many thanks






--
Dushantha Nayanajith Chandima Batuwita
Software Engineer Support | WSO2
[hidden email]
mobile : 0094777453010

_______________________________________________
Dev mailing list
[hidden email]
http://wso2.org/cgi-bin/mailman/listinfo/dev




--
Senduran
Senior Software Engineer,
WSO2, Inc.;  http://wso2.com/
Mobile: <a href="tel:+94%2077%20952%206548" value="+94779526548" target="_blank">+94 77 952 6548

_______________________________________________
Dev mailing list
[hidden email]
http://wso2.org/cgi-bin/mailman/listinfo/dev




--
Madhawa Gunasekara
Senior Software Engineer
WSO2 Inc.; http://wso2.com
lean.enterprise.middleware

mobile:
<a href="tel:+94+719411002" target="_blank">+94 719411002

_______________________________________________
Dev mailing list
[hidden email]
http://wso2.org/cgi-bin/mailman/listinfo/dev