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?

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.

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();