I am trying to save a PDF binary to oracle as Blob,
here is my mapping

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.businessobject.proposal.ProposalDoc" table="FBB_PROPOSAL_DOC">


<composite-id>
<key-property column="PROPOARCH_ID" length="12" name="archivedProposalID" type="java.lang.Long"/>
<key-property column="PROPO_DOC_TYPE" length="1" name="documentType" type="java.lang.String"/>
</composite-id>


<property column="PROPO_DOC" length="4000" name="proposalPDFDoc" not-null="true" type="binary"/>

</class>
</hibernate-mapping>

/** @author Hibernate CodeGenerator */
public class ProposalDoc implements Serializable {

/** identifier field */
private Long archivedProposalID;

/** identifier field */
private String documentType;

/** persistent field */
private byte[] proposalPDFDoc;

/** full constructor */
public ProposalDoc(java.lang.Long archivedProposalID, java.lang.String documentType, byte[] proposalPDFDoc) {
this.archivedProposalID = archivedProposalID;
this.documentType = documentType;
this.proposalPDFDoc = proposalPDFDoc;
}

/** default constructor */
public ProposalDoc() {
}

public java.lang.Long getArchivedProposalID() {
return this.archivedProposalID;
}

public void setArchivedProposalID(java.lang.Long archivedProposalID) {
this.archivedProposalID = archivedProposalID;
}

public java.lang.String getDocumentType() {
return this.documentType;
}

public void setDocumentType(java.lang.String documentType) {
this.documentType = documentType;
}

public byte[] getProposalPDFDoc() {
return this.proposalPDFDoc;
}

public void setProposalPDFDoc(byte[] proposalPDFDoc) {
this.proposalPDFDoc = proposalPDFDoc;
}


}


When I tring to to insert a record, I got the exeption

Hibernate: insert into FBB_PROPOSAL_DOC (PROPO_DOC, PROPOARCH_ID, PROPO_DOC_TYPE) values (?, ?, ?)

net.sf.hibernate.JDBCException: Could not execute JDBC batch update

at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:125)

at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2311)

at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2261)

at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)

at com.sp.fbb.service.serviceimpl.BaseDaoHibernate.storeObject(BaseDaoHibernate.java:299)

at com.sp.fbb.service.serviceimpl.ProposalDaoHibernate.createProposalDoc(ProposalDaoHibernate.java:1337)

at com.sp.fbb.facade.ProposalFacade.generatePDF(ProposalFacade.java:1621)

at com.sp.fbb.ui.proposal.ProposalActions.createArchivedProposal(ProposalActions.java:647)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

....

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533)

at java.lang.Thread.run(Thread.java:536)

Caused by: java.sql.BatchUpdateException: ORA-01483: invalid length for DATE or NUMBER bind variable

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:430)

at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:2948)

at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)

at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:118)

... 54 more


I found in the web the reason to casue this is becaue there is limitaion using preparedStatement, the limited number of bytes is 2000 for Bolb.

How can I create a Bolb has more than 2000 bytes using Hibernate?

Thanks a lot

I made the change of the mapping to Blob

<hibernate-mapping>
<class name="com.sp.fbb.businessobject.proposal.ProposalDoc" table="FBB_PROPOSAL_DOC">


<composite-id>
<key-property column="PROPOARCH_ID" length="12" name="archivedProposalID" type="java.lang.Long"/>
<key-property column="PROPO_DOC_TYPE" length="1" name="documentType" type="java.lang.String"/>
</composite-id>


<property column="PROPO_DOC" name="proposalPDFDoc" not-null="true" type="blob"/>

</class>
</hibernate-mapping>

