Wednesday 7 August 2013

OAF Populate View Object with a data from DB2 database

 

The problem is the following. I have a view object that displays item+lot information from EBS.

Part of the data is located on the remote DB2 server. The goal is somehow to join the data from DB2 with a data in local EBS database.

The idea is the following . We will create global temporary table that will be populated with a data from DB2 when the OAF page is loaded.

Here is the table

create global temporary table XX_OM_LOT_ALLOC_FROM_DB2

  FACILITY                                        VARCHAR2(1000),
  ITEM_ID                                         NUMBER,
  LOT_NUMBER                                VARCHAR2(1000),
  BOX_QUANTITY                              NUMBER,
  ONHAND_QUANTITY                      NUMBER,
  ALLOCATED_ONHAND_QUANTITY NUMBER,
  EXPIRY_DAYS                               NUMBER,
  UNITS_INNER_PACK                     NUMBER,
  NBR_OF_INNER_IN_OUTER           NUMBER
)
on commit delete rows
/

 

The View Object will be based on the following SQL.

select *

from  XX_OM_LOT_ALLOC_FROM_DB2 db2,

        ic_item_mst_b  items

where db2.item_id = items.item_id

 

So all we have to do is to populate the temporary table with a data

To connect connect to DB2 we will use open source jt400.jar file.

Bellow is the java class that connects to the DB2 , reads the data and populates the temporary table. Explanation about the code are found in the code comment

import java.sql.CallableStatement;
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
import java.sql.PreparedStatement; 
import java.sql.SQLException;
import java.util.Properties;
import com.ibm.as400.access.*;
import java.sql.*;

public class LotConnector
{

