BIRT – Handling null parameters

junio 4, 2015

While I was creating a report I needed some parameters to be optional, hence, accepting null values.

My first attempt was to just mark every report parameter with ‘Is Required’ attribute set to false (unchecked). And bind the parameters on the Dataset normally. The report was throwing the following error:

+ An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: dstAudiencias
Cannot set a null value to parameter 1.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement null value.
SQL error #1:Unsupported feature
 ;
    java.sql.SQLException: Unsupported feature

I could avoid these by setting a default value for every parameter, but in this case, null is an accepted value.

The solution is to change the query dynamically with scripting, the problem is that you can not use parameter bindings. You have to contruct the query through concatenation, which is susceptible to SQL injection.
I don’t know if this feature is unsupported by the driver (I’m using Oracle JDBC driver) or BIRT.

So I ended up writing the following code on the dataset ‘before open’ event script.

importPackage(Packages.java.text)
var sdf = new SimpleDateFormat("dd-MM-yyyy")
var sql = 
                            "SELECT employee_id,n" +
                            "       first_name,n" +
                            "       last_name,n" +
                            "       start_date,n" +
                            "       end_date,n" +
                            "       emailn" +
                            "  FROM employeesn"
var where = ""
if(params['EMPLOYEE_ID'].value){
    where += "       AND (employee_id = " + params['EMPLOYEE_ID'].value + "n"
}
if(params['START'].value && params['END'].value){
where += "       AND TRUNC (start_date) BETWEEN TO_DATE('" + sdf.format(params['START']) + "', 'DD-MM-YYYY') AND TO_DATE('" + sdf.format(params['END']) + "', 'DD-MM-YYYY')"
}
// if any parameter is not null, we should apply the where clause otherwise we return an empty dataset
if(where.length){
    this.queryText = sql + where + " order by start_date desc"
}else{
    this.queryText = sql + " WHERE     1 = 2n"
}

sources:
http://www.eclipsezone.com/eclipse/forums/t80682.html

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: