Datetime filter in ad hoc view doesn't work

1

Hi,

I have created a simple domain that gives access to a table with a datetime field. My data source is MS SQL and the date time format is yyyy-mm-dd hh:mm:ss 

I am trying to create a simple ad hoc view using that domain and I put the datetime field in the column. When I try to create a filter for the datetime it is created but nothing happens. 

When looking at the logs I see exception.

 

What am I doing wrong? 

com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select  top 1000 "starttime"
from (select endtime, entitytype, facility, entity, starttime as starttime from dbo.locationchangehistoryNID) "query"
      where ("starttime" between {ts '2015-03-19 00:00:00'} and {ts '2015-03-19 00:00:00'})
 
at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:417)
at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:150)
 
... 127 more
 
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : null
 
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168)
at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:113)
at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet.getJRDataSource(JRQueryDataSet.java:264)
at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet.access$100(JRQueryDataSet.java:58)
at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:408)
 
... 128 more
 
Caused by: java.sql.SQLDataException: [TIBCO][SQLServer JDBC Driver][SQLServer]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
 
at tibcosoftwareinc.jdbc.sqlserverbase.ddcw.b(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.ddcw.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.ddcv.b(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.ddcv.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserver.tds.ddr.v(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserver.tds.ddq.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserver.dda3.m(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.dde7.e(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.dde7.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.ddd2.a(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.dde7.h(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.dde7.u(Unknown Source)
at tibcosoftwareinc.jdbc.sqlserverbase.ddd2.executeQuery(Unknown Source)
at tibcosoftware.jdbc.common.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:45)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)
 
... 133 more
guy.morgenstern's picture
Joined: Mar 23 2015 - 12:57pm
Last seen: 4 years 8 months ago

Guy,

What version of JasperReports Server are you running?

djohnson53 - 4 years 8 months ago

6.0.1

guy.morgenstern - 4 years 8 months ago

I think that the problem relates to the fact that Jasper is using timestamp type and I am using datetime type in my MS SQL Server. 

guy.morgenstern - 4 years 8 months ago
show 2 more...

Do you know how to export the domain? The export will be an XML file.

djohnson53 - 4 years 8 months ago

3 Answers:

0

@guy.morgenstern  Re: I think that the problem relates to the fact that Jasper is using timestamp type and I am using datetime type in my MS SQL Server. 

I think you are right here, you can change the default domain type mappings by changing them in /WEB-INF/applicationContext-semanticLayer.xml search in that file for the bean called "jdbcMetaConfiguration" you will see that it has a property (jdbc2JavaTypeMapping) that maps the DB types to the Java Types that jasper will use, make sure that the mapping is correct, I have not seen this issue with MS SQL server but it happens some times with some Oracle data types (you will see some of them commented out in the file).

Hope this helps.. either way @djohnson53 is also in the right path, this may be something that can be changed at the Domain level.

marianol's picture
4788
Joined: Sep 13 2011 - 8:04am
Last seen: 1 year 1 month ago
0

did the solution work? 

I have the same problem and I checked the semantic file, it is correct:

<entry key="DATE" value="java.util.Date"/>

 <entry key="TIMESTAMP" value="java.sql.Timestamp"/>

 

What else could be the problem?

 

mc.bechara's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
0

anyone help with this!

mc.bechara's picture
Joined: Jul 7 2014 - 4:39am
Last seen: 4 years 2 months ago
Feedback
randomness