Inserting BLOB using BeanPropertySqlParameterSource ?

[xerces8]

Hi!

I have defined a bean (class Foo) having one Long and one byte[] property.

When reading with SimpleJdbcCall ....returningResultSet("return_parameter",
BeanPropertyRowMapper.newInstance(Foo.class)). execute(some_parameters). get("return_parameter"). get(0) it all works fine.

The stored procedure is defined as:

procedure getFoo(a_id IN INTEGER,  return_parameter OUT sys_refcursor);

The return is a select from a table with two columns: NUMBER and BLOB.

The DB is Oracle.

The problem is writing.

procedure createFoo(fooId IN INTEGER, data IN BLOB)

Java code:

new SimpleJdbcCall(m_dataSource).withProcedureName("createFoo").
  execute(new BeanPropertySqlParameterSource(newFoo))

newFoo is an initialized object of class Foo.

The problem is, that the data proprty is set to SQL type OTHER.

If I set the type manually to BLOB like this:

.execute(new BeanPropertySqlParameterSource(newFoo).registerSqlType("data", Types.BLOB))

Then I get a java.lang.ClassCastException: [B cannot be cast to oracle.sql.BLOB


So the conversion works when reading (BLOB to byte array), but not when writing (byte array to BLOB).
Am I missing something obvious here?

What is the proper solution?

Maybe I could use an instance of java.sql.Blob, but I don't want to put SQL specific code in the upper layers of my application (Foo is a DTO).


Regards,
David

[xerces8]

Aha, according to org.springframework.jdbc.core.StatementCreatorUtil s.javaTypeToSqlTypeMap , only Blob.class is mapped to BLOB, so I have to cook up something by hand.

[xerces8]

It is harder than I thought....

Another try:

MapSqlParameterSource inParams = new MapSqlParameterSource();
inParams.addValue("a_id", foo.getA_Id());
inParams.addValue("data", new SerialBlob(foo.getData()));

This gives ORA-17004 "Invalid column type".

If I add:

inParams.registerSqlType("data", Types.BLOB);

I get:
ClassCastException: javax.sql.rowset.serial.SerialBlob cannot be cast to oracle.sql.BLOB



Next try:

...
Blob blob;
blob = m_dataSource.getConnection().createBlob();
blob.setBytes(1, foo.getData());
inParams.addValue("data", blob);

inParams.registerSqlType("data", Types.BLOB);
...

Result:
ORA-22922: nonexistent LOB value

Without the registerSqlType() I get the 17004 error again.

[xerces8]

Fixed!

Instead of SimpleJdbcCall I used AbstractLobCreatingPreparedStatementCallback, as described on its JavaDoc: http://static.springsource.org/sprin...tCallback.html

Here is the new code:

m_jdbcTemplate.execute("{ call createFoo(?, ?) }",
	new AbstractLobCreatingPreparedStatementCallback(m_lobHandler) {
		protected void setValues(PreparedStatement ps,
				LobCreator lobCreator) throws SQLException {
			ps.setLong(1, foo.getAmEnquiryId());
			lobCreator.setBlobAsBytes(ps, 2, foo.getData());
		}
	});

[ScottJones608]

I ran into this same error "java.lang.ClassCastException: [B cannot be cast to oracle.sql.BLOB" and was frustrated by it for several hours. In the end I started trying to register my byte[] field to different data types.

The simple solution that still allowed me to use BeanPropertySqlParameterSource was to register my byte[] field (named "data") as "BINARY" instead of "BLOB"

BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(entity);
		
paramSource.registerSqlType("data", Types.BINARY);

Scott