WSO2 DBLookup not returning a JSON result

Question

I'm really new in WSO2 world and I have a DBLookup config as follow:

    <resource methods="GET" uri-template="/consultadb">
        <inSequence>
            <log/>
            <dblookup>
                <connection>
                    <pool>
                        <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
                        <url>jdbc:sqlserver://****.CORP:1433;databaseName=*****;</url>
                        <user>user</user>
                        <password>user</password>
                    </pool>
                </connection>
                <statement>
                    <sql><![CDATA[SELECT NAME FROM CLIENT]]></sql>
                    <result column="NAME" name="getName"/>
                </statement>
            </dblookup>
            <property name="messageType" scope="axis2" type="STRING" value="application/json"/>
            <log/>
            <respond/>
        </inSequence>
        <outSequence/>
        <faultSequence/>
    </resource>

But when I run, I didn't get any response in postman... what I'm doing wrong? I already setup a Respond Meditor

There is no errors and the response are 200 OK, in log the final message are:

[2021-06-25 11:39:31,542] INFO {LogMediator} - {api:salesforceinit} To: /salesforceinit/consultadb, MessageID: urn:uuid:d9ba7fc8-733e-413a-a47b-f7ebac8ae4e3, correlation_id: d9ba7fc8-733e-413a-a47b-f7ebac8ae4e3, Direction: request

score 1 · Accepted Answer · answered Jun 25 '21 at 15:50

I think that is because your query returns more than one element from DB. And DBLookupMediator can handle only one row. And that is mentioned in mediator documentation:

The DBLookup mediator can set a property from one row in a result set. It cannot return multiple rows. If you need to get multiple records, or if you have a table with multiple parameters (such as URLs), you can use the WSO2 Data Services Server to create a data service and invoke that service from the ESB using the Callout mediator instead.

If you want to read data from DB, you should use Data service. How to create, you can find in documentation: Creating a Data Service

WSO2 DBLookup not returning a JSON result

1 Answers1