1. Configuring the Database Application Tables Application
  2. Sample Stored Procedures and Groovy Scripts

A Sample Stored Procedures and Groovy Scripts

This appendix lists sample stored procedures and Groovy scripts for some of the provisioning operations. Depending on your requirement, you can either extend these stored procedures and groovy scripts or create new ones. Note that the sample stored procedures and groovy scripts listed in this appendix can be created only on an Oracle Database target system.

The appendix includes the following topics:

A.1 Sample Groovy Script for a Create Provisioning Operation

The following is a sample groovy script for performing a create provisioning operation.

Register the create script as follows:

import java.sql.PreparedStatement;
import org.identityconnectors.framework.common.objects.*;
import java.text.*;
 
// START HERE
System.out.println("[Create-Groovy] Attributes::"+attributes);
 
//Get all the attributes from script argument
String uid = attributes.get("__NAME__")!=null? attributes.get("__NAME__").getValue().get(0):null;
String firstName=attributes.get("FIRSTNAME")!=null? attributes.get("FIRSTNAME").getValue().get(0):null;
String lastName=attributes.get("LASTNAME")!=null? attributes.get("LASTNAME").getValue().get(0):null;
String email=attributes.get("EMAIL")!=null? attributes.get("EMAIL").getValue().get(0):null;
String description=attributes.get("DESCRIPTION")!=null? attributes.get("DESCRIPTION").getValue().get(0):null;
salary=attributes.get("SALARY")!=null? attributes.get("SALARY").getValue().get(0):null;
joindate = attributes.get("JOININGDATE")!=null? attributes.get("JOININGDATE").getValue().get(0):null;
enableValue = attributes.get("__ENABLE__")!=null? attributes.get("__ENABLE__").getValue().get(0):true;
 
PreparedStatement createStmt = null;
try {
	//Initialize the prepare statement to insert the data into database table
	createStmt =   conn.prepareStatement("INSERT INTO USERINFO(USERID,FIRSTNAME,LASTNAME,EMAIL,DESCRIPTION,SALARY,JOININGDATE,STATUS) VALUES(?,?,?,?,?,?,?,?)"); 
	//Set the input parameters
	createStmt.setString(1, uid);
	createStmt.setString(2, firstName);
	createStmt.setString(3, lastName);
	createStmt.setString(4, email);
	createStmt.setString(5, description);
	createStmt.setBigDecimal(6, salary);
	dateStr = null;
	//Convert the joindate into oracle date format
	if( joindate != null) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
		java.util.Date date= df.parse(joindate);
		DateFormat targetFormat = new SimpleDateFormat("dd-MMM-yy");
		dateStr = targetFormat.format(date);
	}
	createStmt.setString(7,dateStr);
		if(enableValue)
		createStmt.setString(8,"Enabled");
	else
		createStmt.setString(8,"Disabled");
	//Execute sql statement
	createStmt.executeUpdate();
} finally {
	//close the sql statements
	if (createStmt != null)
		createStmt.close();
}
System.out.println("[Create] Created User::"+uid);
//Return Uid from the script
return new Uid(uid);

A.2 Sample Groovy Script for an Update Provisioning Operation

The following is a sample groovy script for performing an update provisioning operation.

Register the update script as follows:

import org.identityconnectors.framework.common.objects.*;
import java.text.*;
import org.identityconnectors.framework.common.exceptions.*;
 
System.out.println("[Update-Groovy] Atrributes::"+ attributes);
 
/** During an Update operation,OIM  sends the UID attribute along with updated attributes. 
Get all the values of attributes **/
 
