Skip to Content
I went through a JDBC lookup scenario and this idea clicked. In mapping lookup we access the database using DataBaseAccessor class. So I thought to try executing a stored procedure that will insert /updatein the Oracle Database using the lookup logic. This will save us from creating a whole synchronous JDBC scenario. However, DataBaseAccessor class does not support transactional behavior. This can only be
used to read data using Select query from a database table.
So I came up with this workaround to write a UDF in mapping itself that will execute the Stored procedure directly (Not using DataBaseAccessor). Using this method even synchronous scenario will require configuration of a simple file to file one, while UDF handles database communication through sql/pl-sql statements.


I am explaining this using an example.
 


Scenario:  File-JBDC-File.


Here source file contains multiple Employee records containing Name, EmployeeId (mandatory), Age and Salary fields. Target structure has only two fields: Insert_Count and Update_Count. Target file provides us the number of records inserted and updated.
0.1.
Source Structure !https://weblogs.sdn.sap.com/weblogs/images/251835280/InputDataTyp.jpg|height=133|alt=|width=429|src=https://weblogs.sdn.sap.com/weblogs/images/251835280/InputDataTyp.jpg!
0.1. *Target Structure *
 !https://weblogs.sdn.sap.com/weblogs/images/251835280/OutputDataTyp.jpg|height=98|alt=|width=418|src=https://weblogs.sdn.sap.com/weblogs/images/251835280/OutputDataTyp.jpg!
 


In the mapping between source and target structures, source inputs are mapped to a UDF that calls stored procedure Create_Emp. This stored procedure takes input as Name, Employee_Id, Age and Sal and writes the records in the tablename Employee and returns Insert_Count and Update_Count.  So, Inserting/updating the data in Oracle is handled by the UDF.


 


This is how stored procedure in Oracle looks like:


(name varchar2, employee_id NUMBER, age varchar2, sal in number,INSERT_COUNT out number, UPDATE_COUNT out number) AS LCOUNT NUMBER(10,0);     BEGIN   SELECT COUNT(*) INTO LCOUNT FROM EMPLOYEE WHERE EMPID=employee_id;   INSERT_COUNT := 0;   UPDATE_COUNT := 0;   IF LCOUNT = 0 THEN      INSERT INTO employee      VALUES (CREATE_EMP.NAME, CREATE_EMP.employee_id, CREATE_EMP.AGE, CREATE_EMP.SAL );      INSERT_COUNT := SQL%ROWCOUNT ;   END IF;      IF  LCOUNT > 0 THEN     UPDATE EMPLOYEE SET EMPLOYEE.NAME=CREATE_EMP.NAME, EMPLOYEE.AGE=CREATE_EMP.AGE,EMPLOYEE.SAL=CREATE_EMP.SAL     WHERE EMPLOYEE.EMPID=CREATE_EMP.employee_id;     UPDATE_COUNT := SQL%ROWCOUNT ;   END IF;  COMMIT;END;


Procedure name is Create_Emp. Update is done when Employee_ID exists in Database table else Insert is performed.


 


Steps for Creating UDF Mapping


0.1. *Import Jar File *
Now coming to the mapping part, .jar file containing drivers for
Oracle database has to be imported into Imported Archives. You can
check the appropriate .jar file from here:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
 
0.1.
Edit Java Sections!https://weblogs.sdn.sap.com/weblogs/images/251835280/JavaSections.jpg|height=300|alt=|width=450|src=https://weblogs.sdn.sap.com/weblogs/images/251835280/JavaSections.jpg!


0.1.
This is how the UDF code is:</li></ul>
<textarea cols=”80″ rows=”30″>import oracle.jdbc.driver.*;
import java.sql.*;


