Monday, 5 August 2013

NextReports: add your JDBC driver

Starting from version 6.2 NextReports will allow users to add their own JDBC drivers. We are using Vertica JDBC driver here as example. In version 6.2 Vertica driver will be added by default.

Such process can be resumed by following steps :

1. JDBC jar driver is added inside lib folder (designer and server). From 7.0, designer has a jdbc-drivers folder.

2. Driver must be added in driver_templates.xml which can be found :
  • inside designer in installation folder in \lib\nextreports-designer-6.1.jar 
  • inside server in installation folder in \webapps\nextserver\WEB-INF\classes 

    Vertica
    com.vertica.jdbc.Driver
    jdbc:vertica://<server>:<port>/<database>
    5433   

3. You need to create a Dialect class for that type of driver. This dialect does a mapping between database types and java sql types and has some utilities methods.
import java.sql.Types;
import ro.nextreports.engine.util.ProcUtil;

public class VerticaDialect extends AbstractDialect {

public VerticaDialect() {
super();
registerColumnType("binary", Types.BLOB);
registerColumnType("varbinary", Types.BLOB);
registerColumnType("bytea", Types.BLOB);
registerColumnType("raw", Types.BLOB);
registerColumnType("boolean", Types.BOOLEAN);
registerColumnType("char", Types.CHAR);
registerColumnType("varchar", Types.VARCHAR);
registerColumnType("date", Types.DATE);
registerColumnType("timestamp", Types.TIMESTAMP);
registerColumnType("timestamp with timezone", Types.TIMESTAMP);
registerColumnType("datetime", Types.TIMESTAMP);
registerColumnType("smalldatetime", Types.TIMESTAMP);
registerColumnType("double precision", Types.DOUBLE);
registerColumnType("float", Types.FLOAT);
registerColumnType("float8", Types.FLOAT);
registerColumnType("real", Types.DOUBLE);
registerColumnType("bigint", Types.BIGINT);
registerColumnType("smallint", Types.SMALLINT);
registerColumnType("integer", Types.INTEGER);
registerColumnType("int", Types.INTEGER);
registerColumnType("tinyint", Types.INTEGER);
registerColumnType("int8", Types.INTEGER);
registerColumnType("decimal", Types.INTEGER);
registerColumnType("numeric", Types.NUMERIC);
registerColumnType("number", Types.NUMERIC);
registerColumnType("money", Types.NUMERIC);
registerColumnType("time", Types.TIME);
registerColumnType("time with timezone", Types.TIME);
registerColumnType("interval", Types.TIME);
}

public String getCurrentDate() throws DialectException {
return "current_date";
}

public String getCurrentTimestamp() throws DialectException {
return "current_timestamp";
}

public String getCurrentTime() throws DialectException {
return "current_time";
}

public String getCurrentDateSelect() {
return "select current_date";
}

public String getRecycleBinTablePrefix() {
return null;
}

public String getCursorSqlTypeName() {
return ProcUtil.REF_CURSOR;
}

public int getCursorSqlType() {
return Types.OTHER;
}

public String getSqlChecker() {
return "select 1";
}
}
    4. You must register the dialect in NextReports. To make this happen in designer and server you have to add some java VM parameters. (nextreports.vmoptions  file from designer and start-nextserver.vmoptions from server)
    -Dnext.dialect.database_1="Vertica Database" 
    -Dnext.dialect.class_1="mypackage.VerticaDialect"

    First parameter must be the name taken from DataBaseMetaData.getDatabaseProductName().

    If you need to register more dialects, you use different suffix indexes.

    3 comments:

    1. Hi all,
      I'm trying to add the as400 jdbc driver to next-reports (designer). However, I'm up to the point that I can add the connection details and that next-reports finds the jt400.jar file (put it in the jdbc-drivers directory). When trying to connect I get the error 'Could not get Dialect'. Which makes sense as I cannot find how to add the Dialect.java file to next-reports. The last sentence in the blog is 'First parameter must be the name taken from DatabaseMetaData.getDatabaseProductName()' which I do not understand just as the process of adding the Dialect.java file. Can anyone help me out here?
      To further clarify, when saving the connection I am able to browse the schemas on the as400/iSeries but cannot see the tables below.

      ReplyDelete
    2. Furthermore, after unpacking the nextreports-designer.jar file, I've added the driver to driver_template(s).xml but when trying to add a new datasource, it does not see the new datasource. Do I need to make the jar again with the modified driver_template(s).xml?

      ReplyDelete
    3. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java EE Online Training from India . Nowadays Java has tons of job opportunities on various vertical industry.




      Java Online Training

      ReplyDelete