Querries on JDBC adapter

[Former Member]

Dear Experts,

I am working in 2 scenario with SAP ECC and Oracle database using SAP PI 7.4 JAVA stack. I am running out of ideas on the below.

1.In one scenario, I am working with Receiver JDBC adapter. I want to receive System Acknowledgement. Since by default, receiver JDBC adapter doesn't request one, is there any module for it.

2. Does the Oracle database have to perform any changes in the system if receiver JDBC request for System acknowledgement?

3.In another scenario with Sender JDBC adapter (Asynchronous), I am trying to fetch updated records of 2 fieldA and fieldB from database table. Dont want to fetch the already picked records. From the library I found the following.

SQL statement for query: SELECT fieldA,fieldB FROM table WHERE processed = 0;

SQL statement for update: UPDATE table SET processed = 1 WHERE processed = 0; processed is the indicator in the database.

would the above statement will work (currently I am not given table authorization, so cant test

4. Is Stored Procedure a better option than select statement? How to maintain SP in Sender JDBC adapter

Regards..

[suman_saha · Accepted solution]

Hi,

1. You can use RequestResponseBean and ResponseOnewayBean modules to achieve synchronous JDBC scenario.

You may follow:

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/80f96dbf-adca-3010-ffb5-daf2d1f0e...

2. The question is not clear to me."perform any changes in the system"-what do you mean by the change?

3. Yes it seem to work. You need to use the same where clause for both select and update query and you need to update any of the condition(as in where clause) during update query.

4. If it is a simple select from a particular table,why do you want to use stored procedure?

Regards,

Suman

[Former Member · Accepted solution]

Hi Suman,

1. I have a scenario of Asynchronous Communication  between Sender ECC and Receiver Oracle Database. I want to know whether I can use any module parameter in Receiver JDBC to receive SYSTEM Acknowledgement.

2.<< The question is not clear to me."perform any changes in the system"-what do you mean by the change?>>

Whether the Oracle System has to maintain some code in their system to send the system acknowledgement or the JDBC receiver adapter take care of itself to getting this acknowledgement.

4. <<If it is a simple select from a particular table,why do you want to use stored procedure?>>

I may not be given UPDATE authorization in the table. The ORACLE team would also prefer going to SP.

Regards...

[suman_saha · Accepted solution]

Hi,

Check this link to know more about acknowledgement

Acknowledgments - SAP NetWeaver Process Integration - SAP Library

Regards,

Suman

[iaki_vila · Accepted solution]

Hi Rebecca,

1. I have a scenario of Asynchronous Communication  between Sender ECC and Receiver Oracle Database. I want to know whether I can use any module parameter in Receiver JDBC to receive SYSTEM Acknowledgement.

Like the other members i think the best ack is the response itself, however the jdbc receiver has a a system acknowledgement when the message is deliverded. Check the point seven: Configuring the Receiver JDBC Adapter - SAP NetWeaver Exchange Infrastructure - SAP Library

About the sender jdbc question if you have an Oracle database you can not restrict the number of the registers where in the Update in the same way than the Select, from my experience the Update secction with a Stored Prcedurce you can restrict this number of registers taken and to avoid the sender jdbc overload.

Regards.

[Former Member · Accepted solution]

Dear Inaki,

Your input saved a lot of time.

I set the parameter XI.AckFinal=true which is by default in the receiver JDBC adapter but I could not see any message in NWA.

Since scenario has SOAP adapter as sender, do I need to configure SOAP as well.

I think I am close to the solution.

Regards..

[iaki_vila · Accepted solution]

Hi Rebecca,

As far as i know you need to create a http destination in NWA, i haven't configured any for ACKs, may someone can help in this configuration.

Regards.

[nabendu_sen]

Hi Rebecca,

For your first scenario, it should be Synchronous.

In case of SELECT statement JDBC adapter will select some rows/columns and return it.

In case of other statements (UPDATE,INSERT,DELETE) it will return number of rows affected.

SAPTechnical.COM - Implementation of SOAP to JDBC Synchronous scenario

If your Sender ECC calls this Asynchronously, you can consider JDBC Lookup from Mapping.

For Stored Procedures:

Regards,

Nabendu.

[Former Member]

1. is your scenario sync? if yes then JDBC receiver adapter will send back acknowledgement - <StatementName_response>

http://help.sap.com/saphelp_nw70ehp2/helpdata/en/b0/676b3c255b1475e10000000a114084/content.htm?frame...

3.In another scenario with Sender JDBC adapter (Asynchronous), I am trying to fetch updated records of 2 fieldA and fieldB from database table.  - The sql statement you mentioned will work - you need to maintain the column as processed or flag to set the status as "0 or 1". 

4. Is Stored Procedure a better option than select statement? How to maintain SP in Sender JDBC adapter - if requirement is complex like fetching records from multiple tables or you need to use joins are something then you can go for Store procedure.

Refer -

BR

Ramg

[Former Member]

Hello Experts,

Any one to suggest how to handle System acknowledgment and Select statement without duplicate records.

Regards..