Mule JDBC endpoint causing exception while executing SQL query

Question

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 1

I came across the exact same error. Indeed, when Mule doesn't know what to do, unsupported SQL query or even a missing queryKey:

java.lang.IllegalArgumentException: No SQL Strategy found for SQL statement

In my case it was the latter, my test-Suite jdbc:connector was missing from the classpath, so I added it.

In your case try rew-riting the query as follows. This one worked for me:

DROP TABLE if exists your_table; 
CREATE TABLE your_table(...

Answer 2

So as per David's suggestion, ended up using if not exists query in a Java component and Groovy component in the Mule flow and is working for me

Answer 3

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.