Hosting Provided By

Re: [JDBC] Howto set UUID parameter for PGSQL 8.3rc1 / postgresql-8.3dev-602.jdbc3.jar

From: Silvio Bierman <sbierman(at)jambo-software.com>
Date: Mon Jan 14 2008 - 12:08:11 EST

Silvio Bierman wrote:
> Hello all,
>
> I am trying to convert an older PG database where we stored GUID keys
> by explicitly converting them to byte arrays before storing them in
> bytea columns in the DB to a database where we use the new UUID type
> supported by PG 8.3.
>
> I am having trouble setting such a value through a JDBC
> PreparedStatement because any stmt.setXXX(i,...) I have tried results
> in an error about a mismatch between type UUID and XXX.
>
> How can I get my PreparedStatement to work correctly?
>
> Kind regards,
>
> Silvio Bierman
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

Someone at comp.databases.postgresql suggested I change the SQL to do an explicit cast from string to UUID. Unfortunately (well, in this case anyway) we use the same code and SQL strings for Orcacle, MSSQLServer, MySQl, MaxDB/SapDB and PostgreSQL so this is not a viable solution. We do have a backend specific adapter class so there is a call

adapter.setGUID(PreparedStatement stmt,int idx,String guidValue)

that I get to implement to accomodate for PG UUIDs but there is no way to tweak the SQL, the statement is already prepared.

In my opinion at least one external type should be allowed to map implicitly to UUID just as the SQLServer driver allows the mapping of an adequately formatted string to a uniqeuidentifier (=uuid) column. PG allows this internally, I just need support for this through the JDBC driver.

Any chance this will be allowed? If not I will need to continue to use bytea as my GUID representation.

Kind regards,

Silvio Bierman

---------------------------(end of broadcast)---------------------------

TIP 4: Have you searched our list archives?

http://archives.postgresql.org Received on Mon Jan 14 12:10:28 2008

Do you need help?

This message: [ Message body ]
Next message: Jonathan Mast: "[JDBC] Table (or Column?) level permissions"
Previous message: Guillaume Cottenceau: "Re: [JDBC] setQueryTimeout"
In reply to: Silvio Bierman: "[JDBC] Howto set UUID parameter for PGSQL 8.3rc1 / postgresql-8.3dev-602.jdbc3.jar"
Next in thread: Oliver Jowett: "Re: [JDBC] Howto set UUID parameter for PGSQL 8.3rc1 / postgresql-8.3dev-602.jdbc3.jar"
Reply: Oliver Jowett: "Re: [JDBC] Howto set UUID parameter for PGSQL 8.3rc1 / postgresql-8.3dev-602.jdbc3.jar"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Wed Jun 18 2008 - 23:42:45 EDT