Re: How does JOOQ handle writing to vendor specific data types?

[Lukas Eder]

Hello Jon

> What I'm confused on is how JOOQ handles writing to vendor specific data
> types like oracle's TIMESTAMPTZ.

jOOQ generally uses those data types that are available through JDBC.
In this case, the best matching data type is probably
java.sql.Timestamp.

> Also, if JOOQ does not handle a case where a column is a custom data type
> like oracle's TIMESTAMPTZ, how do I use JOOQ to query and oracle table to
> pull back the table's schema/metadata.

The general approach would be to use jOOQ's code generator as
described in the tutorial:
http://www.jooq.org/doc/2.6/manual/getting-started/tutorials/jooq-in-7-steps/

It will then generate the necessary meta data for type-safe data type
handling. Since you ultimately want to convert from / to jodatime
DateTime objects, you may want to consider the manual's sections about
custom data type conversion. This section explains what a converter
is:
http://www.jooq.org/doc/2.6/manual/sql-execution/fetching/data-type-conversion/

And this section explains how to let jOOQ's code generator apply
converters to generated meta data:
http://www.jooq.org/doc/2.6/manual/code-generation/custom-data-types/

Hope this helps,
Lukas

[Jon Inloes]

Thanks a lot for the response Lukas.

 I've looked over that documentation already.

I don't think I explained what I'm trying to do clearly. I'm trying to use JOOQ to dynamically write to a databases. That is, I don't know what database I'm using beforehand nor do I know the structure of the tables. Which is why I asked if was possible to use JOOQ to pull back database metadata easier than querying it through JDBC. I know schemaCrawler does this by supplying it with a connection. Pulling this data back will help me determine which type of converter to use. For example, if I have a jodatime object while writing to an oracle database, I want to see if I'm writing to a standard sql timestamp or an oracle specific TIMESTAMPTZ column. Code generation is not an option because the database being used is determined at run-time.

Thanks a lot again and you've created a great tool,
Jon

[Lukas Eder]

Hi Jon,

Thanks for the clarification. jOOQ iteself doesn't provide any
additional database meta-information other than what is known at
code-generation time. jOOQ-meta could be a slightly better match for
your needs, although it is designed only to serve jOOQ-codegen, so it
probably wouldn't be a very reliable database meta-information source
for you...

Nevertheless, jOOQ should probably wrap JDBC's DatabaseMetaData in a
"jOOQ way", i.e. a MetaData type with signatures as these:

- `List<org.jooq.Schema> schemas()`
- `List<org.jooq.Schema> schemas(... search criteria)`
- `List<org.jooq.Table> tables()`
- `List<org.jooq.Table> tables(... search criteria)`

This might even prove to be useful for jOOQ internally, when dealing
with plain SQL.

I have registered feature request #1968 for this
https://github.com/jOOQ/jOOQ/issues/1968

Cheers
Lukas

2012/11/13 Jon Inloes <rr2...@gmail.com>:

[Jon Inloes]

Thanks Lukas.

I look forward to your next release. You have created a very useful tool.

Cheers,
Jon

[Jon Inloes]

Hey,

Sorry to necro this but I was wondering if it was possible to overrider the functionality of the default converters.

For example with SQLServerDataType and DateTimeOffest, I'm wanting to convert the DateTimeOffset to a joda DateTime Object.

I like to leverage JOOQ's autoconversion but override one one type of the converter. I'd like to be able to do this in a generic fashion because I don't know the schema of the database I'm trying to access and it would be expensive to reiterate over the data to check for DateTimeOffset in the resultSet (JOOQ auto converts to Timestamp which loses the timezone information).

Thanks!

[Lukas Eder]

Hello,

> Sorry to necro this but I was wondering if it was possible to overrider the
> functionality of the default converters.
> For example with SQLServerDataType and DateTimeOffest, I'm wanting to
> convert the DateTimeOffset to a joda DateTime Object.

Unfortunately, this is currently not possible with jOOQ's code
generator. The only possible way to introduce converters is by using
matching table / column names, which you don't want to do...

I agree though, that it should be possible to configure this. I could
think of an enhancement to the code generation configuration's
<ForcedType/> definition. Something along these lines:

<complexType name="ForcedType">
<all>
<!-- The name of the type to be forced upon various artefacts -->
<element name="name" type="string" minOccurs="1" maxOccurs="1" />

