Additional Blogs by Members
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
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=...!
0.1. *Target Structure *
 !https://weblogs.sdn.sap.com/weblogs/images/251835280/OutputDataTyp.jpg|height=98|alt=|width=418|src=...!
 


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=...!


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....!

    5 Comments