JDBC Sender Adapter - Records sent one at a time

[Former Member]

Hello everyone,

Here's a summary of my scenario JDBC > XI > RFC.

My select statement (JDBC) looks like this:

SELECT * FROM BATCH WHERE STATUS='Y'

The problem is, whenever it returns multiple rows, the RFC can only accept one record at a time (1...1), so I need to pick up the records from the DB and send them one at a time.

Currently, this is how the output XML looks like:

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

<ns:SQL_RESP_MT xmlns:ns="http://www.pharmaindustries.com">

<row>

<number>200000000472</number>

<status>Y</status>

</row>

<row>

<number>200000000473</number>

<status>Y</status>

</row>

<row>

<number>200000000474</number>

<status>Y</status>

</row>

</ns:SQL_RESP_MT>

Instead, I need each row to be picked up and sent one at a time.

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

<ns:SQL_RESP_MT xmlns:ns="http://www.pharmaindustries.com">

<row>

<number>200000000472</number>

<status>Y</status>

</ns:SQL_RESP_MT>

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

<ns:SQL_RESP_MT xmlns:ns="http://www.pharmaindustries.com">

<number>200000000473</number>

<status>Y</status>

</row>

</ns:SQL_RESP_MT>

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

<ns:SQL_RESP_MT xmlns:ns="http://www.pharmaindustries.com">

<number>200000000474</number>

<status>Y</status>

</row>

</ns:SQL_RESP_MT>

Does anyone know the changes I need to make in my current SELECT statement or the JDBC Sender Adapter to make this possible.

Thanks in advance!

Glenn

[Former Member · Accepted solution]

2 options.

1. Change your select querry to select only one row at a time

eg. SELECT * FROM BATCH WHERE STATUS='Y' and <field1>=min<field1>

This returns only 1 row at a time

2. Use multimapping with out BPM.

CHange the occurance of RFC from 1..1 to 1..unbounded in Mapping "Messages" tab. Take a look into the blog "Multimapping with out BPM" in SDN.

[Former Member · Accepted solution]

>

> 2 options.
> 
> 1. Change your select querry to select only one row at a time
> 
> eg. SELECT * FROM BATCH WHERE STATUS='Y' and <field1>=min<field1>

Hello Jaishankar,

What should the value of field1 be? is it a column in the table?

Thanks!

Glenn

[Former Member · Accepted solution]

hi Glenn,

you can use the fallowing select statemnt to retrive only 1 record from the source database

SELECT * FROM BATCH WHERE STATUS = 'Y' AND ROWNUM = 1.

Thanks,

Sreedhar Goud.

[Former Member]

You can handle it in mapping..

in graphical mapping, messages tab set the occurrence of target RFC message to unbounded...

in mapping

number (set context to root)---> splitByValue(eachValue) ---> target RFC root message.....

[Former Member]

Hi,

RFC accepts only one record, to send mulitiple record u have to go with BPM concept.

Check this link which is File to RFC with mulitiple record using BPM concept.

http://www.saptechies.com/multiple-occurrence-of-file-to-single-occurrence-of-rfc/

Regards,

Phani

[Former Member]

Thank you all for your replies. I'll check out your link in a bit, Phani. Thanks!

A SpliByValue won't work since the BAPI Header is (1...1).

Luckily, during testing, I found out that the BAPI (BAPI_PRODORDCONF_CREATE_TT) handles multiple lines in one of the subelements of the header so that was where I mapped the Rows.

I might need to trigger multiple BAPIs in the future, so I'll take a good look at your suggestions, but for now, I'm marking this question answered.

Warm regards,

Glenn

[Former Member]

>>A SpliByValue won't work since the BAPI Header is (1...1).

That is why you need multimapping. Multimapping will generate as many RFC calls as you want with a single mapping.

But now that you identified multiple records can be handled in a single RFC call, go for that.

>>What should the value of field1 be? is it a column in the table?

Yes. You can use any colum for this.

Edited by: Jaishankar on Jul 30, 2008 2:13 PM

[Former Member]

Use function in Message mapping.

[Former Member]

Hi,

Did u used Multiple mapping method?

Use BPM or user one function(node function->splitbyvalue).

Thanks,

Sendil