<!--
A Java regular expression matching columns, parameters, attributes,
etc to be forced to have this type
-->
<element name="expressions" type="string" minOccurs="0" maxOccurs="1" />

<!--
A Java regular expression matching data types to be forced to
have this type
-->
<element name="types" type="string" minOccurs="0" maxOccurs="1" />
</all>
</complexType>

The added <types/> element would allow to supply a regular expression
such as VARCHAR(\(\d+\))? or in your case, DATETIMEOFFSET. As with
today's forced type feature, this would then refer to a converter
referenced from <name/>

Would that suit your needs?

I have registered #2352 to track this feature request:
https://github.com/jOOQ/jOOQ/issues/2352

> I like to leverage JOOQ's autoconversion but override one one type of the
> converter. I'd like to be able to do this in a generic fashion because I
> don't know the schema of the database I'm trying to access and it would be
> expensive to reiterate over the data to check for DateTimeOffset in the
> resultSet (JOOQ auto converts to Timestamp which loses the timezone
> information).

java.sql.Timestamp should be able to maintain timezone information.
Could this be a JDBC driver issue? Are you using jconn3 or jtds? How
would you properly deserialise DateTimeOffset through JDBC directly?
Using ResultSet.getString()?

Note, even if you'd apply the above converter, jOOQ would first use
ResultSet.getTimestamp() to deserialise DATETIMEOFFSET types. I'll
have to think about this when implementing #2352. It should be
possible to derive the "best" JDBC type from the converter,
directly...

Cheers
Lukas

[Jon Inloes]

I have been thinking about ways to implement the this custom conversion. Maybe you want to add a conversion strategy config to the factory?

For example, lets look at SQLServerDataType DateTimeOffset. You define that "datetimeoffset" should be converted to a java.sql.Timestamp. What if instead of doing the text search you do a class search to find a converter in a map. By default, the map will be populated with the default conversions defined in those DataType classes but if you want to override one you might edit the config with something like

.withConverter("microsoft.sql.DateTimeOffset", new Converter())

So when retrieving and object from the database, you can do the following to retrieve the converter: converterMap.get(resultSet.getObject().getClass().getCannonicalClassName())

 

java.sql.Timestamp should be able to maintain timezone information.
Could this be a JDBC driver issue? Are you using jconn3 or jtds? How
would you properly deserialise DateTimeOffset through JDBC directly?
Using ResultSet.getString()?

Note, even if you'd apply the above converter, jOOQ would first use
ResultSet.getTimestamp() to deserialise DATETIMEOFFSET types. I'll
have to think about this when implementing #2352. It should be
possible to derive the "best" JDBC type from the converter,
directly...

Well, each database vendor is different for a ResultSet.getTimestamp() call.

From what I noticed with Oracle TIMESTAMPTZ, you might get a string back 2013-1-1 13:30:1234567 +7:00 and if you call getTimestamp you get 2013-1-1 13:30:1234567 back as a timestamp losing the timezone information. You can preserve the timezone offset by parsing it out of the byte array returned to you in their oracle TIMESTAMPTZ object you get in a call to ResultSet.getObject().

So, to construct a joda DateTime object with and oracle TIMESTAMPTZ object you do something like:

Timestamp ts = rs.getTimestamp();

bytes[] objBytes = rs.getBytes();

// Parse bytes and compute offset in hours and minutes

DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(hours, minutes)

DateTime dt = new DateTime(ts.getTime(), dtz)

with DateTimeOffset it's a little simpler

DateTimeOffset dateTimeOffset = (DateTimeOffset) resultSet.getObject();

int minutesOffset = dateTimeOffset.getMinutesOffset()

// Compute hours offset

DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(hours, minutes)

DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(dateTimeOffset.getTimestamp(), dtz)

Parsing the string won't necessarily work for all cases because sometimes people change their formats and sometimes databases return broken formats for the date formatters you can use in java. 

One major reason for wanting to preserve the timezone offset if you reserialization. If you just take the timestamp and lets assume it's based off of UTC offset. Writing it as a string from a JodaTime Object might produce a string showing the incorrect timezone. ("2013-1-1T12:30:123Z" instead of something like "2013-1-1T5:30:123 -7:00" when you preserve the timezone). They might be internally equal times but they don't display the timezones properly.

Yea, I think it would be nice to allow someone to create a deserialization strategy kind of like what Jackson provides you for deserializing and serializing objects for json. 

Cheers
Lukas

[Lukas Eder]

