JDBC Receiver : Handling NULL return from SELECT query

[Former Member]

Hi All,

I have a Proxy <-> XI <-> JDBC synchronous scenario. I have designed my message mapping to perform a select query using JDBC receiver adapter. The request message mapping structure at the JDBC end is as follows.

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_Matmvmt_Jdbc_Request xmlns:ns0="http://esag.com/xi/bat/MatMovementRead">
   <StatementSelect>
      <Tablename action="SELECT">
         <Table>MM_DATA</Table>
         <access>
            <SEQNO/>
            <MATERIAL/>
            <UOM/>
            <SOLDQTY/>
            <SAPUPD/>
         </access>
         <key compareOperation="EQ">
            <SAPUPD>N</SAPUPD>
         </key>
      </Tablename>
   </StatementSelect>
</ns0:MT_Matmvmt_Jdbc_Request>

The scenario works fine when there are records matching the select condition, but when there are no records matching the select condition (i.e. if there are no record with value SAPUPD = 'N' ) then my response message is returning the empty message strucuture as given below.

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_Matmvmt_Jdbc_Request_response xmlns:ns0="http://esag.com/xi/bat/MatMovementRead">
   <StatementSelect_response/>
</ns0:MT_Matmvmt_Jdbc_Request_response>

My requirement is that if there are no records matching the select condition then my response message should look like below.

<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_Matmvmt_Jdbc_Request_response xmlns:ns0="http://esag.com/xi/bat/MatMovementRead">
   <StatementSelect_response>
      <row>
         <SEQNO/>
         <MATERIAL/>
         <UOM/>
         <SOLDQTY/>
         <SAPUPD/>
      </row>
   </StatementSelect_response>
</ns0:MT_Matmvmt_Jdbc_Request_response>

Note : I have made the occurence of the request and response message elements as miniccurs = 0 and maxoccurs = 1.

Any inputs in this regard will be highly appreciated.

Regards,

Sandeep

[Former Member · Accepted solution]

Hi,

I think in your rsponse structure ROW field have occurance 0..n and in case of zero row selection, Row filed and related structre will not be generated in output XML.

You may try to use mapWithDefault API while mapping the ROW filed. this will help to reflect the blank structre in your output payload.

i.e. ROW --> mapWithDefault --> ROW.

Thanks

Swarup

[madhusudana_reddy2]

Hi Sandeep,

you can not expect structure like <?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Matmvmt_Jdbc_Request_response xmlns:ns0="http://esag.com/xi/bat/MatMovementRead">

<StatementSelect_response>

<row>

<SEQNO/>

<MATERIAL/>

<UOM/>

<SOLDQTY/>

<SAPUPD/>

</row>

</StatementSelect_response>

</ns0:MT_Matmvmt_Jdbc_Request_response>

if there is no data in table. The response which you are geting is standard format which you can not change. Instead you map this response with mapwithdefault node function while mapping to target to make the response mapping success, even though there is no data in response message.

thanks,

madhu

[Former Member]

Hi All,

Thanks for your response. Closing the thread.

Regards,

Sandeep