SQL Converter

Hello all,

Do anyone know if it is possible to increment birt to change some specific commands in my dataset query to the selected database (according to the driver selected) sql syntax ?

For example i have a select written in Oracle sql syntax, and i have a few specific language commands like NVL, but im connecting to a mysql database, so i would increment a button that would analyse that query and change those specific commands to the targeted database, so that NVL command in oracle would change to ISNULL accorindg to my example. I could change this manually, but i wish this could be made by incrementing the engine itself or by a plugin.

Find more posts tagged with

Comments

JasonW

This is not currently implemented in BIRT but I can think of a couple of ways to do this. You could always examine the query at driver in the beforeOpen script and change it there or if you want a nice single plugin to do this implement the driverBridge extension point for the jdbc plugin. I have used this plugin to supply my own connection object, throw away calls, and to modify a query that is getting ready to run. Take a look at this post that shows how to use it to change a connection (not really what you want but should get you started).
http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html

If you need more help do the driver bridge let me know. I think it is your best alternative and provides a write once and use many times option.

Jason

Gustavo.Av

Jason, do you know any thread explaining how to create and deploy a plugin to BIRT ?

JasonW

BIRT plugins are really no different that any Eclipse plugins. Although in your case you would not be implementing any ui. Here is an example of a simple eclipse plugin
http://www.eclipse.org/articles/Article-Your First Plug-in/YourFirstPlugin.html

The blogpost pointed to code that is a plugin that you can download into an eclipse workspace and export. The exported plugin could then be placed in the birt designer and the viewer runtime plugins directory. If you want a shelled one for what you are doing let me know. I can put one together for you. Just give me one example of a change in the query you want me to change based on the dburl.

Jason

JasonW

btw If you want I could write it up on Birt World for others to use as well.

Jason

Gustavo.Av

I would be please if you could do that Jason.
I want something like this:

Oracle SQL:
select NVL(COLUMN1, '--') from table

the plugin would change the query according to the driver im passing through, lets say for this example im passing a mySql driver so the query would change to:

select ISNULL(COLUM1, '--') from table

You could write up here or in blogspot its up to you, im just trying to get access to birtworld, because network politics here is really annoying but ill get it soon.

JasonW

ok. Give me a couple of days and I will have it done.

Jason

Gustavo.Av

Thank you very much

JasonW

I have been working a bit on this and wanted to upload a quick example. Essentially you just want to override the default JDBC drivers Statement class. To do this you need to implement the driverBridge extension point with three classes. The first is the driver

package org.eclipse.birt.report.data.changequery;

import org.eclipse.birt.report.data.oda.jdbc.*;
import org.eclipse.datatools.connectivity.oda.IConnection;
import org.eclipse.datatools.connectivity.oda.OdaException;

public class MyJdbcDriver extends OdaJdbcDriver {

public IConnection getConnection(String connectionClassName) throws OdaException
{

return new MyConnection();

}

}

All this does is hook your version of the connection.

package org.eclipse.birt.report.data.changequery;

import org.eclipse.datatools.connectivity.oda.IQuery;
import org.eclipse.datatools.connectivity.oda.OdaException;

public class MyConnection extends org.eclipse.birt.report.data.oda.jdbc.Connection {
private boolean opn = false;

public MyConnection() {
// TODO Auto-generated constructor stub
}
@Override
public IQuery newQuery( String dataSourceType ) throws OdaException
{
return new MyStatement( jdbcConn );
}
}

Which lets you hook your Statement class

package org.eclipse.birt.report.data.changequery;

import java.sql.Connection;
import java.sql.SQLException;

import org.eclipse.birt.report.data.oda.jdbc.Statement;

import org.eclipse.datatools.connectivity.oda.OdaException;

public class MyStatement extends Statement {
private String mydbtype;
private String mydbdriver;

public MyStatement(Connection connection) throws OdaException {
super(connection);
try {
mydbtype = connection.getMetaData().getDatabaseProductName();
mydbdriver = connection.getMetaData().getDriverName();

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

@Override
public void prepare(String command) throws OdaException {
// TODO Auto-generated method stub
command = "Select * from orderdetails";
super.prepare(command);
}

}

In this example I just change the query completely. In your case use mydbtype or mydbdriver variables to determine how you want to change the query. The plugin.xml for this plugin is:

<?xml version="1.0" encoding="UTF-8"?>
<?eclipse version="3.2"?>
<plugin>
<extension
id="org.eclipse.birt.report.data.testjdbc"
point="org.eclipse.datatools.connectivity.oda.consumer.driverBridge">

<bridge
driverType="org.eclipse.birt.report.data.oda.jdbc.OdaJdbcDriver"
bridgeId="org.eclipse.birt.report.data.changequery">
</bridge>
</extension>
<extension
point="org.eclipse.datatools.connectivity.oda.dataSource">
<dataSource
odaVersion="3.0"
driverClass="org.eclipse.birt.report.data.changequery.MyJdbcDriver"
defaultDisplayName="Sample Driver Bridge"
setThreadContextClassLoader="false"
id="org.eclipse.birt.report.data.changequery"/>
<dataSet
defaultDisplayName="%dataset.query.sqltype"
id="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet">
<dataTypeMapping
nativeDataType="BIT"
nativeDataTypeCode="-7"
odaScalarDataType="Integer"/>
</dataSet>
</extension>
</plugin>

The example is attached.

Jason

Gustavo.Av

Ty for your help Jason, sorry for my delay to answer this i was a bit occupied this past week but ill get some work on this now thx.

Gustavo.Av

Oh...just another thing Jason.....is it possible to pass values from my PhP application to a plugin or to a class inside my plugin ?

JasonW

How are you calling the viewer from php currently. There are a couple of ways to do this.

Jason

Gustavo.Av

im calling it throught GET and passing the URL parameters from my Zend Server to Tomcat and opening the viewer in another page.

Btw my converter plugin is almost getting ready when i have it ill attach it here ,so if you want to put it in blogspot or another forum to help others.

JasonW

Just add &paramname=value to the url where paramnam is the name to the report parameter.

Jason