Concatenate data and time to the Control number Custom control number Sterling Integrator Document ( Xpath Replace Service Timestamp utitlity Import Service)

Document on concatinate control number and current date and import the file to dashboard using business process

First we need to create control number in dash board Trading Partner->Control Number

We need to create Business process for concatinate and import the file.In that BP we are using different services like.

• Assign
• Time stamp utility
• Document xpath replace service
• Translation
• Import service

Business Process:

Use of Services:

Assign service: In Assign service by using DocToDOM function we are update primary document to process data.

Time stamp utility:Time stamp utility service is used to get the current date and time.

Assign service: In this service by using concat function we are concatinating the current time and control number.

Document xpath replace service: By using this service we are updating data fom process data to primary document.

Translation: This service is used to translate the map.Here map is used to add the header details to the primary document.

Import service: This service is used to import the primary document to dashboard.

Bpml code:

<process name="default">

<sequence>

<assign name="Assign" to="." from="DocToDOM(PrimaryDocument)"></assign>

<operation name="Timestamp Utility">

<participant name="TimestampUtilService"/>

<output message="TimestampUtilServiceTypeInputMessage">

<assign to="." from="*"></assign>

<assign to="action">current_time</assign>

<assign to="format">YYYYMMddHHMMss</assign>

</output>

<input message="inmsg">

<assign to="." from="*"></assign>

</input>

</operation>

<assign name="Assign" to="Append" from="concat(//VALUE/text(),//time)"></assign>

<operation name="Document XPath Replace Service">

<participant name="DocXPathReplace"/>

<output message="DocXPathReplaceInputMessage">

<assign to="." from="*"></assign>

<assign to="debug">true</assign>

<assign to="keepDocEncoding">false</assign>

<assign to="keepDocType">true</assign>

<assign to="replacementText" from="//Append/text()"></assign>

<assign to="replaceMultiple">false</assign>

<assign to="textNodeXPath">//VALUE/text()</assign>

</output>

<input message="inmsg">

<assign to="." from="*"></assign>

</input>

</operation>

<operation name="Translation">

<participant name="Translation"/>

<output message="TranslationTypeInputMessage">

<assign to="." from="*"></assign>

<assign to="map_name">XportAndImport</assign>

</output>

<input message="inmsg">

<assign to="." from="*"></assign>

</input>

</operation>

<operation name="Import Service">

<participant name="ImportService"/>

<output message="ImportTypeInputMessage">

<assign to="." from="*"></assign>

<assign to="KeepExistingControlNumbers">NO</assign>

</output>

<input message="inmsg">

<assign to="." from="*"></assign>

</input>

</operation>

</sequence>

</process>

Example:

Input:

<CONTROL_NUMBERS>

<CONTROL_NUMBER>

<CONTROL_NUMBER_ID>85298315f0c5f1ab5node1</CONTROL_NUMBER_ID>

<EXTERNAL_OBJECT_ID></EXTERNAL_OBJECT_ID>

<NAME>BP_Control_Number</NAME>

<SENDER_ID>14147844480</SENDER_ID>

<RECEIVER_ID>067429365</RECEIVER_ID>

<VALUE>13</VALUE>

</CONTROL_NUMBER>

</CONTROL_NUMBERS>

Output:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

<SI_RESOURCES xmlns='http://www.stercomm.com/SI/SI_IE_Resources' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>

<CONTROL_NUMBERS>
<CONTROL_NUMBER>
<CONTROL_NUMBER_ID>85298315f0c5f1ab5node1</CONTROL_NUMBER_ID>
<EXTERNAL_OBJECT_ID/><NAME>BP_Control_Number</NAME>
<SENDER_ID>14147844480</SENDER_ID>
<RECEIVER_ID>67429365</RECEIVER_ID>
<VALUE>1320171012071022</VALUE>

</CONTROL_NUMBER>

</CONTROL_NUMBERS>

</SI_RESOURCES>

Screenshot:

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.