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