String id = attributes.get("__UID__")!=null? attributes.get("__UID__").getValue().get(0):null;
String firstName=attributes.get("FIRSTNAME")!=null? attributes.get("FIRSTNAME").getValue().get(0):null;
String lastName=attributes.get("LASTNAME")!=null? attributes.get("LASTNAME").getValue().get(0):null;
String email=attributes.get("EMAIL")!=null? attributes.get("EMAIL").getValue().get(0):null;
String description=attributes.get("DESCRIPTION")!=null? attributes.get("DESCRIPTION").getValue().get(0):null;
salary=attributes.get("SALARY")!=null? attributes.get("SALARY").getValue().get(0):null;
joindate = attributes.get("JOININGDATE")!=null? attributes.get("JOININGDATE").getValue().get(0):null;
status = attributes.get("STATUS")!=null? attributes.get("STATUS").getValue().get(0):null;
enableValue = attributes.get("__ENABLE__")!=null? attributes.get("__ENABLE__").getValue().get(0):true;
 
//Throw exception if uid is null
if(id==null) throw new ConnectorException("UID Cannot be Null");
stmt = null;
try {
//Create prepare statement to update the USERINFO table
		stmt = conn.prepareStatement("UPDATE USERINFO SET FIRSTNAME=COALESCE(?, FIRSTNAME),LASTNAME =COALESCE(?, LASTNAME), EMAIL= COALESCE(?, EMAIL),DESCRIPTION=COALESCE(?, DESCRIPTION),SALARY=COALESCE(?, SALARY),JOININGDATE=COALESCE(to_date(?,'dd-Mon-yy'), JOININGDATE),STATUS=COALESCE(?, STATUS) WHERE USERID =?");
		//Set sql input parameters
		stmt.setString(1, firstName);
		stmt.setString(2, lastName);
		stmt.setString(3, email);
		stmt.setString(4, description);
		stmt.setBigDecimal(5, salary);
		dateStr = null;
		//Convert the joindate into oracle date format
		if( joindate != null) {
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
			java.util.Date date= df.parse(joindate);
			DateFormat targetFormat = new SimpleDateFormat("dd-MMM-yy");
			dateStr = targetFormat.format(date); }
		stmt.setString(6,dateStr);
		if(enableValue)
			stmt.setString(7,"Enabled");
		else
			stmt.setString(7,"Disabled");
		stmt.setString(8, id);
		stmt.executeUpdate();
} finally {
	if (stmt != null)
		stmt.close();
};
System.out.println("[Update] Updated user::"+ id);
return new Uid(id);

A.3 Sample Groovy Script for a Delete Provisioning Operation

The following is a sample groovy script for performing a delete provisioning operation.

Register the delete script as follows:

import java.sql.PreparedStatement;
import org.identityconnectors.framework.common.objects.*;
 
//Get the UID from the input map 'attributes'
String uid = attributes.get("__UID__").getValue().get(0);
System.out.println("[Delete-Groovy] Deleting user:: "+ uid);
 
try {
	//Delete data from child tables and then, main table
	//Delete user roles
	st = conn.prepareStatement("DELETE FROM USER_ROLE WHERE USERID=?");
	st.setString(1, uid);
	st.executeUpdate();
	st.close();
	
	//Delete user groups
	st = conn.prepareStatement("DELETE FROM USER_GROUP WHERE USERID=?");
	st.setString(1, uid);
	st.executeUpdate();
	st.close();
	
	//Delete user account
	st = conn.prepareStatement("DELETE FROM USERINFO WHERE USERID=?");
	st.setString(1, uid);
	st.executeUpdate();
} finally {
	if (st != null)
		st.close(); };
System.out.println("Deleted user:: "+ uid);

A.4 Sample Groovy Script for an Add Child Data Provisioning Operation

The following is a sample groovy script for adding child data.

Register the add child data script as follows:

import org.identityconnectors.framework.common.objects.*;
import java.text.*;
 
