Also this is the generated SQL, I got it from System.out.println, i mean
console
select [scr].[SCR_RQST_ID], max([sah].[SHA_DATETIME_CREATE])
[iHistDateMax], max([srn].[CREATE_DATE]
) [iNoteDateMax] from [service_client_req] [scr] left outer join
[service_action_history] [sah] on [
sah].[SHA_WORK_ID] = [scr].[SCR_RQST_ID] left outer join
[service_req_notes] [srn] on [srn].[WORK_OR
DER_ID] = [scr].[SCR_RQST_ID] where [scr].[SCR_RQST_STAUS] not in (3, 8,
13) group by [scr].[SCR_RQS
T_ID] having ((case when max([sah].[SHA_DATETIME_CREATE]) >
max([srn].[CREATE_DATE]) then max([sah].
[SHA_DATETIME_CREATE]) else max([srn].[CREATE_DATE]) end is null or
datediff(ms, case when max([sah]
.[SHA_DATETIME_CREATE]) > max([srn].[CREATE_DATE]) then
max([sah].[SHA_DATETIME_CREATE]) else max([s
rn].[CREATE_DATE]) end, '2012-12-10 16:40:09.546') > '+0
02:30:00.000000000' or datediff(ms, case wh
en max([sah].[SHA_DATETIME_CREATE]) > max([srn].[CREATE_DATE]) then
max([sah].[SHA_DATETIME_CREATE])
else max([srn].[CREATE_DATE]) end, '2012-12-10 16:40:09.546') < '+0
00:00:00.000000000') and ((max(
[sah].[SHA_DATETIME_CREATE]) is null and max([srn].[CREATE_DATE]) is null)
or max([sah].[SHA_DATETIM
E_CREATE]) < '2012-12-10 16:40:09.546' or max([srn].[CREATE_DATE]) <
'2012-12-10 16:40:09.546'))
The involve datatype is Timestamp/DateTime. Again, it is working in MySQL
> but not in SQL Server. Same Codes and web application project, same DB
> tables
> as well. We have identical db data in MySQL in SQL Server So the only
> difference is the database product and the db driver used..
>
>>
>> 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]>:
>>> > 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.
>>> >
>>>
>>
