Mapping JDBC Lookup - Need to fetch Mutliple values

[Former Member]

Hello Experts,

I have to fetch values from the JDBC table based on input fields and need to pass them to target structure and my input source xml contains multiple records(more than 10k).As the standard JDBC table will fetch one value at a time and not feasible solution in  this regard.

I thought of writing an UDF and am attaching the UDF written(not good at java)-

String query =" ";
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;

query = "SELECT GUID FROM MASTER_EMP_NEW WHERE PWCE_STAFF_ID ='653081,094465'";
try {
channel = LookupService.getChannel("PWC_ACQUIRE","CC_JDBC_RECEIVER");
accessor = LookupService.getDataBaseAccessor(channel);
resultSet = accessor.execute(query);

for(Iterator rows = resultSet.getRows();rows.hasNext();){
Map rowMap = (Map)rows.next();
result.addValue((String)rowMap.get("PWCE_STAFF_ID"));
result.addValue((String)rowMap.get("GUID"));

}
}
catch(Exception ex) {
result.addValue(ex.getMessage());
}

finally{

try{

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

}

catch(Exception e){

result.addValue(e.getMessage());

}

}

It is also fetching a single record and it is giving error when i raise the cintext of the input filed to higher level.

Please let me teh feasible approach to handle this and i am working on Pi 7.31.

As per my knowledge, the sceanrio should be making a single query to the table and get the possible input/output combinations and store them in a array variable internal to PI mapping and  then need to pass to my target as per the input filed.

Please help me with the steps to acheive.

I iwll be very much thnakful if provide me with any code snippets as i m avery bad at java.

Awaiting for your reply all the time.

Thanks & Regards

Narayanaredy B

[Former Member]

Hi

Refer the blog -

Instead of store procedure statement you can use the select query as below

query = "SELECT GUID FROM MASTER_EMP_NEW WHERE PWCE_STAFF_ID in ("+var[0]+")";

var [0]=653081,094465,;

Just concatenate all input field values and pass as a constant , i feel the querying the table will slow down the execution.