Confusion about behaviour of OTHER data type

[Lukas Eder]

Hello,

I've found a little glitch when running the following query:

        PreparedStatement s = connection.prepareStatement("select cast(? as other)");
        s.setObject(1, "10");
        ResultSet rs = s.executeQuery();
        rs.next();
        System.out.println(rs.getObject(1));

I would expect this to return me the same String "10" again (which is what HSQLDB does, for instance). Instead, I get the following Stacktrace:

org.h2.jdbc.JdbcSQLException: De-Serialisierung fehlgeschlagen, Grund: {1}
Deserialization failed, cause: "java.io.EOFException" [90027-163]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:158)
    at org.h2.util.Utils.deserialize(Utils.java:271)
    at org.h2.jdbc.JdbcConnection.convertToDefaultObject(JdbcConnection.java:1769)
    at org.h2.jdbc.JdbcResultSet.getObject(JdbcResultSet.java:465)
    at org.jooq.test.jOOQAbstractTest.testNamedParams(jOOQAbstractTest.java:9054)
    ...
Caused by: java.io.EOFException
    at java.io.ObjectInputStream$PeekInputStream.readFully(ObjectInputStream.java:2280)
    at java.io.ObjectInputStream$BlockDataInputStream.readShort(ObjectInputStream.java:2749)
    at java.io.ObjectInputStream.readStreamHeader(ObjectInputStream.java:779)
    at java.io.ObjectInputStream.<init>(ObjectInputStream.java:279)
    at org.h2.util.Utils.deserialize(Utils.java:267)
    ... 29 more

The problem only occurs when using ResultSet.getObject(). ResultSet.getString() correctly returns "10". I'm not sure, if this is a bug or if I have misunderstood something about H2's implementation of the "OTHER" data type:
http://www.h2database.com/html/datatypes.html#other_type

Cheers
Lukas

[Noel Grandin]

That way that H2 works, that's a valid error because the bytestream for the string "10" is not a valid serialised object.

Why would you want to cast to OTHER?

Lukas Eder wrote:

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/bbh98dYZrUMJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Lukas Eder]

> That way that H2 works, that's a valid error because the bytestream for the> string "10" is not a valid serialised object.

But then, the problem is in CAST(? AS OTHER), I guess? If "10"
cannotbe properly serialised, it might be better when the error occurs
atserialisation time, not at deserialisation time... Or maybe CAST
isnot the right function to convert "10" into the data type "OTHER"?

> Why would you want to cast to OTHER?

I'm playing around with various options of handling H2's strongtyping.
Sometimes, the type of a bind value is not known in Java code.Some
more weakly typed databases can handle that, but with H2 (as wellas
HSQLDB, Derby, DB2) casting is necessary. In my case, OTHER isprobably
a bad choice, though.

[Noel Grandin]

Hmm, still not quite understanding what you want to do.
If you don't know the type of the bind value, you should just use setObject().
H2 has a lot of internal convert logic to handle converting it to the
correct type.

[Lukas Eder]

Hello Noel

> Hmm, still not quite understanding what you want to do.

I'm trying to find a very generic way of dealing with the situation I
described. I'm doing this, because I'm developing jOOQ (www.jooq.org),
a product similar to JaQu. I keep running into corner-case situations
of bind values with unknown type like this one with

- DB2, Derby (very often)
- HSQLDB, H2 (some times)
- Ingres, Sybase SQL Anywhere (very rarely)

I have never encountered a similar problem with

- MySQL, Oracle, Postgres, SQLite, SQL Server, Sybase ASE

That's why I was playing around with H2's data type "OTHER". As I
said, casting to "OTHER" might not be the most appropriate solution.
I'll find a way, don't worry. This user group thread here was intended
to indicate and discuss the glitch I observed when serialising /
deserialising "10" as "OTHER", which I don't think is correct

> If you don't know the type of the bind value, you should just use setObject().
> H2 has a lot of internal convert logic to handle converting it to the
> correct type.

You're correct when it comes to simple queries, like "select ?". Since
I'm looking for a stable, generic solution though, I'm afraid I always
have to cast to some type. e.g. as for this query "(select ? \"y\"
from dual) union (select ? \"y\" from dual)"

For your reference, other corner-cases I have previously reported were
these ones:
http://code.google.com/p/h2database/issues/detail?id=329
http://code.google.com/p/h2database/issues/detail?id=330
http://code.google.com/p/h2database/issues/detail?id=331

Regards,
Lukas

[Thomas Mueller]

Hi,

    PreparedStatement s = conn.prepareStatement("select cast(? as other)");
    s.setObject(1, "10");

This sets the value as a String, the same as:

    PreparedStatement s = conn.prepareStatement("select cast('10' as other)");

What you probably want is:

    PreparedStatement s = conn.prepareStatement("select cast(? as other)");
    s.setObject(1, "10", Types.OTHER);

or simpler:

    PreparedStatement s = conn.prepareStatement("select ?");
    s.setObject(1, "10", Types.OTHER);

Regards,
Thomas 

[Lukas Eder]

Hi Thomas,

> What you probably want is:
>
>     PreparedStatement s = conn.prepareStatement("select cast(? as other)");
>     s.setObject(1, "10", Types.OTHER);
>
> or simpler:
>
>     PreparedStatement s = conn.prepareStatement("select ?");
>     s.setObject(1, "10", Types.OTHER);

That looks interesting. I'll check that.

Thanks!
Lukas

[Lukas Eder]

Hello Thomas,

> What you probably want is:
>
>     PreparedStatement s = conn.prepareStatement("select cast(? as other)");
>     s.setObject(1, "10", Types.OTHER);

Thank you very much!