ReportServer Forum

zjphillips

Member

Registered: 2020-05-20

Add JDBC driver not in database type dropdown list

Trying to add a JDBC driver that's not in the list of supported drivers.  Is this possible?

The driver I'm trying to add is by Extended Systems and is for advantage database.

I've added the .jar file to ../WEB-INF/lib folder and restarted the server.  I don't see a way to add a database type option in datasources.  And of course it gives a "No database type specified" error when testing connection.

Coming from jasperserver, they had an option to add a driver not in the list.

Thanks!

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Add JDBC driver not in database type dropdown list

Hi zjphillips,

please look at the documentation here:
https://reportserver.net/en/guides/scri … tasources/

Regards,
Eduardo

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Add JDBC driver not in database type dropdown list

Hi zjphillips,

pls let us know how this works. We may consider adding support for advantage database in the future.

Regards,
Eduardo

zjphillips

Member

Registered: 2020-05-20

Re: Add JDBC driver not in database type dropdown list

Hi eduardo, thanks for your response.

I followed the instructions from the guide to add an additional datasource.

1. Copied the file AddFirebirdSupport.groovy from guide and changed name to AddAdvantageSupport.groovy
2. Changed the value of the following variables:
    a. class <newclassname> extends DatabaseHelper {
    b. DB_NAME................. (<dbname>)
    c. DB_DRIVER.............. (com.extendedsystems.jdbc.advantage.ADSDriver)
    d. DB_DESCRIPTOR...... (DBHelper_<dbname>)
    e. HOOK_NAME ........... (DATASOURCE_HELPER_<newclassname>)
    f. return Collections.singletonList(new <newclassname>());
3. Left the .jar file in ../WEB-INF/lib
4. Put the AddAdvantageSupport.groovy script in  ../bin/onstartup.d folder and restarted ReportServer
5. After restart, the <classname> now shows in the dropdown, but gives errors when testing connection

Error:
----------
Could not open connection to: jdbc:extendedsystems:advantage://<hostname:port/data/dbname>with user: <username>. com.extendedsystems.jdbc.advantage.ADSException: [Extended Systems][Advantage JDBC]Connection reset by peer: socket write error

Error Details:
----------------

net.datenwerke.rs.core.service.reportmanager.exceptions.DatabaseConnectionException: Could not open connection to: jdbc:extendedsystems:advantage:/<hostname:port/data/dbname> with user: <username>. com.extendedsystems.jdbc.advantage.ADSException: [Extended Systems][Advantage JDBC]Connection reset by peer: socket write error
<br>	at net.datenwerke.rs.base.service.datasources.transformers.database.Database2TableTransformer.transform(Database2TableTransformer.java:57)
<br>	at net.datenwerke.rs.base.service.datasources.transformers.database.Database2TableTransformer.transform(Database2TableTransformer.java:1)
<br>	at net.datenwerke.rs.base.service.datasources.transformers.DatasourceTransformationServiceImpl.transform(DatasourceTransformationServiceImpl.java:29)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:133)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:124)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:111)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl.testConnection(DatasourceTesterRPCServiceImpl.java:98)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.CGLIB$testConnection$0(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2$$FastClassByGuice$$868d7d6d.invoke(&lt;generated&gt;)
<br>	at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.testConnection(&lt;generated&gt;)
<br>	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
<br>	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
<br>	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
<br>	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
<br>	at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:333)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.CGLIB$processCall$3(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2$$FastClassByGuice$$868d7d6d.invoke(&lt;generated&gt;)
<br>	at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.processCall(&lt;generated&gt;)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:303)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.CGLIB$processCall$4(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2$$FastClassByGuice$$868d7d6d.invoke(&lt;generated&gt;)
<br>	at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.processCall(&lt;generated&gt;)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:373)
<br>	at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.CGLIB$service$17(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2$$FastClassByGuice$$868d7d6d.invoke(&lt;generated&gt;)
<br>	at com.google.inject.internal.cglib.proxy.$MethodProxy.invokeSuper(MethodProxy.java:228)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:109)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)
<br>	at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:72)
<br>	at com.google.inject.internal.InterceptorStackCallback.intercept(InterceptorStackCallback.java:52)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$4fe22cf2.service(&lt;generated&gt;)
<br>	at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
<br>	at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
<br>	at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
<br>	at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:89)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:168)
<br>	at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
<br>	at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
<br>	at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
<br>	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
<br>	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
<br>	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
<br>	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
<br>	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
<br>	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
<br>	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
<br>	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
<br>	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
<br>	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
<br>	at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:432)
<br>	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
<br>	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
<br>	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
<br>	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
<br>	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
<br>	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
<br>	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
<br>	at java.base/java.lang.Thread.run(Thread.java:834)
<br>Caused by: java.lang.RuntimeException: com.extendedsystems.jdbc.advantage.ADSException: [Extended Systems][Advantage JDBC]Connection reset by peer: socket write error
<br>	at net.datenwerke.dbpool.DbC3p0PoolServiceImpl$1.get(DbC3p0PoolServiceImpl.java:192)
<br>	at net.datenwerke.dbpool.DbC3p0PoolServiceImpl$1.get(DbC3p0PoolServiceImpl.java:1)
<br>	at net.datenwerke.rs.base.service.datasources.transformers.database.Database2TableTransformer.transform(Database2TableTransformer.java:54)
<br>	... 91 more
<br>Caused by: com.extendedsystems.jdbc.advantage.ADSException: [Extended Systems][Advantage JDBC]Connection reset by peer: socket write error
<br>	at com.extendedsystems.jdbc.advantage.n.a(Unknown Source)
<br>	at com.extendedsystems.jdbc.advantage.ADSStmt.a(Unknown Source)
<br>	at com.extendedsystems.jdbc.advantage.ADSStmt.executeQuery(Unknown Source)
<br>	at com.extendedsystems.jdbc.advantage.ADSConnection.getMetaData(Unknown Source)
<br>	at com.mchange.v2.c3p0.impl.NewProxyConnection.getMetaData(NewProxyConnection.java:808)
<br>	at net.datenwerke.dbpool.DbC3p0PoolServiceImpl.configureConnection(DbC3p0PoolServiceImpl.java:286)
<br>	at net.datenwerke.dbpool.DbC3p0PoolServiceImpl$1.get(DbC3p0PoolServiceImpl.java:166)
<br>	... 93 more
<br>

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Add JDBC driver not in database type dropdown list

