ReportServer Forum

marty210578

Member

Registered: 2015-05-11

Additional JDBC drivers DB2/JT400

I followed the instructions from the other topic : http://forum.reportserver.net/viewtopic.php?id=177
for creating a additional JDBC drive/datasource

I added the jt400.jar in the  "tomcat\webapps\reportserver\WEB-INF\lib" folder
in reportserver added a groovy script:
Location : fileserverroot\bin
Name : AddJTOpensupport.groovy

 
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.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder
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.db.db2.DB2OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.db.db2.DB2LimitQuery

/*
* @see: http://blog.datenwerke.net/2013/06/adding-support-for-additional-databases.html
*/
class JTOpen extends DatabaseHelper {

	public static final String DB_NAME = "JTOpen";
	public static final String DB_DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
	public static final String DB_DESCRIPTOR = "DBHelper_JTOpen";

	@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 'values 1'; /* http://stackoverflow.com/questions/2775184/simple-db2-query-for-connection-validation */
	}


	@Override
	public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder){
		return new DB2LimitQuery(nestedQuery, queryBuilder){
			@Override
			public void appendToBuffer(StringBuffer buf) {
				nestedQuery.appendToBuffer(buf);
				buf.append(" fetch first ");
				buf.append(queryBuilder.getLimit()+1);
				buf.append(" rows only");
			}
		}
	}

	@Override
	public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
		return new DB2OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
			@Override
			public void appendToBuffer(StringBuffer buf) {
          buf.append("SELECT t.* FROM(");
          buf.append("SELECT s.*,ROW_NUMBER() OVER() AS RN FROM (");
          nestedQuery.appendToBuffer(buf);
          buf.append(") s ");
          buf.append(") t where t.RN > ");
          buf.append(queryBuilder.getOffset());
          buf.append(" and t.RN <= ");
          buf.append(queryBuilder.getLiþmit() + queryBuilder.getOffset());          
			}
		}
	}
}


def HOOK_NAME = "DATASOURCE_HELPER_JTOPEN"

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

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

Executed in the terminal :
cd /fileserver/bin
exec -g AddJTOpensupport.groovy

This gave me under Datasources, database: JTOpen

The url i used was to my opinion correct : jdbc:as400://<hostname>
This works under Jaspersoft correctly

When i test the connection i get : The connection test failed:

Query could not be prepared: Error preparing statement for executing the report query : <br><br>SELECT * FROM (values 1) wrappedQry fetch first 1 rows only  /* token: fc9dc460-e7fe-47db-8113-6adf4e9a44fe */   /* currentuser: 6 */ <br><br>
<br>	at net.datenwerke.rs.base.service.dbhelper.querybuilder.ManagedQuery.prepareStatement(ManagedQuery.java:98)
<br>	at net.datenwerke.rs.base.service.datasources.table.impl.TableDBDataSource.open(TableDBDataSource.java:263)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:408)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:400)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:139)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:113)
<br>	at net.datenwerke.rs.base.service.reportengines.table.SimpleDataSupplier.getData(SimpleDataSupplier.java:105)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl.testConnection(DatasourceTesterRPCServiceImpl.java:86)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d.CGLIB$testConnection$0(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d$$FastClassByGuice$$17ec7dd5.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:110)
<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$$c018323d.testConnection(&lt;generated&gt;)
<br>	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
<br>	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
<br>	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
<br>	at java.lang.reflect.Method.invoke(Unknown Source)
<br>	at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d.CGLIB$processCall$2(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d$$FastClassByGuice$$17ec7dd5.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:110)
<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$$c018323d.processCall(&lt;generated&gt;)
<br>	at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
<br>	at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
<br>	at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d.CGLIB$service$12(&lt;generated&gt;)
<br>	at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d$$FastClassByGuice$$17ec7dd5.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:110)
<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$$c018323d.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.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:243)
<br>	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
<br>	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
<br>	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
<br>	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
<br>	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
<br>	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
<br>	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947)
<br>	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
<br>	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
<br>	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009)
<br>	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
<br>	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1852)
<br>	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
<br>	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
<br>	at java.lang.Thread.run(Unknown Source)
<br>Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query : <br><br>SELECT * FROM (values 1) wrappedQry fetch first 1 rows only  /* token: fc9dc460-e7fe-47db-8113-6adf4e9a44fe */   /* currentuser: 6 */ <br><br>
<br>	at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:226)
<br>	at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.getStatement(JasperStyleParameterParser.java:129)
<br>	at net.datenwerke.rs.base.service.dbhelper.querybuilder.ManagedQuery.prepareStatement(ManagedQuery.java:96)
<br>	... 74 more
<br>Caused by: java.sql.SQLException: [SQL0104] Token 1 was not valid. Valid tokens: FULL LEFT CROSS INNER RIGHT EXCEPTION.
<br>	at com.ibm.as400.access.JDError.throwSQLException(JDError.java:710)
<br>	at com.ibm.as400.access.JDError.throwSQLException(JDError.java:676)
<br>	at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1791)
<br>	at com.ibm.as400.access.AS400JDBCPreparedStatement.&lt;init&gt;(AS400JDBCPreparedStatement.java:361)
<br>	at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2180)
<br>	at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1979)
<br>	at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:162)
<br>	at net.datenwerke.rs.base.service.datasources.table.impl.utils.JasperStyleParameterParser.createStatement(JasperStyleParameterParser.java:208)
<br>	... 76 more
<br>

