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