JDBC to RFC synchronous scenario

[former_member460664]

Dear All,

I have a scenario where I need to send data from a database to SAP. There are many new records in database. PI has fetched all records (ex 4records) at a time under one message. But RFC takes first record of the message and processes transaction in SAP and generates new SO number and give back the same SO number to PI. PI will update the SO number in Database against Stock Id. Nothing has done to remaining 3 records. Cant we pass record by record to RFC even PI fetches many records at a time by using written query in sender JDBC adapter? I have checked with both occurrences ( 1....1 and1... many) of sender data type. In both cases PI takes 4 records and executes only one record. I cant understand why PI has taken 4 records at a time even If I use 1...1 occurrence for sender data type. Any help would be great appreciated...

Thanks in advance,

Best Regards,

Ashok.

[Former Member · Accepted solution]

Hello Ashok,

The JDBC Adapter basically does not care what you have defined in your data type.

It depends on the SQL query that you defined in the channel.

You can for instance add an UP TO 1 ROWS statement in you SQL query.

The the adapter will only pick up one record every time it polls the database.

Obviously it can take quite some time until all new records are processed.

But if you have only a limited number of records to read, It'll be OK.

The other option is to create a multi-mapping that creates one single RFC message for every record in your result set. With this option you can process all new records with just one poll.

However, you can use this scenario only asynchronously. If you want to process the response of the RFC function module and write that into the DB Table, you need to create a ccBPM or NetWeaver BPM process. With such a process you can build a bridge between synchronous and asynchronous interfaces.

Alternatively you can build a second asynchronous interface from SAP to non-SAP to transfer the SO numbers.

Hope this helps,

Regards,

Jürgen

[former_member460664 · Accepted solution]

Thank you Jurgen. We are facing the same issue what you have said. By using multimapping, my synchronous scenario become asynchronous. But we want to use only synchronous method. For this we dont want to use ccBPM method. Please let me know if we use ABAP proxies for this requirement, Is proxy accepts multiple records and give the response back to PI to update SO number with out using multimapping?

Regards,

Ashok.

[Former Member · Accepted solution]

Hello Ashok,

You cannot use a JDBC Sender Channel to build a synchronous scenario.

The sender channel uses an update statement to set the records processed.

As a workaround you can go for the following approach:

JDBC sender channel --> PI Mapping

Use RFC Lookup function to create sales order via RFC and receive SO numbers

PI Mapping --> JDBC receiver channel

Basically this would mean, that the sender and the receiver would be your Database.

Regards,

Jürgen

[former_member460664 · Accepted solution]

I have used Request Response bean to use JDBC as synchronous. For this I have created two jdbc channels one as sender and another as asynchronous. My scenario is working fine for one record. If we got multiple records, only the first record has been processed. But we want to process n number of records at a time.According to your earlier reply we dont have any problem for asynchronous JDBC to RFC scenario even we have n number of records from source side. The problem is only with synchronous scenarios.... can we do this with synchronous proxies????

[Former Member · Accepted solution]

Hello Ashok,

I haven't  used the Request Response Beans so far.

But yes, if you create your own ABAP proxy to wrap several SO creations into one call, you can return a table with the created sales order numbers.

But I guess you have to develop the ABAP proxy from scratch.

Regards,

Jürgen

[former_member460664 · Accepted solution]

Dear Jurgen,

Now I want to fetch single record for every poll. I dont have no problem to fetch single record. But while updating, all new records have been updated by Update statement written in channel. How to write Update statement to update the single record which is fetched by the SELECT statement.

Regards,

Ashok.

[Former Member · Accepted solution]

Hello Ashok,

Of course this is depending on your database.

Basically you have to use the same where clause in your update statement as in your select statement.

Example:

select ... from ...  where docnr = ( select min(docnr) from ... where state = '1' )

update ... set state = '5' where docnr = ( select min(docnr) from ... where state = '1' )

The first statement selects the record with the lowest docnr that has status "1".

After the record has be processed, this record is updated to status "5".

Hope this helps,

Jürgen

[former_member460664 · Accepted solution]

Thanks for your quick response. My database is SQL Server 2008 R2.

My Query statement is:  Select top 1 EV.ExportVendorId, EV.CreatedOn, EV.BranchId, EV.ProductCode,EV.Quantity, EV.NotificationNo,VM.VendorCode,NTN.ProductSerialNo AS SerialNo FROM ExportToVendor EV Inner Join Notification NTN ON NTN.NotificationNo = EV.NotificationNo Inner join VendorMaster VM ON VM.VendorId = EV.VendorId and EV.SAPStatus = 0

My Update statement is:  UPDATE ExportToVendor SET SAPStatus = 1 WHERE SAPStatus = 0