Any idea what has been wrongly coded in the groovy script?

thanks

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Additional JDBC drivers DB2/JT400

Hi Marty,

my guess is that the dummy query fails. ReportServer expects the dummy query to be "wrappable". If you look at the stacktrace then it constructs the query

SELECT * FROM (values 1) wrappedQry fetch first 1 rows only

which apperently the database does not like. The second suggestion on the stackoverflow page that you linked to in your code does

SELECT current date FROM sysibm.sysdummy1

This should be regular SQL and hence the wrapping should work. Probably also the simpler

SELECT * FROM sysibm.sysdummy1

could work.

Cheers
-Arno

marty210578

Member

Registered: 2015-05-11

Re: Additional JDBC drivers DB2/JT400

Well i changed it to

@Override
	public String createDummyQuery() {
		return 'sysibm.sysdummy1'; 
	}

Still received a error due to ( sysibm.sysdummy1)
Luckely the database connection works so that is good, only the "test connection" failed

Thanks

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Additional JDBC drivers DB2/JT400

It should still be

public String createDummyQuery() {
    return 'SELECT * FROM sysibm.sysdummy1'
}

That is, if this is a valid statement on your databse. Otherwise you could change it to SELECT * FROM ALWAYS_EXISTING_TABLE if you have a table that you know is always there.
Anyhow, as long as you don't want to use dynamic lists, you are probably good to go with the dummy query not working.

Cheers
-Arno

marty210578

Member

Registered: 2015-05-11

Re: Additional JDBC drivers DB2/JT400

Yes that did the trick.

So for everybody who want to create a DB2 database connection based on the JT400 driver:

1) Add the jt400.jar in the  "tomcat\webapps\reportserver\WEB-INF\lib" folder
2) in reportserver added a groovy script:
    Location : fileserverroot\bin
    Name : AddJTOpensupport.groovy
3) Copy&Paste the following code into the file:

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.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder
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.db.db2.DB2OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.db.db2.DB2LimitQuery

/*
* @see: http://blog.datenwerke.net/2013/06/adding-support-for-additional-databases.html
*/
class JTOpen extends DatabaseHelper {

	public static final String DB_NAME = "JTOpen";
	public static final String DB_DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
	public static final String DB_DESCRIPTOR = "DBHelper_JTOpen";

