cancel
Showing results for 
Search instead for 
Did you mean: 

SAP PI 7.31 - Dynamic JDBC SQL SP Lookup for handling multiple result sets

arkesh_sharma
Active Participant
0 Kudos

Hi Experts,

I have a scenario in which I have to call a Stored Procedure on a SQL Server DB which will return me two different result sets.

The two result sets can have multiple values in it.

From the simple dynamic UDF I was able to achieve single result set with multiple values but I was unable to handle multiple result sets in the code.

Can you please guide me how to handle multiple result sets in my code?

I tried a few things like creating another ResultList queue (i.e. result2) in my code that can handle second result set and also checking if the returning values have more than one result set but none of them worked out.

The Stored Procedure looks like below.

CREATE PROCEDURE uspStudentData

AS

SELECT StudentName AS ValidName FROM dbo.STUDENT_INFORMATION

SELECT StudentGrade AS ValidGrade FROM dbo.STUDENT_INFORMATION

  GO

The output Result Sets will look something like:

ValidName

     Anil

     Arkesh

     Vinay

ValidGrade

     A

     B

     C

Below is the UDF code I am using to do the DB Lookup on Stored Procedure. The code marked in BOLD-GREEN are the different code experiments I was trying to do to handle multiple result sets but it didn't work out for me.

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

// prepare your SQL query

Query = "EXECUTE dbo.uspStudentData";      


try{

//Determine a channel, as created in the Configuration
channel = LookupService.getChannel("BC_Sender","CC_JDBC_Rcvr_Channel");

//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("StudentName"));
//result2.addValue((String)rowMap.get("StudentGrade"));
}


/*
accessor.getMoreResults();
result.addContextChange();

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


*/

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

finally{
try{
if (accessor!=null) accessor.close();
}
catch(Exception e){
result.addValue(e.getMessage());
}

}

Accepted Solutions (1)

Accepted Solutions (1)

arkesh_sharma
Active Participant
0 Kudos

Finally, I have written a blog which solves my problem stated above.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/07/19/sap-pi--udf-to-capture-multiple-r...

Former Member
0 Kudos

HI Experts,

How to send date parameter to DATABASE through PI?

Answers (0)