public class ProposalDoc implements Serializable {

/** identifier field */
private Long archivedProposalID;

/** identifier field */
private String documentType;

/** persistent field */
private java.sql.Blob proposalPDFDoc;

/** full constructor */
public ProposalDoc(java.lang.Long archivedProposalID, java.lang.String documentType, java.sql.Blob proposalPDFDoc) {
this.archivedProposalID = archivedProposalID;
this.documentType = documentType;
this.proposalPDFDoc = proposalPDFDoc;
}

/** default constructor */
public ProposalDoc() {
}

public java.lang.Long getArchivedProposalID() {
return this.archivedProposalID;
}

public void setArchivedProposalID(java.lang.Long archivedProposalID) {
this.archivedProposalID = archivedProposalID;
}

public java.lang.String getDocumentType() {
return this.documentType;
}

public void setDocumentType(java.lang.String documentType) {
this.documentType = documentType;
}

public java.sql.Blob getProposalPDFDoc() {
return this.proposalPDFDoc;
}

public void setProposalPDFDoc(java.sql.Blob proposalPDFDoc) {
this.proposalPDFDoc = proposalPDFDoc;
}

public String toString() {
return new ToStringBuilder(this)
.append("archivedProposalID", getArchivedProposalID())
.append("documentType", getDocumentType())
.toString();
}

public boolean equals(Object other) {
if ( !(other instanceof ProposalDoc) ) return false;
ProposalDoc castOther = (ProposalDoc) other;
return new EqualsBuilder()
.append(this.getArchivedProposalID(), castOther.getArchivedProposalID())
.append(this.getDocumentType(), castOther.getDocumentType())
.isEquals();
}

public int hashCode() {
return new HashCodeBuilder()
.append(getArchivedProposalID())
.append(getDocumentType())
.toHashCode();
}

}

here is my test


ProposalDaoHibernate dao = new ProposalDaoHibernate();

ProposalDoc proposalDoc = new ProposalDoc();
proposalDoc.setDocumentType("A");
proposalDoc.setArchivedProposalID(new Long(199));

Blob b=Hibernate.createBlob(new byte[2999]);

proposalDoc.setProposalPDFDoc(b);
dao.createProposalDoc(proposalDoc);

I got the following error message:


net.sf.hibernate.JDBCException: could not insert: [com.sp.fbb.businessobject.proposal.ProposalDoc#com.sp.fbb.businessobject.proposal.ProposalDoc@3650ed[archivedProposalID=199,documentType=A]]

at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:479)

at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:443)

at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:29)

at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2308)

at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2261)

a
.....
...
Caused by: java.sql.SQLException: operation not allowed: streams type cannot be used in batching at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)

at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:2833)

at net.sf.hibernate.impl.BatchingBatcher.addToBatch(BatchingBatcher.java:30)

at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:469)

... 25 more




I though I am using blob and it will use serBinaryStream to avoid the size limitation, but I don't have the error message if I use

Blob b=Hibernate.createBlob(new byte[99]); in my test case.

Please advise.

BTW, I am using Oracle 8i

Hello,

I had to fight with blobs today and this is how I got it working
- Oracle 8.1.6
- Weblogic 6.1 sp3


class: ignore the Auditable interface

public class Report implements Auditable {
private Long id;
private Long evaId;
private Blob data;
private AuditInfo auditInfo;

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public Long getEvaId() {
return evaId;
}

public void setEvaId(Long evaId) {
this.evaId = evaId;
}

public Blob getData() {
return data;
}

public void setData(Blob data) {
this.data = data;
}

public AuditInfo getAuditInfo() {
if (auditInfo == null) {
auditInfo = new AuditInfo();
}
return auditInfo;
}

public void setAuditInfo(AuditInfo auditInfo) {
this.auditInfo = auditInfo;
}

}


mapping:

<class
name="eu.cec.admin.epes.services.evaluation.Report"
table="EPES_RAPPORTS"
>

<id
name="id"
type="java.lang.Long"
column="ID"
>
<generator class="sequence">
<param name="sequence">epes_seq_rapport</param>
</generator>
</id>

<property name="evaId" type="java.lang.Long" column="EVA_ID" />

<property
name="data"
type="blob"
column="DATA"
/>

<property name="auditInfo" type="eu.cec.admin.epes.services.common.AuditInfoType">
<column name="DATE_MODIF"/>
<column name="DATE_CREAT"/>
<column name="USER_MODIF"/>
<column name="USER_CREAT"/>
</property>

</class>


