Skip to Content

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:

Student_Table.JPG

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.

JDBC_Connection_Parameter.JPG

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:

SP_Call_Mapping.JPG

Output Queue:

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

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

Output queue for result set 1 – 4:

Output_queue_rs1to4.JPG

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

Output Queue for Result Set 5:

Output_queue_rs5.JPG

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

Thanks,

Arkesh

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. deepak jaiswal

    Nice blog Arkesh.It nice to see DB lookup without using BS and Communication channel in your blog functionality together with Stored procedure things in details.

    Keep it up….

    (0) 
  2. omar gibaja

    Hi Arkesh,

    I have a question from UDF CODE. I use your UDF CODE but when i compile this CODE in Function Library (PI 7.1), I get a issue in the RETURN parameter. Can you give, please a solution???.

    I use “java.sql.*” , “javax.sql.*” in Import Instructions – Function Library. Additionaly i forget to mention that I get a issue in this method “result.addValue(rs.getString(k));” (result, does not recognize when i compile ).

    try {

    Class.forName(“oracle.jdbc.OracleDriver”);

             String connectionURL =”jdbc:oracle:thin:@//172.16.2.103:1521/bddescam”;

             Connection con = DriverManager.getConnection(connectionURL,”usua_ds_iface”,”usua_ds_iface”);

             String SQL =”SELECT * from table (PACK_SC_SAP_COMPARATIVO.” + var4 + “(” + var1 +  “,to_date( ‘ ” + var2 + “‘” + “,” + “‘dd/mm/yyyy’)))” ;

             Statement stmt = con.createStatement();

             boolean results = stmt.execute(SQL);

           //  Var8 = results;

             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++){

                                    //results.addValue(rs.getString(k));

                                         }

                    }

      //             result.addContextChange();

                    rs.close();

                 }

                 results = stmt.getMoreResults();

                 } while(results);

               stmt.close();

          }

        catch (SQLException e)

                             {             

      //  a.result.addValue(e.getMessage());

                                }

          catch (Exception e) {

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

          }

    return results ; =========>>

    Regards,

    (0) 
    1. Arkesh Sharma Post author

      Hi Omar,

      I had a quick view on your code. I can see that you are connecting to Oracle DB with an IP address. I am assuming that the DB is outside the PI landscape. If that is the case then firewall settings need to be taken care of. You need to check if you’re able to access the DB from the PI server (you need to login to the PI server the way you login to your laptop/desktop and then check the connections). Next thing that I see is that you’re connecting to Oracle DB whereas in my case I was using MS SQL Server so little bit of syntax can vary here and there. You need to look into that. And above all, you’re using PI 7.1 whereas I was using PI 7.31 single stack. In that case libraries used need to be looked into.

      Since, I do not have access to any Oracle DB here in my current organization, I cannot troubleshoot your UDF code and tell you exactly where you need a fix.

      What I suggest is that you go step-by-step. First try connecting to the Oracle DB from your UDF code. If that is successful then try accessing the DB Name and Table Name by fetching some records. Once that is done then you can format your data as I had mentioned in the code or as per your requirement.

      I hope this helps. Best wishes. 🙂

      Regards,

      Arkesh

      (0) 

Leave a Reply