public void Insert_Update_UDF(String[] Name,String[] EmpId,String[] Age,String[] Salary,ResultList result,Container container)
{
          int x = 0, y = 0;
                // Global Variables Insert_Count and Update_Count. Used in other UDF’s.
          Insert_Count = 0;
          Update_Count = 0;
          try{          
               DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
               Connection con = DriverManager.getConnection(“<TyPe YoUr CoNnEcTiOn StRiNg HeRe>”);     
     
               String storProc= “{ call create_emp(?,?,?,?,?,?) }” ;
               CallableStatement pstmt = (OracleCallableStatement)con.prepareCall(storProc);               
               pstmt.registerOutParameter(5, Types.INTEGER);
               pstmt.registerOutParameter(6, Types.INTEGER);
               for(int i = 0; i<ipId.length ; i++)
               {
                    pstmt.setString(1,Name[i]);
                    pstmt.setInt(2,Integer.parseInt(EmpId[i]) );
                    pstmt.setString(3,Age[i]);
                    pstmt.setInt(4,Integer.parseInt(Salary[i]));
                    //Execute Stored Proc
                    pstmt.executeUpdate();
                    if (pstmt.getInt(5)==1)
                          Insert_Count++ ;
                    if (pstmt.getInt(6)==1)
                          Update_Count++;
               }     
          }catch (SQLException e) {
               result.addValue(e.getMessage());
               // TODO: handle exception
          }
          result.addValue(“Executed Successfully”);
}
</textarea>
<p> </p><p> <u>Note:Insert_Count and Update_Count are the global variables declared in Edit Java Sections. Insert_Update_UDF increments these variables based on the response of Stored Procedure. These are later used in other two udf’s which populate these values in the target structure.


  • Populate_Update_Count.


For both Code is like *return Insert_Count + “” ; *and return Update_Count + “” ; respectively.

 

    Mappings
     


    *Test Message Mapping *

    !https://weblogs.sdn.sap.com/weblogs/images/251835280/MappingOutput.JPG|alt=|src=https://weblogs.sdn.sap.com/weblogs/images/251835280/MappingOutput.JPG!

    To report this post you need to login first.

    5 Comments

    You must be Logged on to comment or reply to a post.

    1. Michal Krawczyk
      Hi,

      There is one but huge disadvantage of this
      scenario: how can you handle exceptions/retries/logging with that ?

      the reason to use adapter framework is to have all that and we should never do any inserts/updates in mappings…

      a) mappings should only be used for mappings
      b) lookups for looking up the data and not inserting as the name suggests

      I do not feel this is a good practice to promote such workarounds. Please add all drawbacks too
      so everyone will be aware – thanks

      Regards,
      Michal Krawczyk 

      (0) 
    2. Krishnakumar Ramamoorthy
      Arpil

      There some design flaws in your approach.

      First, no updates or inserts are to be done in mapping. Why? Because there is a chance that mapping can be executed more than once. For e.g., if the outbound queue fails for some reason (say connection issue with adapter engine and integration server), a retry of the outbound queue will trigger a new mapping call. This will create multiple inserts in your DB. Since mapping engine runs on java stack, integration server sometimes might loose the handshake and even though the mapping was executed, the integration server still thinks the mapping program resulted in an error and there is a inherent ‘Retry’ effort. Again, this will cause a duplicate entry in the DB.
      Now, if I am still convinced to use your design, I see you are passing connection string in the UDF. How will this work when you are transporting your mapping to QA and Prod. Where are you going to change the user ID and password for DB connection?
      Also, you say there is a performance benefit. I am not sure if you are using connection pooling to the DB in your code. If not, this will lead only to poor performance instead of improving it.

      I see your enthusiasm in sharing your ideas and totally appreciate it but unfortunately, this is not the best design and I wanted to document it so that junior developers and consultant don’t try to build something like this in their implementations.
      KK

      (0) 
    3. Arpil Gupta Post author
      I agree that there are some flaws with the above approach, which i did’nt realized earlier.

      Btw, for this example stored procedure will update records, if retry for any duplicated entries goes.

      Arpil

      (0) 

    Leave a Reply