	@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 * FROM sysibm.sysdummy1'
    }

	@Override
	public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder){
		return new DB2LimitQuery(nestedQuery, queryBuilder){
			@Override
			public void appendToBuffer(StringBuffer buf) {
				nestedQuery.appendToBuffer(buf);
				buf.append(" fetch first ");
				buf.append(queryBuilder.getLimit()+1);
				buf.append(" rows only");
			}
		}
	}

	@Override
	public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
		return new DB2OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
			@Override
			public void appendToBuffer(StringBuffer buf) {
          buf.append("SELECT t.* FROM (");
          buf.append("SELECT s.*,ROW_NUMBER() OVER() AS RN FROM (");
          nestedQuery.appendToBuffer(buf);
          buf.append(") s ");
          buf.append(") t where t.RN > ");
          buf.append(queryBuilder.getOffset());
          buf.append(" and t.RN <= ");
          buf.append(queryBuilder.getLiþmit() + queryBuilder.getOffset());          
			}
		}
	}
}


def HOOK_NAME = "DATASOURCE_HELPER_JTOPEN"

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

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

4) Open terminal : <ctrl>+<alt>+<t>
5) Executed in the terminal :
5.1)    cd /fileserver/bin
5.2)    exec -g AddJTOpensupport.groovy

This gave me under Datasources, database: JTOpen
The url : jdbc:as400://<hostname>

Vinodel75

Member

Registered: 2018-07-10

Re: Additional JDBC drivers DB2/JT400

Hello!

I try to add new driver but can not:
-terminal: exec: command not found

Is this a limitation of the community ed.? I use RS3.0.3-6003-2018-06-26-15-44-00-reportserver-ce.zip

Last edited by Vinodel75 (2018-07-10 17:28:22)

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Hi Vinodel75,

scripting is an enterprise feature, check here (and scroll down) for a comparison: https://reportserver.net/en/pricing/

Regards,
Eduardo

stefano.orlandi

Member

From: Italy

Registered: 2023-03-22

Website

Re: Additional JDBC drivers DB2/JT400

Hello,
this is really an helpfull topic.

I used it successfully in ReportServer v3.7.
Now I've updated to version 4.5 and I think that something need to be adjusted.

Method "public String createDummyQuery()" cannot be Overrided. So I comment the annotation and run the Groovy script.
Apparently everything works fine.

Then I assign the new Database type "JTOpen" to a Datasource and when I try to test connection I get this exception:
AbstractMethodError: Receiver class com.ibm.as400.access.AS400JDBCConnectionImpl does not define or inherit an implementation of the resolved method 'abstract boolean isValid(int)' of interface java.sql.Connection.

Anybody can help please?

Thanks a lot,
Stefano

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Hi Stefano,

what exactly are you trying to do? What database are you trying to connect and with which driver exactly ?

Regards,
Eduardo

stefano.orlandi

Member

From: Italy

Registered: 2023-03-22

Website

Re: Additional JDBC drivers DB2/JT400

Hi Eduardo,
of course I'm trying to connect an AS400 database using the jt400.jar driver in the  "tomcat\webapps\reportserver\WEB-INF\lib" folder. This is the same driver and same version I'm using in ReportServer v3.7 before updating to version v4.5.

I launch the Groovy script above (removing @Override annotation into the metod createDummyQuery()); then I restart ReportServer.
In menu "Administration" -> "Datasources" I create a new Datasource selecting Database type "JTOpen".

This is the url connection string (which is a valid string):

jdbc:as400://itbas400.italmark.lcl;libraries=MERSY_DB

If I test the connection in ReportServer v4.5 in menu "Administration" -> "Datasources" -> <select db> -> Test Connection I get the following exception:

