Oracle Connection Pooling
Date: Sun, 23 May 2004 17:30:09 -0500
Message-ID: <c8r8lk$2vhc$1_at_news.wplus.net>
I am trying to implement Oracle connection pooling for the following code, which was written by someone else. Here is my main question -- this java file creates code that is executed every hour, but once per week, the oracle server is brought down for backup. At that time, I get an error that looks like I have the wrong credentials for the database. For every subsequent attempt to run the hourly process, I am getting a broken pipe error, untl the app server (Web Sphere) gets a restart.
My thinking is that, if I implement connection pooling, I can essentially
"trap" this connection that is getting orphaned and messing me up. Once it
is "trapped" in the pool, I can forcefully close it, or just purge the pool
of all connections, prior to getting my new connection. That way, I will
get the first error descripbed above, but the process will work as designed
the next time it runs, instead of getting the broken pipe error.
Please, if anyone can help, let me know if what I am talking about is possible, and how I should modify my code to make it happen.
Thanks.
Joe Mack
jwm68_at_sbcglobal.net
CODE
package com.proprietary.adapter;
import java.sql.*;
import org.w3c.dom.*;
import java.text.SimpleDateFormat;
import java.util.*;
import com.proprietary.util.*; import com.proprietary.object.*; import com.proprietary.security.authn.EncryptedData; import com.proprietary.adapter.*; import com.proprietary.msgcat.*;
import oracle.jdbc.driver.*;
import java.util.ResourceBundle;
public class HRActiveSyncAdapter extends ExampleTableResourceAdapter implements ActiveSync {
private static final String code_id = "$Id: HRActiveSyncAdapter.java,v 1.1 2003/09/25 12:33:47 tshah Exp $";
//////////////////////////////////////////////////////////////////////
//
// Prototype XML
//
//////////////////////////////////////////////////////////////////////
/*
protected static final String DEFAULT_DRIVER =
"oracle.jdbc.driver.OracleDriver";
protected static final String DEFAULT_PORT = "1521";
protected static final String DEFAULT_HOSTNAME = "server.test.com";
protected static final String DEFAULT_PREFIX =
"jdbc:oracle:thin:server.test.com:1521:IDMD";
*/
protected static final String DEFAULT_DRIVER =
"oracle.jdbc.driver.OracleDriver";
protected static final String DEFAULT_PORT = "1521";
protected static final String DEFAULT_HOSTNAME = null;
protected static final String DEFAULT_PREFIX = null;
public static final String RA_UPDATE_IF_DELETE = "Process deletes as
updates";
protected static final String DEFAULT_DATABASE = "IDMD"; protected static final String SQL_DATE_FORMAT = "yyyy-MM-dd hh:mm:ss"; //"yyyy-MM-dd";
protected static final String CREATE_HISTORY_NAME = "createHistory"; protected static final String TERM_HISTORY_NAME = "termHistory"; protected static final String RA_PREHIRE_CREATE = "Pre-hire"; protected static final String RA_HIRE_SEARCH_WINDOW = "Hire search window";
protected static final String RA_HIRE_HISTORY = "Hire history length";
protected static final String RA_TERM_SEARCH_WINDOW = "Term search window";
protected static final String RA_TERM_HISTORY = "Term history length";
protected static final String RA_MOD_HISTORY = "Modification search window";
protected static final String RA_TABLE_NAME = "Table name"; protected static final String DEFAULT_TABLE_NAME = "LH_HR";
protected Connection _connection = null;
protected ResourceBundle dbbundle = null;
ActiveSyncUtil _util = null;
protected String theDriver = null;
protected String thePort = null;
protected String theHost = null;
protected String thePrefix = null;
/**
* Simple struct to hold three strings, used for the schema description
* below.
*/
static class SchemaData {
public String _columnName,
_lighthouseMappedName,
_testData;
public SchemaData(String columnName,
String lighthouseMappedName,
String testData) {
_columnName = columnName;
_lighthouseMappedName = lighthouseMappedName;
_testData = testData;
} // SchemaData(Strg Strg Strg)
}
//////////////////////////////////////////////////////////////////////
//
// Constructors
//
//////////////////////////////////////////////////////////////////////
public HRActiveSyncAdapter()
{
/*
try {
dbbundle = ResourceBundle.getBundle("HRActiveSync",Locale.getDefault());
theDriver = dbbundle.getString("DB_DRIVER"); thePort = dbbundle.getString("DB_PORT"); theHost = dbbundle.getString("DB_HOSTNAME"); thePrefix = dbbundle.getString("DB_PREFIX");
DEFAULT_DRIVER = theDriver; DEFAULT_PORT = thePort; DEFAULT_HOSTNAME = theHost; DEFAULT_PREFIX = thePrefix;
} catch (Exception ex) {
System.out.println("HRActiveSyncAdapter error reading HRActiveSync.properties: " + ex.toString());
}
*/
}
//////////////////////////////////////////////////////////////////////
//
// Utilities
//
//////////////////////////////////////////////////////////////////////
/**
* make this into
* jdbc:oracle:thin:user/password_at_database
*/
public String oracleFormatUrl(String prefix, String host, String port,
String db) {
if (prefix.toUpperCase().indexOf("ORACLE") >= 0) {
String user = null;
Object pwd = null;
try {
user = (String) getRequiredResAttrVal(RA_USER);
pwd = (String) getRequiredResAttrVal(RA_USER);
} catch (proprietaryException ex) {
return null;
}
String password;
if (pwd instanceof EncryptedData)
password = ((EncryptedData)pwd).decryptToString();
else
password = pwd.toString();
return prefix + ":" + user + "/" + password + "_at_" + host + ":" +
port + ":"
+ db;
} else {
return super.formatUrl(prefix,host,port,db);
}
}
/**
* MySql version. end up with:
*
"jdbc:mysql://localhost:3306/mydatabase?user=username;password=password";
*/
public String formatUrl(String prefix, String host, String port,
String db) {
if (prefix.toUpperCase().indexOf("MYSQL") >= 0) {
String user = null;
Object pwd = null;
try {
user = (String) getRequiredResAttrVal(RA_USER);
pwd = (String) getRequiredResAttrVal(RA_USER);
} catch (proprietaryException ex) {
return null;
}
String password;
if (pwd instanceof EncryptedData)
password = ((EncryptedData)pwd).decryptToString();
else
password = pwd.toString();
return prefix + "://" + host + ":" + port + "/" + db; // +
"?user=" + user + ";password=" + password;
} else {
return oracleFormatUrl(prefix,host,port,db);
}
}
/**
* These are methods implemented in sources to get and set attributes.
* Since this is a source with a resource, just pass the calls through.
*/
public Object getAttributeValue(String name) throws proprietaryException {
if ("name".equalsIgnoreCase(name))
return getResource().getName();
else
return getResource().getResourceAttributeVal(name);
}
/**
* These are methods implemented in sources to get and set attributes.
* Since this is a source with a resource, just pass the calls through.
*/
public void setAttributeValue(String name, Object value) throws proprietaryException {
getResource().setResourceAttributeVal(name,value); }
/**
* Map from a resource attribute to a proprietary attribute. Return null
* if there's no mapping.
*/
protected WSAttribute reverseMapAttr(String attr, Object value) {
WSAttribute wsAttr = null;
AccountAttributeType attrType = getAttrTypeFromMapName(attr);
if (attrType != null) {
wsAttr = new WSAttribute(attrType.getName(), value,
attrType.getSyntax());
}
return wsAttr;
}
//////////////////////////////////////////////////////////////////////
//
// ActiveSync implementation
//
//////////////////////////////////////////////////////////////////////
/**
* Return a sql date daysOffset from today.
*
* _at_param daysOffset positive or negative offset from today
*/
protected static java.sql.Timestamp getDate(int daysOffset) {
Calendar rightNow = Calendar.getInstance();
rightNow.add(Calendar.DATE,daysOffset);
return new java.sql.Timestamp(rightNow.getTime().getTime());
}
protected static String getDateString(int daysOffset) {
java.sql.Timestamp date = getDate(daysOffset);
return Util.dateToString(date,SQL_DATE_FORMAT); }
protected int getIntRequiredResAttrVal(String name)
throws proprietaryException, java.lang.NumberFormatException {
Object o = getRequiredResAttrVal(_resource, name);
if (o != null) {
int i = Integer.parseInt((String)o);
return i;
}
return 0; // should not happen, the attribute was required, above
}
private void logIfFound(List viewIDs, String foundBy) {
if (viewIDs != null) {
StringBuffer sb = new StringBuffer();
sb.append("Lighthouse user(s) ");
sb.append(foundBy);
sb.append(": ");
_util.listToStringBuffer(viewIDs, sb);
sb.append('\n');
_util.logString(ActiveSync.TRACE_LEVEL_INFO,sb.toString());
}
}
/**
* Find the view ID for this user and set it in the update. If found,
* return true.
*/
private boolean mapUserToViewID(IAPI update,
String attributeName,
String attributeValue,
boolean attributeIsAccountId)
throws proprietaryException, IAPI.IAPIException {
List viewIDs = null;
if ((viewIDs == null || viewIDs.size() < 1) &&
attributeIsAccountId &&
attributeValue != null) {
viewIDs = update.find(Attribute.NAME.toString(),attributeValue,
2, false);
logIfFound(viewIDs, "Found by " + Attribute.NAME.toString());
if (viewIDs == null) {
viewIDs =
update.find(Attribute.RESOURCE_ACCOUNT_IDS.toString(),attributeValue, 2, true);
logIfFound(viewIDs, "Found by " +
Attribute.RESOURCE_ACCOUNT_IDS.toString());
}
}
if (viewIDs == null && attributeName != null && attributeValue !=
null) {
viewIDs = update.find(attributeName,attributeValue, 2, false);
logIfFound(viewIDs, "Found by " + attributeName);
}
if (viewIDs == null) {
_util.logString(IAPI.TRACE_LEVEL_INFO, "Received update for user
" + attributeName + ":" + attributeValue + " not in Lighthouse\n");
}
else if (viewIDs.size() > 1) {
throw new com.proprietary.util.InternalError("found multiple
matches for " + attributeName + ":" + attributeValue);
}
else {
update.setViewID((String)viewIDs.get(0));
return true;
}
return false;
}
/**
* Give a table row result, get the attributes associated with it
* and stuff them into the attributes of an IAPI object.
*
* This is what takes an update (filtered by the history) and builds
* an IAPI object. That object is then submitted to make the update
* to Lighthouse.
*
* _at_param row
*/
protected IAPI buildEvent(UpdateRow row)
throws proprietaryException, IAPI.IAPIException{
// attrib are the user attributes in a map, one entry for
// each schema map row.
Map attrib = row.getUserMap();
/** Options for provisioning. see UserViewConstants*/
Map options = new HashMap();
String command = IAPIUser.COMMAND_CREATE;
// The IAPI object to hold the update information
IAPIUser iapi = new IAPIUser(options,attrib,command,getResource());
// if we are tracing, ask the event to trace also
iapi.setTraceLevel(_util.getLogLevel());
iapi.setTraceFile(_util.getLogFileFullPath());
// get the mapping attribute
_util.logString(IAPI.TRACE_LEVEL_INFO,"Attribute Map: " +
attrib.toString());
_util.logString(IAPI.TRACE_LEVEL_INFO,"LAN_ID: " +
attrib.get("LAN_ID"));
_util.logString(IAPI.TRACE_LEVEL_INFO,"ACTIONCODE: " +
attrib.get("actionCode"));
String accountId = (String)attrib.get("accountId");
String actionCode = null;
String hrProcessed = null;
_util.logString(IAPI.TRACE_LEVEL_INFO,"accountId: " + accountId);
Object oactionCode = attrib.get("actionCode");
if(oactionCode != null) {
_util.logString(IAPI.TRACE_LEVEL_INFO,"oactionCode: " + oactionCode); actionCode = (String)oactionCode;
}
_util.logString(IAPI.TRACE_LEVEL_INFO,"actionCode: " + actionCode);
Object ohrProcessed = attrib.get("hrProcessed");
if(ohrProcessed != null) {
_util.logString(IAPI.TRACE_LEVEL_INFO,"ohrProcessed: " + ohrProcessed); hrProcessed = (String)ohrProcessed;
}
_util.logString(IAPI.TRACE_LEVEL_INFO,"hrProcessed: " + hrProcessed);
if ( accountId == null )
throw new proprietaryException("Missing required attribute " +
"accountId. Check schema map.");
// map to a lighthouse user. If not found, turn into a create
if (mapUserToViewID(iapi,"accountId",accountId,true)) {
iapi.setCommand(IAPIUser.COMMAND_UPDATE);
} else {
if(oactionCode == null) {
return null;
}
_util.logString(IAPI.TRACE_LEVEL_INFO,"Here"); iapi.setCommand(IAPIUser.COMMAND_CREATE); }
return iapi;
}
/**
* Give a table row result, get the attributes associated with it
* and stuff them into the attributes of an event.
*
* _at_param row
*/
protected IAPI buildTerminateEvent(UpdateRow row)
throws proprietaryException, IAPI.IAPIException {
// attrib are the user attributes in a map, one entry for
// each schema map row.
Map attrib = row.getUserMap();
/** Options for provisioning. see UserViewConstants*/
Map options = new HashMap();
// pull out the proprietary account ID and find that view ID
IAPIUser iapi = new
IAPIUser(options,attrib,IAPI.COMMAND_DELETE,getResource());
boolean treatDeleteAsUpdate = _util.checkOption(this, RA_UPDATE_IF_DELETE);
if (treatDeleteAsUpdate)
_util.logString(IAPI.TRACE_LEVEL_INFO,
"delete event changed to update because " +
RA_UPDATE_IF_DELETE + " true\n");
// if we are tracing, ask the event to trace also
iapi.setTraceLevel(_util.getLogLevel());
iapi.setTraceFile(_util.getLogFileFullPath());
// get the mapping attribute
String accountId = (String)attrib.get("accountId");
if ( accountId == null )
throw new proprietaryException("Missing required attribute " +
"accountId. Check schema map.");
// map to a lighthouse user. If not found, it was logged, ignore.
if (mapUserToViewID(iapi,"accountId",accountId,true)) {
if (treatDeleteAsUpdate)
iapi.setCommand(IAPIUser.COMMAND_UPDATE);
else
iapi.setCommand(IAPIUser.COMMAND_DELETE);
}
else
return null;
return iapi;
} // buildEvent(UpdateRow)
/**
* Take a list of arrays of UpdateRows, turn them into IAPI
* objects, and submit them
*/
protected void processCreateRequests(List list)
throws proprietaryException, IAPI.IAPIException {
String eventMsg = "";
int eventHasError = 0;
if (list != null) {
Iterator it = list.iterator();
while (it.hasNext() && !Thread.currentThread().isInterrupted())
{
eventMsg = "";
eventHasError = 0;
UpdateRow row = (UpdateRow)it.next();
if (row!=null)
{
logUpdate(IAPI.TRACE_LEVEL_INFO,row,null);
IAPI event = buildEvent(row);
proprietaryResult result = null;
if (event != null) {
try
{
result = event.submit();
if (result!=null)
{
List errorList = result.getErrors();
if (errorList!=null)
{
Iterator errorIterator = errorList.iterator();
while (errorIterator.hasNext())
{
String proprietaryMsg = (String) errorIterator.next();
if (proprietaryMsg!=null)
{
eventMsg = proprietaryMsg + eventMsg;
eventHasError = 1;
}
}
}
}
} catch (Exception evEx) {
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->proprietary
Event exception in processCreateRequests: " + evEx.toString());
eventHasError = 1;
}
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->BEGINNING OF proprietaryRESULT");
logUpdate(IAPI.TRACE_LEVEL_INFO,row,result); _util.logString(IAPI.TRACE_LEVEL_INFO, "-->END OF proprietaryRESULT");
if (eventHasError==0)
{
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->No Errors,
Calling UpdateLHProcessed");
updateLHProcessed(row);
_util.logString(IAPI.TRACE_LEVEL_INFO, "Calling UpdateSOAFProcessed");
updateSOAFProcessed(row);
} else {
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->Error
processing event, not calling UpdateLHProcessed");
updateSOAFProcessed(row);
_util.logString(IAPI.TRACE_LEVEL_INFO, "Calling UpdateSOAFProcessed");
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->Errors
found, dumping error list");
List errorList = result.getErrorMessages();
Iterator errorIterator = errorList.iterator();
while (errorIterator.hasNext())
{
Message proprietaryMsg = (Message) errorIterator.next();
//SDM Append the message strings
eventMsg = proprietaryMsg.getMessage() + eventMsg;
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->proprietary Event msg in
processCreateRequests: " + (String)proprietaryMsg.getMessage());
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->proprietary Event msg in
processCreateRequests: " + proprietaryMsg.toXml() );
eventHasError = 1;
}
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->End dumping
error list");
} // eventHasError == 0
} // event != null
} // row != null
} //while
} //if
}
protected void updateLHProcessed(UpdateRow row) {
PreparedStatement s = null;
try {
Map m = row.getAuditMap();
Object o = m.get("LAN_ID");
//SDM 2-16, addition for HR_SEQ
Object seqObject = m.get("HR_SEQ");
String seqString = null;
String lanid = null;
if (seqObject!=null)
seqString = seqObject.toString();
if (o != null)
lanid = o.toString().toUpperCase();
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->HR SEQ in processCreateRequests: " + seqString);
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->Lan ID in processCreateRequests: " + lanid);
if (lanid != null && seqString!= null) {
StringBuffer sb = new StringBuffer();
sb.append("update LH_HR set LH_PROCESSED_YN='Y' where
upper(LAN_ID)='");
sb.append(lanid);
sb.append("' and HR_SEQ = ");
sb.append(seqString);
String dml = sb.toString();
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->Update SQL in
processCreateRequests: " + dml);
s = _connection.prepareStatement(dml);
s.execute();
/* SDM - 3/20 close statement before we prepare the next one */
try {
s.close();
} catch (Exception closeEx) {
}
/* SDM - end of 3/20 mod to close 1st statement before creating second,
finally block closes out 2nd statement */
sb = new StringBuffer();
sb.append("update LH_SOAF set LH_PROCESSED_YN='R' where upper(LAN_ID)='");
sb.append(lanid);
sb.append("' and LH_PROCESSED_YN='N'");
dml = sb.toString();
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->Update SQL in
processCreateRequests: " + dml);
s = _connection.prepareStatement(dml);
s.execute();
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->After update SQL in processCreateRequests");
}
//o != null
} catch(Exception e){
_util.logString(IAPI.TRACE_LEVEL_INFO, "exception setting LH_HR.LH_PROCESSED_YN to R in processCreateRequests");
} finally {
try {
s.close();
} catch (Exception closeEx) {
}
}
}
protected void updateSOAFProcessed(UpdateRow row) {
PreparedStatement s = null;
try {
Map m = row.getAuditMap();
Object o = m.get("LAN_ID");
String lanid = null;
if (o != null)
lanid = o.toString().toUpperCase();
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->Lan ID in updateSOAFProcessed: " + lanid);
if (lanid != null) {
StringBuffer sb = new StringBuffer();
sb.append("update LH_SOAF set LH_PROCESSED_YN='R' where upper(LAN_ID)='");
sb.append(lanid);
sb.append("' and LH_PROCESSED_YN='N'");
String dml = sb.toString();
s = _connection.prepareStatement(dml);
s.execute();
}
//o != null
} catch(Exception e){
_util.logString(IAPI.TRACE_LEVEL_INFO, "exception setting LH_HR.LH_PROCESSED_YN to R in updateSOAFProcessed");
} finally {
try {
s.close();
} catch (Exception closeEx) {
}
}
}
/**
* Take a list of arrays of UpdateRows, turn them into IAPI
* objects, and submit them
*/
protected void processTermRequests(List list)
throws proprietaryException, IAPI.IAPIException {
if (list != null) {
Iterator it = list.iterator();
while (it.hasNext() && !Thread.currentThread().isInterrupted())
{
UpdateRow row = (UpdateRow)it.next();
logUpdate(IAPI.TRACE_LEVEL_INFO,row,null);
IAPI event = buildTerminateEvent(row);
if (event != null) {
proprietaryResult result = event.submit(); logUpdate(IAPI.TRACE_LEVEL_INFO,row,result);
String eventMsg = "";
int eventHasError = 0;
if (result!=null)
{
List errorList = result.getErrorMessages();
Iterator errorIterator = errorList.iterator();
while (errorIterator.hasNext())
{
Message proprietaryMsg = (Message) errorIterator.next();
//SDM Append the message strings
eventMsg = proprietaryMsg.getMessage() + eventMsg;
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->proprietary Event msg in
processTermRequests: " + proprietaryMsg.getMessage());
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->proprietary Event msg in
processTermRequests: " + proprietaryMsg.toXml() );
eventHasError = 1;
}
}
if (eventHasError==0)
{
try
{
updateLHProcessed(row);
} catch(Exception e){
_util.logString(IAPI.TRACE_LEVEL_INFO, "exception setting
LH_HR.LH_PROCESSED_YN to R in processTermRequests");
}
} //eventHasErr == 0
} //event != null
} //while
} // list != null
}
/**
* From a result set and a list of column names,
* make a Map. Only map names that are in the schema map, they are named
* from the "left hand" side of the schema map
*
* _at_param result - the result set from JDBC
* _at_param names - the list of names
*
* _at_returns the Map, with the attributes added.
*/
protected UpdateRow rowToUpdateRow(ResultSet result, List names)
throws java.sql.SQLException {
Map userMap = new HashMap(); // schema mapped data
Map auditMap = new HashMap(); // raw columns
for (int i=0; i < names.size(); i++) {
SchemaData row = (SchemaData)names.get(i);
Object rowData = result.getObject(i+1);
if (rowData != null) {
WSAttribute attr =
reverseMapAttr(row._columnName,rowData.toString().trim());
// if this is a mapped lighthouse attribute, stuff it as a
string
if (attr != null)
userMap.put((String)attr.getName(),attr.getValueAsString());
}
// always stuff the audit data, even for null columns (like
TERMINATION_DATE)
auditMap.put(row._columnName,rowData);
} // for each column
return new UpdateRow(auditMap,userMap);
}
/**
* return true if a>b. This is used to figure out if we have already
* processed a row, so it can fudge it if needs to.
*
* We expect LAST_MODIFIED_DATE to be a native date object but do the
* conversion if we have to. they should be java.sql.Timestamp(s).
*/
protected boolean isAfter(Map a, Map b) {
if (a == null || b == null) return true;
Object oa = a.get("LAST_MODIFIED_DATE");
Object ob = b.get("LAST_MODIFIED_DATE");
java.util.Date da = null, db = null;
try {
if (oa instanceof java.util.Date)
da = (java.util.Date)oa;
else
da = new
java.util.Date(Util.stringToDate(oa.toString(),SQL_DATE_FORMAT).getTime());
if (ob instanceof java.util.Date)
db = (java.util.Date)ob;
else
db = new
java.util.Date(Util.stringToDate(ob.toString(),SQL_DATE_FORMAT).getTime());
} catch (java.text.ParseException ex) {
_util.logString(IAPI.TRACE_LEVEL_ERROR,"error parsing dates\n");
_util.logString(IAPI.TRACE_LEVEL_ERROR,Util.stackToString(ex),false);
}
return (da != null && db != null) ? da.after(db) : true;
}
/**
* return true if a>b. This is used to figure out if we have already
* processed a row, so it can fudge it if needs to.
*
* We the strings to be native date objects toString()'ed. They should
* be java.sql.Timestamp(s).
*/
protected boolean isAfter(String a, String b) {
if (a == null || b == null) return true;
java.util.Date da = null, db = null;
try {
da = new
java.util.Date(Util.stringToDate(a,SQL_DATE_FORMAT).getTime());
db = new
java.util.Date(Util.stringToDate(b,SQL_DATE_FORMAT).getTime());
} catch (java.text.ParseException ex) {
_util.logString(IAPI.TRACE_LEVEL_ERROR,"error parsing dates\n");
_util.logString(IAPI.TRACE_LEVEL_ERROR,Util.stackToString(ex),false);
}
return (da != null && db != null) ? da.after(db) : true;
}
/**
* Because we get dates in different formats out of the different
* sql drivers, handle a data, sql date, timestamp, or string.
*/
protected String dateToString(Object date) {
if (date == null) return null;
if (date instanceof java.util.Date) {
//MWK for debug
_util.logString(IAPI.TRACE_LEVEL_INFO, "preprocessed date: " +
date.toString());
return Util.dateToString((java.util.Date)date,SQL_DATE_FORMAT);
}
else {
return date.toString();
}
}
// We are going to share our connection with the resource so that // we can use it to fetch user records. protected void connect() throws proprietaryException {
_connection = null;
_connection = getConnection();
}
protected void disconnect() {
if (_connection != null) {
try {
_connection.close();
} catch (Throwable th) {
}
_connection = null;
}
}
protected void addToHistoryMap(Map rowMap, Map map, String dateKey) {
if (rowMap != null) {
Object o = rowMap.get("BADGE");
Object date = rowMap.get(dateKey);
if (o != null && date != null) {
// This may replace an existing item but is
// generally a new entry
map.put(o.toString(),dateToString(date));
}
}
}
/**
* Given an existing map (or null), add name, date entries to
* the map. Then trim off any entries older than the date.
*/
protected Map addToHistoryMap(List updateRows, Map map, String dateKey, int historyLength) {
if (map == null) map = new HashMap();
// add new rows
if (updateRows != null) {
Iterator it = updateRows.iterator();
while (it.hasNext()) {
UpdateRow row = (UpdateRow)it.next();
Map rowMap = row.getAuditMap();
addToHistoryMap(rowMap,map,dateKey);
}
}
// trim old ones off
Iterator it = map.entrySet().iterator();
String oldestDate = getDateString(-1*historyLength);
while (it.hasNext()) {
Map.Entry me = (Map.Entry)it.next();
Object o = me.getValue();
if (o != null) {
String date = o.toString();
if (isAfter(oldestDate,date)) it.remove();
} else
it.remove();
}
return map;
}
public int poll() {
int numProcessed = 0;
try {
connect();
// use this to get at our persistent data (the last row that we
successfully processed)
IAPI prox = new IAPIUser(null,null,null,getResource());
UpdateRow ur = new UpdateRow();
Map history = (Map)prox.getProperty(CREATE_HISTORY_NAME);
if (history != null) {
ur.setAuditMap(ActiveSyncUtil.reconstituteEmptyStrings(history));
} else {
ur = null;
}
if (Thread.currentThread().isInterrupted()) {
return numProcessed;
}
List requests = null;
//SDM 5-5 most likely we are here because of a database connectivity issue
//try to reconnect and perform the getAgain
try {
requests = getCreateRequests();
} catch (Exception getEx) {
_util.logString(IAPI.TRACE_LEVEL_ERROR, "exception in getCreateRequests - attempting reconnect: " + getEx.toString());
try {
disconnect();
connect();
requests = getCreateRequests();
} catch (Exception reconnectEx) {
_util.logString(IAPI.TRACE_LEVEL_ERROR, "exception attempting to reconnect, throwing exception: " + reconnectEx.toString());
throw reconnectEx;
}
}
if (requests != null && requests.size() > 0 && !Thread.currentThread().isInterrupted()) {
numProcessed = requests.size();
_util.logString(IAPI.TRACE_LEVEL_ERROR, "before
processCreateRequests, numprocessed: " + numProcessed);
processCreateRequests(requests);
}
} catch (Throwable th) {
_util.logString(IAPI.TRACE_LEVEL_ERROR, "Error processing updates ");
_util.logString(IAPI.TRACE_LEVEL_ERROR, Util.stackToString(th));
} finally {
disconnect();
}
return numProcessed;
}
/**
* Log whatever we want to from an update.
*/
protected void logUpdate(int level, UpdateRow update, proprietaryResult result) {
if (update == null || _util.getLogLevel() < level) return;
Map audit = (update!= null) ? update.getAuditMap() : null;
Map user = (update!= null) ? update.getUserMap() : null;
List msgs = (result != null) ? result.getMessages() : null;
List errs = (result != null) ? result.getErrors() : null;
StringBuffer sb = new StringBuffer();
if (result == null) {
sb.append("Starting update processing for resource ");
sb.append(getResource().getName());
sb.append('\n');
_util.logString(level,sb.toString());
sb.setLength(0);
}
_util.mapToStringBuffer(audit,sb);
_util.mapToStringBuffer(user,sb);
_util.listToStringBuffer(errs,sb);
_util.listToStringBuffer(msgs,sb);
try {
_util.logString(level, sb.toString(), false);
} catch (Throwable th) {
System.out.println("PeopleSoft Resource adapter. Error writing
log: " + th.toString());
}
if (result != null) {
sb.setLength(0);
sb.append("End update processing for resource ");
sb.append(getResource().getName());
sb.append('\n');
_util.logString(level, sb.toString());
}
}
public void callCompleted(IAPI call) {
throw new java.lang.UnsupportedOperationException("Method callCompleted() not yet implemented.");
}
/**
* One row of data from the audit log on PeopleSoft. Really just 2 maps
* of attributes, one from the audit data and the other filled in by the
* getUser data
*/
class UpdateRow {
Map _auditMap,
_userMap;
public UpdateRow() {
}
public UpdateRow(Map auditMap, Map userMap) {
_auditMap = auditMap;
_userMap = userMap;
}
public Map getAuditMap() { return _auditMap; }
public Map getUserMap() { return _userMap; }
public void setAuditMap(Map auditMap) { _auditMap = auditMap; }
public void setUserMap(Map userMap) { _userMap = userMap; }
} // class UpdateRow
/**
* Check to see if an account can be created.
* We ping the server and see if the user already exists.
*/
public proprietaryResult checkCreateAccount(WSUser user) throws proprietaryException {
proprietaryResult result = new proprietaryResult();
Connection con = getConnection();
closeConnection(con);
return result;
}
/**
* From here down is the database specific information
*/
private static final String COLUMN_TERM_DATE = "TERM_DATE";
private static final String COLUMN_HIRE_DATE = "LAST_HIRE_DATE";
private static final String COLUMN_HR_TYPE = "HR_TYPE";
private static final String COLUMN_MODIFIED_DATE = "LAST_MODIFIED_DATE";
//////////////////////////////////////////////////////////////////////
//
// Prototype XML
//
//////////////////////////////////////////////////////////////////////
/**
* These are the columns in the database. The first entry is the column
name.
*
* The second is the attribute name that we want to map it to (or null
for
* no attribute). This map name is used for the default schema in the
* prototype xml for the adapter.
*
* The third entry is the test data. Things starting with TST will have
an
* ordinal appended in the test data generation. Things ending with
_DATE
* or _TIMESTAMP will get replaced with tomorrow's date or the timestamp
* at execution time.
*
* This table is used to generate the prototype XML, the select
statement,
* and to parse the result set coming back from select.
*/
private static final SchemaData[] _cols = {
new SchemaData("HR_SEQ", "sequenceNum", "TST_SEQ_NUM"),
new SchemaData("LAN_ID", "accountId", "TST"),
new SchemaData("ACTION_CD", "actionCode", "TST_ACTION_CODE"),
new SchemaData("HR_PROCESSED_YN", "hrProcessed",
"TST_HR_PROCESSED"),
new SchemaData("HR_LOAD_DATE", "hrLoadDate",
"TST_HR_LOAD_DATE"),
new SchemaData("FIRSTNAME", "firstname", "TST_FIRST_NAME"),
new SchemaData("MIDDLEINIT", "middlename", "TST_MIDDLE_NAME"),
new SchemaData("LASTNAME", "lastname", "TST_LAST_NAME"),
new SchemaData("PREFERREDNAME", "preferredName",
"TST_NICK_FIRST"),
new SchemaData("EMPLOYEEID", "employeeId", "TST_EMPLOYEE_ID"),
new SchemaData("DIVISIONCODE", "division", "TST_DIVISION_ID"),
new SchemaData("COSTCENTER", "costCenter", "TST_COST_CENTER"),
new SchemaData("WORKPHONE", "workPhone", "TST_WORK_PHONE"),
new SchemaData("JOBCODE", "jobCode", "TST_JOB_CODE"),
new SchemaData("ZIP", "zipCode", "TST_ZIP_CODE"),
new SchemaData("EMPLOYEETYPE", "employeeType",
"TST_EMPLOYEE_TYPE"),
new SchemaData("COMPANY", "company", "TST_COMPANY"),
new SchemaData("SUPERVISORNAME", "supervisorName",
"TST_SUPERVISOR_NAME"),
new SchemaData("SUPERVISOREMAIL", "supervisorEmail",
"TST_SUPERVISOR_EMAIL"),
new SchemaData("BUSINESS_UNIT", "businessUnit",
"TST_BUSINESS_UNIT"),
new SchemaData("SOAF_LOAD_DATE", "soafLoadDate",
"TST_SOAF_LOAD_DATE"),
new SchemaData("LAST_MOD_DATE", "lastModDate",
"TST_LAST_MOD_DATE"),
new SchemaData("SOAF_PROCESSED_YN", "soafProcessed",
"TST_SOAF_PROCESSED"),
new SchemaData("AD_ACCESS_YN", "adAccess", "TST_AD_ACCESS"),
new SchemaData("AD_GRANTED_YN", "adGranted", "TST_AD_GRANTED"),
new SchemaData("AD_COMMENT", "adComment", "TST_AD_COMMENT"),
new SchemaData("AD_SDID", "adSDID", "TST_AD_SDID"),
new SchemaData("AD_HELPDESK", "adHelpdesk", "TST_AD_HELPDESK"),
new SchemaData("EX_ACCESS_YN", "exAccess", "TST_EX_ACCESS"),
new SchemaData("EX_GRANTED_YN", "exGranted", "TST_EX_GRANTED"),
new SchemaData("EX_COMMENT", "exComment", "TST_EX_COMMENT"),
new SchemaData("EX_SDID", "exSDID", "TST_EX_SDID"),
new SchemaData("EX_HELPDESK", "exHelpdesk", "TST_EX_HELPDESK"),
new SchemaData("RACF_ACCESS_YN", "racfAccess",
"TST_RACF_ACCESS"),
new SchemaData("RACF_GRANTED_YN", "racfGranted", "TST_RACF_GRANTED"),
new SchemaData("RACF_COMMENT", "racfComment", "TST_RACF_COMMENT"),
new SchemaData("RACF_SDID", "racfSDID", "TST_RACF_SDID"),
new SchemaData("RACF_HELPDESK", "racfHelpdesk",
"TST_RACF_HELPDESK"),
new SchemaData("AS400_ACCESS_YN", "as400Access",
"TST_AS400_ACCESS"),
new SchemaData("AS400_GRANTED_YN", "as400Granted", "TST_AS400_GRANTED"),
new SchemaData("AS400_COMMENT", "as400Comment", "TST_AS400_COMMENT"),
new SchemaData("AS400_SDID", "as400SDID", "TST_AS400_SDID"),
new SchemaData("AS400_HELPDESK", "as400Helpdesk",
"TST_AS400_HELPDESK"),
new SchemaData("FORM_NO", "formNumber", "TST_FORM_NUMBER")
};
static final List _colsList = Arrays.asList(_cols); static final String basePrototypeXml = "<Resource name='HRDatabaseActiveSync' " +
" class='com.proprietary.adapter.HRActiveSyncAdapter' " +
" typeString='HR Database ActiveSync' typeDisplayString='"+ "Active
Sync Adapter" +"' " +
" " + Attribute.SYNC_SOURCE + "='true' >\n"+
" <ResourceAttributes>\n"+
" <ResourceAttribute name='"+RA_HOST+"' displayName='"+Messages.RESATTR_HOST+"' type='string' multi='false' " +
" value='" + DEFAULT_HOSTNAME + "'\n"+
" description='" + Messages.RESATTR_HELP_103 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_PORT+"'
displayName='"+Messages.RESATTR_PORT+"' type='string' multi='false'\n"+
" description='" + Messages.RESATTR_HELP_159 + "'\n"+
" value='" + DEFAULT_PORT + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_DATABASE+"' displayName='"+Messages.RESATTR_DATABASE+"' type='string' multi='false'\n"+
" description='" + Messages.RESATTR_HELP_80 + "'\n"+
" value='" + DEFAULT_DATABASE + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_TABLE_NAME+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_80 + "'\n"+
" value='" + DEFAULT_TABLE_NAME + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_USER+"' displayName='"+Messages.RESATTR_USER+"' type='string' multi='false'\n"+
" description='" + Messages.RESATTR_HELP_200 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_PASSWORD+"'
displayName='"+Messages.RESATTR_PASSWORD+"' type='encrypted'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_154 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_DRIVER+"'
displayName='"+Messages.RESATTR_DRIVER+"' type='string' multi='false'\n"+
" description='" + Messages.RESATTR_HELP_121 + "'\n"+
" value='"+DEFAULT_DRIVER+"'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_URL_PREFIX+"' displayName='"+Messages.RESATTR_URL_PREFIX+"' type='string' multi='false'\n"+
" description='" + Messages.RESATTR_HELP_123 + "'\n"+
" value='"+DEFAULT_PREFIX+"'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_PREHIRE_CREATE+"'
displayName='"+Messages.RESATTR_PREHIRE_CREATE+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_7 + "'\n"+
" value='2'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_HIRE_SEARCH_WINDOW+"'
displayName='"+Messages.RESATTR_HIRE_SEARCH_WINDOW+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_3 + "'\n"+
" value='15'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_HIRE_HISTORY+"'
displayName='"+Messages.RESATTR_HIRE_HISTORY+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_2 + "'\n"+
" value='30'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_TERM_SEARCH_WINDOW+"'
displayName='"+Messages.RESATTR_TERM_SEARCH_WINDOW+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_11 + "'\n"+
" value='30'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_TERM_HISTORY+"'
displayName='"+Messages.RESATTR_TERM_HISTORY+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_10 + "'\n"+
" value='60'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_MOD_HISTORY+"'
displayName='"+Messages.RESATTR_MOD_HISTORY+"' type='string'
multi='false'\n"+
" description='" + Messages.RESATTR_HELP_6 + "'\n"+
" value='15'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_UPDATE_IF_DELETE+"'
displayName='"+Messages.RESATTR_UPDATE_IF_DELETE+"' type='string'
multi='false' value='1'\n"+
" description='" + Messages.RESATTR_HELP_60 + "'>\n"+
" </ResourceAttribute>\n" +
" <ResourceAttribute name='"+ActiveSync.RA_PROXY_ADMINISTRATOR+"'
displayName='"+Messages.RESATTR_PROXY_ADMINISTRATOR+"' type='string'
multi='false' value='Configurator'\n"+
" description='" + Messages.RESATTR_HELP_30 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+ActiveSync.RA_FORM+"'
displayName='"+Messages.RESATTR_FORM+"' type='string' multi='false'\n"+
" description='" + Messages.RESATTR_HELP_26 + "'\n"+
" value='HR Test Form 1'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_SCHEDULE_INTERVAL+"' displayName='"+Messages.RESATTR_SCHEDULE_INTERVAL+"' type='string' multi='false' \n"+
" description='" + Messages.RESATTR_HELP_51 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_SCHEDULE_INTERVAL_COUNT+"'
displayName='"+Messages.RESATTR_SCHEDULE_INTERVAL_COUNT+"' type='string'
multi='false' \n"+
" description='" + Messages.RESATTR_HELP_52 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_SCHEDULE_START_TIME+"'
displayName='"+Messages.RESATTR_SCHEDULE_START_TIME+"' type='string'
multi='false' \n"+
" description='" + Messages.RESATTR_HELP_56 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+RA_SCHEDULE_START_DATE+"'
displayName='"+Messages.RESATTR_SCHEDULE_START_DATE+"' type='string'
multi='false' \n"+
" description='" + Messages.RESATTR_HELP_54 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+ActiveSync.RA_LOG_LEVEL+"'
displayName='"+Messages.RESATTR_LOG_LEVEL+"' type='string' multi='false'
value='2'\n"+
" description='" + Messages.RESATTR_HELP_27 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+ActiveSync.RA_LOG_PATH+"'
displayName='"+Messages.RESATTR_LOG_PATH+"' type='string' multi='false' \n"+
" description='" + Messages.RESATTR_HELP_28 + "'>\n"+
" </ResourceAttribute>\n"+
" <ResourceAttribute name='"+ActiveSync.RA_LOG_SIZE+"' displayName='"+Messages.RESATTR_LOG_SIZE+"' type='string' multi='false' \n"+
" description='" + Messages.RESATTR_HELP_29 + "'>\n"+
" </ResourceAttribute>\n"+
" </ResourceAttributes>\n"+
" <Template>\n"+
" <AttrDef name='accountId' type='string'/>\n"+
" </Template>\n"+
" <AccountAttributeTypes>\n";
static final String baseXmlEnd =
" </AccountAttributeTypes>\n"+
"</Resource>\n";
static String prototypeXml = null;
/**
* Build the prototype XML from the base defined above, and the schema
* data defined before that.
*/
static {
StringBuffer sb = new StringBuffer(basePrototypeXml);
for (int i=0; i< _cols.length; i++) {
if (_cols[i]._lighthouseMappedName != null) {
sb.append(" <AccountAttributeType name='");
sb.append(_cols[i]._lighthouseMappedName);
sb.append("' mapName='");
sb.append(_cols[i]._columnName);
sb.append("' mapType='string' type='string' required='false'
/>\n");
} // if to be added to the schema
}
sb.append(baseXmlEnd);
prototypeXml = sb.toString();
};
//////////////////////////////////////////////////////////////////////
//
// Constructors
//
//////////////////////////////////////////////////////////////////////
/**
* Create the prototype instance.
*/
public static Resource staticCreatePrototypeResource() throws proprietaryException {
Resource res = new Resource(prototypeXml);
return res;
}
/**
* This adapter watches the table active_directory_data for items with
* a LAST_HIRE_DATE between today and two days from now. When it finds
* entries, it creates or updates the accounts.
*/
public void init() throws Exception {
_util = new ActiveSyncUtil(this.getResource(),this);
_util.logString(IAPI.TRACE_LEVEL_INFO,
"Done initializing HR ActiveSync adapter.\n");
}
public void shutdown() {
_util.logString(IAPI.TRACE_LEVEL_INFO,
"Shutting down HR ActiveSync adapter.\n");
}
/**
- Query for rows with LAST_HIRE_DATE between today+RA_PREHIRE_CREATE and
- today-RA_HIRE_SEARCH_WINDOW. Filter against rows already processed
- (in the createHistory Map). Handle the hire-terminate-rehire case
- by comparing both the badge number and the hire date. *
- This means that the tuple (LAST_HIRE_DATE,BADGE) must be unique. */ protected List getCreateRequests() throws proprietaryException, BadRowException {
CallableStatement s = null;
ResultSet rs = null;
List list = null;
try {
list = new ArrayList();
s = _connection.prepareCall("{ call asd_pkg.get_lh_data(?) }");
s.registerOutParameter(1, OracleTypes.CURSOR);
s.execute();
rs = (ResultSet) s.getObject(1);
while (rs.next() && !Thread.currentThread().isInterrupted()) {
UpdateRow row = rowToUpdateRow(rs,_colsList);
Map m = row.getAuditMap();
Object o = m.get("LAN_ID");
if (o != null)
{
String lanid = o.toString();
list.add(row);
}
}
} catch (SQLException e) {
try {
if (rs != null) rs.close();
s.close();
} catch (Exception closeEx) {
}
throw new com.proprietary.util.InternalError(e);
} finally {
try {
if (rs != null) rs.close();
s.close();
} catch (Exception closeEx) {
}
}
return list;
}
/**
* Query for rows with TERMINATION_DATE between today and
* today-RA_TERMINATE_SEARCH_WINDOW. Filter against rows already process
ed
* (in the history Map). Handle the terminate-hire-terminate case
* by comparing both the badge number and the terminate date.
*
* This means that the tuple (TERMINATION_DATE,BADGE) must be unique.
*/
protected List getTermRequests(Map history) throws proprietaryException, BadRowException {
ResultSet rs = null;
CallableStatement s = null;
List list = null;
try {
list = new ArrayList();
s = _connection.prepareCall("{ call asd_pkg.get_lh_data(?) }");
s.registerOutParameter(1, OracleTypes.CURSOR);
s.execute();
rs = (ResultSet) s.getObject(1);
while (rs.next() && !Thread.currentThread().isInterrupted()) {
UpdateRow row = rowToUpdateRow(rs,_colsList);
Map m = row.getAuditMap();
Object o = m.get("LAN_ID");
String lanid = o.toString();
if (o != null) {
list.add(row);
_util.logString(IAPI.TRACE_LEVEL_INFO, "-->lanid: " +
lanid);
}
}
} catch (SQLException e) {
try {
if (rs != null) rs.close();
s.close();
} catch (Exception closeEx) {
}
throw new com.proprietary.util.InternalError(e);
} finally {
try {
if (rs != null) rs.close();
s.close();
} catch (Exception closeEx) {
}
}
return list;
}
/**
* Get the name of the hire date column for creating the processed row
* history
*/
protected String getHireDateColumnName() { return COLUMN_HIRE_DATE; }
/**
* Get the name of the hire date column for creating the processed row
* history
*/
protected String getTermDateColumnName() { return COLUMN_TERM_DATE; }
class BadRowException extends Exception {
}
}
Received on Mon May 24 2004 - 00:30:09 CEST