Question
Last activity: 15 Dec 2015 11:56 EST
Read data from a BLOB column in external DB
Hi,
We have a requirement to read the data from a BLOB column in external DB, Is it possible to read BLOB column ?
Accepted Solution
Wells Fargo
US
Use a java activity step or create a function... rough code:
Use a java activity step or create a function... rough code:
// Your code starts after this line. DO NOT REMOVE THIS LINE
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
String url = "";
String username = "";
String password = "";
String key = "";
boolean hadError = false;
byte streamBytes[];
try
{
Class.forName("oracle.jdbc.OracleDriver");
url = "jdbc:oracle:thin:@//your.dbserver.com:dbport/dbschema";
username = "dbuserid";
password = "dbpassword";
conn = java.sql.DriverManager.getConnection(url, username, password);
}
catch(Exception ex)
{
oLog.infoForced("Exception - "+ex.toString());
hadError = true;
}
if(!hadError)
{
try
{
key = tools.getParamValue("inputkey");
StringBuffer sql = new StringBuffer();
sql.append("select pzpvstream from your_table where pzinskey = '"+key+"'");
stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
java.sql.ResultSet rs1 = stmt.executeQuery(sql.toString());
rs1.next();
java.sql.Blob streamBlob = rs1.getBlob(1);
long streamLength = streamBlob.length();
streamBytes = streamBlob.getBytes(1L, (int)streamLength);
rs1.close();
stmt.close();
conn.close();
}
catch(Exception ex)
{
oLog.infoForced("Exception - "+ex.toString());
}
}
// Your code ends prior to this line. DO NOT REMOVE THIS LINE
Pegasystems
IN
There is no support in Pega platform, as of now, for reading data from a BLOB column, outside of the BLOB column used by Pega for its own storage stream (pzPVStream).
Accepted Solution
Wells Fargo
US
Use a java activity step or create a function... rough code:
Use a java activity step or create a function... rough code:
// Your code starts after this line. DO NOT REMOVE THIS LINE
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
String url = "";
String username = "";
String password = "";
String key = "";
boolean hadError = false;
byte streamBytes[];
try
{
Class.forName("oracle.jdbc.OracleDriver");
url = "jdbc:oracle:thin:@//your.dbserver.com:dbport/dbschema";
username = "dbuserid";
password = "dbpassword";
conn = java.sql.DriverManager.getConnection(url, username, password);
}
catch(Exception ex)
{
oLog.infoForced("Exception - "+ex.toString());
hadError = true;
}
if(!hadError)
{
try
{
key = tools.getParamValue("inputkey");
StringBuffer sql = new StringBuffer();
sql.append("select pzpvstream from your_table where pzinskey = '"+key+"'");
stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
java.sql.ResultSet rs1 = stmt.executeQuery(sql.toString());
rs1.next();
java.sql.Blob streamBlob = rs1.getBlob(1);
long streamLength = streamBlob.length();
streamBytes = streamBlob.getBytes(1L, (int)streamLength);
rs1.close();
stmt.close();
conn.close();
}
catch(Exception ex)
{
oLog.infoForced("Exception - "+ex.toString());
}
}
// Your code ends prior to this line. DO NOT REMOVE THIS LINE