Hi Raffaelle, You're right. The current implementation can handle SQL standard intervals only if you provide them as bind values, not if they're contained in the database. As a matter of fact, Derby doesn't fully support such interval types.
Coincidentally, there had been a recent feature request in another thread: - https://github.com/jOOQ/jOOQ/issues/3009 - https://groups.google.com/forum/#!topic/jooq-user/u7iOUdgqMrY A new DSL.dateAdd() feature was added to jOOQ 3.3. You can then write: dateAdd(PERSONS.BIRTH, SCHEDULES.YEARS_TO_ADD, DatePart.YEAR) In the mean time, you'll probably have to resort to plain SQL. Cheers Lukas 2014-02-03 <[email protected]>: > Hi Lukas, > > thanks for your help and keep up with your great work! I'd like to use the > Interval API in order not to mess with CustomField, weird conversions, and > the like, but I think the Field.add() API isn't suitable for my use-case. I > have a table like this > > create table schedules ( > years_to_add int, months_to_add int, days_to_add int); > > > so must use fragments like the following to build my queries: > > where(dateAdd(Persons.BIRTH, Schedules.YEARS_TO_ADD)).le(userInput) > > > As far as I can tell, the > YearToMonth<http://www.jooq.org/javadoc/3.2.x/org/jooq/types/YearToMonth.html>ctor's > only accept static integers, not Field's. Is there any workaround or > something that I am missing? I'm actually having troubles in converting > back and forth between Timestamp and Date (Derby doesn't seem to like a > Date to be cast to a Timestamp, and I wonder how can I work around this) > > Il giorno lunedì 3 febbraio 2014 15:10:08 UTC+1, [email protected] ha > scritto: > >> I'm looking for a way to call the JDBC function >> timestampadd<http://db.apache.org/derby/docs/10.2/ref/rrefjdbc88908.html> in >> my queries built with jOOQ. The typical output SQL should look like: >> >> {fn timestampadd(SQL_TSI_YEAR, 5, CUSTOMER.BIRTH)} >> >> I implemented my solution by extending CustomField: >> >> public class JdbcTimestampAddField extends CustomField<Timestamp> { >> >> private final String interval; >> private final Field<Integer> count; >> private final Field<Timestamp> timestamp; >> >> public JdbcTimestampAddField(String interval, Field<Integer> count, >> Field<?> timestamp) { >> super("timestampadd", SQLDataType.TIMESTAMP); >> this.interval = interval; >> this.timestamp = timestamp.cast(SQLDataType.TIMESTAMP); >> this.count = count; >> } >> >> public JdbcTimestampAddField(String interval, Field<Integer> count, Date >> timestamp) { >> this(interval, count, val(timestamp)); >> } >> >> public JdbcTimestampAddField(String interval, int count, Field<?> >> timestamp) { >> this(interval, val(count), timestamp); >> } >> >> public JdbcTimestampAddField(String interval, int count, Date timestamp) >> { >> this(interval, count, val(timestamp)); >> } >> >> @Override >> public void toSQL(RenderContext context) { >> context.sql("{fn TIMESTAMPADD(" + interval + ", "); >> context.sql(context.render(count)); >> context.sql(", "); >> context.sql(context.render(timestamp)); >> context.sql(")}"); >> } >> >> @Override >> public void bind(BindContext context) throws DataAccessException { >> context.visit(count); >> context.visit(timestamp); >> } >> } >> >> >> and building my own DerbyDsl namespace: >> >> public class DerbyDsl { >> >> public static Field<Timestamp> timestampAdd(Field<Integer> yearsCount, >> Field<Integer> monthsCount, Field<Integer> daysCount, Field<?> timestamp) { >> return timestampAddDays(daysCount, timestampAddMonths(monthsCount, >> timestampAddYears(yearsCount, timestamp))); >> } >> >> public static Field<Timestamp> timestampAdd(int yearsCount, int >> monthsCount, int daysCount, Field<?> timestamp) { >> return timestampAdd(val(yearsCount), val(monthsCount), >> val(daysCount), timestamp); >> } >> >> public static Field<Timestamp> timestampAddYears(Field<Integer> count, >> Field<?> timestamp) { >> return new JdbcTimestampAddField("SQL_TSI_YEAR", count, timestamp); >> } >> >> public static Field<Timestamp> timestampAddMonths(Field<Integer> count, >> Field<?> timestamp) { >> return new JdbcTimestampAddField("SQL_TSI_MONTH", count, timestamp); >> } >> >> public static Field<Timestamp> timestampAddDays(Field<Integer> count, >> Field<?> timestamp) { >> return new JdbcTimestampAddField("SQL_TSI_DAY", count, timestamp); >> }} >> >> >> Is there a better way to solve this problem? Did I make any mistake in my >> implementation that pass my tests but are waiting in the shadows to screw >> everything up? >> > -- > 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/groups/opt_out. > -- 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/groups/opt_out.
