You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by andiqo <no...@gmail.com> on 2009/03/15 21:53:44 UTC
postgres : storing binary data using OID
Dear all,
I am trying to persist large binary object using PostgreSQL database.
With this mapping, no problem but performance are not optimum for large
blobs:
// Object:
@Basic(fetch = FetchType.LAZY)
@Column(name = "DATA")
private byte[] data;
// Database:
CREATE TABLE SPLITBLOBPART (ID BIGSERIAL NOT NULL, DATA BYTEA, ..., PRIMARY
KEY (ID));
So I would like to use OIDs:
http://jdbc.postgresql.org/documentation/83/binary-data.html
I tried the following
// Object:
@Lob
@Basic(fetch = FetchType.LAZY)
@Column(name = "DATA", columnDefinition = "OID")
private byte[] data;
// Database:
CREATE TABLE SPLITBLOBPART (ID BIGSERIAL NOT NULL, DATA OID, ..., PRIMARY
KEY (ID)) WITH (OIDS=TRUE);
But I still get:
org.apache.openjpa.lib.jdbc.ReportingSQLException: ERREUR: la colonne « data
» est de type oid mais l'expression est de type bytea {prepstmnt 435456241
INSERT INTO SPLITBLOBPART (DATA, DATASIZE, ORDR, VERSION, PARENT_ID) VALUES
(?, ?, ?, ?, ?) [params=(byte[]) [B@773c550f, (int) 8000000, (int) 5, (int)
1, (long) 1]} [code=0, state=42804]
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:58)
at
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:983)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1533)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:220)
at
org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:113)
What is the best way to achieve persistence using OIDs with PostgreSQL and
OpenJPA?!
Best regards,
andiqo
--
View this message in context: http://n2.nabble.com/postgres-%3A-storing-binary-data-using-OID-tp2482851p2482851.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: postgres : storing binary data using OID
Posted by andiqo <no...@gmail.com>.
Thanks a lot for your help Milosz!
Miłosz Tylenda wrote:
>
> Hi Andiqo,
>
> That "compatible" property seems promising. I once used it when needed to
> tweak how String parameters are sent to PostgreSQL.
>
> I am not sure what you mean by "directly specify properties". If I want to
> specify a JDBC driver property, usually I add it to the database URL or
> configure the data source in an application server-specific way. If what
> you want
> is to set a property when OpenJPA runs outside an application server you
> can
> try the openjpa.ConnectionProperties property [1]. That should work when
> used
> in a persistence.xml file and also as a JVM property
> (-Dopenjpa.ConnectionProperties=...).
>
> Cheers,
> Milosz
>
> [1]
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#openjpa.ConnectionProperties
>
>
>> Hi Milosz,
>>
>> LOB support using InputStream looks great in OpenJpa. But I would like to
>> conform to JPA-2 specifications...
>>
>> Is there a way to directly specify JDBC connection properties with
>> OpenJPA,
>> as 'compatible' parameter could do the trick
>> (http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters)?!
>>
>> Thanks a lot!
>>
>> andiqo
>>
>>
>
>
>
--
View this message in context: http://n2.nabble.com/postgres-%3A-storing-binary-data-using-OID-tp2482851p2493727.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: postgres : storing binary data using OID
Posted by Miłosz Tylenda <mt...@o2.pl>.
Hi Andiqo,
That "compatible" property seems promising. I once used it when needed to
tweak how String parameters are sent to PostgreSQL.
I am not sure what you mean by "directly specify properties". If I want to
specify a JDBC driver property, usually I add it to the database URL or
configure the data source in an application server-specific way. If what you want
is to set a property when OpenJPA runs outside an application server you can
try the openjpa.ConnectionProperties property [1]. That should work when used
in a persistence.xml file and also as a JVM property
(-Dopenjpa.ConnectionProperties=...).
Cheers,
Milosz
[1]
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#openjpa.ConnectionProperties
> Hi Milosz,
>
> LOB support using InputStream looks great in OpenJpa. But I would like to
> conform to JPA-2 specifications...
>
> Is there a way to directly specify JDBC connection properties with OpenJPA,
> as 'compatible' parameter could do the trick
> (http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters)?!
>
> Thanks a lot!
>
> andiqo
>
>
Re: postgres : storing binary data using OID
Posted by andiqo <no...@gmail.com>.
Hi Milosz,
LOB support using InputStream looks great in OpenJpa. But I would like to
conform to JPA-2 specifications...
Is there a way to directly specify JDBC connection properties with OpenJPA,
as 'compatible' parameter could do the trick
(http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters)?!
Thanks a lot!
andiqo
Miłosz Tylenda wrote:
>
> Andiqo,
>
> You could try using the so called streaming LOB support. However, this
> needs using InsputStream instead of byte[] I think. There is a section on
> it in the manual [1]. Also, there is a test case which can help you too
> [2].
>
> Greetings,
> Milosz
>
> [1]
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_streamsupport
> [2]
> http://fisheye6.atlassian.com/browse/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/jdbc/meta/strats
>
>
>
>> Dear all,
>>
>> I am trying to persist large binary object using PostgreSQL database.
>> With this mapping, no problem but performance are not optimum for large
>> blobs:
>> // Object:
>> @Basic(fetch = FetchType.LAZY)
>> @Column(name = "DATA")
>> private byte[] data;
>> // Database:
>> CREATE TABLE SPLITBLOBPART (ID BIGSERIAL NOT NULL, DATA BYTEA, ...,
>> PRIMARY
>> KEY (ID));
>> So I would like to use OIDs:
>> http://jdbc.postgresql.org/documentation/83/binary-data.html
>> I tried the following
>> // Object:
>> @Lob
>> @Basic(fetch = FetchType.LAZY)
>> @Column(name = "DATA", columnDefinition = "OID")
>> private byte[] data;
>> // Database:
>> CREATE TABLE SPLITBLOBPART (ID BIGSERIAL NOT NULL, DATA OID, ..., PRIMARY
>> KEY (ID)) WITH (OIDS=TRUE);
>> But I still get:
>> org.apache.openjpa.lib.jdbc.ReportingSQLException: ERREUR: la colonne «
>> data
>> » est de type oid mais l'expression est de type bytea {prepstmnt
>> 435456241
>> INSERT INTO SPLITBLOBPART (DATA, DATASIZE, ORDR, VERSION, PARENT_ID)
>> VALUES
>> (?, ?, ?, ?, ?) [params=(byte[]) [B@773c550f, (int) 8000000, (int) 5,
>> (int)
>> 1, (long) 1]} [code=0, state=42804]
>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:58)
>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:983)
>> at
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
>> at
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1533)
>> at
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:220)
>> at
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:113)
>> What is the best way to achieve persistence using OIDs with PostgreSQL
>> and
>> OpenJPA?!
>> Best regards,
>> andiqo
>> --
>> View this message in context:
>> http://n2.nabble.com/postgres-%3A-storing-binary-data-using-OID-tp2482851p2482851.html
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://n2.nabble.com/postgres-%3A-storing-binary-data-using-OID-tp2482851p2488372.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: postgres : storing binary data using OID
Posted by Miłosz Tylenda <mt...@o2.pl>.
Andiqo,
You could try using the so called streaming LOB support. However, this needs using InsputStream instead of byte[] I think. There is a section on it in the manual [1]. Also, there is a test case which can help you too [2].
Greetings,
Milosz
[1] http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_streamsupport
[2] http://fisheye6.atlassian.com/browse/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/jdbc/meta/strats
> Dear all,
>
> I am trying to persist large binary object using PostgreSQL database.
> With this mapping, no problem but performance are not optimum for large
> blobs:
> // Object:
> @Basic(fetch = FetchType.LAZY)
> @Column(name = "DATA")
> private byte[] data;
> // Database:
> CREATE TABLE SPLITBLOBPART (ID BIGSERIAL NOT NULL, DATA BYTEA, ..., PRIMARY
> KEY (ID));
> So I would like to use OIDs:
> http://jdbc.postgresql.org/documentation/83/binary-data.html
> I tried the following
> // Object:
> @Lob
> @Basic(fetch = FetchType.LAZY)
> @Column(name = "DATA", columnDefinition = "OID")
> private byte[] data;
> // Database:
> CREATE TABLE SPLITBLOBPART (ID BIGSERIAL NOT NULL, DATA OID, ..., PRIMARY
> KEY (ID)) WITH (OIDS=TRUE);
> But I still get:
> org.apache.openjpa.lib.jdbc.ReportingSQLException: ERREUR: la colonne « data
> » est de type oid mais l'expression est de type bytea {prepstmnt 435456241
> INSERT INTO SPLITBLOBPART (DATA, DATASIZE, ORDR, VERSION, PARENT_ID) VALUES
> (?, ?, ?, ?, ?) [params=(byte[]) [B@773c550f, (int) 8000000, (int) 5, (int)
> 1, (long) 1]} [code=0, state=42804]
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:58)
> at
> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:983)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
> at
> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
> at
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1533)
> at
> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:220)
> at
> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:113)
> What is the best way to achieve persistence using OIDs with PostgreSQL and
> OpenJPA?!
> Best regards,
> andiqo
> --
> View this message in context: http://n2.nabble.com/postgres-%3A-storing-binary-data-using-OID-tp2482851p2482851.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>