ReportEngine JDBC issue with SAS JDBC drivers

Hi, 
 
We are trying to execute reports that use the Birt Runtine engine. 
Those reports connect to a SAS db through the v 9.2 SAS JDBC driver named com.sas.rio.MVADriver and to a MySQL DB. 
 
Here are our findings: 
Birt Designer 2.5.0 + SAS JDBC Driver: Test JDBC connection does not work 
Birt Designer 2.5.1 + SAS JDBC Driver: Test JDBC connection does not work 
Birt Designer 2.5.2 + SAS JDBC Driver: Test JDBC connection DOES work! 
 
DB Visualizer (a standard Java DB querying tool <a class='bbc_url' href='http://www.dbvis.com/'>DbVisualizer - The Universal Database Tool</a>) + SAS JDBC Driver: Test JDBC connection DOES work. 
 
BIRT Runtime (ReportEngine)+ SAS JDBC Driver : We are getting numerous errors. As shown below. 
Please note that the SAS drivers jar files are put into the ReportEnginepluginsorg.eclipse.birt.report.data.oda.jdbc_2.5.2.v20100205drivers directory as specified. 
 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.adapter.impl.ModelAdapter adaptDataSet 
ATTENTION: handle type: org.eclipse.birt.report.model.api.OdaDataSetHandle 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.adapter.impl.ModelAdapter adaptDataSet 
ATTENTION: handle type: org.eclipse.birt.report.model.api.OdaDataSetHandle 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.adapter.impl.ModelAdapter adaptDataSet 
ATTENTION: handle type: org.eclipse.birt.report.model.api.OdaDataSetHandle 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.adapter.impl.ModelAdapter adaptDataSet 
ATTENTION: handle type: org.eclipse.birt.report.model.api.OdaDataSetHandle 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.adapter.impl.ModelAdapter adaptDataSet 
ATTENTION: handle type: org.eclipse.birt.report.model.api.JointDataSetHandle 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager loadDriverExtensions 
INFO: Found JDBC driverinfo extension: driverClass=org.apache.derby.jdbc.EmbeddedDriver, connectionFactory=null 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager$DriverClassLoader addNewURL 
INFO: JDBCDriverManager: found JAR file drivers/mysql-connector-java-5.0.6-bin.jar. URL=bundleentry://40.fwk26100519/drivers/mysql-connector-java-5.0.6-bin.jar 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager$DriverClassLoader addNewURL 
INFO: JDBCDriverManager: found JAR file drivers/sas.core.jar. URL=bundleentry://40.fwk26100519/drivers/sas.core.jar 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager$DriverClassLoader addNewURL 
INFO: JDBCDriverManager: found JAR file drivers/sas.security.sspi.jar. URL=bundleentry://40.fwk26100519/drivers/sas.security.sspi.jar 
4 mai 2010 14:42:29 org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager$DriverClassLoader addNewURL 
INFO: JDBCDriverManager: found JAR file drivers/sas.svc.connection.jar. URL=bundleentry://40.fwk26100519/drivers/sas.svc.connection.jar 
4 mai 2010 14:42:29 org.eclipse.birt.report.engine.api.impl.RunAndRenderTask doRun 
GRAVE: Error happened while running the report. 
java.lang.ExceptionInInitializerError 
at com.sas.rio.RIOUtil.getWorkspace(RIOUtil.java:1478) 
at com.sas.rio.RIOUtil.<init>(RIOUtil.java:115) 
at com.sas.rio.MVAConnection.<init>(MVAConnection.java:192) 
at com.sas.rio.MVADriver.connect(MVADriver.java:267) 
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager$WrappedDriver.connect(JDBCDriverManager.java:1162) 
at java.sql.DriverManager.getConnection(DriverManager.java:525) 
at java.sql.DriverManager.getConnection(DriverManager.java:140) 
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.doConnect(JDBCDriverManager.java:251) 
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.getConnection(JDBCDriverManager.java:212) 
at org.eclipse.birt.report.data.oda.jdbc.Connection.connectByUrl(Connection.java:234) 
at org.eclipse.birt.report.data.oda.jdbc.Connection.open(Connection.java:160) 
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaConnection.open(OdaConnection.java:239) 
at org.eclipse.birt.data.engine.odaconsumer.ConnectionManager.openConnection(ConnectionManager.java:165) 
at org.eclipse.birt.data.engine.executor.DataSource.newConnection(DataSource.java:190) 
at org.eclipse.birt.data.engine.executor.DataSource.open(DataSource.java:178) 
at org.eclipse.birt.data.engine.impl.DataSourceRuntime.openOdiDataSource(DataSourceRuntime.java:208) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.openDataSource(QueryExecutor.java:405) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:315) 
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:448) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178) 
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:145) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:159) 
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery.populatePreparedQuery(PreparedJointDataSourceQuery.java:553) 
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery.populatePreparedQuery(PreparedJointDataSourceQuery.java:492) 
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery.access$2(PreparedJointDataSourceQuery.java:490) 
at org.eclipse.birt.data.engine.impl.PreparedJointDataSourceQuery$JointDataSetQueryExecutor.createOdiQuery(PreparedJointDataSourceQuery.java:596) 
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:323) 
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:448) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:190) 
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:178) 
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:560) 
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:152) 
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265) 
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1875) 
at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:80) 
at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:62) 
at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:43) 
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:46) 
at org.eclipse.birt.report.engine.internal.executor.l18n.LocalizedReportItemExecutor.execute(LocalizedReportItemExecutor.java:34) 
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:65) 
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90) 
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doRun(RunAndRenderTask.java:170) 
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run(RunAndRenderTask.java:75) 
 
 
Caused by: java.lang.NullPointerException 
at com.sas.services.connection.ConnectionFactoryManager.<clinit>(ConnectionFactoryManager.java:194) 
... 50 more 
 
 
It seems that the connectionFactory is not found. Is this correct? 
I tried to add it to the driverInfo in plugin.xml but then an error message displays that the Connection Factory cannot be instantiated. 
Also, looking at the following web doc: 
<a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=135246#c3'>https://bugs.eclipse.org/bugs/show_bug.cgi?id=135246#c3</a> 
How do we externally supply a JDBC connection? Setting the OdaJDBCDriverClassPath is enough? 
 
