Обсуждение: Howto set UUID parameter for PGSQL 8.3rc1 / postgresql-8.3dev-602.jdbc3.jar

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

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

Silvio Bierman wrote:

> In my opinion at least one external type should be allowed to map
> implicitly to UUID

Try PreparedStatement.setObject(...,Types.OTHER) with the current
development driver

-O