JasperReports Server sun.jdbc.odbc.JdbcOdbcDriver troubles

[fandral]

Hello everyone. I ask for help with Jdbc-Odbc bridge connection from JasperReports Server to my odbc source. 

There are no problems with connection from TIBCO Jaspersoft Studio-6.0.0.final. 

i have exception when try to connect from JasperReports Server

About JasperReports Server Copyright © 2005-2014 TIBCO Software Inc.

 

Error Details

 

The server has encountered an error. Please excuse the inconvenience.

 

 

Error Message

 

An error occurred while creating the connection. Try again.

 

Error Trace

 

com.jaspersoft.jasperserver.api.JSException: jsexception.error.creating.connection at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.createConnection(JdbcDataSourceService.java:63) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.BaseJdbcDataSource.setReportParameterValues(BaseJdbcDataSource.java:49) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.setReportParameterValues(JdbcDataSourceService.java:68) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1725) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1086) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1015) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:908) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724) Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.TibcoDriverManagerImpl.getOriginalConnection(TibcoDriverManagerImpl.java:186) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.TibcoDriverManagerImpl.unlockConnection(TibcoDriverManagerImpl.java:77) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.createConnection(JdbcDataSourceService.java:55) ... 9 more Caused by: java.util.NoSuchElementException: Could not create a validated object, cause: [schneider Electric][ClearSCADA database driver]Option Value Changed at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:806) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95) ... 12 more

 

Error Message

 

org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted

 

Error Trace

 

org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.TibcoDriverManagerImpl.getOriginalConnection(TibcoDriverManagerImpl.java:186) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.TibcoDriverManagerImpl.unlockConnection(TibcoDriverManagerImpl.java:77) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.createConnection(JdbcDataSourceService.java:55) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.BaseJdbcDataSource.setReportParameterValues(BaseJdbcDataSource.java:49) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.setReportParameterValues(JdbcDataSourceService.java:68) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1725) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1086) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1015) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:908) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724) Caused by: java.util.NoSuchElementException: Could not create a validated object, cause: [schneider Electric][ClearSCADA database driver]Option Value Changed at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:806) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95) ... 12 more

 

Error Message

 

java.util.NoSuchElementException: Could not create a validated object, cause: [schneider Electric][ClearSCADA database driver]Option Value Changed

 

Error Trace

 

java.util.NoSuchElementException: Could not create a validated object, cause: [schneider Electric][ClearSCADA database driver]Option Value Changed at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:806) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.TibcoDriverManagerImpl.getOriginalConnection(TibcoDriverManagerImpl.java:186) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.TibcoDriverManagerImpl.unlockConnection(TibcoDriverManagerImpl.java:77) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.createConnection(JdbcDataSourceService.java:55) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.BaseJdbcDataSource.setReportParameterValues(BaseJdbcDataSource.java:49) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.JdbcDataSourceService.setReportParameterValues(JdbcDataSourceService.java:68) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1725) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1086) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1015) at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:908) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724)

Best Regards, Aleksey.

[fandral]

Here is the solution. Useful to someone.

1) stop jasperreports server service

2) in C:Jaspersoftjasperreports-server-cp-5.6.1apache-tomcatwebappsjasperserverWEB-INFweb.xml

 

add

 

<resource-ref><description>ClearScadaDataSource</description><res-ref-name>jdbc/OdbcClient</res-ref-name><res-type>javax.sql.DataSource</res-type><res-auth>Container</res-auth></resource-ref>

3) in C:Jaspersoftjasperreports-server-cp-5.6.1apache-tomcatwebappsjasperserverMETA-INFcontext.xml 

 

add

 

<Resource name ="jdbc/OdbcClient" auth="Container" type="javax.sql.DataSource"driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"url="jdbc:odbc:OdbcClient"servicename="OdbcClient"  username="su" password="su"maxActive="20" maxIdle="10" maxWait="20000"/>

 

4) start jasper service then in login in JasperServer like admin/superuser

5) add new DataSource

 

6) Enjoy!

 

Best regards, Aleksey.

 

[hozawa]

JFYI, Oracle stopped releasing JDBC - ODBC bridge in Java 8. It's probably better to use other third party solution.

[fandral]

There are many systems using odbc. And you can always set JAVA variable in the properties.ini to any version of Java.