SAP PI – UDF to capture multiple result sets from a Stored Procedure in DB

Finally, my first blog post to SCN! 🙂

I was wondering when will I get a chance to write like the experts and finally here I am. I am really happy about it.

My blog post addresses a scenario to capture multiple Result Sets by calling a Stored Procedure.

I thought a sender JDBC channel would be capable enough to handle multiple result sets when a JDBC call is made to a Stored Procedure. But that was not the case. I tried a pass through scenario on a JDBC-to-File in which I called a JDBC Stored Procedure and tried writing it to a file directly. The Channel captured only the first result set by default and ignored the rest. So, I wrote a UDF which directly connects to the DB and captures multiple result sets.

Before we begin, there are few terminologies which need to be kept in mind.

Stored Procedure: A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary.

Result Set: An SQL result set is a set of rows from a database, as well as metadata about the query such as the column names, and the types and sizes of each column.

Result List: This class is used in advanced user-defined functions (execution type “Context values” or “All values of a Queue”) to return the result of a function.

System Setup:

PI: SAP PI 7.31

DB: MS SQL Server 2008

Assumptions and Pre-requisites:

• The DB resides on the PI server. If you wish to access the DB on a remote server using the code, you need to deploy the required JDBC Drivers on the PI System and have the required jars in place. In my case, the jar required is sqljdbc4.jar. Since the DB resided on the PI Server itself, it had the required drivers and jars already in place.
• Access to the DB is available. There is little basic knowledge required for writing a SP in DB.

Let’s Begin!

Stored Procedure:

The table name in DB is STUDENT_INFORMATION. The STUDENT_INFORMATION table looks like below:

My Stored Procedure looks something like as below:

CREATE PROCEDURE uspStudentDetails

AS

SELECT StudentName FROM <DB_Name>.dbo.STUDENT_INFORMATION

SELECT StudentGrade FROM <DB_Name>.dbo.STUDENT_INFORMATION

SELECT StudentNo FROM <DB_Name>.dbo.STUDENT_INFORMATION

SELECT StudentName FROM <DB_Name>.dbo.STUDENT_INFORMATION

SELECT * FROM <DB_Name>.dbo.STUDENT_INFORMATION

GO

Where <DB_Name> is the name of the data base being used on the MS SQL Server. Since there are 5 select statements in my SP, it will return 5 result sets. The output of each result is captured in each context (please see the output queue at the end of the blog).

I have generalized the UDF so that it takes the connection details, user id, password and stored procedure name as input to make the call to the DB and fetch the required details. The connection details are the same that you mention in your JDBC sender channel.

The general format of the Connection is: jdbc:sqlserver://<hostname>:<port>;databaseName=<DBName>

UDF DETAILS:

UDF Type: Queue

Input Constant Parameters: String[] db_connect_string, String[] db_user_id, String[] db_password, String[] storedproc_name

Output: ResultList[] result;

UDF Code:

try {

Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);

         Connection con = DriverManager.getConnection(db_connect_string[0],db_user_id[0],db_password[0]);

         String SQL = “EXECUTE dbo.”+storedproc_name[0];

         Statement stmt = con.createStatement();

         boolean results = stmt.execute(SQL);

         do {

             if(results) {

                ResultSet rs = stmt.getResultSet();

                                                                ResultSetMetaData rsmd = rs.getMetaData();

                                                                 int numberOfColumns = rsmd.getColumnCount();

                while (rs.next()) {

                                for (int k=1;k<=numberOfColumns;k++){

                                                                                                                                                                                                result.addValue(rs.getString(k));

                                                                                }

                }

                                                                                                                                result.addContextChange();

                rs.close();

             }

             results = stmt.getMoreResults();

             } while(results);

           stmt.close();

      }

    catch (SQLException e)

                         {               

1. result.addValue(e.getMessage());

                            }

      catch (Exception e) {

         result.addValue(e.getMessage());

      }

Mapping:

Output Queue:

Since the length of the UDF was long I captured the first 4 result sets in one snapshot and the 5^th resultset in another snapshot.

The below queue displays the first four result sets captured from the UDF.

Output queue for result set 1 – 4:

The below output queue is in continuation to the above one.

Output Queue for Result Set 5:

Since this is my first blog, Please share your feedback and comments below. 🙂

Thanks,

Arkesh