net.datenwerke.rs.adminutils.client.datasourcetester.ConnectionTestFailedException: AbstractMethodError: Receiver class com.ibm.as400.access.AS400JDBCConnectionImpl does not define or inherit an implementation of the resolved method &#39;abstract boolean isValid(int)&#39; of interface java.sql.Connection.<br>    at net.datenwerke.rs.adminutils.service.datasourcetester.DatasourceTesterServiceImpl.testConnection(DatasourceTesterServiceImpl.java:59)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl.testConnection(DatasourceTesterRPCServiceImpl.java:52)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$1778113810.GUICE$TRAMPOLINE(&lt;generated&gt;)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)<br>    at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:64)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:112)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$1778113810.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$$1778113810.GUICE$TRAMPOLINE(&lt;generated&gt;)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:112)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$1778113810.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$$1778113810.GUICE$TRAMPOLINE(&lt;generated&gt;)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:112)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$1778113810.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:696)<br>    at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$1778113810.GUICE$TRAMPOLINE(&lt;generated&gt;)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:74)<br>    at net.datenwerke.security.service.security.aop.SecurityCheckInterceptor.invoke(SecurityCheckInterceptor.java:112)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at net.datenwerke.gf.service.gwtstacktrace.CatchStacktraceInterceptor.invoke(CatchStacktraceInterceptor.java:38)<br>    at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:75)<br>    at com.google.inject.internal.InterceptorStackCallback.invoke(InterceptorStackCallback.java:55)<br>    at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$1778113810.service(&lt;generated&gt;)<br>    at com.google.inject.servlet.ServletDefinition.doServiceImpl(ServletDefinition.java:290)<br>    at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:280)<br>    at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:184)<br>    at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:89)<br>    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:85)<br>    at com.google.inject.persist.PersistFilter.doFilter(PersistFilter.java:94)<br>    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:82)<br>    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:121)<br>    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:133)<br>    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)<br>    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)<br>    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177)<br>    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)<br>    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)<br>    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)<br>    at psiprobe.Tomcat90AgentValve.invoke(Tomcat90AgentValve.java:38)<br>    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)<br>    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)<br>    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)<br>    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)<br>    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)<br>    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)<br>    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:891)<br>    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1784)<br>    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)<br>    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)<br>    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)<br>    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)<br>    at java.base/java.lang.Thread.run(Thread.java:829)<br>Caused by: java.lang.reflect.UndeclaredThrowableException<br>    at com.sun.proxy.$Proxy2402.isValid(Unknown Source)<br>    at net.datenwerke.rs.adminutils.service.datasourcetester.DatasourceTesterServiceImpl.testConnection(DatasourceTesterServiceImpl.java:57)<br>    ... 75 more<br>Caused by: java.lang.reflect.InvocationTargetException<br>    at jdk.internal.reflect.GeneratedMethodAccessor802.invoke(Unknown Source)<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 net.datenwerke.dbpool.proxy.ProxiedConnectionHandler.handleInvocation(ProxiedConnectionHandler.java:25)<br>    at com.google.common.reflect.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:87)<br>    ... 77 more<br>Caused by: java.lang.AbstractMethodError: Receiver class com.ibm.as400.access.AS400JDBCConnectionImpl does not define or inherit an implementation of the resolved method &#39;abstract boolean isValid(int)&#39; of interface java.sql.Connection.<br>    at com.mchange.v2.c3p0.impl.NewProxyConnection.isValid(NewProxyConnection.java:1746)<br>    ... 82 more<br>

Everything is working in previous version of ReportServer v.3.7.

Thanks again,
Stefano

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Hi Stefano,

what version exactly of the jt400.jar driver are you using? Here you can see the version numbers: https://mvnrepository.com/artifact/net.sf.jt400/jt400
which one is yours ?

Regards,
Eduardo

stefano.orlandi

Member

From: Italy

Registered: 2023-03-22

Website

Re: Additional JDBC drivers DB2/JT400

Hi Eduardo,
I'm using version 11.0.

Regards,
Stefano

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Hi Stefano,

we are thinking of supporting AS400 out-of-the-box in next versions, so this information is really helpful. We have some more questions:

1. can you pls post a screenshot of the datasource properties? right click on your datasource -> information -> database information
You can use http://imgbb.com for posting the screenshot

2. I think the only thing that does not work is the "datasource test" button. This is because in newer versions we use JDBC 4 .isValid() for this (different as before). Can you pls confirm this? Can you pls create a simple dynamic list based on your Datasource and check if you see the data as expected? It is important that you use a dynamic list for this purpose.

3. Using the dynamic list above: can you pls check if pagination works as expected? So you can click on second page , third page, etc. Does this work as expected ?

4. can you pls post here the exact script you are using for your datasource ?

