Anirban Sen Chowdhary Anirban Sen Chowdhary - 4 months ago 37
SQL Question

Mule JDBC endpoint causing exception while executing SQL query

I got a Strange issue in Mule .. I have a webservice exposed in Mule that perform simple CRUD operation..
Now the issue is there is a SQL query :-

if not exists (select * from sysobjects where name='getData' and xtype='U')create table getData (ID int NOT NULL, NAME varchar(50) NULL,AGE int NULL,DESIGNATION varchar(50) NULL)


What this query does is it check whether the table exists on Database .. if it exists it, leaves and if it doesn't exists it create a new table with the same name and same fields ..

Now I want to use this query before an insert DB operation .. that is if the table exists then it will leave it and will perform insert data into it.. and if it doesn't exists then it will create the table first and then it will insert data into it ..
So my Mule Flow is following :

<jdbc-ee:connector name="Database_Global" dataSource-ref="DB_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database">
<jdbc-ee:query key="CheckTableExistsQuery" value="if not exists (select * from sysobjects where name='getData' and xtype='U')create table getData (ID int NOT NULL, NAME varchar(50) NULL,AGE int NULL,DESIGNATION varchar(50) NULL)"/>
<jdbc-ee:query key="InsertQuery" value="INSERT INTO getData(ID,NAME,AGE,DESIGNATION)VALUES(#[flowVars['id']],#[flowVars['name']],#[flowVars['age']],#[flowVars['designation']])"/>
</jdbc-ee:connector>

<flow name="MuleDbInsertFlow1" doc:name="MuleDbInsertFlow1">
<http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8082" path="mainData" doc:name="HTTP"/>
<cxf:jaxws-service service="MainData" serviceClass="com.test.services.schema.maindata.v1.MainData" doc:name="SOAPWithHeader" />
<component class="com.test.services.schema.maindata.v1.Impl.MainDataImpl" doc:name="JavaMain_ServiceImpl"/>
<mulexml:object-to-xml-transformer doc:name="Object to XML"/>
<choice doc:name="Choice">
<when expression="#[message.inboundProperties['SOAPAction'] contains 'insertDataOperation']">
<processor-chain doc:name="Processor Chain">
<logger message="INSERTDATA" level="INFO" doc:name="Logger"/>
<jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="CheckTableExistsQuery" queryTimeout="-1" connector-ref="Database_Global" doc:name="Database (JDBC)"/>
<jdbc-ee:outbound-endpoint exchange-pattern="request-response" queryKey="InsertQuery" queryTimeout="-1" connector-ref="Database_Global" doc:name="Database (JDBC)"/>

//remaining code ......


As you can see .. I am trying to call CheckTableExistsQuery before InsertQuery so that it checks the table exists or not and then perform insertion of Data .. but I am getting following exception :-

ERROR 2014-09-21 14:03:48,424 [[test].connector.http.mule.default.receiver.02] org.mule.exception.CatchMessagingExceptionStrategy:
********************************************************************************
Message : Failed to route event via endpoint: DefaultOutboundEndpoint{endpointUri=jdbc://CheckTableExistsQuery, connector=EEJdbcConnector
{
name=Database_Global
lifecycle=start
this=79fcce6c
numberOfConcurrentTransactedReceivers=4
createMultipleTransactedReceivers=false
connected=true
supportedProtocols=[jdbc]
serviceOverrides=<none>
}
, name='endpoint.jdbc.CheckTableExistsQuery', mep=REQUEST_RESPONSE, properties={queryTimeout=-1}, transactionConfig=Transaction{factory=null, action=INDIFFERENT, timeout=0}, deleteUnacceptedMessages=false, initialState=started, responseTimeout=10000, endpointEncoding=UTF-8, disableTransportTransformer=false}. Message payload is of type: String
Code : MULE_ERROR--2
--------------------------------------------------------------------------------
Exception stack is:
1. No SQL Strategy found for SQL statement: {if not exists (select * from sysobjects where name='getData' and xtype='U')create table getData (ID int NOT NULL, NAME varchar(50) NULL,AGE int NULL,DESIGNATION varchar(50) NULL)} (java.lang.IllegalArgumentException)
com.mulesoft.mule.transport.jdbc.sqlstrategy.EESqlStatementStrategyFactory:105 (null)
2. Failed to route event via endpoint: DefaultOutboundEndpoint{endpointUri=jdbc://CheckTableExistsQuery, connector=EEJdbcConnector
{
name=Database_Global
lifecycle=start
this=79fcce6c
numberOfConcurrentTransactedReceivers=4
createMultipleTransactedReceivers=false
connected=true
supportedProtocols=[jdbc]
serviceOverrides=<none>
}
, name='endpoint.jdbc.CheckTableExistsQuery', mep=REQUEST_RESPONSE, properties={queryTimeout=-1}, transactionConfig=Transaction{factory=null, action=INDIFFERENT, timeout=0}, deleteUnacceptedMessages=false, initialState=started, responseTimeout=10000, endpointEncoding=UTF-8, disableTransportTransformer=false}. Message payload is of type: String (org.mule.api.transport.DispatchException)
org.mule.transport.AbstractMessageDispatcher:117 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/transport/DispatchException.html)
--------------------------------------------------------------------------------
Root Exception stack trace:
java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement: {if not exists (select * from sysobjects where name='getData' and xtype='U')create table getData (ID int NOT NULL, NAME varchar(50) NULL,AGE int NULL,DESIGNATION varchar(50) NULL)}
at com.mulesoft.mule.transport.jdbc.sqlstrategy.EESqlStatementStrategyFactory.create(EESqlStatementStrategyFactory.java:105)
at org.mule.transport.jdbc.JdbcMessageDispatcher.doSend(JdbcMessageDispatcher.java:65)
at org.mule.transport.AbstractMessageDispatcher.process(AbstractMessageDispatcher.java:84)
+ 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************


But the Strange fact is that .. if I implement the same using Java code it works fine .. for example in Java code I use JDBCTemplate to execute query :-

Check table exists and create it */
String checkTableExists=getQueryByKey("CheckTableExistsQuery"); // Query for check existing table
jdbcTemplate.execute(checkTableExists); //Create Table If not exists

try {

String insertDataIntoDB = getQueryByKey("InsertQuery");
jdbcTemplate.update(insertDataIntoDB, ID, NAME, AGE,
DESIGNATION);
dataResponse.setResponse("Data inserted Successfully");
} catch (DataIntegrityViolationException e) {
SQLException sql = (SQLException) e.getCause();
e.printStackTrace();
throw sql;
} catch (Exception e) {
e.printStackTrace();
throw e;
}


Please help me .. Please let me know how to execute the query

if not exists (select * from sysobjects where name='getData' and xtype='U')create table getData (ID int NOT NULL, NAME varchar(50) NULL,AGE int NULL,DESIGNATION varchar(50) NULL)


successfully ... why it's not getting executed from Mule JDBC endpoint while it's getting executed from JDBCTemplate in Java Code

Answer

Mule doesn't recognize the if not exists... query and thus doesn't know what to do with it.

To fix this you need to:

  • create your own org.mule.transport.jdbc.sqlstrategy.SqlStatementStrategyFactory by sub-classing the default one and adding extra behaviour to support this type of query,
  • Spring-inject it into the JdbcConnector.
Comments