Hello all,

I am trying to convert an older PG database where we stored GUIDs by
explicitly converting them to byte arrays before storing them 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 <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Silvio Bierman <sbierman (AT) jambo-software (DOT) com> wrote:
Since there are no type casts for uuid, the only way I can see is to
use a string and explicitly cast this string (type "unknown") to uuid.

java.sql.PreparedStatement stmt =
conn.prepareStatement("UPDATE tab SET uid = ?::uuid WHERE ...");
stmt.setString(1, "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11");
stmt.executeUpdate();

You would somehow have to convert your GUIDs to the format above.

Yours,
Laurenz Albe

Laurenz Albe wrote:
Hello Laurens,

Thanks for the reply. I was hoping that one of the external JDBC types
could automatically be mapped/cast to GUID.

Unfortunately, changing the SQL is not an option since the application
runs on multiple database backends including Oracle, SQLServer, MySQL
and PostgreSQL. The only back-end specific twist I can do is on the
generic PreparedStatement since for each applicable type all DB code
goes througgh an adapter call like

adapter.setXXX(PreparedStatement stmt,int idx,XXX value)

which in this case actually looks like

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

since we represent GUID values as Strings internally.

Can I define an implicit cast in PostgreSQL?

Kind regards,

Silvio Bierman