JDBC Lookup and populate fields for output

[Former Member]

I have a flat file like this:

RecordNo,EmpNo,EmpCom,EmpName,Age,No,Char

1,0024,A Company,Ryan,36,44,male,X2

2,0023,B Company,,34,45,,X1

I have to do a JDBC look up from PI and populate the string and send the file out.

For eg Reocrd 1 for emp no 0024 has the name as "Ryan" and "male" being populated and I want to populate from SQL Databse if name and age are not populated as in record 2.

look up 0023 in SQL table and get the name and age as Sam male only if the fields are blank .

Result:

1,0024,A Company,Ryan,36,44,male,X2

2,0023,B Company,Sam,34,45,male,X1

How can this be acheived .I am totally new to look ups from PI and I have gome thru couple of threads

but not find a sloution.

/people/jin.shin/blog/2008/02/15/sap-pi-71-mapping-enhancements-series-graphical-support-for-jdbc-and-rfc-lookups

Can you please let me know...

thanks,

[Shabarish_Nair · Accepted solution]

have you read this blog on DB lookups?

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

[Former Member]

I am on PI 7.0 and not on 7.1 .helpful hints.

thank you people

[Former Member]

if you are on PI 7.1 it very easy, all things you can do using some clicks...

otherwise the blogs suggested above are the good ref.

Edited by: NetWeaver Expert on May 12, 2009 9:04 AM

[Former Member]

Hi Kumar,

Check this link

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

In the lookup

1. First we need to check weather the fields are empty or not.

2. If the field(s) is/are empty then we have to connect to the DB

3. By using the select query based on EmpNo we have to take the corresponding missing field(s) from the DB table and set to the target structure.

Regards

Ramesh

[Former Member]

Hi,

If you are working on PI 7.1 then you have ready functions for JDBC lookups.

Follow as per below steps:

1. For your message mapping define parameters under signature tab. This parameter will be of JDBC adapter type in which at runtime you will pass your CC.

2. Now pass the where field as input to your JDBClookup function.

3. Inside function you need to provide the above CC and select fields you want to retrive.

4. Also you need to define a variable in operation mapping and bind these two.

5. In interface determination you have to pass the value to this OM variable. This should be your communication channel name of type JDBC receiver.

I Hope this clears most of you doubts for using JDBC look ups.

Regards,

Siddhesh S.Tawate