5. why do you have to use the jt400.jar driver for accessing the database? If I understand this correctly, this is a DB2 database, correct? Can't you use the db2jcc4.jar driver as shown here? https://www.ibm.com/support/pages/db2-j … -downloads
or is this not a DB2 database?

Regards,
Eduardo

stefano.orlandi

Member

From: Italy

Registered: 2023-03-22

Website

Re: Additional JDBC drivers DB2/JT400

Hi Eduardo,
I answer your questions:

1. I don't see any Information of the datasource because I get the same exception I get testing the connection
"AbstractMethodError: Receiver class com.ibm.as400.access.AS400JDBCConnectionImpl does not define or inherit an implementation of the resolved method 'abstract boolean isValid(int)' of interface java.sql.Connection."
This happens when using both JTOpen and DB2 driver.

2. Your observation is correct. I create dynamic list with a simple query and:
case a. if I use DB2 driver everything works (data and pagination)
case b. if I use JTOpen driver I get a new error: "MissingMethodException: No signature of method: net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder.getLiþmit() is applicable for argument types: () values: [] Possible solutions: getLimit(), isLimit(), limit(int), getOffset()"

3. see point 2.

4. the script is the following (I've just commented an Override annotation):

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.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder
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.db.db2.DB2OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.db.db2.DB2LimitQuery

/*
* @see: http://blog.datenwerke.net/2013/06/addi … bases.html
*/
class JTOpen extends DatabaseHelper {

    public static final String DB_NAME = "JTOpen";
    public static final String DB_DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
    public static final String DB_DESCRIPTOR = "DBHelper_JTOpen";

    @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 * FROM sysibm.sysdummy1';
    }

    @Override
    public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
        return new DB2LimitQuery(nestedQuery, queryBuilder) {
            @Override
            public void appendToBuffer(StringBuffer buf) {
                nestedQuery.appendToBuffer(buf);
                buf.append(" fetch first ");
                buf.append(queryBuilder.getLimit()+1);
                buf.append(" rows only");
            }
        }
    }

    @Override
    public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
        return new DB2OffsetQuery(nestedQuery, queryBuilder, columnNamingService){
            @Override
            public void appendToBuffer(StringBuffer buf) {
          buf.append("SELECT t.* FROM(");
          buf.append("SELECT s.*,ROW_NUMBER() OVER() AS RN FROM (");
          nestedQuery.appendToBuffer(buf);
          buf.append(") s ");
          buf.append(") t where t.RN > ");
          buf.append(queryBuilder.getOffset());
          buf.append(" and t.RN <= ");
          buf.append(queryBuilder.getLiþmit() + queryBuilder.getOffset());         
            }
        }
    }
}

def HOOK_NAME = "DATASOURCE_HELPER_JTOPEN"

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

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

5. I confirm that I can use also DB2 driver (db2jcc4.jar)

Regards,
Stefano

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Hi Stefano,

thanks for your answer.
You have a typo in your script:
buf.append(queryBuilder.getLiþmit() + queryBuilder.getOffset());         

pls change it to
buf.append(queryBuilder.getLimit() + queryBuilder.getOffset());         

Also, pls try the newest jt400 driver: 11.2. (https://mvnrepository.com/artifact/net.sf.jt400/jt400)

Can you pls check again 1-4 with these two changes ?

Regards,
Eduardo

stefano.orlandi

Member

From: Italy

Registered: 2023-03-22

Website

Re: Additional JDBC drivers DB2/JT400

Hi Eduardo,
now I've corrected the Groovy script (i didn't notice the typo) and relaunched it.
I've updated the jdbc driver jt400.jar v.11.2
Then I've restarted ReportServer.

1. I get the same error as before (testing connection & information)

2. I get this new error: "StringIndexOutOfBoundsException: String index out of range: -9"

3. see point 2

4. the updated Groovy script:

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.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder
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.db.db2.DB2OffsetQuery
import net.datenwerke.rs.base.service.dbhelper.db.db2.DB2LimitQuery

/*
* @see: http://blog.datenwerke.net/2013/06/addi … bases.html
*/
class JTOpen extends DatabaseHelper {