System.out.println("[addMultiValuedAttributeScript-Groovy] Adding Child data::"+ attributes);
childst =null;
try {
	//Adding Group data
	childDataEOSet = null;
	
	/**The child attributes are returned as a set of embedded objects. Each	Embedded    object will provide a row of data in the child table.
	For example, if DBAT contains USER_GROUP as a child in OIM and contains two rows of groups data then, we will get a set of embedded objects with count 2 and each embedded	object represents a row in child data.
	This groovy script is based on a child table named USER_GROUP and containing USERID, GROUP_ID  as its columns.**/
	
	if(attributes.get("USER_GROUP")!=null)
	{
		childDataEOSet=attributes.get("USER_GROUP").getValue();
		childst = conn.prepareStatement("INSERT INTO USER_GROUP VALUES (?,?)");
		String id = attributes.get("__UID__").getValue().get(0);
		if(childDataEOSet !=null){
			//Iterate through child data and insert into table
			System.out.println("[addMultiValuedAttributeScript] Adding Group data.");
			for( iterator = childDataEOSet.iterator(); iterator.hasNext(); )
			{
				eo = iterator.next();
				attrsSet = eo.getAttributes();
				grpattr=AttributeUtil.find("GROUPID",attrsSet);
				if(grpattr!=null){
					groupid=grpattr.getValue().get(0);
					childst.setString(1, id);
					childst.setString(2, groupid);
					childst.executeUpdate();
					childst.clearParameters();
				} };
		} } } finally {
	if (childst != null)
	childst.close();
};

try {
	//Adding Role data
	childDataEOSet = null;
	if(attributes.get("USER_ROLE")!=null){
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.S");
		DateFormat targetFormat = new SimpleDateFormat("dd-MMM-yy");
		childDataEOSet=attributes.get("USER_ROLE").getValue();
		childst = conn.prepareStatement("INSERT INTO USER_ROLE VALUES (?,?,?,?)");
		String id = attributes.get("__UID__").getValue().get(0);
		if(childDataEOSet !=null){
			System.out.println("[addMultiValuedAttributeScript] Adding Role data.");
			for( iterator = childDataEOSet.iterator(); iterator.hasNext(); ) {
				eo = iterator.next();
				attrsSet = eo.getAttributes();
				roleattr=AttributeUtil.find("ROLEID",attrsSet);
				fromdateAttr=AttributeUtil.find("FROMDATE",attrsSet);
				todateAttr=AttributeUtil.find("TODATE",attrsSet);
								if(roleattr!=null){
					roleid=roleattr.getValue().get(0);
	    			childst.setString(1, id);
					childst.setString(2, roleid);
					fromdate = null;
					if(fromdateAttr!= null)
					{
						java.util.Date date= df.parse(fromdateAttr.getValue().get(0));
						fromdate = targetFormat.format(date);
					}
					childst.setString(3, fromdate);
					todate = null;
					if(todateAttr!= null)
					{
						java.util.Date date= df.parse(todateAttr.getValue().get(0));
						todate = targetFormat.format(date);
					}
					childst.setString(4, todate);
					childst.executeUpdate();
					childst.clearParameters();
				} };
	} } } finally {
	if (childst != null)
	childst.close();
};

A.5 Sample Stored Procedure and Groovy Script for a Delete Child Data Provisioning Operation

The following is a sample groovy script for deleting child data.

The delete child data procedure is called as follows:

delSt= conn.prepareCall("{call DELETE_USERGROUP(?,?)}");

delSt= conn.prepareCall("{call DELETE_USERROLE(?,?)}");

The procedure for DELETE_USERGROUP is as follows: 

create or replace PROCEDURE DELETE_USERGROUP
(  userin IN VARCHAR2, gId IN VARCHAR2
) AS
BEGIN
DELETE from USER_GROUP where USERID=userin and GROUPID=gId;
END DELETE_USERGROUP;

The procedure for DELETE_USERROLE is as follows: 

create or replace PROCEDURE DELETE_USERROLE
(  userin IN VARCHAR2, rId IN VARCHAR2
) AS
BEGIN
DELETE  from USER_ROLE where USERID=userin and ROLEID=rId;
END DELETE_USERROLE;

Register the delete child data script as follows:

import org.identityconnectors.framework.common.objects.*;
System.out.println("[removeMultiValuedAttributeScript] Removing Child data::"+ attributes);
 