Code: the trick is to insert a new row with a single byte in the blob; this will initialize the lob-locator in Oracle; once the lob-locator is created, you refetch the lob and fill it up with the data (which is in fact a pdf document passed as a byte stream); this solution is inspired on the clob example in the wiki-area.

Also notice that I had to cast the blob to (what I think) is the weblogic implementation; otherwise It didn't work (WL 6.1 sp3)

AppLogger.info("createReport");
checkParam("userContext", userContext);
checkParam("idEvaluation", idEvaluation);
checkParam("data", data);

Session session = HibernateUtil.currentSession(userContext.getLogin());

byte[] test = {1};
Report report = null;
Transaction tx = null;

try {
tx = session.beginTransaction();
report = new Report();
report.setEvaId(idEvaluation);
// create lob with minimal length to circumvent oracle Lob bug
report.setData(Hibernate.createBlob(test));
session.save(report);
// flush to the database to create the lob locator
session.flush();
// refresh the object
session.refresh(report, LockMode.UPGRADE);
// only way found, otherwise classcastException
weblogic.jdbc.rmi.SerialOracleBlob blob = (SerialOracleBlob) report.getData();
java.io.OutputStream pw = blob.getBinaryOutputStream();
//pw.write(data, 1, data.length);
pw.write(data);
AppLogger.debug("writing data with id " + report.getId() + ", size " + data.length );
pw.close();
tx.commit();

} catch (HibernateException e) {
if (tx != null) {
try {
tx.rollback();
} catch (HibernateException e1) {
AppLogger.warn("createReport: error while rolling back transaction");
}
}

throw new SystemException("createReport: " + e.getMessage());
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} catch (IOException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
} finally {
HibernateUtil.closeSession();
}

Hallo,

if you create a blob in a create table statement and you don't specifiy any
special storage attributes, the blob length is allways limited to 2000 character. Here is an example

CREATE TABLE TEST(
ID NUMBER(12),
TEST BLOB,

constraint PK_kalkdatei primary key (ID)
)
LOB (KALK) STORE AS SEGNAME (TABLESPACE testspace CHUNK 4096
PCTVERSION 5
NOCACHE LOGGING
STORAGE (MAXEXTENTS 5)
);

To insert or update the blob I use this code:




Hope this give you some hints

Sorry there is an error in the example, the correct version is:

Hello,

Thanks for your reply so I tried to figure things out for the lob storage; I don't want to start an Oracle discussion on this forum, but for the complete picture of this thread, here is some additional info and tests:

the blob will be limited by your storage clause in the lob storage clause and by the tablespace parameters (initial and next extent, pctincrease...) if you don't specifiy them in the lob storage clause; I noticed you have a maxextents of 5; suppose your initial and next extent are 400 bytes, you will have a maxextents-error when your lob reaches 2000 bytes for the sum of all the records in the table (these values are so small I can't imagine someone has ever created a table with these storage clauses). The lob storage clause is defined on the whole of the table, not for each record separately.

Here is a small test with the code which I supplied earlier and a pdf-file of 42424 bytes. Notice that the create table statement is not the ideal situation but only done for this test

CREATE TABLE EPES_RAPPORTS (
id NUMBER,
eva_id NUMBER,
data BLOB,
date_creat DATE DEFAULT SYSDATE,
date_modif DATE DEFAULT SYSDATE,
user_creat VARCHAR2(30) DEFAULT USER,
user_modif VARCHAR2(30) DEFAULT USER
) TABLESPACE misctab
STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0)
LOB(data) STORE AS (TABLESPACE misctab STORAGE (INITIAL 30K NEXT 30K PCTINCREASE 0 MAXEXTENTS 5));

This definition will allow 150K of storage for pdf-files with a total of 5 extents; each insert will use 2 extents because my file is 42K, wasting 18K for each insert!!

If I insert a pdf-file of 42424 bytes into this table, everything goes fine for the first two inserts, but at the third I get the (expected) error "ORA-01693: max # extents (5) reached in lob segment APP_EPES.SYS_LOB0000026855C00003$$"; this is ofcourse, done by the definition of the small extents, but also by the low value of the maxextents -parameter.

Hope it helps, Jan