    public static final String DB_NAME = "JTOpen";
    public static final String DB_DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
    public static final String DB_DESCRIPTOR = "DBHelper_JTOpen";

    @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 * FROM sysibm.sysdummy1';
    }

    @Override
    public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
        return new DB2LimitQuery(nestedQuery, queryBuilder) {
            @Override
            public void appendToBuffer(StringBuffer buf) {
                nestedQuery.appendToBuffer(buf);
                buf.append(" fetch first ");
                buf.append(queryBuilder.getLimit()+1);
                buf.append(" rows only");
            }
        }
    }

    @Override
    public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService) {
        return new DB2OffsetQuery(nestedQuery, queryBuilder, columnNamingService) {
            @Override
            public void appendToBuffer(StringBuffer buf) {
                buf.append("SELECT t.* FROM(");
                buf.append("SELECT s.*,ROW_NUMBER() OVER() AS RN FROM (");
                nestedQuery.appendToBuffer(buf);
                buf.append(") s ");
                buf.append(") t where t.RN > ");
                buf.append(queryBuilder.getOffset());
                buf.append(" and t.RN <= ");
                buf.append(queryBuilder.getLimit() + queryBuilder.getOffset());         
            }
        }
    }
}

def HOOK_NAME = "DATASOURCE_HELPER_JTOPEN"

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

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

This are last rows in tomcat/logs/catalina.out:

23-Mar-2023 16:59:36.647 INFO [Timer-6] org.apache.catalina.loader.WebappClassLoaderBase.checkStateForResourceLoading Illegal access: this web application instance has been stopped already. Could not load [com/ibm/db2/jcc/DB2JccConfiguration.properties]. The following stack trace is thrown for debugging purposes as well as to attempt to terminate the thread which caused the illegal access.
    java.lang.IllegalStateException: Illegal access: this web application instance has been stopped already. Could not load [com/ibm/db2/jcc/DB2JccConfiguration.properties]. The following stack trace is thrown for debugging purposes as well as to attempt to terminate the thread which caused the illegal access.
        at org.apache.catalina.loader.WebappClassLoaderBase.checkStateForResourceLoading(WebappClassLoaderBase.java:1432)
        at org.apache.catalina.loader.WebappClassLoaderBase.getResource(WebappClassLoaderBase.java:1057)
        at com.ibm.db2.jcc.am.ci.run(ci.java:71)
        at java.base/java.security.AccessController.doPrivileged(Native Method)
        at com.ibm.db2.jcc.am.GlobalProperties.a(GlobalProperties.java:152)
        at com.ibm.db2.jcc.am.GlobalProperties.d(GlobalProperties.java:100)
        at com.ibm.db2.jcc.am.lx.run(lx.java:125)
        at java.base/java.util.TimerThread.mainLoop(Timer.java:556)
        at java.base/java.util.TimerThread.run(Timer.java:506)
Exception in thread "dwdaemon-MaintenanceServiceImpl-2780" java.lang.NullPointerException
Exception in thread "dwdaemon-MaintenanceServiceImpl-1467" java.lang.NullPointerException
Exception in thread "dwdaemon-MaintenanceServiceImpl-1438" java.lang.NullPointerException
Exception in thread "dwdaemon-MaintenanceServiceImpl-1858" java.lang.NullPointerException
Exception in thread "dwdaemon-MaintenanceServiceImpl-3911" java.lang.NullPointerException

I hope that could be helpfull.

Regards,
Stefano

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Thanks Stefano, we will look into this and hopefully we can support the as400 driver soon.

Regards,
Eduardo

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Additional JDBC drivers DB2/JT400

Hi Stefano,

pls note that ReportServer 4.6.1 supports DB2 for IBM i out-of-the-box: https://reportserver.net/releasenotes/RS4.6.1.html

Here the documentation: https://reportserver.net/en/guides/admi … -for-IBM-i

Regards,
Eduardo

stefano.orlandi

Member

From: Italy

Registered: 2023-03-22

Website

Re: Additional JDBC drivers DB2/JT400

Hi Eduardo,
this is a very good news!

Many Thanks,
regards

Stefano