Would it be the right way to go or am I missing something big? 
 
Thanks 
 
Stephane

Find more posts tagged with

Comments

JasonW

Stephane,

Is this failing with the RE API in version 2.5.2 of BIRT?

The OdaJDBCDriverClassPath just allows you to relocate the driver from the
ReportEnginepluginsorg.eclipse.birt.report.data.oda.jdbc_2.5.2.v20100205drivers directory to some other directory. If you use this approach set the value of the OdaJDBCDriverClassPath in the app context as a classpath string.

config.getAppContext().put("OdaJDBCDriverClassPath", "location1/jar1.jar;location2/jar2.jar");

The driver bridge extension point is used if you want to supply a connection at runtime. This is generally used with some connection pooling scheme.

Jason

Sintissar

Hi Jason,

Thank you for your prompt reply.

Yes we use RE API 2.5.2.
Do you see why the SAS Driver would fail with the RE API 2.5.2?

I am looking at inserting the connection at runtime for the time being. I am stuck on the following point:
On my rptdesign, I use a datasource called <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="sasConn" id="7">

Should I set the driver properties to org.eclipse.birt.report.data.oda.subjdbc.SubOdaJdbcDriver with url, userName and pwd or is there a more appriopriate way for this?

In other words: How do I map a connection that has been created using the driver bridge extension point to a given oda-data-source?

JasonW

I have not used the SAS driver, I was just wondering what issue you had with the 2.5.0 and 2.5.1 designers. 
 
Take a look at these blogs that describe the driverBridge extension point and various connection methods: 
 
<a class='bbc_url' href='http://birtworld.blogspot.com/2005/12/using-supplied-connection-with-birt.html'>BIRT World: Using a supplied connection with BIRT</a> 
<a class='bbc_url' href='http://birtworld.blogspot.com/2007/01/birt-connection-pooling-continued.html'>BIRT World: BIRT Connection Pooling Continued</a> 
<a class='bbc_url' href='http://birtworld.blogspot.com/2008/11/birt-connection-pooling-continued-again.html'>BIRT World: BIRT Connection Pooling Continued Again</a> 
 
Jason

Sintissar

In version 2.5.0 and 2.5.1 all I could see is a test connection button that would not do much. 
 
Regarding the posts you sent. I am actually in the exact same situation as the one that led to your comment: 
<a class='bbc_url' href='http://birtworld.blogspot.com/2008/11/birt-connection-pooling-continued-again.html?showComment=1263568465477#c8145685655978973972'>BIRT World: BIRT Connection Pooling Continued Again</a> 
 
Bascally, I have one report, 2 DB connections (one is MySQL, the other is SAS) and I am stuck with that.

JasonW

Try doing something like this:

Change the getConnection to always return an instance of appContextDBConnection()
public IConnection getConnection(String connectionClassName) throws OdaException
{

//if( passedInConnection != null){
return new appContextDBConnection();
//}else{
// return new org.eclipse.birt.report.data.oda.jdbc.Connection();
//}
}

Then modify the open method of the appContextDBConnection class to look at one of the connection properties to decide whether to use the passed in connection.

public void open(Properties connProperties) throws OdaException
{
if( /*check connProperties for something like odaUser or odaDriverUrl*/){
super.jdbcConn = passedInConnection; }else{
super.open(connProperties);
}
}

Jason

Sintissar

Hi,

No, for some reasons, it did not work for me but I guess the myJdbc plugin was not accounted in the BIRT engine.

However, I have been able to customize the Connection by using some of the principle described in the sampledb (Derby) OSGI Bundle.

Do you think that is a correct way to go?

Anyway, many thanks, you made my day

Stephane

JasonW

Yes I think that is an excellent approach.

Jason