By using above select statement I got only one record. But Update query updates all the records at a time. So from second poll, we dont have any new records. We want to update single record which is fetched by select statement. For testing we have inserted five records in table to process one record for each poll. Any help........

Regards,

Ashok.

[Former Member · Accepted solution]

Hello Ashok,

I'm not an MS SQL expert, but I guess that the "top 1" command in your select statement delivers you the first record. If so, you should also use this command in your update statement.

Please close the discussion if solved, and reward points for helpful answers.

Regards,

Jürgen

[former_member200386]

Hi Ashok,

I worked on the this type of requirement before. It's better to go with two asynch scenarios. I worked on abap proxies .

Regards,

Pavan

[former_member460664]

Thank you Pavan,

Now I have used proxy approach. But I have used Synchronous ABAP proxy in receiver side. If I use two asynchronous proxies, what are the uses? Please let me know.

Regards,

Ashok.

[former_member200386]

Hi Ashok,

If it JDBC at sender side i mean jdbc to proxy , you can't use it for synchrounus. if jdbc at receiver side i mean proxy to jdbc you can use it for synchronous scenario.

Regards,

Pavan

[former_member460664]

Yes Pavan you are right. We cant use sender jdbc as synchronous. I have used Request Response bean to make sender jdbc as synchronous.

Now I am facing another issue that is related to update statement in sender jdbc adapter. My requirement is, for example I have 10 new records in table with Status=N. I have to pick only one record for each poll interval specified in communication channel. By using select statement I have fetched first record and send it to SAP. But update statement has updated all the 10 records at one time with Status=O. From next poll interval onwards, there are no records with Status=N. But I should update only one record that is fetched by select statement. How to do this? Please let me know the solution.

Regards,

Ashok.

[former_member201264]

Hi Ashok,

You can check in your new Discussion thread.

Regards,

Sreeni.

[former_member200386]

Hi Ashok,

In your SQL select query use row count=1, & while sending the response from SAP. plz calculate the time to take process 10 sales orders in sap  accordingly  schedule a job that run for every 10 records got processed.

Regards,

Pavan

[former_member201264]

Hi Ashok,

I did the same in my last object.

Here you can not use JDBC at sender side as synchrononous.

You can use JDBC to JDBC Asynchronous Scenario but use RFC Lookup Graphically in the mapping.

And make sure 0-unbound Occurance at receiver JDBC Data type at Recordset and Record Level.

Keep posting for further queries.

Regards,

Sreeni.

[former_member460664]

Thank you Sree,

we have jdbc receiver structure like this.

statementname

   dbtablename

      action

      table

      access

      key

I have to update SO number based on two key field values. In the above structure, which is Recordset and which is Record level. I think statementname is recordset and access is record. Is my guess is correct?

Regards,

Ashok.

[nabendu_sen]

Hi Ashok,

You dont need to populate at <StatementName>, please find the below blog for the correct structure:

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

http://wiki.sdn.sap.com/wiki/display/XI/JDBC+TO+JDBC

[former_member181985]

Hello Ashok,

Better proceed with two asynchronous scenarios, but this approach needs some custom development on SAP back end system.This way you can avoid BPM and this design will ensure accurate status of processing for each data base record.

1. JDBC (pool with e.g., status = 'N', update status = 'R')  --> PI --> ZRFC1 (SAP)

pool with JDBC sender channel e.g., let us say with (status = 'N' - new) and then use update statement to mark as (status = 'R' - read) in database. Now pass the read message (multiple records in one message) to custom FM (RFC) which should accept multiple records. The custom FM process each record and then send the response for each record via another custom ZRFC to PI.

2. ZRFC2 (SAP) --> PI --> JDBC (Update accordingly)

After receiving response for each record via RFC sender channel, PI should send status to DB via update statement.

Status = 'S' - success

Status = 'E' - Error

Note: Instead of function module (FM), ABAP proxies can be used for better performance.

Regards,

Praveen Gujjeti

[former_member460664]

Thank you Praveen for your help.

If I use ABAP proxies, do I create two asynchronous scenarios or one synchronous scenario is sufficient for this requirement.

Regards,

Ashok.

[former_member181985]

Two Asyncrhonous proxies.

one server proxy --> call client proxy from server proxy for each record processing status

one client proxy

[nabendu_sen]

Hi Ashok,

If you are getting 4 Records from Database but sending only one to SAP, then the rest 3 transactions will be lost. You can not save for later processing, as its Synchronous. The Response from SAP will come back to Database, and that will complete the whole processing part.

So please change the Query at the Sender side so that it fetches only one Record at a time, otherwise you need to make this interface from Synchronous to Asynchronous. Otherwise you need to use BPM for Async-Sync.