Bug 777995 (SOA-522)

Link: Added: This issue depends JBESB-1690

Verified in CR1

The solution works for CLOB types but not from BLOB types. According to code both types should be supported.
Results from PostgreSQL
2008-06-11 12:06:35,363 DEBUG [org.jboss.internal.soa.esb.couriers.SqlTableCourier] SQL exception during deliver
org.postgresql.util.PSQLException: ERROR: column "string_data_column" is of type bytea but expression is of type character varying
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1531)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1313)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:278)
        at org.jboss.internal.soa.esb.couriers.SqlTableCourier.deliver(SqlTableCourier.java:158)
        at org.jboss.internal.soa.esb.couriers.TwoWayCourierImpl.deliver(TwoWayCourierImpl.java:188)
        at org.jboss.soa.esb.client.ServiceInvoker$EPRInvoker.attemptDelivery(ServiceInvoker.java:525)
        at org.jboss.soa.esb.client.ServiceInvoker$EPRInvoker.access$200(ServiceInvoker.java:447)
        at org.jboss.soa.esb.client.ServiceInvoker.post(ServiceInvoker.java:316)
        at org.jboss.soa.esb.client.ServiceInvoker.deliverAsync(ServiceInvoker.java:225)
        at org.jboss.internal.soa.esb.persistence.format.db.DBMessageStoreImpl.redeliver(DBMessageStoreImpl.java:394)
        at org.jboss.soa.esb.actions.MessageRedeliverer.process(MessageRedeliverer.java:74)
        at org.jboss.soa.esb.listeners.message.ActionProcessingPipeline.process(ActionProcessingPipeline.java:316)
        at org.jboss.soa.esb.listeners.ScheduleListener.onSchedule(ScheduleListener.java:121)
        at org.jboss.soa.esb.schedule.ScheduleProvider$ESBScheduledJob.execute(ScheduleProvider.java:218)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:203)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)

Probably the stream approcha should be used instead of setObject

I guess postgres handles this differently as both were tested.

I will create a new issue to cover the postgres case as it may be seen on other DBs.

Link: Added: This issue depends JBESB-1805

JBESB-1805 has been created to cover the new work

Link: Added: This issue depends JBESB-1833

Link: Added: This issue depends JBESB-1834

Link: Added: This issue related SOA-858

There is still one ptifall with the implementation
                        if (type == Types.BLOB) {
                            final Blob blob = RS.getBlob(iCurr) ;
                            row.put(sCol, StreamUtils.readStreamString(blob.getBinaryStream(), "UTF-8"));
                        } else if (type == Types.CLOB) {
                            final Clob clob = RS.getClob(iCurr) ;
                            row.put(sCol, StreamUtils.readReader(clob.getCharacterStream()));
                        } else {
                            row.put(sCol, RS.getObject(iCurr));
                        }
The problem is that on some databases (e.g. Oracle) if the column contains null value, then the cals RS.getXlob(iCurr) return null and the next line causes NPE. There should be something like
if (blob == null) {
row.put(sCol, null);
}
else {
                            row.put(sCol, StreamUtils.readStreamString(blob.getBinaryStream(), "UTF-8"));
}

Please create a new issue and relate it to this one.

Link: Added: This issue related JBESB-2116

Link: Added: This issue is a dependency of JBESB-2135