Hi zjphillips,

you seem to have some connection issue, please check here: https://www.ibm.com/support/pages/conne … or-message and https://community.oracle.com/thread/1691330

The error is returned by your jdbc driver. Can you try to connect with an external program using the same driver, and in the same host your reportserver is installed in?

Regards,
Eduardo

zjphillips

Member

Registered: 2020-05-20

Re: Add JDBC driver not in database type dropdown list

Yes, right now I have jasperserver installed on the same host and it is able to connect using the same driver.  Wondering if there's anything else in the script I need to change or edit.

IF_Eduardo

Administrator

Registered: 2016-11-01

Website

Re: Add JDBC driver not in database type dropdown list

Hi zjphillips,

is there any possibility that we get access to a test DB, so we could take a look?

Regards,
Eduardo

zjphillips

Member

Registered: 2020-05-20

Re: Add JDBC driver not in database type dropdown list

Just sent you an email, eduardo.

zjphillips

Member

Registered: 2020-05-20

Re: Add JDBC driver not in database type dropdown list

Below is the groovy script that is working now for our Advantage database:

package databasehelper;

import net.datenwerke.rs.scripting.service.scripting.scriptservices.GlobalsWrapper;
import net.datenwerke.rs.base.service.dbhelper.DatabaseHelper
import net.datenwerke.rs.base.service.dbhelper.hooks.DatabaseHelperProviderHook
import net.datenwerke.rs.base.service.dbhelper.queries.LimitQuery
import net.datenwerke.rs.base.service.dbhelper.queries.OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.queries.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder



class Advantage extends DatabaseHelper {

    public static final String DB_NAME = "Prodent";
    public static final String DB_DRIVER = "com.extendedsystems.jdbc.advantage.ADSDriver";
    public static final String DB_DESCRIPTOR = "DBHelper_Prodent";


    @Override
    public String getDescriptor() {
        return DB_DESCRIPTOR;
    }

    @Override
    public String getDriver() {
        return DB_DRIVER;
    }

    @Override
    public String getName() {
        return DB_NAME;
    }

    @Override
    public String createDummyQuery() {
        return 'SELECT 1 dummy from operator';
    }

    @Override
    public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
        return new LimitQuery(nestedQuery, queryBuilder){
        @Override
        public void appendToBuffer(StringBuffer buf) {
            buf.append("SELECT TOP ");
            buf.append(queryBuilder.getLimit());
            buf.append(" * FROM (");
            nestedQuery.appendToBuffer(buf);
            buf.append(") limitQry");
            }
        }
    }

    @Override
    public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
        return new OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
            @Override
            public void appendToBuffer(StringBuffer buf) {
                buf.append("SELECT TOP ");
                buf.append(queryBuilder.getLimit());
                buf.append(" * FROM (");
                nestedQuery.appendToBuffer(buf);
                buf.append(") limitQry");
            }
        }
    }

}


def HOOK_NAME = "DATASOURCE_HELPER_PRODENT"

def callback =  [
    provideDatabaseHelpers : {
        return Collections.singletonList(new Advantage());
    }
] as DatabaseHelperProviderHook;

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, DatabaseHelperProviderHook.class, callback)

Thanks for your help Eduardo!