New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

JDBC driver UUID setString() getString() doesn't appear to work #1133

Closed
Rob46 opened this Issue Feb 27, 2018 · 20 comments

Comments

Projects
None yet
5 participants
@Rob46

Rob46 commented Feb 27, 2018

When connecting via JDBC, it appears that I can't use getString() to get UUID columns as a string. Is there a way to do this without using getObject()? If not, can the driver be modified so that you can use setString() and getString() on UUID columns if the string is correctly formatted? This would greatly improve compatibility with other systems.

@jorsol

This comment has been minimized.

Contributor

jorsol commented Feb 27, 2018

Do you get any error?

This test suggest that it should work:

assertEquals(uuid.toString(), rs.getString(1));

@Rob46

This comment has been minimized.

Rob46 commented Feb 27, 2018

No error, just blank data. There are some comments on the web that seem to indicate that the problem either does or doesn't happen with prepared statements... from memory I think queries submitted as text SQL can get a UUID as string, but if I understood the post maybe if done as a prepared statement cannot... is that even possible? (sounds strange to me, and the posts were old)

Can you try doing the SELECT as a prepared statement with a bound query parameter? and both set and get as string?

@jorsol

This comment has been minimized.

Contributor

jorsol commented Feb 27, 2018

Driver version?

@Rob46

This comment has been minimized.

Rob46 commented Feb 27, 2018

Driver 42.2.1 on java8

@davecramer

This comment has been minimized.

Member

davecramer commented Feb 28, 2018

@Rob46
Do you have a test case showing this ?

@Rob46

This comment has been minimized.

Rob46 commented Feb 28, 2018

Not one I can easily share because its via CONNECT engine JDBC on MariaDB going to a private server with data restrictions. I have submitted something on MariaDB connect side of things, but I am 99% sure they are doing a prepared (or not) statement and the datatype on "our" end is VARCHAR to it would translate to a getString() in JDBC. I could work up a stripped down test case, but it wouldn't be easy.

@jorsol

This comment has been minimized.

Contributor

jorsol commented Feb 28, 2018

That it's a a weird scenario, without a test case it's difficult to track the problem. If you test the getString() isolated on a UUID column it should works as shown in the UUIDTest.java, so is possible that the issue is from the CONNECT engine side.

@davecramer

This comment has been minimized.

Member

davecramer commented Feb 28, 2018

@Rob46 well if you can provide a small test case which exhibits the problem then we can work on it.

@Rob46

This comment has been minimized.

Rob46 commented Feb 28, 2018

Great I'll work on that and get back to you.

@Rob46

This comment has been minimized.

Rob46 commented Mar 7, 2018

Regarding the test case (pgjdbc/pgjdbc/src/test/java/org/postgresql/test/jdbc4/UUIDTest.java) if you test the SELECT of a UUID with a

con.prepareStatement("SELECT id FROM uuidtest WHERE b = ?")

... possibly even without the WHERE clause, getString() is blank.

so the
stmt.executeQuery("SELECT id FROM uuidtest");
produces different results than the equivalent prepareStatement
which I am sure is not the intention.

@jorsol

This comment has been minimized.

Contributor

jorsol commented Mar 8, 2018

@Rob46 it's not clear what do you mean, could you please send a test?
This works for me:

  @Test
  public void testUUIDStringPreparedStatement() throws SQLException {
    String uuid = "0dcdf03a-058c-4fa3-b210-8385cb6810d5";
    try (PreparedStatement ps = con.prepareStatement("INSERT INTO uuidtest VALUES (?)")) {
      ps.setObject(1, uuid, Types.OTHER);
      ps.executeUpdate();
    }

    try (PreparedStatement ps = con.prepareStatement("SELECT id FROM uuidtest WHERE id = ?::uuid")) {
      ps.setString(1, uuid);
      try (ResultSet rs = ps.executeQuery()) {
        assertTrue(rs.next());
        assertEquals(uuid, rs.getString(1));
      }
    }
  }
@Buggynours

This comment has been minimized.

Buggynours commented Mar 9, 2018

I can confirm that using the PostgreSQL JDBC driver 42.2.1 getString returns null for a UUID column and that trying to set it by setString fails with a message saying something like:

