Database Handling in SAP PI Map Using JDBC API

[Former Member]

Applies to:  

  SAP Process Integration 7.0 and Above Versions. Database handling using JDBC API in SAP PI graphical maps.

Summary:

In many business scenarios we like to insert/ update or delete the database record of a particular database. We can achieve this in various ways, this article demonstrates how to access database records and execute DDL and DML statements from SAP PI Mapping level. This article demonstrate the code and it’s functionality for SQL Select statement but the code can be extended and modified with other SQL statement as well.

Introduction:

JDBC stands for "Java DataBase Connectivity". It is an API (Application Programming Interface) which consists of a set of Java classes, interfaces and exceptions and a specification to which both JDBC driver vendors and JDBC developers adhere when developing applications which are platform and database independent applications in java. JDBC is a very popular data access standard. RDBMS (Relational Database Management Systems) or third-party vendors develop drivers which adhere to the JDBC specification. Other developers use these drivers to develop applications which access those databases.

SUN (Currently Oracle) prepares and maintains the JDBC specification. Since JDBC is just a specification (suggestions for writing and using JDBC drivers), third-party vendors develop JDBC drivers adhering to this specification. JDBC developers then use these drivers to access data sources.

Prerequisite:

Knowledge of SAP Process Integration 7.0 and/or above, JDBC API knowledge, knowledge of Graphical Mapping in SAP PI.

What is JDBC and its scope within Java:

  JDBC provides a standard library for accessing relational databases

  JDBC API standardizes

• Way to establish connection to database

• Approach to initiating queries

• Method to create stored (parameterized) queries

• The data structure of query result (table)

– Determining the number of columns

– Looking up metadata, etc.

– API does not standardize SQL syntax

• JDBC is not embedded SQL

– JDBC class located in

    java.sql package

• Note: JDBC is not officially an acronym; unofficially,

“Java Database Connectivity” is commonly used

Most important members, such as Connection, Statement, and ResultSet, are interfaces instead of being classes.

This is because; the point of JDBC is to hide the specifics of accessing a particular database.

The implementation of the underlying classes is done in the vendor provided driver and associated classes

What are the JDBC drivers and how it is used:

A JDBC driver is a software component enabling a Java application to interact with a database.

JDBC Drivers are set of classes that enables the Java application to communicate with databases. Java.sql that ships with JDK contains various classes for using relational databases. But these classes do not provide any implementation, only the behaviors are defined. The actual implementations are done in third-party drivers. Third party vendors implement the java.sql.Driver interface in their database driver.

                  

There are 4 types of JDBC drivers. Commonest and most efficient of which are type 4 drivers. Here is the description of each of them:

• JDBC Type 1 Driver - They are JDBC-ODBC Bridge drivers. They delegate the work of data access to ODBC API. They are the slowest of all. SUN provides a JDBC/ODBC driver implementation.

                                                          

• JDBC Type 2 Driver - They mainly use native API for data access and provide Java wrapper classes to be able to be invoked using JDBC drivers.

                                                              

• JDBC Type 3 Driver - They are written in 100% Java and use vendor independent Net-protocol to access a vendor independent remote listener. This listener in turn maps the vendor independent calls to vender dependent ones. This extra step adds complexity and decreases the data access efficiency.

                                                                  

• JDBC Type 4 Driver - They are also written in 100% Java and are the most efficient among all driver types.

                                                                          

SUN encourages developing and using type 4 drivers in applications.

Overview of JDBC Architecture and API:

The JDBC API is available in the java.sql and javax.sql packages. Following are important JDBC classes, interfaces and exceptions in the java.sql package:

• DriverManager - Loads JDBC drivers in memory. Can also be used to open connections to a data source.
• Connection - Represents a connection with a data source. Is also used for creating Statement, PreparedStatement and CallableStatement objects.
• Statement - Represents a static SQL statement. Can be used to retrieve ResultSet object/s.
• PreparedStatement - Higher performance alternative to Statement object, represents a precompiled SQL statement.
• CallableStatement - Represents a stored procedure. Can be used to execute stored procedures in a RDBMS which supports them.
• ResultSet - Represents a database result set generated by using a SELECT SQL statement.
• SQLException - An exception class which encapsulates database base access errors.

  javax.sql is part of J2SE 1.4 and J2EE 1.3. It adds following features to JDBC in addition to the ones provided by java.sql package: 

