User Exit - DB Interface

Mirjana's picture

1.   Java Code for User Exit

 

package com.mirjana.userexit;

 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

 

import com.sterlingcommerce.woodstock.translator.util.JDBCService;

 public class DBInterface {

     

      Connection con = null;
      PreparedStatement stm = null;
      ResultSet res = null;
      String result=null;

            public Connection getConnection(String poolName)

      {

            try

            {    
                  Connection con=JDBCService.getConnection(poolName);
                  return con;
            }

            catch (Exception e)

            {
                  e.printStackTrace();
                  return null;
            }

      }

      public PreparedStatement getPreparedStatement(String SQLstatement, Connection con) {

            try{

                  stm = con.prepareStatement(SQLstatement);

                  return stm;

            } catch (Exception e)

            {
                  e.printStackTrace();
                  return null;
            }

      }

      public ResultSet getResultSet(PreparedStatement stm) {

            try{
                  res = stm.executeQuery();
                  return res;

            } catch (Exception e)

            {
                  e.printStackTrace();
                  return null;
            }

      }

      public int getRowCount(ResultSet res, String SQLstatement)

          {

              int i = 0;
              try {
                 
                  stm = con.prepareStatement(SQLstatement);
                  res = stm.executeQuery();
                  res.next();
                  i = res.getInt("CNT");
                  return i;
                   

              } catch (Exception ex){

                  ex.printStackTrace();
                  return i;
              }

       }

      public void executeNext(ResultSet res)

      {

           
        try {
           
            res.next();
           
        } catch (Exception ex){ 
            ex.printStackTrace();
        }

     }

public String getData(ResultSet res, String columnName)

      {
      try {                        
                //res.next();       
                result = res.getString(columnName);            
        } catch (Exception ex){ 
            ex.printStackTrace();
        }

        return result;

      }

      public void closePreparedStatement(PreparedStatement stm) {

            try{
                  stm.close();
            } catch (Exception ex) {

                  ex.printStackTrace();

            }

      }

      public void closeResultSet(ResultSet res) {

           

            try{
                  res.close();
                 
            } catch (Exception ex) {
                  ex.printStackTrace();
            }

        }

            public void closeConnection(Connection con)

      {
            try
            {
                  if (con != null && !con.isClosed())
                  {
                        con.close();
                  }
            }
            catch (Exception e)
            {
                  e.printStackTrace();
            }
      }

            public void freeConn(String poolName,Connection con)

            {
                  try
                  {
                        if (con != null && !con.isClosed())
                        {
                              //con.close();
                              JDBCService.freeConnection(poolName,con);
                        }
                  }
                  catch (Exception e)
                  {
                        e.printStackTrace();
                  }
      }
}

 2.   Pre-Session extended rule

object jdbc_object, connection;

string[100] poolName, table, column, sql_string;

integer i;

object stm, res;

 

sql_string = "select BUILD_NUMBER, PRODUCT_LABEL from si_version where PRODUCT_LABEL = 'SI'";

// Conn object for obtaining connection

jdbc_object = new("com.mirjana.userexit.DBInterface");

connection = jdbc_object.getConnection("mysqlPool");

i = 1;

 3.   Extended rule in field level

string[255] sql_result_pl, sql_result_bn, pl, bn;

sql_result_pl = "a";

sql_result_bn = "b";

 

pl = "PRODUCT_LABEL";

bn = "BUILD_NUMBER";

 

stm = jdbc_object.getPreparedStatement(sql_string,connection);

res = jdbc_object.getResultSet(stm);

jdbc_object.executeNext(res);

sql_result_pl = jdbc_object.getData(res, pl);

sql_result_bn = jdbc_object.getData(res, bn);

 

#product_label = sql_result_pl;

#build_number = sql_result_bn;

 4.   Post-Session extended rule

jdbc_connection.freeConnection(connection) ;