try {
	childDataEOSet = null;
	delSt = null;
	//Get UID 
	String id = attributes.get("__UID__").getValue().get(0);
	if(attributes.get("USER_GROUP")!=null)
	{
		childDataEOSet=attributes.get("USER_GROUP").getValue();
		//Delete child data using stored procedure
		delSt= conn.prepareCall("{call DELETE_USERGROUP(?,?)}");
	    if(childDataEOSet !=null){
			System.out.println("[removeMultiValuedAttributeScript] Removing Group data.");
			//Iterate through child data and delete
			for( iterator = childDataEOSet.iterator(); iterator.hasNext(); )
			{
				eo = iterator.next();
				attrsSet = eo.getAttributes();
				grpattr=AttributeUtil.find("GROUPID",attrsSet);
				if(grpattr!=null){
					groupid=grpattr.getValue().get(0);
					delSt.setString(1, id);
					delSt.setString(2, groupid);
					delSt.executeUpdate();
					System.out.println("[removeMultiValuedAttributeScript] Deleted Group::"+ grpattr);
				} }; } }
} finally {
	if (delSt != null)
	delSt.close();
};
try {
	childDataEOSet = null;
	delSt = null;
	String id      = attributes.get("__UID__").getValue().get(0);
	if(attributes.get("USER_ROLE")!=null)
	{
		childDataEOSet=attributes.get("USER_ROLE").getValue();
		delSt= conn.prepareCall("{call DELETE_USERROLE(?,?)}");
	    if(childDataEOSet !=null){
			System.out.println("[removeMultiValuedAttributeScript] Removing Role data.");
			for( iterator = childDataEOSet.iterator(); iterator.hasNext(); )
			{
					eo = iterator.next();
				attrsSet = eo.getAttributes();
				roleattr=AttributeUtil.find("ROLEID",attrsSet);
				if(roleattr!=null){
					rolename=roleattr.getValue().get(0);
					delSt.setString(1, id);
					delSt.setString(2, rolename);
					delSt.executeUpdate();
					System.out.println("[removeMultiValuedAttributeScript] Deleted Role::"+ rolename);
				} 			}; 		} 	}
} finally {
	if (delSt != null)
		delSt.close();
};

A.6 Sample Stored Procedure and Groovy Script for Lookup Field Synchronization

The following is a sample groovy script for performing lookup field synchronization.

The Lookup field procedures are called as follows:

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

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

The procedure for GET_ROLES is as follows: 

create or replace PROCEDURE GET_ROLES
( user_cursor OUT TYPES.cursorType
) AS
BEGIN
OPEN user_cursor FOR
SELECT ROLENAME,ROLEID from ROLES;
END GET_ROLES;

The procedure for GET_GROUPS is as follows: 

create or replace PROCEDURE GET_GROUPS
( user_cursor OUT TYPES.cursorType
) AS
BEGIN
OPEN user_cursor FOR
SELECT GROUPNAME,GROUPID from GROUPS;
END GET_GROUPS;

Register the lookup field synchronization script as follows:

import org.identityconnectors.framework.common.objects.*;
rs = null;
st = null;
try {
	System.out.println("[Lookup] Lookup Recon timing::"+ timing);
	System.out.println("[Lookup] Attributes to Get::"+ ATTRS_TO_GET);
	// This script is common for all lookups. Read the timing ( input) and return the data accordingly
	// The format of timing is : executeQuery:<objectclass>
		String codekey =  ATTRS_TO_GET[0];
	String decodekey = ATTRS_TO_GET[1];
	if( timing.equals("executeQuery:Role"))
	{
		System.out.println("[Lookup] Getting Roles.");
		st = conn.prepareCall("{call GET_ROLES(?)}");
	}	
	else
	{
		System.out.println("[Lookup] Getting Groups.");
		st = conn.prepareCall("{call GET_GROUPS(?)}"); }
	st.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
	st.execute();
	rs = st.getObject(1);
	while (rs.next()) {
		cob = new ConnectorObjectBuilder();
		Attribute codeattr= AttributeBuilder.build(decodekey,rs.getString(2));
		Attribute decodeattr= AttributeBuilder.build(codekey,rs.getString(1));
		cob.addAttribute(codeattr);
	cob.addAttribute(decodeattr);
		cob.setUid(rs.getString(2));
		cob.setName(rs.getString(2));
		handler.handle(cob.build());		
	} } finally {
	if( null != rs)
		rs.close();
	if( null != st)
		st.close();
}