• DataSource - Abstracts a data source. This object can be used in place of DriverManager to efficiently obtain data source connections (possibly using hidden connection pooling).
• Provides built-in connection pooling.
• XADataSource, XAConnection - Allows/supports distributed transactions.
• RowSet - It extends ResultSet interface to add support for disconnected result sets.

     Basics of JDBC and creating Statements: 

  Seven Basic Steps in Using JDBC

1. Load the driver
2. Define the Connection URL
3. Establish the Connection
4. Create a Statement object
5. Execute a query
6. Process the results
7. Close the connection

Basic Code Snippets are given below:

1. 1. Load the driver

try {

1. Class.forName("oracle.jdbc.driver.OracleDriver");
2. Class.forName("org.gjt.mm.mysql.Driver");

} catch { ClassNotFoundException cnfe) {

1. System.out.println("Error loading driver: " cnfe);

}

1. 2. Define the Connection URL

String host = "dbhost.yourcompany.com";

String dbName = "someName";

int port = 1234;

String oracleURL = "jdbc:oracle:thin:@" + host +

":" + port + ":" + dbName;

String mysqlURL = "jdbc:mysql://" + host

+

":" + port + "/" + dbName

1. 3. Establish the Connection

String username = "jay_debesee";

String password = "secret";

Connection connection =

1. DriverManager.getConnection(oracleURL,

username,

password);

1. 4. Create a Statement

Statement statement = connection.createStatement();

1. 5. Execute a Query

String query = "SELECT col1, col2, col3 FROM sometable";

ResultSet resultSet = statement.executeQuery(query);

–

To modify the database, use executeUpdate , supplying a string that uses UPDATE , INSERT , or DELETE

–

Use setQueryTimeout to specify a maximum delay to wait for results

1. 6. Process the Result

while(resultSet.next()) {

1. System.out.println(resultSet.getString(1) + " " +
2. resultSet.getString(2) + " " +
3. resultSet.getString(3));

}

–

First column has index 1, not 0

– ResultSet provides various getXxx methods that take a column index or name and returns the data

1. 7. Close the Connection
2. connection.close();

–As opening a connection is expensive, postpone this step if additional database operations are expected

Step by Step approach and Java Code in Detail:

In SAP PI Graphical Mapping we can access the database and manipulate the records of that using standard JDBC API. The following attached Java code is used in this case to retrieve the record based on a select statement and populating the same value in the target field.

UDF Code:

try

   { AbstractTrace trace = container.getTrace();

      String connectionURL ="jdbc:oracle:thin:@//msp06db4.pace.mdtx.com:1521/pfwgdw.pace.mdtx.com";

      Connection connection = null;

      Statement statement = null;

      ResultSet rs = null;

1. trace.addInfo("Connecting");

Class.forName("oracle.jdbc.OracleDriver").newInstance();

     connection = DriverManager.getConnection(connectionURL,"userId","password");

      trace.addInfo("Connected");

      statement = connection.createStatement();

   String Query="SELECT A2.COMPONENTSERIALNO AS BATTERY_SERIALNUMBER FROM FW.FWLOT F1, FW.ABCTBL A2 WHERE A2.FACILITY = F1.FACILITY AND A2.TOPLEVELLOTID = F1.LOTID AND SERIALNUMBER='"; 

   Query= Query +SRL_NO[0]+"'AND A2.LOCATION IN ('Battery','PowerSource') AND A2.DELETEDLOT='NO'"; 

  //String Query= "SELECT LOTID FROM FW.FWLOT F1 WHERE PRODUCTNAME='042193004'";

rs = statement.executeQuery(Query);

1. trace.addInfo("Query Executed");

while (rs.next()) {

   trace.addInfo(rs.getString(1));

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

  }  

  rs.close();

  statement.close();

  connection.close();

}

                   catch (SQLException e)

                         {               

result.addValue(e.getMessage());

                            }

                   catch(Exception e)

                          {

result.addValue(e.getMessage());

                           }

The above code takes one input and fetches the record from the database and populates the value to the target field.

Testing the Scenario:

Following are the two structures for sender and receiver in mapping-

                                       

The UDF is used in between the below mentioned field-

The UDF takes one input as Serial number which is the value of SERIAL_NO source field and using this value it is calling the database and populating the  target field BATSRL.

                                          

Output on execution in test tab-

                                            

Output checked from TOAD SQL Navigator-

                                              

Restriction:

Though using this approach is very simple and easy but still it is having some restrictions as below-

1. User Id and password for the database is hardcoded in the JDBC code which can also be passed to the UDF by maintaining a FixValueMap, which will contain the password for each environment.
2. package java.util.Date and java.sql.Date should be used carefully.