Unable to insert into DB from Mule Groovy

1
votes

I have a Database table GroovyTest as following :-

enter image description here

As you can see it has 4 fields :- ID, NAME,AGE and DESIGNATION and among them ID and AGE are integer type and remaining NAME and DESIGNATION are String

Now I have the following Mule flow which is trying to insert the values in this DB using Groovy Script:-

<flow name="GroovyWithJDBCFlow1" doc:name="GroovyWithJDBCFlow1">
    <http:inbound-endpoint exchange-pattern="request-response" host="localhost" port="8082" path="groovy" doc:name="HTTP"/>
  <scripting:component doc:name="Initialise Database"> 
            <scripting:script engine="Groovy">
                <scripting:text><![CDATA[
               jdbcConnector = muleContext.getRegistry().lookupConnector("Database_Global");
                qr = jdbcConnector.getQueryRunner();
                conn = jdbcConnector.getConnection();
                qr.update(conn, "INSERT INTO GroovyTest VALUES(message.getInboundProperty('id'),message.getInboundProperty('name'),message.getInboundProperty('age'),message.getInboundProperty('designation'))")
return "Inserted into Table";]]></scripting:text>
       </scripting:script>  
        </scripting:component>
</flow>

Now when I trigger the flow with following url :- http://localhost:8082/groovy/?method=insert&id=1&name=Anirban&age=29&designation=SoftwareEngineer

I am getting following exception :-

Root Exception stack trace:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "message" or the user-defined function or aggregate "message.getInboundProperty", or the name is ambiguous.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
    + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************

But if I hardcode the value as following :-qr.update(conn, "INSERT INTO GroovyTest VALUES(5,'testvalue',333,'test')"); it's getting inserted without any issue ..

Also I checked the log .. I am getting all the inbound properties value correctly but I am unable to make it insert in DB using Groovy

Please help

2
groovymulemule-studio

2 Answers

2
votes

The problem is that you are literally passing message.getInboundProperty('id') (and so on) to the database, in the SQL string, which of course can not work.

You need to pass the value, like this:

"INSERT INTO GroovyTest VALUES(${message.getInboundProperty('id')}, ...

But stop right here: this is a very bad idea as it is open to SQL injection, since you're exposing this over HTTP.

Instead do:

qr.update(conn,
          'INSERT INTO GroovyTest VALUES (?,?,?,?)',
          message.getInboundProperty('id').toInteger(),
          message.getInboundProperty('name'),
          message.getInboundProperty('age').toInteger(),
          message.getInboundProperty('designation'))
1
votes

Thanks to David and the final working solution is :-

 <scripting:component doc:name="Initialise Database"> 
            <scripting:script engine="Groovy">
                        <scripting:text><![CDATA[
               import org.mule.api.transport.*;// For PropertyScope.INBOUND
               import org.apache.commons.dbutils.handlers.ArrayHandler;
                jdbcConnector = muleContext.getRegistry().lookupConnector("Database_Global");
                qr = jdbcConnector.getQueryRunner();
                conn = jdbcConnector.getConnection();
                log.info('NAME ' + message.getProperty('name',PropertyScope.INBOUND)) ;

                qr.update(conn,'INSERT INTO getData (ID,NAME,AGE,DESIGNATION) VALUES (?,?,?,?)',(Integer) message.getInboundProperty('id').toInteger(), message.getInboundProperty('name'), (Integer) message.getInboundProperty('age').toInteger(),message.getInboundProperty('designation'));
  log.info('Data inserted Successfully');            
return "Data inserted Successfully";]]></scripting:text>

       </scripting:script>  
        </scripting:component>

And yes I agree with David and Ryan that this is not a useful approach to use Groovy to interact with DB .. but yes, this is an alternate approach