private Map<String, String> getConnector(String entityId, String connectorName) {
final String sql =
"select "
+ COL_PASSWORD
+ ","
+ COL_ISACTIVE
+ ",CONNECTOR_PKEY"
+ " from "
+ TABLE_CONNECTOR
+ " with(nolock)"
+ " where "
+ COL_ENTITY_CODE
+ "=? and "
+ COL_CONNECTOR_NAME
+ "=?";
ResourceFactory rf = Context.rf.get();
ResultSet rs =
Sql.sqlQuery(
sql,
new Object[] {entityId, connectorName},
new int[] {Types.VARCHAR, Types.VARCHAR},
rf);
try {
Map<String, String> ret = null;
while (rs.next()) {
if (null != ret) {
throw new IllegalStateException(
"Duplicate ATS_CONNECTOR record in DB: " + entityId + '/' + connectorName);
}
ret = new HashMap<String, String>();
ret.put(COL_PASSWORD, rs.getString(1));
ret.put(COL_ISACTIVE, rs.getString(2));
ret.put(COL_ID, rs.getString(3));
}
if (null == ret) {
throw new ReportableException(
ReportableException.BAD_COMPANY, // bad connector, in this case
"Failed to find ATS_CONNECTOR record in DB: " + entityId + '/' + connectorName);
}
return ret;
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
}
private static String getOrCreateSession(int connectorId) {
String sessionId = getConnectorSession(connectorId);
if (null != sessionId) {
return sessionId; // we're using the key to the session table rec as the session ID
}
String sql =
"insert into "
+ TABLE_SESSION
+ "("
+ "SESSION_PKEY,"
+ COL_DATE_CREATE
+ ",CONNECTOR_KEY,"
+ COL_DATE_EXPIRE
+ ") values (?,?,?,?)";
sessionId = Util.getGuid();
Timestamp now = new Timestamp(Gmt.getGmtDate().getTime());
ResourceFactory rf = Context.rf.get();
Sql.sqlExe(
sql,
new Object[] {sessionId, now, connectorId, getExpireDt(now)},
new int[] {Types.VARCHAR, Types.TIMESTAMP, Types.INTEGER, Types.TIMESTAMP},
rf);
System.out.println(new Date() + ": Created new connector session -- " + sessionId);
return sessionId; // we're using the key to the session table rec as the session ID
}
@Override
public void logWs(LogLevel severity, WsRequest req, String logText) {
if (!isInit) {
init();
}
String sql =
"insert into "
+ TABLE_LOG_WS
+ "( date, severity, machine_name, entity_code, action, description, request, millis ) "
+ "values ( getutcdate(),?,?,?,?,?,?,? )";
long millis = System.currentTimeMillis() - req.getStartTime();
Sql.sqlExe(
sql,
new Object[] {
severity.toString(),
Util.getHostName(),
req.getCompId(),
req.getVerb(),
Str.truncate(logText, 2048),
null == req.getXmlRequest() ? "" : Str.truncate(req.getXmlRequest(), 4096),
millis
},
new int[] {
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.INTEGER
},
Context.rf.get().getLogRF());
}
private static String getConnectorSession(int conKey) {
final String sql =
"select SESSION_PKEY,"
+ COL_DATE_EXPIRE
+ " from "
+ TABLE_SESSION
+ " with(nolock) where CONNECTOR_KEY=?";
Date now = Gmt.getGmtDate();
ResourceFactory rf = Context.rf.get();
ResultSet rs = Sql.sqlQuery(sql, new Object[] {conKey}, new int[] {Types.INTEGER}, rf);
try {
while (rs.next()) {
String sessionId = rs.getString(1);
Date expireDate = rs.getTimestamp(2);
if (expireDate.getTime() >= now.getTime()) {
System.out.println(new Date() + ": Extending existing session -- " + conKey);
final Date expireDt = getExpireDt(now);
Sql.sqlExe(
"update " + TABLE_SESSION + " set " + COL_DATE_EXPIRE + "=? where CONNECTOR_KEY=?",
new Object[] {expireDt, conKey},
new int[] {Types.TIMESTAMP, Types.INTEGER},
rf);
return sessionId;
} else {
System.out.println(new Date() + ": Removing expired session -- " + sessionId);
Sql.sqlExe(
"delete " + TABLE_SESSION + " where SESSION_PKEY=?",
new Object[] {sessionId},
new int[] {Types.VARCHAR},
rf);
}
}
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
return null;
}
private static void extendSessionExpiration(
String sessionId, Date newExpireDate, ResourceFactory rf) {
String updateSql =
"update " + TABLE_SESSION + " set " + COL_DATE_EXPIRE + "=? where SESSION_PKEY=?";
Sql.sqlExe(
updateSql,
new Object[] {newExpireDate, sessionId},
new int[] {Types.TIMESTAMP, Types.VARCHAR},
rf);
}
@Override
public String getTuple(String sessionId, String key) {
if (!isInit) {
init();
}
ResourceFactory rf = Context.rf.get();
final Map<String, Object> sessRec = findSession(sessionId, rf);
final int conKey = (Integer) sessRec.get(COL_CONNECTOR_ID);
final String sql =
"select "
+ COL_TUPLE_VALUE
+ " from "
+ TABLE_DATA
+ " with(nolock) "
+ "where CONNECTOR_KEY=? and "
+ COL_TUPLE_KEY
+ "=?";
ResultSet rs =
Sql.sqlQuery(sql, new Object[] {conKey, key}, new int[] {Types.INTEGER, Types.VARCHAR}, rf);
try {
String val = null;
while (rs.next()) {
if (null != val) {
throw new IllegalStateException(
"Duplicate ATS_CONNECTOR_DATA record in DB: " + conKey + '/' + key);
}
val = rs.getString(1);
}
return val;
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
}
@Override
public String getConfigs(String token) {
if (!isInit) {
init();
}
ResourceFactory rf = Context.rf.get();
final Map<String, Object> sessRec = findSession(token, rf);
String sql =
"select "
+ COL_TUPLE_KEY
+ ","
+ COL_TUPLE_VALUE
+ " from "
+ TABLE_CONFIG
+ " with(nolock) where CONNECTOR_KEY=?";
ResultSet rs =
Sql.sqlQuery(
sql, new Object[] {sessRec.get(COL_CONNECTOR_ID)}, new int[] {Types.INTEGER}, rf);
try {
StringBuilder buf = new StringBuilder(1024);
while (rs.next()) {
buf.append("<tuple>");
buf.append("<key>").append(rs.getString(1)).append("</key>");
buf.append("<data>").append(Str.cdata(rs.getString(2))).append("</data>");
buf.append("</tuple>");
}
return buf.toString();
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
}
private static Map<String, Object> findSession(String sessionId, ResourceFactory rf) {
String sql =
"select CONNECTOR_KEY,"
+ COL_DATE_EXPIRE
+ " from "
+ TABLE_SESSION
+ " with(nolock) where SESSION_PKEY=?";
ResultSet rs = Sql.sqlQuery(sql, new Object[] {sessionId}, new int[] {Types.VARCHAR}, rf);
try {
if (rs.next()) {
Date now = Gmt.getGmtDate();
Date expireDate = rs.getTimestamp(2);
if (expireDate.getTime() >= now.getTime()) {
// we've received a valid transaction; move the session expiration date out
Date newExpireDate = getExpireDt(now);
extendSessionExpiration(sessionId, newExpireDate, rf);
Map<String, Object> ret = new HashMap<String, Object>();
ret.put(COL_DATE_EXPIRE, newExpireDate);
ret.put(COL_CONNECTOR_ID, rs.getInt(1));
return ret;
}
}
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
throw new ReportableException(ReportableException.STALE_TRANSACTION, sessionId);
}
private static void createTestConnector() {
final String entityId = "phos123488";
final String connName = "Test Connector";
// final String company = "Test Company";
final String desc = "Test connector so that Concur can make sure that things work properly.";
final String version = "1.0";
final String isActive = "Y";
final String password = Hash.saltedHash(entityId, "DustyParensMistHawaii");
String sql =
"select 1 from "
+ TABLE_CONNECTOR
+ " with(nolock) where "
+ COL_ENTITY_CODE
+ "=? and "
+ COL_CONNECTOR_NAME
+ "=?";
ResourceFactory rf = Context.rf.get();
ResultSet rs =
Sql.sqlQuery(
sql, new Object[] {entityId, connName}, new int[] {Types.VARCHAR, Types.VARCHAR}, rf);
try {
if (rs.next()) {
String updateSql =
"update "
+ TABLE_CONNECTOR
+ " set "
+ COL_DESC
+ "=?,"
+ COL_VERSION
+ "=?,"
+ COL_ISACTIVE
+ "=?,"
+ COL_PASSWORD
+ "=?"
+ " where "
+ COL_CONNECTOR_NAME
+ "=? and "
+ COL_ENTITY_CODE
+ "=?";
Sql.sqlExe(
updateSql,
new Object[] {desc, version, isActive, password, connName, entityId},
new int[] {
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR
},
rf);
} else {
String insertSql =
"insert into "
+ TABLE_CONNECTOR
+ "("
+ COL_DESC
+ ","
+ COL_VERSION
+ ","
+ COL_ISACTIVE
+ ","
+ COL_PASSWORD
+ ","
+ COL_DATE_CREATE
+ ","
+ COL_CONNECTOR_NAME
+ ","
+ COL_ENTITY_CODE
+ ")"
+ " values (?,?,?,?,?,?,?)";
final Timestamp dateCreated = new Timestamp(System.currentTimeMillis());
Sql.sqlExe(
insertSql,
new Object[] {desc, version, isActive, password, dateCreated, connName, entityId},
new int[] {
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.TIMESTAMP,
Types.VARCHAR,
Types.VARCHAR
},
rf);
}
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
// now add a couple config items
String configSql =
"declare @conKey int; "
+ "select @conKey=connector_pkey from ats_connector c with(nolock) "
+ " where c.entity_code='phos123488' and c.connector_name='Test Connector' "
+ " "
+ "if @conKey is not null "
+ "begin "
+ " if not exists( select 1 from ats_connector_config cc with(nolock) "
+ " where cc.connector_key=@conKey and cc.tuple_key='Author' ) "
+ " begin "
+ " insert into ats_connector_config (connector_key,tuple_key,tuple_value) values( @conKey, 'Author', 'DouglasW' ) "
+ " end "
+ " if not exists( select 1 from ats_connector_config cc with(nolock) "
+ " where cc.connector_key=@conKey and cc.tuple_key='Created' ) "
+ " begin "
+ " insert into ats_connector_config (connector_key,tuple_key,tuple_value) values( @conKey, 'Created', cast( getutcdate() as varchar(50))) "
+ " end "
+ "end";
Sql.sqlExe(configSql, new Object[] {}, new int[] {}, rf);
}
@Override
public void putTuple(String token, String key, String value) {
if (!isInit) {
init();
}
ResourceFactory rf = Context.rf.get();
Map<String, Object> sessRec = findSession(token, rf);
int conId = (Integer) sessRec.get(COL_CONNECTOR_ID);
if (null == value) {
String sql = "delete " + TABLE_DATA + " where CONNECTOR_KEY=? and " + COL_TUPLE_KEY + "=?";
Sql.sqlExe(sql, new Object[] {conId, key}, new int[] {Types.INTEGER, Types.VARCHAR}, rf);
} else {
final String sql =
"select 1 from "
+ TABLE_DATA
+ " with(nolock) where CONNECTOR_KEY=? and "
+ COL_TUPLE_KEY
+ "=?";
final ResultSet rs =
Sql.sqlQuery(
sql, new Object[] {conId, key}, new int[] {Types.VARCHAR, Types.VARCHAR}, rf);
try {
if (rs.next()) // if tuple pair already exists in the DB
{
String updateSql =
"update "
+ TABLE_DATA
+ " set "
+ COL_TUPLE_VALUE
+ "=? where CONNECTOR_KEY=? and "
+ COL_TUPLE_KEY
+ "=?";
Sql.sqlExe(
updateSql,
new Object[] {value, conId, key},
new int[] {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR},
rf);
} else {
String insertSql =
"insert into "
+ TABLE_DATA
+ "(CONNECTOR_KEY,"
+ COL_TUPLE_KEY
+ ","
+ COL_TUPLE_VALUE
+ ")values(?,?,?)";
Sql.sqlExe(
insertSql,
new Object[] {conId, key, value},
new int[] {Types.VARCHAR, Types.VARCHAR, Types.VARCHAR},
rf);
}
} catch (RuntimeException rte) {
throw rte;
} catch (Throwable t) {
throw new IllegalStateException(t);
} finally {
Sql.closeAll(rs);
}
}
}
@Override
public void log(LogEntry le) {
if (!isInit) {
init();
}
ResourceFactory rf = Context.rf.get();
if (le.level.ordinal() < rf.getLoggingLevel().ordinal()) {
return;
}
prepLogEntry(le, rf);
final LogContext lc = Log.logContext.get();
if (alreadyCalled.get() == 0) {
try {
alreadyCalled.set(1);
String sql =
"INSERT INTO "
+ TABLE_LOG
+ "("
+ COL_SYSTEM
+ ","
+ COL_ENTITY_CODE
+ ","
+ COL_DATE
+ ","
+ COL_SEVERITY
+ ","
+ COL_MACHINE_NAME
+ ","
+ COL_THREAD_ID
+ ","
+ COL_KEY
+ ","
+ COL_DESC
+ ")"
+ "VALUES(?,?,getutcdate(),?,?,?,?,?)";
Sql.sqlExe(
sql,
new Object[] {
le.serviceName,
le.compId,
le.level.toString(),
Util.getHostName(),
le.threadId,
le.transKey == -1 ? null : le.transKey,
le.desc.length() > MAX_LOG_DESC ? le.desc.substring(0, MAX_LOG_DESC) : le.desc
},
new int[] {
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.INTEGER,
Types.VARCHAR
},
rf);
} catch (Throwable t) {
handleNonLoggableError(
LogLevel.ERROR,
le.serviceName,
getClass(),
"log",
"Log to database was unsuccessful. " + buildNonLoggableDesc(le));
} finally {
alreadyCalled.set(0);
}
} else {
handleNonLoggableError(
LogLevel.ERROR,
le.serviceName,
getClass(),
"logImageProcessing",
"Recursive logging call cannot log. Log data: " + buildNonLoggableDesc(le));
}
}
