For BLOB: Is there a special lobhandler for PostgreSQL

[joeserel]

or DefaultLobHandler is fine? I see there is a special handler for Oracle.

I tried DefaultLobHandler, but it gives me error when I try to save the object: SQL error shows that I am trying to insert an expression of bytea to a oid column.

The table is generated by using schemaexport. The code is as follows:

/**
* @return Returns the content.
*
* @hibernate.property
* column="content"
* type="org.springframework.orm.hibernate3.support.B lobByteArrayType"
* update="false"
* not-null="true"
*/
public byte[] getContent() {
return content;
}
/**
* @param content The content to set.
*/
public void setContent(byte[] content) {
this.content = content;
}


When using spring DefaultLobHandler, unit test failed:

[junit] [mytest] ERROR [main] JDBCExceptionReporter.logExceptions(72) | ERROR: column "content" is of type oid but expression is of type bytea


Again, the table is generated by schemaexport - looks like it set the BLOB column to type oid.

Any idea? Thanks.

[trisberg]

Do you have the generated DDL or could you go into psql and issue a \d 'tablename' command and post the results?

[joeserel]

create table blob_tb (
id int8 not null,
name varchar(256) not null,
createdt timestamp not null,
content oid not null,
size int8 not null,
primary key (id)
);

[joeserel]

I gave up on Blob, just use byte[] and it works fine.

There are two type of blob storage in postgresql: bytea and oid. Looks like hibernate schemaexport automatically uses oid based on the hbm, but spring's BlobArrayUserType uses bytea.

[Costin Leau]

This comes a little too late but FYI, we have moved our app from MySQL to PostgreSQL for a client and we experienced the annoying blob problem also. We have used byte[] at Java level along with bytea for PostgreSQL and everything worked without a problem.

[joeserel]

yep, costin, that's exactly what I did. bytea holds 1G data in table - that's more than enough for me.