JDBC SYN Receiver Call failed

[Former Member]

BPM is created to kick off receiver SYN call to Oracle database via JDBC.

Request is:

<?xml version="1.0" encoding="UTF-8" ?>

<ns0:AggrRequest_MT xmlns:ns0="xxxxxxxxxxxxxx">

<STATEMENT>

<dbTABLENAME action="select">

<TABLE>REACH_AGGR</TABLE>

<access>

<REGULATION />

<RFGL_CMPNY_CODE />

<PER_YEAR />

<REACH_SUBST_CODE />

<DSR_TYPE />

<SUPLY_BP_CODE />

<ONLY_REPR />

<PROD_QNTY_IN_MT />

<IMPORT_QNTY_IN_MT />

<INTMD_GMID />

<INTMD_TRANSP_GMID />

<THREE_YR_AVG />

<ROLL_AVG />

<ACT_DATE />

<LAST_ACT_USER />

<ROW_CNT />

</access>

</dbTABLENAME>

</STATEMENT>

</ns0:AggrRequest_MT>

But cannot get response correctly, and there is no detail error message return from database as previous posts. XI trace level is 3.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<!-- Inbound Message

-->

<SAP:Error xmlns:SAP="http://sap.com/xi/XI/Message/30" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" SOAP:mustUnderstand="1">

<SAP:Category>XIAdapterFramework</SAP:Category>

<SAP:Code area="MESSAGE">GENERAL</SAP:Code>

<SAP:P1 />

<SAP:P2 />

<SAP:P3 />

<SAP:P4 />

<SAP:AdditionalText>com.sap.aii.af.ra.ms.api.DeliveryException</SAP:AdditionalText>

<SAP:ApplicationFaultMessage namespace="" />

<SAP:Stack />

Please help!

Edited by: Zhenyu Zhao on Feb 25, 2008 2:47 PM

[Former Member · Accepted solution]

try to check the status of the JDBC adapter. Are you seeing any detailed information?

[Former Member · Accepted solution]

i created a single JDBC sender channel and use a simple "select * from REACH_AGGR" to get the recordset directly from database. It works.

[Former Member · Accepted solution]

fetching data from database is simple in XI.

But for sending message from XI to JDBC and then get response is not like normal scenario. for JDBC receiver we have to use standard documents to currect XML message(represent SQL ) and JDBC rec adapter will conver this XML to SQL call. so if there is anything missing or not as per the standards then XI will cannot insert/update or select the data using Receiver JDBC adapter.

thanks

farooq

[Former Member · Accepted solution]

It is very much required:

action=SELECT

Statements with this action cause existing table values to be selected. Therefore, the statement corresponds to an SQL SELECT statement.

The <access> block contains the column names to be selected, a <key> element contains the columns whose values must be identical with the specified value to get the new column values. The name of the <key> element is arbitrary. Column values within a <key> element are combined with a logical AND; different <key> elements are combined with a logical OR.

A statement with the action SELECT must have exactly one <access> element. The number of <key> elements with arbitrary names is not restricted.

The corresponding SQL statement for StatementName4 in the example above is as follows:

“

SELECT col1,col2,col3 FROM dbTableName WHERE ((col2=’val2old’ AND col4=’val4’) OR (col2=’val2old2’))

“

If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be selected. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

[Former Member · Accepted solution]

thank all of you. after adding the <key> element, the problem resolved.

[Former Member]

1)As per my knowledge let me know whether,u have created response message type with convention as

<name of source message type.Response>,i.e u should append Response to the message type in case of JDBC synchronous calls.

2)As u told that u used BPM in this scenario, check the number of messages out in parameter <MESSAGES_OUT> from transaction SXMB_MONI_BPE or from SXMB_MONI select the process radiobutton to get into the technical details

[Former Member]

Select statement must have "Key" tag in the Message structure...

[Former Member]

your strucure should be like this

<StatementName>

<dbTableName action=”SELECT”>

<table>realDbTableName</table>

<access>

<col1/>

<col2/>

<col3/>

</access>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

<key2>

<col2>val2old2</col2>

</key2>

</dbTableName>

</StatementName>

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm see this link for detailed information.

[Former Member]

If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be selected.

it seems that <key> element is not a reqiured element&#65292; right?