Hi all,

I've been having a problem trying to persist an object that maps an object to a table that contains a blob field. I read this link

http://www.hibernate.org/73.html

and created the BinaryBlobType and the mapping as directed and I'm still having problems.

I also tried one of the solutions from the page, which was to change the batch_size to 0, but I was not able to do that, we're using a jboss-service mapping file, and it seems that the version we are using does not allow you to set the JdbcBatchSize, because it says this attribute does not exist.

We have gotten other tables to persist so we think the problem is with the blob type.

Here's the information for our problem table,

Hibernate version:
Hibernate 2.0.3

Mapping documents:
<property
name="blob"
type="com.crisys.sf.dto.BinaryBlobType"
column="blob"
/>

The BinaryBlobType is copied from the link we found and here are the only changes we really made to it

public void nullSafeSet(PreparedStatement st, Object value, int index)
throws HibernateException, SQLException
{
//st.setBlob(index, Hibernate.createBlob((byte[]) value));
st.setBytes(index, (byte[]) value);
}

The setters and getters from our object are

public byte[] getBlob() {
return this.blob;
}

public void setBlob(byte[] blob) {
this.blob = blob;
}


Full stack trace of any exception that occurs:

13:40:35,045 INFO [STDOUT] Hibernate: insert into RESPONSE_PLAN (title, description, blob, addressID, addressDataOwnerID, id, dataOwner) values (?, ?, ?, ?, ?, ?, ?)
13:40:35,050 ERROR [JDBCExceptionReporter] Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob, addressID, addressDataOwnerID, id, dataOwner) values (nul"
13:40:35,051 ERROR [JDBCExceptionReporter] Could not synchronize database state with session
java.sql.BatchUpdateException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob, addressID, addressDataOwnerID, id, dataOwner) values (nul"
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1404)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:701)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:50)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:105)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2103)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2061)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2005)
at net.sf.hibernate.transaction.JTATransaction.commit(JTATransaction.java:52)
at com.crisys.sf.framework.dataAccess.HibernateProxy.persistAlarm(Unknown Source)
at com.crisys.sf.framework.dataAccess.ejb.DataFacadeBean.persistAlarm(Unknown Source)
at sun.reflect.GeneratedMethodAccessor413.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:629)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:186)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:84)
at org.jboss.ejb.plugins.AbstractTxInterceptorBMT.invokeNext(AbstractTxInterceptorBMT.java:144)
at org.jboss.ejb.plugins.TxInterceptorBMT.invoke(TxInterceptorBMT.java:62)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:72)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:117)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionContainer.java:322)
at org.jboss.ejb.Container.invoke(Container.java:674)
at sun.reflect.GeneratedMethodAccessor402.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:359)
at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
at sun.rmi.transport.Transport$1.run(Transport.java:148)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
at java.lang.Thread.run(Thread.java:534)

Name and version of the database you are using:
mysql ver. 12.21 Distrib 4.0.14 for pc-linux

The generated SQL (show_sql=true):
insert into RESPONSE_PLAN (title, description, blob, addressID, addressDataOwnerID, id, dataOwner) values (?, ?, ?, ?, ?, ?, ?)


Thanks for any help you can give,
Somsack

I suppose "blob" is a resevred word and needs to be quoted.

Thanks for the quick reply. We changed the name of the blob field to be something other than 'blob' and now we can persist it without any problem.

Thanks again,
Somsack

Here is a sample method I use to write a Blob ...
Perhaps you'll find this useful ...


public void add(Form form, String sData ) {
byte[] data = sData.getBytes(); // convert String to byte[]
Session session = null;
boolean autoCommit = false;
try {
session = SessionHelper.openSession();
form.setRepresentation(Hibernate.createBlob(new byte[1]));
session.save(form);
session.flush();

autoCommit = session.connection().getAutoCommit();
session.connection().setAutoCommit(false);
session.refresh(form, LockMode.UPGRADE);

weblogic.jdbc.common.OracleBlob blob=(weblogic.jdbc.common.OracleBlob)form.getRepresentation();
//// oracle.sql.BLOB blob = (oracle.sql.BLOB)form.getRepresentation();
OutputStream os = blob.getBinaryOutputStream();
os.write(data);
os.close();
session.connection().commit();
} catch (HibernateException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
session.connection().setAutoCommit(autoCommit);
} catch (HibernateException e1) {
e1.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();
}
SessionHelper.close(session);
}
}