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..
On Monday, December 10, 2012 4:30:12 PM UTC+8, Vine wrote:
>
> 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]>:
>> > 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.
>> >
>>
>