{noformat}
org.postgresql.util.PSQLException: ERROR:column "id" is of type uuid but the expression is of type character varying
hint: You should rewrite the expression or apply to it a transformation of type.
{noformat}

(translated from French)

This is bad because, even it is easy to get the UUID value by getObject and toString and to set it by setObject of a UUID Java object, it requires CONNECT to be updated.

Indeed, when reading, the remote column type can be retrieved and when it is 1111 a special function must be called. However, the local column type being CHAR(36) or VARCHAR(36), CONNECT has to get the type of the remote column to know if it must call a specialized function. This is not trivial and could be avoided if the JDBC driver was fixed.

@davecramer

This comment has been minimized.

Member

davecramer commented Mar 9, 2018

@Buggynours as you can see from @jorsol example above this works. Can you provide code that does not work ?

@Rob46

This comment has been minimized.

Rob46 commented Mar 9, 2018

I think it would be desirable that rather than this: ps.setObject(1, uuid, Types.OTHER);
you could simply do this: ps.setString(1, uuid);
and similarly on query rather than con.prepareStatement("SELECT id FROM uuidtest WHERE id = ?::uuid")
this would be more compatible: con.prepareStatement("SELECT id FROM uuidtest WHERE id = ?")
In short, an implicit cast of uuid to/from string in both directions (set/get/query)... unless I'm missing something.

@davecramer

This comment has been minimized.

Member

davecramer commented Mar 9, 2018

I can see your point, but the extended protocol https://www.postgresql.org/docs/current/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY demands that we bind the parameters to the correct types. uuid is not a string in the backend, and JDBC did not contemplate the notion of UUID types so we are stuck with using this work around.

@Rob46

This comment has been minimized.

Rob46 commented Mar 9, 2018

At the JDBC driver level (b4 the backend sees it), would it not be possible, since it knows the column is a UUID type, to accept getString and setString and then internally call the setObject method? Essentially for the special case of column type = uuid then setString and getString are cover methods for setObject and getObject. Is that possible?

@davecramer

This comment has been minimized.

Member

davecramer commented Mar 9, 2018

Well that will open us up to implementing this for every other non supported type using setString.

The spec has a notion of how to deal with this. Why not use it?

@jorsol

This comment has been minimized.

Contributor

jorsol commented Mar 9, 2018

I see the issue now, well another reasonable workaround is to set the connection property stringType=unspecified

Specify the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to VARCHAR (the default), such parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified, parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt().

@vlsi vlsi closed this in 5827858 Mar 9, 2018

@vlsi vlsi added this to the 42.2.2 milestone Mar 9, 2018

@Buggynours

This comment has been minimized.

Buggynours commented Mar 10, 2018

For information: the same functions (getString or setString) used with a prepared statement work with a UUID column when called from a Java client. However, the same program fail when these functions are called from a C++ application via JNI.

@davecramer

This comment has been minimized.

Member

davecramer commented Mar 10, 2018

@Buggynours interesting. Do you have any idea what is going on there?

vlsi added a commit to vlsi/pgjdbc that referenced this issue Mar 10, 2018

tests: UUID vs setString test
By default, setString sends 'varchar' datatype, and PostgreSQL might result in
<<ERROR: column "id" is of type uuid but expression is of type character varying>>

There's stringType option that enables sending strings as UNSPECIFIED type to overcome that error.

fixes pgjdbc#1133

rhavermans added a commit to bolcom/pgjdbc that referenced this issue Jul 13, 2018

tests: UUID vs setString test
By default, setString sends 'varchar' datatype, and PostgreSQL might result in
<<ERROR: column "id" is of type uuid but expression is of type character varying>>

There's stringType option that enables sending strings as UNSPECIFIED type to overcome that error.

fixes pgjdbc#1133

rhavermans added a commit to bolcom/pgjdbc that referenced this issue Jul 13, 2018

tests: UUID vs setString test
By default, setString sends 'varchar' datatype, and PostgreSQL might result in
<<ERROR: column "id" is of type uuid but expression is of type character varying>>

There's stringType option that enables sending strings as UNSPECIFIED type to overcome that error.

fixes pgjdbc#1133
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment