Chapter 11. Adding Additional Datasources

By default ReportServer is shipped with support only for the most common database systems, although it can interact with virtually every database that offers a JDBC compliant driver. In this chapter we will show how to develop a groovy script that adds support for the Firebird database (www.firebirdsql.org) to ReportServer.

Although SQL is well standardized and the JDBC specification defines a vendor independent interface to the actual database driver, simply adding the database driver to the classpath does not suffice. ReportServer tries to use mostly ANSI-SQL whenever possible, but in some situations not using a vendor-specific extension bears a high performance penalty. One of these situations is the limit/offset-mechanism, that allows the application to request only a certain chunk of the resultset. Most databases offer some solution to this problem, but there is no standard approach. To manage these differences in different SQL dialects ReportServer uses a DatabaseHelper class for each supported SQL dialect.

To add support for a new datatabase, what we need to do, is to implement a DatabaseHelper for the database and register it with ReportServer.

• Please note that Firebird is already integrated in ReportServer out-of-the-box. In order to use Firebird with ReportServer, you just have to add the Firebird drivers to your lib directory. The example below is meant for understanding the principle.

Lets start by looking at the DatabaseHelper for the H2 database engine:

public class H2 extends DatabaseHelper {

 public static final String DB_NAME = "H2";
 public static final String DB_DRIVER = "org.h2.Driver";
 public static final String DB_DESCRIPTOR = "DBHelper_H2";

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

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

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

The three methods getDescriptor, getName and getDriver are the minimum each DatabaseHelper has to implement.

To adapt this to the Firebird database, we simply change the values of the three constants

class Firebird extends DatabaseHelper {

 public static final String DB_NAME = "Firebird";
 public static final String DB_DRIVER = "org.firebirdsql.jdbc.FBDriver";
 public static final String DB_DESCRIPTOR = "DBHelper_Firebird";

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

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

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

If we added this class to ReportServer, we would already be able to execute Birt and Jasper Reports where the query is simply passed through, but using a dynamic list with a Firebird datasource would still fail.

There are basically three more changes to be made to fully support Firebird from within ReportServer.

• As of ReportServer 4.2.0, the old createDummyQuery() method is not further used, as it now uses JDBC 4 connection.isValid() for this purpose. You should remove this method from your scripts if you use it.

The other two enhancements concern the earlier mentioned matter of limit and offset. ReportServers default implementation creates queries with LIMIT and OFFSET keywords at the end, so we need to provide a different implementation for Firebird.

@Override
 public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder) {
  return new LimitQuery(nestedQuery, queryBuilder){
   @Override
   public void appendToBuffer(StringBuffer buf) {
    buf.append("SELECT FIRST ");
    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 FIRST ");
    buf.append(queryBuilder.getLimit());
    buf.append(" SKIP ");
    buf.append(queryBuilder.getOffset());
    buf.append(" * FROM (");
    nestedQuery.appendToBuffer(buf);
    buf.append(") limitQry");
   }
  }
 }

The way ReportServer constructs dynamic queries is by basically wrapping multiple layers of SQL around each other. The LimitQuery follows the same approach. It has access to a nestedQuery and is asked to write itself into the supplied buffer, wrapping this nested query.

The final step is to register the newly created DatabaseHelper with ReportServer. This is done by implementing the net.datenwerke.rs.base.service.dbhelper.hooks.DatabaseHelperProviderHook. Following is the implementation needed to attach our custom Firebird database helper class

def HOOK_NAME = "DATASOURCE_HELPER_FIREBIRD"
def callback =  [
   provideDatabaseHelpers : {
    return Collections.singletonList(new Firebird());
   }
  ] as DatabaseHelperProviderHook;
GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, DatabaseHelperProviderHook.class, callback)

You will find the complete script (AddFirebirdSupport.groovy) in the appendix as well as in the support portal for download.

Note that, in order for this to work you of course need to place the Firebird JDBC driver into your classpath. Download Jaybird from http://www.firebirdsql.org/en/jdbc-driver/ and put the files jaybird-2.2.3.jar and lib/connector-api-1.5.jar into the WEB-INF/lib subdirectory of your ReportServer installation. You might have to restart ReportServer afterwards.

Beware, that after restarting ReportServer hooks attached from the terminal will no longer be present. To automatically attach a hook on start-up put the script in the bin/onstartup.d directory in the fileserver.