guy.morgenstern Posted March 23, 2015 Share Posted March 23, 2015 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 moreCaused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : nullat 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 moreCaused 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[/code] Link to comment Share on other sites More sharing options...
djohnson53 Posted March 23, 2015 Share Posted March 23, 2015 Guy,What version of JasperReports Server are you running? Link to comment Share on other sites More sharing options...
guy.morgenstern Posted March 23, 2015 Author Share Posted March 23, 2015 6.0.1 Link to comment Share on other sites More sharing options...
guy.morgenstern Posted March 23, 2015 Author Share Posted March 23, 2015 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. Link to comment Share on other sites More sharing options...
djohnson53 Posted March 23, 2015 Share Posted March 23, 2015 Do you know how to export the domain? The export will be an XML file. Link to comment Share on other sites More sharing options...
djohnson53 Posted March 23, 2015 Share Posted March 23, 2015 FYI: http://community.jaspersoft.com/documentation/tibco-jasperreports-server-user-guide/v60/exporting-design-file-domain Link to comment Share on other sites More sharing options...
marianol Posted March 25, 2015 Share Posted March 25, 2015 @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. Link to comment Share on other sites More sharing options...
mc.bechara Posted August 25, 2015 Share Posted August 25, 2015 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? Link to comment Share on other sites More sharing options...
mc.bechara Posted September 4, 2015 Share Posted September 4, 2015 anyone help with this! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now