A.7 Sample Stored Procedure and Groovy Script for Full or Filter Reconciliation

The following is a sample groovy script for performing full or filter reconciliation.

The full reconciliation procedure is called as follows: 

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

The filtered reconciliation procedure is called as follows: 

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

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 is as follows: 

create or replace PROCEDURE EXECUTE_QUERY
( user_cursor OUT TYPES.cursorType
) AS
BEGIN
OPEN user_cursor FOR
SELECT USERINFO.USERID, USERINFO.FIRSTNAME , USERINFO.LASTNAME, USERINFO.EMAIL ,USERINFO.DESCRIPTION,USERINFO.SALARY,USERINFO.JOININGDATE ,USERINFO.STATUS FROM USERINFO;
END EXECUTE_QUERY;

The procedure for EXECUTE_QUERY_WITH_FILTER is as follows: 

create or replace PROCEDURE EXECUTE_QUERY_WITH_FILTER
( user_cursor OUT TYPES.cursorType, columnName IN VARCHAR2, columnValue IN VARCHAR2
) AS
BEGIN
open user_cursor for 'SELECT USERINFO.USERID, USERINFO.FIRSTNAME , USERINFO.LASTNAME, USERINFO.EMAIL ,USERINFO.DESCRIPTION,USERINFO.SALARY,USERINFO.JOININGDATE ,USERINFO.STATUS FROM USERINFO USERINFO where '|| columnName ||' like '''||columnValue||'''';
END EXECUTE_QUERY_WITH_FILTER;

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;

Register the full or filtered reconciliation script as follows:

import org.identityconnectors.framework.common.objects.*;
import java.lang.reflect.*;
import java.lang.String;
import org.identityconnectors.common.security.GuardedString;
import java.text.*;
 
rs = null;
st = null;
try {
	if( filterString != "")
	{
		System.out.println("[Execute Query] Performing Recon with Filter. Filter is::"+ filterString+" And Filer Params are::"+filterParams);
		String[] filter = filterParams.get(0).split(":");
	    st = conn.prepareCall("{call EXECUTE_QUERY_WITH_FILTER(?,?,?)}");
		st.setString(2, filter[0]);
		st.setString(3, filter[1]);
	}
	else
	{
		System.out.println("[Execute Query] Performing Full Recon.");
		st = conn.prepareCall("{call EXECUTE_QUERY(?)}");
}
	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));
        
		if(!handler.handle(cob.build())) return;
	} } finally {
	if( null != rs)
	rs.close();
	if( null != st)
	st.close();
}

A.8 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;

Register the incremental reconciliation script as follows:

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();
}

A.9 Tables Used for Sample Groovy and Configuration Scripts

The tables that are used by sample groovy scripts and configuration scrips are listed below:

  • Lookup tables for roles and groups:

    create table ROLES(
roleid varchar2(50), 
rolename varchar2(50)); 

create table GROUPS(
groupid varchar2(50),
groupname varchar2(50));

  • Tables for user accounts:

    • Parent Table:

      create table USERINFO(
UserId varchar2(50),
FirstName varchar2(50),
LastName varchar2(50),
email varchar2(50),
Description varchar2(50),
Salary NUMBER,
JoiningDate date,
status varchar2(50),
lastupdated timestamp,
PRIMARY KEY ( UserId ));

    • Child Table: 

      create table USER_ROLE(
userid varchar2(50),
roleid varchar2(50),
fromdate date,
todate date);

create table USER_GROUP(
userid varchar2(50),
groupid varchar2(50));

ALTER TABLE USER_GROUP ADD CONSTRAINT GROUP_PK PRIMARY KEY ("USERID", "GROUPID") ENABLE;

ALTER TABLE USER_ROLE ADD CONSTRAINT ROLE_PK PRIMARY KEY ("USERID", "ROLEID") ENABLE;