Sample Stored Procedure and Groovy Script for Incremental Reconciliation

The following is a sample groovy script for performing incremental reconciliation.

The incremental reconciliation procedure is called as follows:

st = conn.prepareCall("{call EXECUTE_QUERY_INCREMENTAL(?,?,?)}");

The get user role procedure is called as follows:

roleStmt = conn.prepareCall("{call GET_USERROLE(?,?)}");

The get user group procedure is called as follows:

groupStmt = conn.prepareCall("{call GET_USERGROUP(?,?)}");

The procedure for EXECUTE_QUERY_INCREMENTAL is as follows:

create or replace PROCEDURE EXECUTE_QUERY_INCREMENTAL
( user_cursor OUT TYPES.cursorType, columnName IN VARCHAR2, columnValue IN VARCHAR2
) AS
BEGIN
if columnValue is NULL then
open user_cursor for 'SELECT USERID,FIRSTNAME,LASTNAME,EMAIL,DESCRIPTION,SALARY,JOININGDATE,STATUS, to_char(LASTUPDATED) FROM USERINFO';
else
open user_cursor for 'SELECT USERID,FIRSTNAME,LASTNAME,EMAIL,DESCRIPTION,SALARY,JOININGDATE,STATUS, to_char(LASTUPDATED) FROM USERINFO where '|| columnName ||' >  to_timestamp ('''||columnValue||''')';
end if;
END EXECUTE_QUERY_INCREMENTAL;

The procedure for GET_USERROLE is as follows:

create or replace PROCEDURE GET_USERROLE
( user_cursor OUT TYPES.cursorType, userin IN VARCHAR2
) AS
BEGIN
OPEN user_cursor FOR
SELECT ROLEID,FROMDATE,TODATE from USER_ROLE where USERID=userin;
END GET_USERROLE;

The procedure for GET_USERGROUP is as follows:

create or replace PROCEDURE GET_USERGROUP
( user_cursor OUT TYPES.cursorType, userin IN VARCHAR2
) AS
BEGIN
OPEN user_cursor FOR
SELECT GROUPID from USER_GROUP where USERID=userin;
END GET_USERGROUP;

import org.identityconnectors.framework.common.objects.*;
import java.lang.reflect.*;
import org.identityconnectors.common.security.GuardedString;
import java.text.*;
import java.lang.String;
rs = null;
st = null;
try {
System.out.println("[Sync] Performing Incremental Recon.");
System.out.println("[Sync] Sync Attribute::"+syncattribute);
System.out.println("[Sync] Sync token:: "+synctoken);
st = conn.prepareCall("{call EXECUTE_QUERY_INCREMENTAL(?,?,?)}");
st.setString(2, syncattribute);
st.setString(3, synctoken!=null? synctoken.getValue():null);
st.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
st.execute();
rs = st.getObject(1);
SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss z");
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
while (rs.next()) {
                cob = new ConnectorObjectBuilder();
                cob.setObjectClass(ObjectClass.ACCOUNT);
                Attribute fname= AttributeBuilder.build(new String("FIRSTNAME"),rs.getString(2));
                Attribute lname= AttributeBuilder.build(new String("LASTNAME"),rs.getString(3));
                Attribute uid= AttributeBuilder.build(new String("__UID__"),rs.getString(1));
                Attribute name= AttributeBuilder.build(new String("__NAME__"),rs.getString(1));
                Attribute email= AttributeBuilder.build(new String("EMAIL"),rs.getString(4));
                Attribute salary= AttributeBuilder.build(new String("SALARY"),rs.getBigDecimal(6));
                Attribute description= AttributeBuilder.build(new String("DESCRIPTION"),rs.getString(5));
                dbDate = rs.getDate(7);
                joinDateStr = null;
                if( null != dbDate)
                {
                        java.util.Date date= df.parse(dbDate.toString());
                        joinDateStr = targetFormat.format(date);
                }
                Attribute joindate= AttributeBuilder.build(new String("JOININGDATE"),joinDateStr);
                Attribute status= AttributeBuilder.build(new String("STATUS"),rs.getString(8));
                                cob.addAttribute(fname);
                cob.addAttribute(lname);
                cob.addAttribute(uid);
                cob.addAttribute(name);
                cob.addAttribute(email);
                cob.addAttribute(salary);
                cob.addAttribute(description);
                cob.addAttribute(joindate);
                cob.addAttribute(status);
                roleStmt = conn.prepareCall("{call GET_USERROLE(?,?)}");
                roleStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
        roleStmt.setString(2, rs.getString(1));
        roleStmt.execute();
                roleResultSet = roleStmt.getObject(1);
                java.util.List<EmbeddedObject> eoList = new ArrayList<EmbeddedObject>();
                while (roleResultSet.next()) {
                        Attribute roleId= AttributeBuilder.build(new String("ROLEID"),roleResultSet.getString(1));
                        dbDate = roleResultSet.getDate(2);
                        fromDateStr = null;
                        if( null != dbDate)
                        {
                                java.util.Date date= df.parse(dbDate.toString());
                                fromDateStr = targetFormat.format(date);
                        }
                        dbDate = roleResultSet.getDate(2);
                        toDateStr = null;
                        if( null != dbDate)
                        {
                                java.util.Date date= df.parse(dbDate.toString());
                                toDateStr = targetFormat.format(date);
                        }
        Attribute fromdate= AttributeBuilder.build(new String("FROMDATE"),fromDateStr);
                        Attribute todate= AttributeBuilder.build(new String("TODATE"),toDateStr);
                                        EmbeddedObjectBuilder roleEA = new EmbeddedObjectBuilder();
                        roleEA.addAttribute(roleId);
                        roleEA.addAttribute(fromdate);
                        roleEA.addAttribute(todate);
                        roleEA.setObjectClass(new ObjectClass("USER_ROLE"));
                        eoList.add(roleEA.build());
                }  
                roleResultSet.close();
                roleStmt.close();
                EmbeddedObject[] roleEm = eoList.toArray(new EmbeddedObject[eoList.size()]);
cob.addAttribute(AttributeBuilder.build("USER_ROLE", (Object[]) roleEm));
                                groupStmt = conn.prepareCall("{call GET_USERGROUP(?,?)}");
                groupStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
        groupStmt.setString(2, rs.getString(1));
        groupStmt.execute();
                groupResultSet = groupStmt.getObject(1);
                java.util.List<EmbeddedObject> geoList = new ArrayList<EmbeddedObject>();
                while (groupResultSet.next()) {
                        Attribute groupId= AttributeBuilder.build(new String("GROUPID"),groupResultSet.getString(1));
                        EmbeddedObjectBuilder groupEA = new EmbeddedObjectBuilder();
                        groupEA.addAttribute(groupId);
                        groupEA.setObjectClass(new ObjectClass("USER_GROUP"));
                        geoList.add(groupEA.build());
                }  
                groupResultSet.close();
                groupStmt.close();
                EmbeddedObject[] groupEm = geoList.toArray(new EmbeddedObject[geoList.size()]);
cob.addAttribute(AttributeBuilder.build("USER_GROUP", (Object[]) groupEm));
                Attribute timestamp= AttributeBuilder.build(new String("LASTUPDATED"),rs.getString(9));
                token = AttributeUtil.getSingleValue(timestamp);
                SyncToken syncToken  = new SyncToken(token);
                SyncDeltaBuilder bld = new SyncDeltaBuilder();
                bld.setObject(cob.build());
                bld.setToken(syncToken);
                bld.setDeltaType(SyncDeltaType.CREATE_OR_UPDATE);
                handler.handle(bld.build());
        } } finally {
        if( null != rs)
                rs.close();
        if( null != st)
                st.close();
}