Need help in writing java UDF for the jdbc look up scenario

[Former Member]

Hi Experts,

need help in writing java UDF for the jdbc look up scenario:

If any material is created in sap, the status code coming as status = 'create' then pi system will insert the record with the status 'create' in the receiver sql database.

This part is working fine but if any material is modified in SAP the status code coming as status = 'created' then , PI system should insert the record by checking in the database for the record status 'created' and update the database with the status as Modify .

If the status of record in the sql database is still in 'create' , then the PI system should insert the record and update the status as 'create'.

Here some program in legacy system will update the status after reading the record.

I am looking for writing the jdbc lookup for getting the record status value from sql database and compare the value with the input SAP status field.

I need the UDF java code for this reqmt. pls help me.

Thanks & Regards,

Ram

[Shabarish_Nair · Accepted solution]

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

these are some good references available in SDN

[Former Member]

After discussions with my client, we decided to do minor changes in the design keeping performance issues in view, finally I could solve the issue with the help of JDBC LOOKUP and UDF in the Message mapping which retrives the required string value from receiver table structure.

Many Thanks..

Best Regards,

Ram.

[Former Member]

hi Ram,

maybe a stupid question: but why this test is not done by your target database ? that should be easy for them to do it if they use a stored procedure to receive data from PI.

because in your design, you will add a new SQL request and so a PI-DataBase connection for each Material ! in term of perf that could be not really good, if you have plenty of Material.

Else if you really want to use JDBC lookup, for the graphical function is:

http://help.sap.com/saphelp_nwpi71/helpdata/EN/44/749dd812dd3676e10000000a114a6b/content.htm

Regards.

Mickael

Edited by: Mickael Huchet on Oct 13, 2011 11:33 AM

[Former Member]

Hi Mickael

Thanks for the quick response.

Here the scenerio is from the Legacy end the program will read the record from the intermediate database table and update the status from 'Create' to 'Created'. then if any updates happens from sap end, then the pi system will update the record by checking the status of the intermediate table and if the status of the record found was 'Created" then SAP PI will change the status as 'Modify'.

if the value in the intermediate table is still in ' CREATE' state then the pi system will update the same record once again and put the status as ' CREATE'.

so i am expecting the code for doing the validations in jdbc lookup udf based on the intermediate resultset values i.e record status with the input value of the record status field from sap.

Pls note that the client is having the SAP PI7.0 version

Thanks and Regards

Ram

[PriyankaAnagani]

Hi Ram,

pass the material no that you are getting from SAP as input to the UDF which will return the status and using graphical mapping compare the result of lokkup with the input that you are getting from sap and map your target elements respectively.

UDFcode:

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

// prepare your SQL query

Query = "Select status from DBtablename where materialNo'" +matNo +"'";

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("DB-SYSTEM-NAME","DB-CHANNEL-NAME");

//Get a database accessor for the channel.

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("status"));

}

}

catch(Exception ex){

result.addValue(ex.getMessage())

}

finally{

try{

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

}

catch(Exception e){

result.addValue(e.getMessage())

}

}

Regards,

Priyanka

Edited by: priyanka.anagani on Oct 13, 2011 1:23 PM

[Former Member]

Hi Priyanka,

Thanks for the quick response.

I have seen the code that was mailed in one of the weblog but I am expecting the code for doing the validations in jdbc lookup udf based on the intermediate resultset values i.e record status with the input value of the record status field from sap.

the code is required for doing the following validations:

if result set value from the database table rec_status = u2018createu2019

then compare with the input field value Rec_Status1.

if matches then we have to send the status has u2018createu2019 please note this is for the first time creation of the material from sap end.

if result set value from the database table rec_status = u2018createdu2019

then compare with the input field value Rec_Status1. if matches then we have to update the same record and send the status has u2018modifyu2019 if not matches we have to update the same record and send the status has u2018createu2019 .

please note this is after modifiication of the same material from sap end.

Thanks and Regards

Ram