Using ODBC datasource with JasperReports Server

[hozawa]

I'm able to use JDBC-ODBC driver with Jaspersoft Studio and with created reports on JasperReports Server, but using JDBC-ODBC driver in Ad Hoc causes error. I was wondering if anybody has been able to use JDBC-ODBC driver in Adhoc views.

Caused by: com.jaspersoft.commons.dataset.DataSetException: exception fetching data
    at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:183)
    at com.jaspersoft.commons.datarator.BaseDataSetCacheImpl.getWorkingDataSet(BaseDataSetCacheImpl.java:140)
    ... 130 more
Caused by: com.jaspersoft.commons.dataset.DataSetException: Exception calling JRDataSource.next() for query select `col3`
from (SELECT * FROM excel limit 10) `query`
 limit 1000

    at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:395)
    at com.jaspersoft.commons.datarator.CachedData.fetchData(CachedData.java:148)
    ... 131 more
Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query :

select `col3`
from (SELECT * FROM excel limit 10) `query`
 limit 1000



    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:377)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:196)
    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:254)
    at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet.access$100(JRQueryDataSet.java:57)
    at com.jaspersoft.commons.semantic.dsimpl.JRQueryDataSet$JRDataSetIterator.next(JRQueryDataSet.java:386)
    ... 132 more
Caused by: java.sql.SQLException: Invalid Fetch Size
    at sun.jdbc.odbc.JdbcOdbcStatement.setFetchSize(JdbcOdbcStatement.java:825)
    at org.apache.commons.dbcp.DelegatingStatement.setFetchSize(DelegatingStatement.java:276)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:300)
    ... 138 more

[elizam]

This may be a feature request.

[hozawa]

This actually seems to be a well known problem with using preparedstatement with oracle's JDBC-ODBC bridge.

[hozawa]

I think the main problem is JasperReports Server requires at least JDBC 2.1 compliance while oracle's sun jdbc-odbc driver is only partial JDBC 2.0 compliant.

Furthermore, Oracle will no longer be supporting JDBC-ODBC driver in Java 8.