Writing Blobs using Hibernate doesn't work about pgjdbc-ng HOT 17 CLOSED

At first glance it seems I just need to unwrap any objects passed into the JDBC layer. Is this something I can test by creating a simple blob column in any Hibernate project? If you could attach a minimal test project that would be most awesome.

from pgjdbc-ng.

I've created a project here: https://github.com/andreak/hibernatetest/tree/hibernate-branch
hibernate-branch shows errors in hibernate
master uses EclipseLink and shows an error while reading a blob as well as writing a blob.

Run the tests in BlobTest separately (and in both branches) to see the errors.

Any feedback on resolving these errors makes me one step closer to switching jdbc-driver, and persistence-provider (I'm trying to switch to EclipseLink from Hibernate but I'm currently having issues with blobs I have to sort out first).

Note that in order to run the tests in EclipseLink you have to enabel a javaagent, se comment on class BlobTest for details.

Hope this helps improving the driver and thanks for looking at this!

from pgjdbc-ng.

Commits 9627c26 and 7f7ecea make the test cases you created pass. Can you checkout the latest develop branch and see if it works for you now?

from pgjdbc-ng.

Supercool!
I'm very exited you're looking into this, will check right now!

from pgjdbc-ng.

It works! Superthanks!

For some reason I have to override EclipseLink's PostgreSQLPlatform with my custom, overriding the method getObjectFromResultSet like this:

public class PostgreSQLPlatform extends org.eclipse.persistence.platform.database.PostgreSQLPlatform {

    @Override
    public Object getObjectFromResultSet(ResultSet resultSet, int columnNumber, int type, AbstractSession session) throws SQLException {
        if ((type == Types.BIGINT || type == Types.INTEGER) && resultSet.getMetaData().getColumnTypeName(columnNumber).equals("oid")) {
            return resultSet.getBlob(columnNumber);
        } else {
            return super.getObjectFromResultSet(resultSet, columnNumber, type, session);
        }
    }
}

It would be cool if this "just worked" without having to customize anything.

Without this customization I get this exception:

Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to java.sql.Blob
    at no.officenet.test.hibernatetest.model.FileRawData._persistence_set(FileRawData.java)
    at org.eclipse.persistence.internal.descriptors.PersistenceObjectAttributeAccessor.setAttributeValueInObject(PersistenceObjectAttributeAccessor.java:46)

Is there a good reason why this customization is necessary?

Thanks.

from pgjdbc-ng.

As you have tested for yourself, a blob in PostgreSQL is nothing more than an oid linking to a separate blob entity; so blob columns are basically storing an integer. Unfortunately there is no "standard" way of knowing which oid columns are referencing blobs and which are not. This means that when calling "getObject" it doesn't know to convert the Integer to a blob before returning it; getBlob has enough information to do the conversion.

If we settle on a type, maybe "blobId", that I could put into the driver to test for during "getObject" and do the conversion that would solve it; it could be made a per connection parameter . You'd have to create the type itself in your database and then create all of your blobs with that type, instead of oid. Would that work for you?

from pgjdbc-ng.

Hm, I'm not so keen on inventing "magic-types" for solving this. Having to test for column-name='oid' and type=INTEGER is better. BTW; Isn't an OID of type BIGINT, not INTEGER?

from pgjdbc-ng.

The problem is when you call "getObject" you can't just convert all "oid" and "integer" columns to blobs, since those are common names and somebody might use that name/type combo for something else. Also, blob ids are of type "oid" (a separate type from int4 in postgres); we should be checking for that instead. We could try to identify a fairly unambiguous type name like "blobid" to use. That would mean any column named "blobid" with type "oid" would be recognized as a blob.

The major problem here is that relying on a name means that people cannot select it from the database with any other name (e.g. "SELECT blobid as image FROM images") as that would kill the recognition. With so-called "magic-types" this would work fine as only the type is what we are recognizing.

from pgjdbc-ng.

Given my previous comment I went ahead and added the "magic-type" method; since it really is more useful. The driver defaults to looking for a oid alias type named "loid" that name can be customized by setting the custom property "blob.type" when calling connect.

To create the oid alias type use the following SQL (remembering that you can customize the name)

CREATE TYPE loid;

