That's what I get for trying to figure this out so late in the evening.
Thanks.
What I was thinking is that I defined dateField with a name
private final Field<LocalDate> dateField = DSL.field(DSL.name("jooqtest",
"dt"), dateType);
(or just "dt") so when the record.get(field) was executed it could look at
the record's field names (from the ResultSetMetaData) and the field's name
and make the match that way.
In any case I think we mostly (only?) use a RecordMapper so I guess the
read code will look something like
// outer loops elided...
switch(field.getDataType().getSqlType()) {
case Types.SMALL_INT:
case Types.INT:
map.put(field.getName(),
BigIntegerType.convert(record.getField(field)));
break;
case Types.DATE:
map.put(field.getName(),
LocalDateType.convert(record.getField(field)));
break;
...
case Types.ARRAY:
List values = (mumble)
switch(field.getDataType().getArrayDataType().getSqlType()) {
case SMALL_INT:
case INTEGER:
map.put(field.getName(), LocalDateType.convert(values));
break;
}
break;
...
// Types corresponding to UDT will need to be handled by looking at
additional information
where the (mumble) involves the ResultSet -> Record tools, unless there's
a more direct way to get this. I know I can't just use a map from sqltype
to datatype since many databases define additional datatypes so we would
have to check for null values and that's where we got into trouble before.
Better to be a little verbose so it's clear that when we add support for
new types we do it by adding a 'case' statement and a converter.
Now I'm wondering about the other side of this - I was thing that I could
go through the Param list, do the same getSqlType() branch, etc., and that
it would use the database metadata to determine the correct sqltype. Now
I'm thinking that the information won't be available since we're using
sql-based queries instead of queries built up like you did. I need to look
more closely at how we're doing it now since I know we're able to throw
data exceptions if someone is trying to convert a BigInteger into a SQL
type that can't handle it.
On Wed, Mar 21, 2018 at 2:13 AM, Lukas Eder <[email protected]> wrote:
> The problem is here:
>
> ResultQuery query = ctx.resultQuery("select dt from jooqtest
> limit 1");
>
> How would you expect jOOQ to know that your dt column inside a plain SQL
> string is actually your dateField? It doesn't and it can't. So, the query
> is run directly as is against JDBC, and without any type information
> available at compile time, the ResultSetMetaData type information is used -
> you get a java.sql.Date, and since you switch on that type (the one
> retrieved via ResultSetMetaData), you get the behaviour you're observing.
>
> This wouldn't happen, of course, if you had written:
>
> ctx.select(dt).from(table(name("jooqtest"))).limit(1).fetchOne();
>
> There's a feature request for allowing to pass type information to plain
> SQL ResultQuery types, but it hasn't been implemented yet:
> https://github.com/jOOQ/jOOQ/issues/4473
>
> The other option is the one you've mentioned further down, applying the
> converter on the Record that has been fetched without conversion.
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "jOOQ User Group" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/
> topic/jooq-user/mVAv6TQfApg/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
