How to get Database "java.sql.Connection" in a java step from an existing Database instance
I needed to write a very custom java.sql.PreparedStatement in a java step and the only missing piece is the "java.sql.Connection" object. Is there any Pega Engine API that will get this java object assuming I have the Database name and/or JDBC URL. Also, I have tried the following it works but I do not want to hardcode the password.
//Works but I don't want to hardcode the password
java.sql.Connection con = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@dev.mycompany.com:8091:DEV", "username", "password");
//Note: getConnection("jdbc:url") signature does not work since password is require to get the instance
Pegasystems
US
Are any of the the Database.executeRDB methods of use to you? You can read about them from the developer portal with
( i ) > Engine API > Database
US
Thanks for the reply, I've look into the API and was able to get simple SQL execution working with executeRDB but what I need to do is to set a BLOB column to an external database. The code I currently have is something like below and everything will work except I don't want to hardcode the password. For executeRDB how do I pass in byte[] ?
ClipboardPage paramPage = tools.findPage("PODocument");
String fileName = paramPage.getString("FILE_NAME");
String fileData = paramPage.getString("FILE_DATA");
byte[] byteFileStream = org.apache.commons.codec.binary.Base64.decodeBase64(fileData.getBytes());
java.sql.Connection con = java.sql.DriverManager.getConnection("jdbc:url", "username", "password");
con.setAutoCommit(false);
java.sql.PreparedStatement ps = con.prepareStatement("insert into XXFB_PEGA_.....");
ps.setBytes(1, byteFileStream);
ps.setString(2, fileName);
ps.executeUpdate();
Pegasystems
US
Here's some code I created for one of my tools. This code does alot more than you need, but the part that hopefully is of interest to you is the construct that inserts the class. That's the part that avoids specifying the username and password.
// We need to use an RDB Execute, so that we are sure that the update date time
// we read is from the database and not the deferred list.
boolean debugFlag = false; // don't forget to falsify this before ship !
ClipboardPage workFromDB = tools.createPage("Code-Pega-List", "fromDB");
String thisClassName = myStepPage.getClassName();
String thisKey = myStepPage.getString(".pzInsKey");
workFromDB.putString("pyObjClass", thisClassName);
workFromDB.putString("pyMaxRecords", "1");
String sqlString = null;
for (int pass = 0; pass<=1; pass++) {
// First we read from column because cheaper, but if we suspect an issue,
// we read from blob on second pass
if (0==pass) sqlString = "select pxUpdateDateTime as \"pxUpdateDateTime\" from {Class:" + thisClassName + "} where pzInsKey = '" + thisKey + "'";
else sqlString = "select * from {Class:" + thisClassName + "} where pzInsKey = '" + thisKey + "'";
try {
tools.getDatabase().executeRDB(sqlString, workFromDB);
} catch (DatabaseException de) {
nextBlock = "Clear";
}
Here's some code I created for one of my tools. This code does alot more than you need, but the part that hopefully is of interest to you is the construct that inserts the class. That's the part that avoids specifying the username and password.
// We need to use an RDB Execute, so that we are sure that the update date time
// we read is from the database and not the deferred list.
boolean debugFlag = false; // don't forget to falsify this before ship !
ClipboardPage workFromDB = tools.createPage("Code-Pega-List", "fromDB");
String thisClassName = myStepPage.getClassName();
String thisKey = myStepPage.getString(".pzInsKey");
workFromDB.putString("pyObjClass", thisClassName);
workFromDB.putString("pyMaxRecords", "1");
String sqlString = null;
for (int pass = 0; pass<=1; pass++) {
// First we read from column because cheaper, but if we suspect an issue,
// we read from blob on second pass
if (0==pass) sqlString = "select pxUpdateDateTime as \"pxUpdateDateTime\" from {Class:" + thisClassName + "} where pzInsKey = '" + thisKey + "'";
else sqlString = "select * from {Class:" + thisClassName + "} where pzInsKey = '" + thisKey + "'";
try {
tools.getDatabase().executeRDB(sqlString, workFromDB);
} catch (DatabaseException de) {
nextBlock = "Clear";
}
if (workFromDB.getProperty(".pxResultCount").toInteger() == 1)
{
ClipboardProperty DBUpdateTime = workFromDB.getProperty(".pxResults(1).pxUpdateDateTime");
ClipboardProperty ThisUpdateTime = myStepPage.getProperty(".pxUpdateDateTime");
// CompareTo returns the value 0 if the argument Date is equal to this Date;
// a value less than 0 if this Date is before the Date argument;
// and a value greater than 0 if this Date is after the Date argument.
if (DBUpdateTime.getStringValue().length() > 0 && DBUpdateTime.toDate().compareTo(ThisUpdateTime.toDate()) > 0) {
if (1 == pass) {
mismatch = "The pxUpdateDateTime of the object in the database " + DBUpdateTime.getStringValue() + " is newer than what is being saved " + ThisUpdateTime.getStringValue() + " for " + thisKey;
if (debugFlag) pega_rules_utilities.sendDebugMessageToTracer ("Staleness detected in blob: blob stamp = " + DBUpdateTime.toDate() + " step page stamp = " + ThisUpdateTime.toDate(), "Pega-ProCom");
} else if (debugFlag) pega_rules_utilities.sendDebugMessageToTracer ("Staleness detected on column but ignoring it so we can check blob: column stamp = " + DBUpdateTime.toDate() + " step page stamp = " + ThisUpdateTime.toDate(), "Pega-ProCom");
}
else {
if (debugFlag) pega_rules_utilities.sendDebugMessageToTracer ("Writing non-stale data: column stamp = " + DBUpdateTime.toDate() + " step page stamp = " + ThisUpdateTime.toDate(), "Pega-ProCom");
break; // if not stale, skip blob pass entirely
}
} /* end of is result-count 1 */ else if (debugFlag) pega_rules_utilities.sendDebugMessageToTracer ("result count not 1 so not checking for staleness during pass = " + pass, "Pega-ProCom");
} // end of for loop