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://
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(<generated>)
<br> at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d$$FastClassByGuice$$17ec7dd5.invoke(<generated>)
<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(<generated>)
<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(<generated>)
<br> at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d$$FastClassByGuice$$17ec7dd5.invoke(<generated>)
<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(<generated>)
<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(<generated>)
<br> at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl$$EnhancerByGuice$$c018323d$$FastClassByGuice$$17ec7dd5.invoke(<generated>)
<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(<generated>)
<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.<init>(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?
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
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.
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.
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):
we are thinking of supporting AS400 out-of-the-box in next versions, so this information is really helpful. We have some more questions:
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
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.
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 ?
can you pls post here the exact script you are using for your datasource ?
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-jdbc-driver-versions-and-downloads
or is this not a DB2 database?
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.
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()”
see point 2.
the script is the following (I’ve just commented an Override annotation):
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());
}
}
}
}
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.
I get the same error as before (testing connection & information)
I get this new error: “StringIndexOutOfBoundsException: String index out of range: -9”
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());
}
}
}
}
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