2013/3/23 Jon Inloes <rr2...@gmail.com>:

Unfortunately, jOOQ's runtime won't know that the original data type
was a DATETIMEOFFSET. It'll only know of TIMESTAMP data types (the SQL
/ JDBC standard)

>> java.sql.Timestamp should be able to maintain timezone information.
>> Could this be a JDBC driver issue? Are you using jconn3 or jtds? How
>> would you properly deserialise DateTimeOffset through JDBC directly?
>> Using ResultSet.getString()?
>>
>> Note, even if you'd apply the above converter, jOOQ would first use
>> ResultSet.getTimestamp() to deserialise DATETIMEOFFSET types. I'll
>> have to think about this when implementing #2352. It should be
>> possible to derive the "best" JDBC type from the converter,
>> directly...
>
>
> Well, each database vendor is different for a ResultSet.getTimestamp() call.
> From what I noticed with Oracle TIMESTAMPTZ, you might get a string back
> 2013-1-1 13:30:1234567 +7:00 and if you call getTimestamp you get 2013-1-1
> 13:30:1234567 back as a timestamp losing the timezone information. You can
> preserve the timezone offset by parsing it out of the byte array returned to
> you in their oracle TIMESTAMPTZ object you get in a call to
> ResultSet.getObject().
>
> So, to construct a joda DateTime object with and oracle TIMESTAMPTZ object
> you do something like:
> Timestamp ts = rs.getTimestamp();
> bytes[] objBytes = rs.getBytes();
> // Parse bytes and compute offset in hours and minutes
> DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(hours, minutes)
> DateTime dt = new DateTime(ts.getTime(), dtz)

Nice to know, I wasn't aware of that.

> with DateTimeOffset it's a little simpler
> DateTimeOffset dateTimeOffset = (DateTimeOffset) resultSet.getObject();
> int minutesOffset = dateTimeOffset.getMinutesOffset()
> // Compute hours offset
> DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(hours, minutes)
> DateTimeZone dtz =
> DateTimeZone.withHoursAndMinutesOffset(dateTimeOffset.getTimestamp(), dtz)
>
> Parsing the string won't necessarily work for all cases because sometimes
> people change their formats and sometimes databases return broken formats
> for the date formatters you can use in java.
>
> One major reason for wanting to preserve the timezone offset if you
> reserialization. If you just take the timestamp and lets assume it's based
> off of UTC offset. Writing it as a string from a JodaTime Object might
> produce a string showing the incorrect timezone. ("2013-1-1T12:30:123Z"
> instead of something like "2013-1-1T5:30:123 -7:00" when you preserve the
> timezone). They might be internally equal times but they don't display the
> timezones properly.
>
> Yea, I think it would be nice to allow someone to create a deserialization
> strategy kind of like what Jackson provides you for deserializing and
> serializing objects for json.

OK. I hope I can come up with a generic solution for jOOQ 3.1. I think
that #2352 should provide enough flexibility for the task at hand. You
should then be able to define custom converters for any combination of
[column / parameter / attribute name pattern] and [data type name
pattern]. Converters define two Class<T> and Class<U> types between
which they are responsible for conversion. In your case, T=Object (or
DateTimeOffset) and U=DateTimeZone.

Clearly, I'll have to think through a couple of corner cases.

Note, I'm currently not sure if a generic ConverterFactory is a good
idea, for two reasons:
1. I'm never a big fan of custom factories registered in the Executor.
They're hard to implement, document, maintain, and understand. Often,
they are not well thought through. If there's a better solution, I'd
like to avoid the extra configuration.
2. A generic ConverterFactory will provide two-way conversion tools
between any U <-> T types. This conversion is unlikely to match the
actual types that should be returned by a Record.getValue() call, as
you're passing it a Field<T> argument. In other words, a generic
ConverterFactory might break typesafety.

Cheers
Lukas

[Jon Inloes]

Thanks for the reply Lukas. I understand where you're coming from. I was looking through the code a bit and figured I'd ask :)  

I think having some way of registering a converter for DateTImes with timezone would be a great feature since Java's internal Date implementation is so broken.

[Lukas Eder]

Hello,

#2352 (https://github.com/jOOQ/jOOQ/issues/2352) has been implemented and will be pushed to GitHub master with the next larger push. This will allow for enforcing type rewrites for all columns of a given type and/or a given name.

Cheers

Lukas

2013/3/25 Jon Inloes <rr2...@gmail.com>

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.