How to get data from database in Map Editor through Extended rules Sterling Integrator User Exit

This was a just a demonstration to show how to retrieve data from database through extended rules without using SQL map.



Presession rules:

object jdbc_object,jdbcConnection,connection;
string[100] poolName, table, column, sql_string;
integer i;
object stm, res;
sql_string = "select FIRST_NAME from STUDENT where STD_NO = '3'";
// Conn object for obtaining connection
//create 3rd party jar (package.class)
jdbc_object= new("mynewjavadatabase.MyNewJavaDataBase");
jdbcConnection= new("com.sterlingcommerce.woodstock.util.frame.jdbc.Conn");
connection = jdbcConnection.getConnection("db2Pool");
i = 1;

Extended rules:



string[255] sql_result_pl, pl;
sql_result_pl = "a";
pl = "FIRST_NAME";
stm = jdbc_object.getPreparedStatement(sql_string,connection);
res = jdbc_object.getResultSet(stm);
jdbc_object.executeNext(res);
sql_result_pl = jdbc_object.getData(res, pl);
#input = sql_result_pl;   //here we can get first name from database.

Java Code in MyNewJavaDataBase class:


package mynewjavadatabase;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.sterlingcommerce.woodstock.util.frame.jdbc.JDBCService;
public class MyNewJavaDataBase  {

   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();
                  }
      }
}
   


Required jars:

install_foundation.jar
platform_afc.jar


Please correct me if did any thing wrong.

Whenever we have a need to  custom java classes in Sterling Integrator. The first thing we need to do is creating the jars in the SI.

The example scenarios for this is need to get the data from database without using sql map i.e through extended rules. In this cases we have no other choice except using custom java classes.

To create jar files in Sterling Integrator first we need to stop the sterling Integrator server
through stopWindowsService.cmd

After that use  install3rdParty.cmd/sh to install jar.

install3rdParty.cmd MyDataBase 1_0 -j C:\Users\MyDataBase.jar

Run setupfiles.cmd

and deployer.cmd in command line so that it effects to all the files.

Then start the Server by using the command startWindowsService.cmd.

To verfiy wheather jar was installed properly or not.

You can check it by visiting the Operations->troubleshotter->class path.

You can locate the jar file.