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
3 Answers:
@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.
Guy,
What version of JasperReports Server are you running?
6.0.1
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.
Do you know how to export the domain? The export will be an XML file.
FYI: http://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v60/exporting-design-file-domain