Product Knowledge Base

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Observations

  • Users observe errors in the JOC Cockpit log about an "Invalid parameter index".

    SQL Server stack trace 
    2017-02-01 16:04:44,448 DEBUG qtp1212899836-16 o.h.h.i.a.QueryTranslatorImpl - HQL: select new com.sos.jitl.reporting.db.DBItemReportTriggerWithResult(t,r) from com.sos.jitl.reporting.db.DBItemReportTrigger t,com.sos.jitl.reporting.db.DBItemReportTriggerResult r where t.schedulerId=:schedulerId and t.startTime>= :startTimeFrom and t.startTime < :startTimeTo and t.id = r.triggerId order by t.startTime desc
2017-02-01 16:04:44,448 DEBUG qtp1212899836-16 o.h.h.i.a.QueryTranslatorImpl - SQL: select dbitemrepo0_.[ID] as col_0_0_, dbitemrepo1_.[ID] as col_1_0_ from REPORTING_TRIGGERS dbitemrepo0_ cross join REPORTING_TRIGGER_RESULTS dbitemrepo1_ where dbitemrepo0_.[SCHEDULER_ID]=? and dbitemrepo0_.[START_TIME]>=? and dbitemrepo0_.[START_TIME]<? and dbitemrepo0_.[ID]=dbitemrepo1_.[TRIGGER_ID] order by dbitemrepo0_.[START_TIME] desc
2017-02-01 16:04:44,448 DEBUG qtp1212899836-16 o.h.h.i.a.ErrorCounter - throwQueryException() : no errors
Hibernate:
select
TOP 10000 dbitemrepo0_.[ID] as col_0_0_,
dbitemrepo1_.[ID] as col_1_0_
from
REPORTING_TRIGGERS dbitemrepo0_ cross
join
REPORTING_TRIGGER_RESULTS dbitemrepo1_
where
dbitemrepo0_.[SCHEDULER_ID]=?
and dbitemrepo0_.[START_TIME]>=?
and dbitemrepo0_.[START_TIME]<?
and dbitemrepo0_.[ID]=dbitemrepo1_.[TRIGGER_ID]
order by
dbitemrepo0_.[START_TIME] desc
2017-02-01 16:04:44,450 TRACE qtp1212899836-16 o.h.t.d.s.BasicBinder - binding parameter [1] as [LONGVARCHAR] - [scheduler01.localdomain_4444]
2017-02-01 16:04:44,450 TRACE qtp1212899836-16 o.h.t.d.s.BasicBinder - binding parameter [2] as [TIMESTAMP] - [Wed Feb 01 00:00:00 UTC 2017]
2017-02-01 16:04:44,450 TRACE qtp1212899836-16 o.h.t.d.s.BasicBinder - binding parameter [3] as [TIMESTAMP] - [Thu Feb 02 00:00:00 UTC 2017]
2017-02-01 16:04:44,472 WARN qtp1212899836-16 o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 07009
2017-02-01 16:04:44,472 ERROR qtp1212899836-16 o.h.e.j.s.SqlExceptionHelper - Invalid parameter index 4.
2017-02-01 16:04:44,475 INFO qtp1212899836-16 c.s.j.c.JOCDefaultResponse -
REQUEST: ./orders/history
PARAMS: {"jobschedulerId":"scheduler01.localdomain_4444","orders":[],"excludeOrders":[],"compact":false,"processingStates":[],"types":[],"dateFrom":"2017-02-01T00:00:00.000Z","dateTo":"2017-02-02T00:00:00.000Z","folders":[],"limit":10000,"historyStates":[]}
USER: root
2017-02-01 16:04:44,475 ERROR qtp1212899836-16 c.s.j.c.JOCDefaultResponse - javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:147)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:155)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1419)
at com.sos.jitl.reporting.db.ReportTriggerDBLayer.getSchedulerOrderHistoryListFromTo(ReportTriggerDBLayer.java:191)
at com.sos.joc.orders.impl.OrdersResourceHistoryImpl.postOrdersHistory(OrdersResourceHistoryImpl.java:82)
  • Such errors occur when navigating in the JOC Cockpit or when executing specific jobs, e.g. the /sos/dailyplan/CreateDailyPlan job.

Explanations

  • This error is due to a bug in the jTDS JDBC Driver when using a "top" expression. At the time of writing this bug seems not be resolved.
  • This error occurs with SQL Server 2012 and later.

Resolution

  • Adjust the SQL Server dialect setting in your hibernate configuration file to SQL Server 2012

    • <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
      should be
      <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2012Dialect</property>

  • The resolution has to be applied to
    • ./resources/joc/jobscheduler.hibernate.cfg.xml
    • ./resources/joc/reporting.hibernate.cfg.xml
  • The JOC Cockpit installer specifies the default dialect org.hibernate.dialect.SQLServerDialect, therefore any changes have to be manually applied.

 

  • No labels