cancel
Showing results for 
Search instead for 
Did you mean: 

File - - JDBC - - File scenario

Former Member
0 Kudos

Hi,

I have to develop an XI interface for the following scenario:

The input is an XML file... Depending on one of the field (divsion) in the XML, i have to fetch records (plants) from an oracle database... These values (plants) have to be mapped to a single field in the target.... the output required is also an XML file... the remaining fields of the target have to be mapped from the intial input XML...

Can someone suggest a solution for the same?

Thanks in advance....

Regards,

Karen.

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Hi Karen;

In the XI lookup mapping you will have the source xml and the target xml .

In the select query for the xi lookup you fetch the fields from the database based on the field value from the source xml structure.

you will add the result fetched from the database to teh result list.

If you require anymore information you can discuss it further.

Mudit

Award points if it helps

Former Member
0 Kudos

Hi Mudit,

THanks for the reply..

I am checking the same..

Regards,

Karen

VijayKonam
Active Contributor
0 Kudos

Putting ot simply here, you can do this using BPM. Receive the source xml in BPM, then do the Oracle database sync call. now you have another result from the DB. You would use another container variable to hold this data. Now have multi mapping with the participating 2 xml messages and create the target xml. Then send.

Your BPM looks like -

Receve- Transform (if at all you have any mapping for Oracle query) - Send to Oracle (Sync) - Transfom (Multimapping) - Send (final XML).

VJ

Former Member
0 Kudos

The input file (XML) is from a legacy system which is a PLM syetem which sends material data.... this data has to go to an MDM system (o/p needed is also XML file)... but one of the fields (plants to which the material is extended) of the target structure will have multiple values.. these values will be picked from an oracle databse table... the other target field mappings are directly from the source...

Regards

Karen

Former Member
0 Kudos

hi;

In this case you will have to use BPM and XI lookup.

As you have to map the target file structure to source file fields as well as fields fetched from Oracle database you will have to do XI lookup in this mapping.

Refer These Blogs

Database lookup in XI

/people/sap.user72/blog/2005/12/06/optimizing-lookups-in-xi

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

Mudit

Award Points if it helps

bhavesh_kantilal
Active Contributor
0 Kudos

Karen,

You have 2 options,

A) Use JDBC look up as shown in this blog ,

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

B) Use JDBC adapter and a BPM and make a synchronous Select as shown in this blog,

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Regards

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I have used the first blog for JDBC lookup.

But it is returning null value in the target field?

What could be the problem?

Thanks in advance..

Regards,

Karen Pereira

bhavesh_kantilal
Active Contributor
0 Kudos

Can you procide us with the UDF code being used?

Check if the Select Query returns valid data.

regards

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I have written the code as mentioned in the blog.

my UDF code is as follows:

//write your code here

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

// Build the Query String

Query = "Select STAGE from I2_ROUTEMASTER where ROUTEID = '" + RouteId[0] + "' ";

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("BS_I2APPG","JDBC_I2SiteMaster_APPG");

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.

accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset

resultSet = accessor.execute(Query);

for(Iterator rows = resultSet.getRows();rows.hasNext();){

Map rowMap = (Map)rows.next();

result.addValue((String)rowMap.get("STAGE"));

}

}

catch(Exception ex){

result.addValue(ex.getMessage());

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage());

}

}

I also checked the query. It is returning proper values.

Regards,

Karen Pereira

bhavesh_kantilal
Active Contributor
0 Kudos

Karen,

Are you using an Advanced user Defined Function?

You select Query contains <b>RouteId[0]</b> . This should be used only for Advanced UDF's.

Also can you confirm that the input parameters i RouteId to the UDF?

Regards

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

When i created the UDF, i didnt get any option of simple or advanced function.

I am working on PI 7.0.

While creating the UDF, i got an option of selecting value, context or queue..

Regards,

Karen Pereira

bhavesh_kantilal
Active Contributor
0 Kudos

What option did you select?

Make sure you select <b>Value</b> and then use the import parameter that you defined as such with out [0]

Regards

Bhavesh

Former Member
0 Kudos

The resultset could contain multiple rows and i need to put value from all these rows in a single target field. If i select value, there is no argument in the UDF of type resultlist.

Is it possible to collect values without using resultlist.

Regards,

Karen Pereira

bhavesh_kantilal
Active Contributor
0 Kudos

> The resultset could contain multiple rows and i need

> to put value from all these rows in a single target

> field.

If you mean to say that the oyutput field is 1.. unbounded then yes you need to use the Cache / Queue option that you had selected earlier.

The way ahead to debug would be to follow this blog and check the query being created by XI,

<a href="/people/prasad.illapani/blog/2006/10/25/how-to-check-jdbc-sql-query-syntax-and-verify-the-query-results-inside-a-user-defined-function-of-the-lookup-api">How to check JDBC SQL Query Syntax and verify the query results inside a User Defined Function of the Lookup API</a>

Regards

Bhavesh

Former Member
0 Kudos

can u elaborate ur case....

can u give the XML structure of ur input file...

Former Member
0 Kudos

Hey

have a look at this

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

/people/arpit.seth/blog/2005/06/27/rfc-scenario-using-bpm--starter-kit

thanx

ahmad

Message was edited by:

Ahmad