Bugzilla – Full Text Bug Listing
| Summary: | JNDI Datasource not being used with BIRT 3.7.1 | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| Product: | z_Archived | Reporter: | Ramses Gomez <ramgom> | ||||||
| Component: | BIRT | Assignee: | Birt-ReportViewer <Birt-ReportViewer-inbox> | ||||||
| Status: | NEW --- | QA Contact: | Sissi Zhu <szhu> | ||||||
| Severity: | major | ||||||||
| Priority: | P3 | CC: | bluesoldier, jasonweathersby | ||||||
| Version: | unspecified | ||||||||
| Target Milestone: | --- | ||||||||
| Hardware: | PC | ||||||||
| OS: | Windows 7 | ||||||||
| Whiteboard: | |||||||||
| Attachments: |
|
||||||||
|
Description
Ramses Gomez
Any update on this issue? What db are you accessing? Where do you have the driver jar? Jason Hi Jason, Sorry I just saw your comment I thought I would've gotten and email for the update. This is not related to the driver jars this is related that Birt can not access the resource reference: Dec 21, 2011 11:00:39 AM org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager JDBCDriverManager FINE: JDBCDriverManager starts up Dec 21, 2011 11:00:39 AM org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager getConnection FINE: Request JDBC Connection: driverClass=null; url=null; jndi name url=java:comp/env/jdbc/BamServerDataSource Dec 21, 2011 11:00:39 AM org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager getJndiDSConnection FINER: Calling getJndiDSConnection: JNDI name url=java:comp/env/jdbc/BamServerDataSource Dec 21, 2011 11:00:39 AM JndiDataSource getConnection FINER: ENTRY java:comp/env/jdbc/BamServerDataSource Dec 21, 2011 11:00:39 AM org.eclipse.birt.report.data.oda.jdbc.JndiDataSource getConnection INFO: javax.naming.NameNotFoundException: env not bound Dec 21, 2011 11:00:39 AM JndiDataSource getConnection as you can see for some reason the org.eclipse.birt.report.data.oda.jdbc.JndiDataSource can not access java:comp/env, if instead I use the global JNDI name it works with no issues. The problem is we can not use the global JNDI name since our application needs to be deployed to multiples application servers and we can not tie the report to a specific global JNDI name. The nullpointer exception is related to not having a driver specified in the report since I only want to use the JNDI name. Thanks Can you post some details on how you setup your JNDI connection so we can try to reproduce? Jason This is what I've done:
Created the appropriate datasource in jboss, then added the jboss-web.xml file to the war file with:
<resource-ref>
<res-ref-name>jdbc/BamServerDataSourceRef</res-ref-name>
<jndi-name>java:jdbc/BamServerDataSource</jndi-name>
</resource-ref>
Then added the resource reference to the web.xml file:
<resource-ref>
<description/>
<res-ref-name>jdbc/BamServerDataSourceRef</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
I'm also uploading two test cases I created one for Birt 2.6.2 and the other one for 3.7.1, it has a very simple report with the appropriate set up.
This is the statemente to crete the table to test:
CREATE TABLE `test`.`test` (
`idtest` INT(11) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NULL ,
PRIMARY KEY (`idtest`) );
You will find three file:
the datasource configuration xml file plus the two birt war files with the appropriate files inside.
Created attachment 211051 [details]
datasource config file
I wasn't able to upload the war files so I put them in the cloud and these are the links to access them: Birt 2.6.2 https://docs.google.com/open?id=0BxpMhw7SOiXONTY4ZjUyNjAtMTczYi00MTEzLTg4MDUtMDU5ZWZhYTNlZDlk Birt 3.7.1 https://docs.google.com/open?id=0BxpMhw7SOiXOMWEzNjVkZjctM2M1ZS00ZGM3LWIyZjctMGU2NTI0NDZlMTFj What version of JBoss are you using? I created a mysql-ds.xml file and copied it to my deploy directory. <?xml version="1.0" encoding="UTF-8"?> <!-- $Id: mysql-ds.xml 41016 2006-02-07 14:23:00Z acoliver $ --> <!-- Datasource config for MySQL using 3.0.9 available from: http://www.mysql.com/downloads/api-jdbc-stable.html --> <datasources> <local-tx-datasource> <jndi-name>MySqlDS</jndi-name> <connection-url>jdbc:mysql://localhost:3306/mydb</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>root</user-name> <password>root</password> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name> <!-- should only be used on drivers after 3.22.1 with "ping" support <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name> --> <!-- sql to call when connection is created <new-connection-sql>some arbitrary sql</new-connection-sql> --> <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql> --> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) --> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources> I then just use this for my Jndi url: java:/MySqlDS Hi Jason, Yes that's right if you use the GLobal JNDI name it works (java:/MySqlDS) however we can not use the global JNDI name we need to use a reference, since the same report should be able to work with multiple application servers (websphere, glassfish) We are using Jboss EAP 5.1.2 Thanks, Maybe I am not setting this up correctly but I have my data source: <?xml version="1.0" encoding="UTF-8"?> <!-- $Id: mysql-ds.xml 41016 2006-02-07 14:23:00Z acoliver $ --> <!-- Datasource config for MySQL using 3.0.9 available from: http://www.mysql.com/downloads/api-jdbc-stable.html --> <datasources> <local-tx-datasource> <jndi-name>env/jdbc/MySqlDS</jndi-name> <connection-url>jdbc:mysql://localhost:3306/mydb</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>root</user-name> <password>root</password> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name> <!-- should only be used on drivers after 3.22.1 with "ping" support <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name> --> <!-- sql to call when connection is created <new-connection-sql>some arbitrary sql</new-connection-sql> --> <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql> --> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) --> <metadata> <type-mapping>mySQL</type-mapping> </metadata> </local-tx-datasource> </datasources> and jboss web <jboss-web> <context-root>birt</context-root> <resource-ref> <res-ref-name>jdbc/MySqlDB</res-ref-name> <res-type>javax.sql.DataSource</res-type> <jndi-name>env/jdbc/MySqlDS</jndi-name> </resource-ref> </jboss-web> and this in my web.xml <resource-ref> <description> Resource reference to a factory for java.sql.Connection instances that may be used for talking to a particular database that is configured in the server.xml file. </description> <res-ref-name> jdbc/MySqlDB </res-ref-name> <res-type> javax.sql.DataSource </res-type> <res-auth> Container </res-auth> </resource-ref> I use this jndi name in the report: java:env/jdbc/MySqlDS This works, but if I change to java:comp/env/jdbc/MySqlDB it fails Yes you still have it wrong, still using the global jndi name a simple way to do it is add ref to every reference name.
The jndi nmae you set in the datasource file is the global jndi name, I would advise you not to use env at the begining so lets set it up to: jdbc/MySqlDS
<datasources>
<local-tx-datasource>
<jndi-name>jdbc/MySqlDS</jndi-name> (Global JNDI name)
Then you set up the resource reference in the web.xml file. You are telling your application to define a resource reference. Lets name it jdbc/MySqlDBRef
<resource-ref>
<description>
Resource reference to a factory for java.sql.Connection
instances that may be used for talking to a particular
database that is configured in the server.xml file.
</description>
<res-ref-name>
jdbc/MySqlDBRef (Resource Reference JNDI name)
</res-ref-name>
<res-type>
javax.sql.DataSource
</res-type>
<res-auth>
Container
</res-auth>
</resource-ref>
Then you map the resource reference JNDI name to the Global Jndi name in the jboss-web.xml file:
<jboss-web>
<context-root>birt</context-root>
<resource-ref>
<res-ref-name>jdbc/MySqlDBRef</res-ref-name> (Resource Reference JNDI name)
<res-type>javax.sql.DataSource</res-type>
<jndi-name>java:jdbc/MySqlDS</jndi-name> (Global JNDI name)
</resource-ref>
</jboss-web>
All resource references get created in the component context (hence using java:comp/env), now in your report you will use the JNDI Reference Name that gets created in the component context: java:comp/env/jdbc/MySqlDSRef
What's the idea behind this, if I want to use this report in glassfish which the global jndi name is jdbc/MySqlDS (notice no java: at the begining) I don't have to change my report since it is using a reference all I do is define the right sun-web.xml that does the mapping (similar to jboss-web.xml)
Hope this clarifies the problem you are having.
Thanks for the clarification. The code in the JDBC driver that does the lookup is defined like:
initCtx = new InitialContext( getDriverJndiProperties() );
namedObject = initCtx.lookup( jndiNameUrl );
Which means that jdbc/MySqlDBRef is not being located in this example. You would not have a JSP page that we could add to the viewer that successfully looks up the local JNDI would you? This would just be a test. Be sure to use the 3.7.1 viewer as it has the POJO runtime instead of the OSGi runtime.
Hi Jason, Sorry I'm not able to understand when you need from me? Can you please elaborate? Thanks This is strange. I created a jsp page and added it to the viewer:
<html>
<head><title>Enter to database</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>
<%
java.sql.Connection c1;
java.sql.Statement s1;
java.sql.ResultSet rs1;
java.sql.PreparedStatement pst1;
DataSource paymentDB;
c1=null;
s1=null;
pst1=null;
rs1=null;
try{
javax.naming.Context initCtx1 = new javax.naming.InitialContext();
javax.naming.Context envCtx1 = (javax.naming.Context) initCtx1.lookup("java:comp/env");
paymentDB = (DataSource) envCtx1.lookup("jdbc/MySqlDBref");
c1 = paymentDB.getConnection();
String sq1= "select * from orderdetails";
pst1 = c1.prepareStatement(sq1);
rs1 = pst1.executeQuery();
while( rs1.next() ){
%>
<tr>
<td>tst<%= rs1.getString("productCode") %></td>
</tr>
<%
}
if(pst1!=null) pst1.close();
if(rs1!=null) rs1.close();
if(c1!=null) c1.close();
}catch(Exception e){
System.out.println("inside the context exception");
e.printStackTrace();
}
%>
</body>
</html>
This works. But the following beforeFactory script always has an exception.
importPackage( Packages.java.io );
importPackage( Packages.javax.naming );
importPackage( Packages.java.util );
out = new PrintWriter( new FileWriter( "c:/test/jndievents2.txt", true ) );
try{
initCtx1 = new javax.naming.InitialContext();
envCtx1 = initCtx1.lookup("java:comp/env");
paymentDB = envCtx1.lookup("jdbc/MySqlDBref");
out.println( "con "+ paymentDB );
}catch(e){
out.println( "exception "+ e );
}
out.close();
This seems like local refs are not available in the report engine.
This is strange. I created a jsp page and added it to the viewer:
<html>
<head><title>Enter to database</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>
<%
java.sql.Connection c1;
java.sql.Statement s1;
java.sql.ResultSet rs1;
java.sql.PreparedStatement pst1;
DataSource paymentDB;
c1=null;
s1=null;
pst1=null;
rs1=null;
try{
javax.naming.Context initCtx1 = new javax.naming.InitialContext();
javax.naming.Context envCtx1 = (javax.naming.Context) initCtx1.lookup("java:comp/env");
paymentDB = (DataSource) envCtx1.lookup("jdbc/MySqlDBref");
c1 = paymentDB.getConnection();
String sq1= "select * from orderdetails";
pst1 = c1.prepareStatement(sq1);
rs1 = pst1.executeQuery();
while( rs1.next() ){
%>
<tr>
<td>tst<%= rs1.getString("productCode") %></td>
</tr>
<%
}
if(pst1!=null) pst1.close();
if(rs1!=null) rs1.close();
if(c1!=null) c1.close();
}catch(Exception e){
System.out.println("inside the context exception");
e.printStackTrace();
}
%>
</body>
</html>
This works. But the following beforeFactory script always has an exception.
importPackage( Packages.java.io );
importPackage( Packages.javax.naming );
importPackage( Packages.java.util );
out = new PrintWriter( new FileWriter( "c:/test/jndievents2.txt", true ) );
try{
initCtx1 = new javax.naming.InitialContext();
envCtx1 = initCtx1.lookup("java:comp/env");
paymentDB = envCtx1.lookup("jdbc/MySqlDBref");
out.println( "con "+ paymentDB );
}catch(e){
out.println( "exception "+ e );
}
out.close();
This seems like local refs are not available in the report engine.
So what does that means now? What would be the next step? I will ask someone on the data team to look at it. Also as a work around you know the jndi url can be changed using property binding, script, or a connection profile xml file. Can you please give me more details? it might help us as a work around. Thanks, Sure. Script
Add a beforeOpen javascript event handler and put in something like:
this.setExtensionProperty("odaJndiName","thenameyouwanttouse");
The name can come from a report parameter params["myjndi"].value or from session variable reportContext.getHttpServletRequest().getSession().getAttribute("myjndiname"); or from some external Java class or properties file.
The property binding feature is similar to the script approach, just create an expression in the data source editor property binding tab under JNDI URL:
I do not think the connection profile method supports jndi. Finally another option would be to have the data source in a rptlibrary and change it per install. Not ideal but still just an xml change.
Hi Jason Is there any update from the development team? Thanks hi Jason, I got this update from RedHat it will probably give you an idea: We were able to find a more definitive reason as to why the datasource lookup is failing. Access to java:comp/env (ENC) is based on what the thread's context class loader (TCCL) is set to at that given point. With JBoss, org.jboss.web.tomcat.service.WebCtxLoader$ENCLoader is what the TCCL should be set to when the war runs in order to look up "java:comp/env/john/doe". Testing birt, we saw that the TCCL was set to the birt.war instead of org.jboss.web.tomcat.service.WebCtxLoader$ENCLoader, thus causing the lookup to fail. For some reason, Birt is changing the TCCL. We have attached a WAR (enc-example.zip), which is setup like that of the birt.war. It also does a lookup of datasource using resource references. Instructions on setup/deployment can be found in the README provided. I will be attaching the sample they provided. Created attachment 211758 [details]
sample provided by RedHat
|