You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Hal Hildebrand <ha...@me.com> on 2015/03/28 00:28:41 UTC
OpenJPA PostgreSQL UUID column
I’m trying to use the native UUID type for PostgreSQL in my OpenJPA model. I’m using OpenJPA 2.3.0 and at the current level of the PostgreSQL JDBC driver.
I’ve tried to accomplish this several times and I’ve gotten a bit further this time. I created a ValueHolder (see attached file) and it does what I believe is the correct setup for this type. I use this as the strategy for the column - i.e. :
@Strategy("com.chiralbehaviors.CoRE.openjpa.UuidValueHandler")
I’ve validated via breakpoint that the UuidValueHolder is indeed called in setup and when converting values. I’m at the point of trying to do an insert, and regardless of whether I use an actual UUID as the return mapping (i.e. no transform) or a string (UUID.toString()) I get the same error:
ERROR: column "id" is of type uuid but expression is of type bytea
Hint: You will need to rewrite or cast the expression.
Position: 93 {prepstmnt 1848199707
INSERT INTO ruleform.agency (id, notes, update_date, description, name,
updated_by)
VALUES (?, ?, ?, ?, ?, ?)
[params=(byte[]) [B@9ac8b5b, (null) null, (Timestamp) 2015-03-27 15:20:05.921, (null) null, (String) CoRE, (byte[]) [B@15b17c95]} [code=0, state=42804]
I believe that this error comes from the postgres jdbc driver, not openJPA. I’ve even tried using PGObject, which surprisingly works just fine, but throws the same error.
In my google searches, this has come across as a typical problem, and I’ve only found solutions for this (hypothesized) for Toplink and Hibernate. The one thing I could find suggested writing the ValueHolder and that does work up until the point I try to actually insert in JDBC land.
Note that the UUID is being passed as a byte array.
So my question is, am I doing anything incorrect? Missing something? From the error message, it would appear postgresql wants me to cast the result, but I’m not sure how to accomplish that in OpenJPA. It could simply be a red herring.
Any help appreciated, as I simply can’t find a working solution via Der Google
-Hal
Re: OpenJPA PostgreSQL UUID column
Posted by Hal Hildebrand <ha...@me.com>.
Ah, no I wasn’t. Thanks for this solution ;)
-Hal
> On Oct 28, 2015, at 12:13 AM, rishabhtulsian <ri...@gmail.com> wrote:
>
> Hal, were you able to resolve this? I am trying to achieve the same.
>
> From what I have read the Postgresql driver support it and for it to work
> with a Prepared Statement you need to create a PGobject and set its type to
> "uuid" and value to the uuid string. And a setObject on prepared statement
> would work. So the 'toDataStoreValue' method should look like this
> if (object instanceof UUID) {
> PGobject pg = new PGobject();
> pg.setType("uuid");
> try {
> pg.setValue(object.toString());
> } catch (SQLException e) {
> logger.error("Failed to convert value: " + object, e);
> }
> return pg;
> }
> return null;
>
> But, OpenJPA translates the column type from Types.OTHER to Types.BLOB after
> calling UuidValueHandler.map
> Thats because DBDictionary.getJDBCType defaults to returning Types.BLOB as a
> result the setBlobObject is called on PreparedStatement.
>
> My solution was to provide a custom DBDictionary which returns Types.OTHER
> for JavaTypes.PC and in UuidValueHandler.map set the java type on the column
> to JavaTypes.PC and it worked.
>
>
>
> --
> View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-PostgreSQL-UUID-column-tp7587837p7588657.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: OpenJPA PostgreSQL UUID column
Posted by rishabhtulsian <ri...@gmail.com>.
Hal, were you able to resolve this? I am trying to achieve the same.
>From what I have read the Postgresql driver support it and for it to work
with a Prepared Statement you need to create a PGobject and set its type to
"uuid" and value to the uuid string. And a setObject on prepared statement
would work. So the 'toDataStoreValue' method should look like this
if (object instanceof UUID) {
PGobject pg = new PGobject();
pg.setType("uuid");
try {
pg.setValue(object.toString());
} catch (SQLException e) {
logger.error("Failed to convert value: " + object, e);
}
return pg;
}
return null;
But, OpenJPA translates the column type from Types.OTHER to Types.BLOB after
calling UuidValueHandler.map
Thats because DBDictionary.getJDBCType defaults to returning Types.BLOB as a
result the setBlobObject is called on PreparedStatement.
My solution was to provide a custom DBDictionary which returns Types.OTHER
for JavaTypes.PC and in UuidValueHandler.map set the java type on the column
to JavaTypes.PC and it worked.
--
View this message in context: http://openjpa.208410.n2.nabble.com/OpenJPA-PostgreSQL-UUID-column-tp7587837p7588657.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: OpenJPA PostgreSQL UUID column
Posted by Hal Hildebrand <ha...@me.com>.
Thanks. It’s a bit weird, but I’ll figure it out eventually. And will definitely update and contribute what I find.
-Hal
> On Mar 31, 2015, at 6:51 AM, Kevin Sutter <kw...@gmail.com> wrote:
>
> Hi Hal,
> You said you've been googling this problem, so my suggestions might have
> already been tried (and failed)...
>
> The use of a ValueHandler sounds like the proper approach for supporting
> the native UUID type. OpenJPA provides a couple of ValueHandlers for
> normal use and for test purposes. Have you tried running the associated
> junits to see how the current ValueHandlers are used? That's how I would
> debug this problem. Look at the processing that currently works and then
> compare that to the path that is not working.
>
> Since ValueHandlers are verified by OpenJPA's test bucket, I would guess
> there is some slight issue with your specific UuidValueHandler. I took a
> quick look, but nothing jumped out at me. Other than your toDataStoreValue
> and toObjectValue look a little light. But, maybe you are just trying to
> get the basic flow to work first?
>
> Based on this exercise, if you find something that should be beefed up in
> the OpenJPA test suite, please do offer to contribute your findings.
>
> Thanks, Kevin
>
> On Fri, Mar 27, 2015 at 6:28 PM, Hal Hildebrand <ha...@me.com>
> wrote:
>
>>
>>
>> I’m trying to use the native UUID type for PostgreSQL in my OpenJPA
>> model. I’m using OpenJPA 2.3.0 and at the current level of the PostgreSQL
>> JDBC driver.
>>
>> I’ve tried to accomplish this several times and I’ve gotten a bit further
>> this time. I created a ValueHolder (see attached file) and it does what I
>> believe is the correct setup for this type. I use this as the strategy for
>> the column - i.e. :
>>
>> @Strategy("com.chiralbehaviors.CoRE.openjpa.UuidValueHandler")
>>
>> I’ve validated via breakpoint that the UuidValueHolder is indeed called in
>> setup and when converting values. I’m at the point of trying to do an
>> insert, and regardless of whether I use an actual UUID as the return
>> mapping (i.e. no transform) or a string (UUID.toString()) I get the same
>> error:
>>
>> ERROR: column "id" is of type uuid but expression is of type bytea
>> Hint: You will need to rewrite or cast the expression.
>> Position: 93 {prepstmnt 1848199707
>> INSERT INTO ruleform.agency (id, notes, update_date, description, name,
>> updated_by)
>> VALUES (?, ?, ?, ?, ?, ?)
>> [params=(byte[]) [B@9ac8b5b, (null) null, (Timestamp) 2015-03-27
>> 15:20:05.921, (null) null, (String) CoRE, (byte[]) [B@15b17c95]} [code=0,
>> state=42804]
>>
>>
>> I believe that this error comes from the postgres jdbc driver, not
>> openJPA. I’ve even tried using PGObject, which surprisingly works just
>> fine, but throws the same error.
>>
>> In my google searches, this has come across as a typical problem, and I’ve
>> only found solutions for this (hypothesized) for Toplink and Hibernate.
>> The one thing I could find suggested writing the ValueHolder and that does
>> work up until the point I try to actually insert in JDBC land.
>>
>> Note that the UUID is being passed as a byte array.
>>
>> So my question is, am I doing anything incorrect? Missing something?
>> From the error message, it would appear postgresql wants me to cast the
>> result, but I’m not sure how to accomplish that in OpenJPA. It could
>> simply be a red herring.
>>
>> Any help appreciated, as I simply can’t find a working solution via Der
>> Google
>>
>> -Hal
>>
>>
Re: OpenJPA PostgreSQL UUID column
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Hal,
You said you've been googling this problem, so my suggestions might have
already been tried (and failed)...
The use of a ValueHandler sounds like the proper approach for supporting
the native UUID type. OpenJPA provides a couple of ValueHandlers for
normal use and for test purposes. Have you tried running the associated
junits to see how the current ValueHandlers are used? That's how I would
debug this problem. Look at the processing that currently works and then
compare that to the path that is not working.
Since ValueHandlers are verified by OpenJPA's test bucket, I would guess
there is some slight issue with your specific UuidValueHandler. I took a
quick look, but nothing jumped out at me. Other than your toDataStoreValue
and toObjectValue look a little light. But, maybe you are just trying to
get the basic flow to work first?
Based on this exercise, if you find something that should be beefed up in
the OpenJPA test suite, please do offer to contribute your findings.
Thanks, Kevin
On Fri, Mar 27, 2015 at 6:28 PM, Hal Hildebrand <ha...@me.com>
wrote:
>
>
> I’m trying to use the native UUID type for PostgreSQL in my OpenJPA
> model. I’m using OpenJPA 2.3.0 and at the current level of the PostgreSQL
> JDBC driver.
>
> I’ve tried to accomplish this several times and I’ve gotten a bit further
> this time. I created a ValueHolder (see attached file) and it does what I
> believe is the correct setup for this type. I use this as the strategy for
> the column - i.e. :
>
> @Strategy("com.chiralbehaviors.CoRE.openjpa.UuidValueHandler")
>
> I’ve validated via breakpoint that the UuidValueHolder is indeed called in
> setup and when converting values. I’m at the point of trying to do an
> insert, and regardless of whether I use an actual UUID as the return
> mapping (i.e. no transform) or a string (UUID.toString()) I get the same
> error:
>
> ERROR: column "id" is of type uuid but expression is of type bytea
> Hint: You will need to rewrite or cast the expression.
> Position: 93 {prepstmnt 1848199707
> INSERT INTO ruleform.agency (id, notes, update_date, description, name,
> updated_by)
> VALUES (?, ?, ?, ?, ?, ?)
> [params=(byte[]) [B@9ac8b5b, (null) null, (Timestamp) 2015-03-27
> 15:20:05.921, (null) null, (String) CoRE, (byte[]) [B@15b17c95]} [code=0,
> state=42804]
>
>
> I believe that this error comes from the postgres jdbc driver, not
> openJPA. I’ve even tried using PGObject, which surprisingly works just
> fine, but throws the same error.
>
> In my google searches, this has come across as a typical problem, and I’ve
> only found solutions for this (hypothesized) for Toplink and Hibernate.
> The one thing I could find suggested writing the ValueHolder and that does
> work up until the point I try to actually insert in JDBC land.
>
> Note that the UUID is being passed as a byte array.
>
> So my question is, am I doing anything incorrect? Missing something?
> From the error message, it would appear postgresql wants me to cast the
> result, but I’m not sure how to accomplish that in OpenJPA. It could
> simply be a red herring.
>
> Any help appreciated, as I simply can’t find a working solution via Der
> Google
>
> -Hal
>
>