This is how i contructs a sql with the help of jooq, just a part of it
Field<Timestamp> maxField =
Factory.decode().when(histTime.gt(noteTime), histTime).otherwise(noteTime);
Field<DayToSecond> dateDiff = Factory.timestampDiff(startTime,
maxField);
if ("More than 2H 30mins Ago".equals(categoryFilterValue)) {
catCond = maxField.isNull().or(
dateDiff.gt(new DayToSecond(0, 0, 150)).or(dateDiff.lt(new
DayToSecond(0, 0, 0))));
} else if ("1H 30mins Ago".equals(categoryFilterValue)) {
catCond = dateDiff.le(new DayToSecond(0, 0, 90));
} else if ("2H Ago".equals(categoryFilterValue)) {
catCond = dateDiff.le(new DayToSecond(0, 0, 120));
} else if ("2H 30mins Ago".equals(categoryFilterValue)) {
catCond = dateDiff.le(new DayToSecond(0, 0, 150));
}
This is my call to jdbc
Factory create = new Factory(this.dataSource, sqlDialect);
Select clause = ReportQueryUtil.generateChartSql(reportForm, create);
this.jdbcTemplate.query(clause.getSQL(),
clause.getBindValues().toArray(), mapper = new ReportRowMapper(
metaDataSet));
return mapper.getResult();
As you can see above, I have a utility class which is ReportQueryUtil and
this uses JOOQ API. The code snippet in the first one is the part that
construct the DayToSecond interval as part of the HAVING Clause.
What I understand is it could be the Sprint JDBCTemplate that has the issue
and not JOOQ right?
I will delve into that aspect and see what will i find and inform you. I
will also be waitinf or your further help. THANKS!
On Thursday, December 6, 2012 8:30:50 PM UTC+8, Lukas Eder wrote:
>
> Hello,
>
> jOOQ uses its own internal data types to model SQL INTERVAL types
> where applicable, as JDBC is missing formal support for such types.
> These data types are probably not compatible with what Spring's
> JDBCTemplate expects. Could you please provide me with a minimal
> necessary set of code artefacts to reproduce this issue? This set
> should include:
>
> - Some SQL Server DDL showing the involved SQL data types of the
> tables in your query
> - The generated artefacts thereof
> - A minimal Java snippet showing how you compose the jOOQ query, and
> how you hand over SQL and bind values to Spring
>
> Cheers
> Lukas
>
>
> 2012/12/6 <[email protected] <javascript:>>:
> > I encountered this scenario when I use SQL Server but not in MySQL. It
> seems
> > that SQL Server driver doesnt know how to convert DayToSecond to
> JavaObject.
> > But its weird, it is working in mysql database.
> > I am using JDCBTemplate as sql executor for jooq returned sql. I use
> JOOQ
> > primarily as SQL Builder and not executor.
> > Any assistance would be very much appreciated!
> >
> > org.springframework.dao.DataIntegrityViolationException:
> > PreparedStatementCallback; SQL [select [scr
> > ].[SCR_RQST_ID], max([sah].[SHA_DATETIME_CREATE]) [iHistDateMax],
> > max([srn].[CREATE_DATE]) [iNoteDat
> > eMax] from [service_client_req] [scr] left outer join
> > [service_action_history] [sah] on [sah].[SHA_W
> > ORK_ID] = [scr].[SCR_RQST_ID] left outer join [service_req_notes] [srn]
> on
> > [srn].[WORK_ORDER_ID] = [
> > scr].[SCR_RQST_ID] where [scr].[SCR_RQST_STAUS] not in (?, ?, ?) group
> by
> > [scr].[SCR_RQST_ID] having
> > ((case when max([sah].[SHA_DATETIME_CREATE]) > max([srn].[CREATE_DATE])
> > then max([sah].[SHA_DATETIM
> > E_CREATE]) else max([srn].[CREATE_DATE]) end is null or datediff(ms,
> case
> > when max([sah].[SHA_DATETI
> > ME_CREATE]) > max([srn].[CREATE_DATE]) then
> max([sah].[SHA_DATETIME_CREATE])
> > else max([srn].[CREATE_
> > DATE]) end, ?) > ? or datediff(ms, case when
> > max([sah].[SHA_DATETIME_CREATE]) > max([srn].[CREATE_DA
> > TE]) then max([sah].[SHA_DATETIME_CREATE]) else max([srn].[CREATE_DATE])
> > end, ?) < ?) and ((max([sah
> > ].[SHA_DATETIME_CREATE]) is null and max([srn].[CREATE_DATE]) is null)
> or
> > max([sah].[SHA_DATETIME_CR
> >>
> >> EATE]) < ? or max([srn].[CREATE_DATE]) < ?))]; Unable to convert
> between
> >> org.jooq.types.DayToSecond
> >
> > and JAVA_OBJECT.; nested exception is java.sql.SQLException: Unable to
> > convert between org.jooq.type
> > s.DayToSecond and JAVA_OBJECT.
> >
>
