Problem with PostgreSQL and @Lob byte[]

mamue · **Joined:** Tue Aug 31, 2010 9:41 am **Posts:** 5

Hi,

we have a problem with Hibernate 3.5.2 persisting the following entity:

Code:

import javax.persistence.*;
@Entity public class Foo {
    @Id   private int id;
    @Lob  private byte[] someBytes = new byte[] { 'b', 'a', 'r', };
}

With PostgreSQL 8.4 we get the following error:
SQL Error: 0, SQLState: 42804, "FEHLER: Spalte »someBytes« hat Typ oid, aber der Ausdruck hat Typ bytea"
[means: ERROR: Column »someBytes« has type oid, but the expression has type bytea]

Oracle 11.1, MS SQL-Server 2008 and HyperSQL 2.0 are working fine, there is no problem persisting instances of class Foo.

If we use the annotation "@Column(length = 1000000000)" instead of "@Lob", there is no error,
but table FOO is created in PostgreSQL DB with a bytea column instead of an oid column.

Is there a chance to use oid columns with PostgreSQL?
Our Java code must work with every database, therefore we don't want to add PostgreSQL specific workarounds.
But modifying the PostgreSQLDialect or setting special properties in the persistence-unit would be ok.

Thanks and regards
MaMue

nordborg · **Posted:** Tue Aug 31, 2010 1:36 pm

Steve blogged about this some time ago: http://in.relation.to/Bloggers/PostgreSQLAndBLOBs

I don't know if the information will help you or not since I have no experience with @Lob:s, etc.

mamue · **Joined:** Tue Aug 31, 2010 9:41 am **Posts:** 5

Thanks for the link http://in.relation.to/Bloggers/PostgreSQLAndBLOBs !

I tried the proposed "oid way" which uses the annotation @Lob and sets the property "hibernate.jdbc.use_streams_for_binary" to false in persistence.xml.
But this way I still get the same error
"FEHLER: Spalte »someBytes« hat Typ oid, aber der Ausdruck hat Typ bytea"
"ERROR: Column »someBytes« has type oid, but the expression has type bytea"

We prefer the "oid way" over the "bytea way" because the latter fails with Oracle if the length of byte[] exceeds 4000.

Here are the details:

My class Foo:

Code:

import javax.persistence.*;
@Entity public class Foo {
    @Id   private int id;
    @Lob  private byte[] someBytes = new byte[] { 'b', 'a', 'r', };
}

My persistence.xml:

Code:

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="PostgreSQL">
        <properties>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/postgres"/>
            <property name="hibernate.connection.username" value="postgres"/>
            <property name="hibernate.connection.password" value="postgres"/>
            <property name="hibernate.jdbc.use_streams_for_binary" value="false"/>
        </properties>
    </persistence-unit>
</persistence>

My main method:

Code:

    public static void main(String[] args) {
        Map<String, Object> overrides = new HashMap<String, Object>();
        overrides.put("hibernate.hbm2ddl.auto", "create"); 
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("PostgreSQL", overrides);
        EntityManager em = emf.createEntityManager();
        EntityTransaction trans = em.getTransaction();
        trans.begin();
        em.persist(new Foo());
        trans.commit();
        emf.close();
    }

The error:

Code:

13:42:31,852  WARN JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804
13:42:31,852 ERROR JDBCExceptionReporter:101 - Batch-Eintrag 0 insert into Foo (someBytes, id) values ('<stream of 3 bytes>', '0') wurde abgebrochen.  Rufen Sie 'getNextException' auf, um die Ursache zu erfahren.
13:42:31,852  WARN JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804
13:42:31,852 ERROR JDBCExceptionReporter:101 - FEHLER: Spalte »somebytes« hat Typ oid, aber der Ausdruck hat Typ bytea
  Hinweis: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung vornehmen.
  Position: 41

The created table:

Code:

CREATE TABLE foo
(
  id integer NOT NULL,
  somebytes oid,
  CONSTRAINT foo_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

hallmit · **Posted:** Wed Sep 01, 2010 9:06 am

maybe if you subclass the PostgreSQLDialect and override useInputStreamToInsertBlob() method to return true it'll work. I saw the Hibernate type for byte[] (with @Lob) is PrimitiveByteArrayBlobType and this class doesn't use hibernate.jdbc.use_streams_for_binary property to set the binary data in ResultSet.

hope this help.

mamue · **Joined:** Tue Aug 31, 2010 9:41 am **Posts:** 5

Thanks for the hint, but sorry, there is still the same error, probably because useInputStreamToInsertBlob() wasn't called:

Here comes the added line in persistence.xml:

Code:

And here is MyPostgreSQLDialect.java:

Code:

package mapping;
public class MyPostgreSQLDialect extends org.hibernate.dialect.PostgreSQLDialect {
    @Override
    public boolean useInputStreamToInsertBlob() {
        return true;
    }
    @Override
    protected void registerColumnType(int code, String name) {
        super.registerColumnType(code, name);
    }
}

A breakpoint at "super.registerColumnType(code, name);" was hit many times, therefore I assume that the added property in persistence.xml is active.
But a breakpoint at "return true" was never hit!
Also a breakpoint at org.hibernate.type.ByteArrayBlobType.set(PreparedStatement, Object, int, SessionImplementor) wasn't hit.
(This method contains a call to useInputStreamToInsertBlob().)

Here is the error output:

Code:

16:20:37,365  WARN JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804
16:20:37,381 ERROR JDBCExceptionReporter:101 - Batch-Eintrag 0 insert into "Foo" ("someBytes", "id") values ('<stream of 3 bytes>', '0') wurde abgebrochen.  Rufen Sie 'getNextException' auf, um die Ursache zu erfahren.
16:20:37,381  WARN JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42804
16:20:37,381 ERROR JDBCExceptionReporter:101 - FEHLER: Spalte »someBytes« hat Typ oid, aber der Ausdruck hat Typ bytea
  Hinweis: Sie müssen den Ausdruck umschreiben oder eine Typumwandlung vornehmen.
  Position: 47