Mule ESB Database connector (JDBC) error:

Question

I have a Database connector using the "Generic Database Configuration" (In other words, JDBC as opposed to MySQL).

The test connection works, and I am connecting to DB2 when I execute the program, but I am having difficulty with the configuration of the SELECT statement

What I am trying to achieve is something like this (which works well within the Command Line Processor

SELECT SUM(BALANCE) FROM ACCOUNT

So when I click on the Database Connector and go to the database tab below, I see the following

Connector Information:  Generic Database Configuration
Operation:              Select

Query
   Type:                   Parameterized 
    Parameterized Query:   ACCTNUM FROM ACCOUNT WHERE ACCTNUM = '11111'

When I run this as a Mule app, and send a http burst to it, I get the following:

C:\curl>curl http://localhost:8081
Query type must me '[SELECT, STORE_PROCEDURE_CALL]' but was 'DDL' (java.lang.Ill
egalArgumentException). Message payload is of type: String

So I suspect I did not set up the query correctly, so I tried the following:

Connector Information:  Generic Database Configuration
Operation:              Select

Query
   Type:                   Parameterized 
   Parameterized Query:    SELECT ACCTNUM FROM ACCOUNT WHERE ACCTNUM = '11111'

This time I got the following error

C:\curl>curl http://localhost:8081
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=INSTUID.ACCOUNT, DRIVER=3.
63.123 (com.ibm.db2.jcc.am.SqlSyntaxErrorException). Message payload is of type:
String

How can I resolve this issue ?

Thanks

Answer 1

If you are using Mule 3.5 and the new Database connector ... Try this :-

<db:select config-ref="Generic_Database_Configuration" doc:name="Database">
    <db:parameterized-query><![CDATA[SELECT SUM(BALANCE) FROM ACCOUNT]]></db:parameterized-query>
    </db:select>