Working with Binary Data

, ,
1

I am storing some binary data in a Oracle long field. When I retrieve this data using the db adapter it puts it in a text string. This is causing my binary data to get corrupted. Does anyone know how to get integration server to treat this data as binary and return it appropriately?

2

Hi Tim,

I dont think the pub.db.execSQL will handle binary data. I have created one java service for handling binary fields. This is the java code for that service

String user="", pass="", dburl="", command="";

int rowcount=0;
ArrayList al_results = new ArrayList();
Connection conn=null;
byte bytes=null;
IDataHashCursor id = pipeline.getHashCursor();
// only will use the first occurance of the message element
if (id.first(“user”)) user = (String)id.getValue();

if (id.first("password"))   pass = (String)id.getValue(); 

if (id.first("url"))  dburl = (String)id.getValue();

if (id.first(“sql”)) command = (String)id.getValue();
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(dburl, user, pass);
}
catch(SQLException sqle)
{
id.last();
id.insertAfter( “$dbMessage”, sqle.getMessage() );
id.insertAfter( “$rowCount”, “0” );
}

Statement stmt = null;
try
{
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(command);
ResultSetMetaData rsmd = rs.getMetaData();

 int colCount = rsmd.getColumnCount();

while( rs.next() )
{
rowcount++;
IData result = IDataFactory.create();

IDataCursor resultCursor = result.getCursor();

for( int i=1; i<=colCount; i++ )
{
if( rsmd.getColumnType(i) == java.sql.Types.BINARY || rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY || rsmd.getColumnType(i) == java.sql.Types.BLOB )
{
resultCursor.insertAfter(rsmd.getColumnName(i), rs.getBytes(i));

} 
else 
{ 
 resultCursor.insertAfter(rsmd.getColumnName(i), rs.getString(i)); 

}

}
resultCursor.destroy();

al_results.add(result);
}
rs.close();
stmt.close();
conn.close();
}
catch(SQLException sqle)
{
id.last();
id.insertAfter( “$dbMessage”, sqle.getMessage() );
id.insertAfter( “$rowCount”, “0” );
}

Object obj = al_results.toArray();
IData results = new IData[obj.length];
for( int i=0; i<obj.length; i++)
results[i] = (IData) obj[i];
id.last();
id.insertAfter( “results”, results );
id.insertAfter(“$rowCount”, rowcount+“”);
id.insertAfter( “$dbMessage”, “” );
id.destroy();

I hope this will help.

3

Thanks Abdul, I changed your code to include Oracle Cursors and it works great. I made the stmt a CallableStatement with stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = (ResultSet) ((OracleCallableStatement)stmt).getCursor(1);

and added rsmd.getColumnType(i) == OracleTypes.CURSOR

Here’s my version:
String user=“”, pass=“”, dburl=“”, command=“”;
int rowcount=0;
ArrayList al_results = new ArrayList();
Connection conn=null;
byte bytes=null;
IDataHashCursor id = pipeline.getHashCursor();
// only will use the first occurance of the message element
if (id.first(“user”)) user = (String)id.getValue();

if (id.first(“password”)) pass = (String)id.getValue();

if (id.first(“url”)) dburl = (String)id.getValue();

if (id.first(“sql”)) command = (String)id.getValue();

try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(dburl, user, pass);
}
catch(SQLException sqle)
{
id.last();
id.insertAfter( “$dbMessage”, sqle.getMessage() );
id.insertAfter( “$rowCount”, “0” );
}

CallableStatement stmt = null;
try
{
stmt = conn.prepareCall(command);
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = (ResultSet) ((OracleCallableStatement)stmt).getCursor(1);
ResultSetMetaData rsmd = rs.getMetaData();

int colCount = rsmd.getColumnCount();

while( rs.next() )
{
rowcount++;
IData result = IDataFactory.create();

IDataCursor resultCursor = result.getCursor();

for( int i=1; i<=colCount; i++ )
{
if( rsmd.getColumnType(i) == java.sql.Types.BINARY || rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY || rsmd.getColumnType(i) == java.sql.Types.BLOB || rsmd.getColumnType(i) == OracleTypes.CURSOR )
{
resultCursor.insertAfter(rsmd.getColumnName(i), rs.getBytes(i));

}
else
{
resultCursor.insertAfter(rsmd.getColumnName(i), rs.getString(i));

}
}
resultCursor.destroy();

al_results.add(result);
}
rs.close();
stmt.close();
conn.close();
}
catch(SQLException sqle)
{
id.last();
id.insertAfter( “$dbMessage”, sqle.getMessage() );
id.insertAfter( “$rowCount”, “0” );
}

Object obj = al_results.toArray();
IData results = new IData[obj.length];
for( int i=0; i<obj.length; i++)
results[i] = (IData) obj[i];
id.last();
id.insertAfter( “results”, results );
id.insertAfter(“$rowCount”, rowcount+“”);
id.insertAfter( “$dbMessage”, “” );
id.destroy();