    /* All parameters to the fucntion are ment to be used
       to connect to DB2. Except "localConn", which is used to
       connect to local database (EBS database)
    */
   public static String getItemOnHandQuantity(String env,
                                               String item,
                                               String serverHost,
                                               java.lang.Integer  serverPort,
                                               String serverUser,
                                               String serverPwd,
                                               String serverDb,
                                               String serverLib,
                                               Connection localConn)
    {

       //insert statement to populate the temporary table
      final String insertSql = "insert into XX_OM_LOT_ALLOC_FROM_DB2 values (?,?,?,?,?,?,?,?,?)";
        String PkEnv = env;
        String Item_id = item;
        String server = serverHost;
        int port = serverPort.intValue();
        String username = serverUser;
        String password = serverPwd;
        String dbName = serverDb;
        String LibraryName = serverLib;
        Connection connection = null; 
        CallableStatement proc_stmt = null;
        ResultSet rs = null;

         boolean isResultSetExists = false;
       //do something only if connection details to DB2 exist
        try
            if(PkEnv!=null  && server!=null && port!=0 && username!=null && password!=null && dbName!=null && LibraryName!=null)
            {   
                // load the driver and set properties for connection
                // to db2
                DriverManager.registerDriver( new AS400JDBCDriver());
                Properties props = new Properties();
                props.setProperty("user",username);
                props.setProperty("password",password);
                props.setProperty("naming","system");
                props.setProperty("date_format","iso");
                props.setProperty("transaction_isolation","none");

                connection = DriverManager.getConnection("jdbc:as400://"+server+":"+port+"/"+dbName+"", props);
               //note the format of the call
                // in Oracle it is MY_PACKAGE.MY_FUNCTION
                // but for DB2 we specify MY_LIBRARY/MYFUNCTION
                proc_stmt = connection.prepareCall("{call "+LibraryName+"/TV38U0RP(?,?)}"); //IN Parameter
                proc_stmt.setString(1, PkEnv);
                proc_stmt.setString(2,Item_id);
                proc_stmt.execute();
                rs = proc_stmt.getResultSet();
                Connection localConnection = localConn;

               //insert to local table
                PreparedStatement ps = localConnection.prepareStatement(insertSql);
                while (rs.next())
                {
                   //indication that something found
                    isResultSetExists = true;
                   //record data from DB2
                    String facility =  rs.getString(1);
                    String ITEM_ID  = rs.getString(2);
                    String lot = rs.getString(3);
                    String InnerPackQuantity = rs.getString(4);
                    String OnHandQuantity = rs.getString(5);
                    String AllocatedOnHandQuantity  = rs.getString(6);
                    String ExpiryDays = rs.getString(7);
                    String UnitsInnerPack = rs.getString(8);
                    String NbrOfInnerInouter = rs.getString(9);

                    //local insert
                      ps.setString(1,facility);
                      ps.setInt(2,Integer.parseInt(ITEM_ID.trim()));

                      if (lot!=null && !lot.equals(""))
                         ps.setString(3,lot);
                      else
                         ps.setNull(3,Types.NULL);
                      if (InnerPackQuantity!=null && !InnerPackQuantity.equals(""))
                         ps.setString(4,InnerPackQuantity);
                      else
                         ps.setNull(4,Types.NULL);

                       if (OnHandQuantity!=null && !OnHandQuantity.equals(""))
                         ps.setString(5,OnHandQuantity);
                       else
                         ps.setNull(5,Types.NULL);

                       if (AllocatedOnHandQuantity!=null && !AllocatedOnHandQuantity.equals(""))
                         ps.setString(6,AllocatedOnHandQuantity);
                       else
                         ps.setNull(6,Types.NULL); 

                       if (ExpiryDays!=null && !ExpiryDays.equals(""))
                         ps.setString(7,ExpiryDays);
                       else
                         ps.setNull(7,Types.NULL);

                       if (UnitsInnerPack!=null && !UnitsInnerPack.equals(""))
                         ps.setString(8,UnitsInnerPack);
                       else
                         ps.setNull(8,Types.NULL); 

                       if (NbrOfInnerInouter!=null && !NbrOfInnerInouter.equals(""))
                         ps.setString(9,NbrOfInnerInouter);
                       else
                         ps.setNull(9,Types.NULL);
                   //use batch insert to improve the performance                        
                    ps.addBatch();
                } //end while

            if (isResultSetExists)
            {
              ps.executeBatch();
              ps.close();
            }
            }
            else
            {
                return ("Parameter missing.");
            }
        }
       // handle exceptions and connections
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            return ("SQLException: " + sqle.getMessage());
        }
        catch (Exception e) { 
            e.printStackTrace();
            return ("SQLException: " + e.getMessage());           
        }
        finally
        {
            if (proc_stmt != null) {
                try {
                    proc_stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    return ("SQLException: " + e.getMessage());
                }
            }
            if (rs!= null){
                try {
                    rs.close();
                }catch (Exception expre) {
                    expre.printStackTrace();
                    return ("Exception : " + expre.getMessage());
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException exp) {
                    exp.printStackTrace();
                    return ("SQLException: " + exp.getMessage());
                }
            }
        }
        return "OK";
    }
}

We will define in application module the following function

public class LotAllocationAMImpl extends OAApplicationModuleImpl
{

  public String pullDataFromDb2(String env,
                                               String itemId,
                                               String server,
                                               String port,
                                               String user,
                                               String pwd,
                                               String name,
                                               String library)
  {
    Connection conn = getOADBTransaction().getJdbcConnection();

   //call the class we defined above
    return LotConnector.getItemOnHandQuantity(env,
                                               itemId,
                                               server,
                                               new Integer(port),
                                               user,
                                               pwd,
                                               name,
                                               library,
                                               conn);
    }

}

All we need to do now is to call this code when the page is loaded , before VO is executed.

As every OAF developer knows the way to do it , is to call this function from processRequest functon in the page controller

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);

// for obvious reasons I don’t publish the connection details

    String db2Env = "dummy";
    String itemId = "dummy";
    String db2Server = "dummy";
    String db2Port = "dummy";
    String db2User = "dummy";
    String db2Pwd = "dummy";
    String db2Name = "dummy";
    String db2Lib = "dummy";

    Serializable[] db2Params = { db2Env, itemId,db2Server,db2Port,db2User,db2Pwd,db2Name,db2Lib};
    String result =  (String)pageContext.getRootApplicationModule().invokeMethod("pullDataFromDb2",db2Params);
   }

 

Enjoy…