Skip to Content

Introduction:

In this blog, we will have a look on how to store and retrieve data in SAP PI on java database in a custom table. And perform CRUD (Create, Read,Update and Delete) of records in the custom table. Also, I will show how to expose this functionality as a web service of that other local applications can easily access the data stored in the custom table and perform operations on the table (after authentication of course).

Why store data:

There can be many use cases to store data and retrieve data from SAP PI database tables, for example, to store key attributes of set of interfaces for administration or store business error details of set of interfaces for tracking purpose.

Why on java stack:

In dual stack PI systems, data persistence can be achieved in ABAP stack or Java stack. However, in single stack PI systems (SAP Process Orchestration),  data persistence can be achieved only in Java stack. Also, by persisting data on java stack in dual stack systems, interfaces can be PO ready.

Scenario:

Let’s assume that PI is interfacing ticketing tool, and we want to store all the tickets operations (like create, update, close etc.) made on all tickets through PI. Later we can check for any particular ticket what operations were made through PI.

Step 1: Create new table using Java dictionary.

Open NWDS and Select Window > Open Perspective > Other > Dictionary

/wp-content/uploads/2016/01/1_a_869899.jpg

Create new project. Select File > New > Other

/wp-content/uploads/2016/01/1_b_869884.jpg

Select Development Infrastructure > Development Component

/wp-content/uploads/2016/01/1_c_869885.jpg

Select Dictionary in Development Component Type and Click Next

/wp-content/uploads/2016/01/1_d_869886.jpg

Select MyComponents and Click Next

/wp-content/uploads/2016/01/1_e_869736.jpg

Provide development component name and click Finish

/wp-content/uploads/2016/01/1_f_869756.jpg

In Dictionary Explorer, Expand Dictionaries > Local Dictionary > Database Tables and right click and select Create Table

/wp-content/uploads/2016/01/1_g_869757.jpg


Give a prefix and suffix and select Finish

/wp-content/uploads/2016/01/1_h_869758.jpg

Add columns to the table and save

/wp-content/uploads/2016/01/1_869780.jpg

Right-click on project, Select Development Component > Build

/wp-content/uploads/2016/01/1_j_869781.jpg

Right-click on project, Select Development Component > Deploy

/wp-content/uploads/2016/01/1_k_869782.jpg

Verify the table created in SAP PI NWA > Troubleshooting > Database > Open SQL Data Browser

/wp-content/uploads/2016/01/1_l_869786.jpg

STEP 2: Create Entity Bean for the table created

Create new project in NWDS

/wp-content/uploads/2016/01/2_a_869787.jpeg

Select Development Infrastructure > Development Component and click Next

/wp-content/uploads/2016/01/2_b_869788.jpg

Select J2EE > EJB Module and click Next

/wp-content/uploads/2016/01/2_c_869813.jpg

Select MyComponents and Click Next

/wp-content/uploads/2016/01/2_d_869814.jpg

Provide Development Component Name and click Next

/wp-content/uploads/2016/01/2_e_869820.jpg

Select Java EE Version 5.0 and click Finish

/wp-content/uploads/2016/01/2_f_869821.jpg

Switch to Java EE perspective

/wp-content/uploads/2016/01/2_g_869822.jpg

In project explorer, Expand to ejbModule and Right-click on it and select New Class

/wp-content/uploads/2016/01/2_h_869823.jpg

Provide package name and Class name. Implement Serializable interface and click Finish

/wp-content/uploads/2016/01/2_i_869824.jpg

Add @Entity, @Table annotations to entity bean class. Add @Id annotation to all primary key fields. The attributes of the class should be exactly same as columns of the database table. The table name should be same as the table created in java dictionary

/wp-content/uploads/2016/01/2_j_869837.jpg

Since our primary key is composite (more than 1 column) we have to create composite key class and implement Serializable interface. Right click on EJB project and select New > Class and provide package name and class name and Implement Serializable interface

/wp-content/uploads/2016/01/2_k_869838.jpg

Add all the primary key fields in this key class

/wp-content/uploads/2016/01/2_l_869839.jpg

Generate getters and setters for the primary key class attributes

/wp-content/uploads/2016/01/2_m_869850.jpg

Select all attributes and click OK

/wp-content/uploads/2016/01/2_n_869887.jpg

The primary key class should look like this

/wp-content/uploads/2016/01/2_o_869897.jpg

Generate getters and setters method for entity bean class

2.p.JPG

Add default default serial version ID and IdClass annotation to the entity bean class

/wp-content/uploads/2016/01/2_q_869903.jpg

Add a NamedQuery to the entity bean. Provide a name to this named query so that we can call this query later. This query will return all the rows in the table

/wp-content/uploads/2016/01/2_r_869864.jpg

Next steps are available in part-2

To report this post you need to login first.

5 Comments

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

  1. Anupam Ghosh

    Hi Anand,

                      This is a beautiful blog with clearly explained steps.

    Thank you for writing this.

    Regards

    Anupam

    (0) 
  2. Vikas Singh

    Nice blog. However, in order to display tables in SQL browser you may need to deploy a UME action . Something like this :

    <?xml version=”1.0″ encoding=”UTF-8″ standalone=”no”?>

    <BUSINESSSERVICE>

      <ACTION

      NAME=”TableRead”>

      <DESCRIPTION

      LOCALE=”en”

      VALUE=”TableRead”/>

    <PERMISSION class=”com.sap.sql.itsam.databrowser.permission.DataBrowserPermission” NAME=”ReadTables” VALUE=”Z_*” />

      </ACTION>

    </BUSINESSSERVICE>

    (0) 
      1. Anand V D Post author

        Hi Bhavesh,

        You need to create UME permissions project in NWDS and edit actions.xml file in it and deploy it into Java AS.

        Then you need to create UME role for the deployed action. Then assign the created role to user who wants display access to the table entries.

        Thanks,

        Anand

        (0) 
  3. Jon Nyholt

    Thanks for sharing these instructions. One suggestion I found is that it’s easier to create the Entity Bean by using the New / Entities From Tables menu option (right-clicking on the ejb source folder).

    The Entities From Tables option will auto-generate the java code and you don’t have to worry about manually adding all the java methods.

    (0) 

Leave a Reply