CREATE OR REPLACE FUNCTION loidin(cstring)
RETURNS loid AS
'oidin'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION loidout(loid)
RETURNS cstring AS
'oidout'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION loidrecv(internal)
RETURNS loid AS
'oidrecv'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION loidsend(loid)
RETURNS bytea AS
'oidsend'
LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE loid
(INPUT=loidin,
OUTPUT=loidout,
RECEIVE=loidrecv,
SEND=loidsend,
CATEGORY='N',
PREFERRED=true,
PASSEDBYVALUE, DEFAULT='',
INTERNALLENGTH=4, ALIGNMENT=int4, STORAGE=PLAIN);

CREATE CAST (oid as loid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (loid as oid) WITHOUT FUNCTION AS IMPLICIT;

Using the driver from the latest "develop" branch and the above SQL you should be able to create columns of type "loid" that auto-magically convert to Blobs when calling "getObject".

from pgjdbc-ng.

Ok, I may have misunderstood something, so just to clarify; I'm not suggesting converting all columns with name=OID to Blobs but with type=OID.
If I understand correctly only Blobs are of type OID in PG. Why not just test for that type in the driver and map it to Blob, with resultSet.getMetaData().getColumnTypeName(columnNumber).equals("oid") ? AFAIU this is not ambiguous and cannot mean anything else?

Thanks again for taking time to sort this out, it means a lot.

from pgjdbc-ng.

Unfortunately the OID type is used for a large number of things in PG, not just blobs. That is why I suggest using the "loid" type (which is basically a distinct OID type).

Curiously enough, since the driver can recognize any OID type or alias thereof, you can just set the property "blob.type" to "oid" in the driver connect parameters and it will work the way you are referencing.

from pgjdbc-ng.

Ah, I see.

Specifically I'd like the call in org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject():
type = metaData.getColumnType(columnNumber);
to return type=2004 (Types.BLOB) if the column-type is oid (despite the fact that oid might mean something else, in my db it doesn't), not type=4 which is Types.INTEGER.

Are you saying it will return Types.BLOB if I set the property blob.type=oid in the driver? If so, great! How to I do that?
I'm using Spring to wire up the DataSource:

    <bean id="myDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource">
        <property name="driverClassName" value="${infrastructure.datasource.driver}" />
        <property name="url" value="${infrastructure.datasource.url}" />
        <property name="username" value="${infrastructure.datasource.username}" />
        <property name="password" value="${infrastructure.datasource.password}" />
        <property name="minIdle" value="${infrastructure.datasource.minPoolSize}" />
        <property name="maxActive" value="${infrastructure.datasource.maxPoolSize}" />
        <property name="maxWait" value="${infrastructure.datasource.maxIdle}" />
    </bean>

Thanks!

from pgjdbc-ng.

FYI; I tried setting the blob.type=oid in the URL; jdbc:postgresql://localhost:5432/hibernatetest?blob.type=oid
and then as the value in connectionProperties, neither without luck:

    <bean id="myDataSource" class="org.apache.tomcat.dbcp.dbcp.BasicDataSource">
        <property name="driverClassName" value="${infrastructure.datasource.driver}" />
        <property name="url" value="${infrastructure.datasource.url}" />
        <property name="username" value="${infrastructure.datasource.username}" />
        <property name="password" value="${infrastructure.datasource.password}" />
        <property name="minIdle" value="${infrastructure.datasource.minPoolSize}" />
        <property name="maxActive" value="${infrastructure.datasource.maxPoolSize}" />
        <property name="maxWait" value="${infrastructure.datasource.maxIdle}" />
        <property name="connectionProperties" value="blob.type=oid"/>
    </bean>

Are you sure you have pushed your changes? I cannot find any references to the property blob.type in the pgjdbc-ng code...

from pgjdbc-ng.

My apologies, I had not pushed the required commits to github. Update and it should work with the URL parameter you show. If the connectionProperties you show are passed to java.sql.Driver.connect then that method should work as well.

from pgjdbc-ng.

Ha ha, it works! It's finally possible to use EclipseLink with Blobs and PG without any fuss.
Thanks a lot!

from pgjdbc-ng.

It would be cool if the "CREATE TYPE loid"-approach (which I admit is a more robust approach than mapping all oid-types to blobs) was documented someplace where it is easily found, together with the "Note; you can pass blob.type=oid to the driver for mapping all columns of type=oid to Blobs".
I'm sure you're making the life of many easier with you new driver. I will recommend it to anyone.

from pgjdbc-ng.

Yes I will be documenting it on the wiki pages. One thing the driver is still lacking is good documentation. This puts this issue to be so I am closing it as well. Glad you are finding it so useful!

from pgjdbc-ng.