scripted data source using sql entirely in rhino?

I'm trying to create a scripted sql data source that doesn't involve an "external" class to do the sql. Seems like this ought to be possible but I can't get it to find the appropriate driver class so I get "java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:....". The driver jar is in the usual place and it's found by my non-scripted jdbc data sources. Surely someone has done this before, anyone know what the trick is?

Joel

Find more posts tagged with

Comments

jsherriff

To clarify and save someone the trouble of posting regarding where my driver jar is or isn't located, it's in both the scriptlib dir so that rhino should be able to see it, as well as the usual drivers dir.

After adding the usual Class.forName("oracle.jdbc.driver.OracleDriver"); call, I now get the ClassNotFoundException that shows that it really isn't finding the class. Is there somewhere else I need to put it?

CBR

Hi,

i think the problem is that the rhino code is run by a seperate eclipse plugin, so you have to add your jdbc jar file to the classpath of this plugin. Because of the classload hierarchy of eclipse rcp a plugin can only see his own classpath and none of the other plugins.

Let me have a look for the plugin that runs the rhino. Can you provide me with the rhino script so that i can try out myself?

jsherriff

Hi, this little snippet is enough to test it's finding of the driver class. This is where it's throwing up for me.

importPackage(Packages.oracle.jdbc.driver); // doubt this is needed, but wth
importPackage(Packages.java.lang);

Class.forName("oracle.jdbc.driver.OracleDriver");

Of course you could replace the oracle driver with your favorite jdbc driver.
I have a copy of my ojdbc14.jar in my eclipse/plugins/org.eclipse.birt.report.viewer_2.3.2.r232_20090212/birt/scriptlib dir.

Thanks

J

CBR

Hi,

i think the problem is the classloader indeed. When BIRT runs a report, it gives Rhino a classloader so that it can load and run Java classes in the scriptlib dir. This classloader has access to all jars in this directory.
But when you call Class.forName() you ask a different classloader to load the class and this classloader hasn't got access to the scriptlib dir.

You can see it if you create a dummy class in your report project and add the following lines of rhino:
t = new Packages.de....DummyClass();
Class.forName("oracle.jdbc.driver.OracleDriver", true, t.getClass().getClassLoader());
Because you now give Class.forName the correct classloader (the one that already loaded dummy class for you and in consequence must have access to the scriptlib dir) you can now load the jdbc driver class without a ClassNotFoundException

Unfortunately the DriverManager of the JDBC framework won't find your driver if you didn't load it with the accordring classloader. So you now end up with a java.sql.NoSuitableDriverFoundException. There are some workarounds available to make it work (but it's really heavy code so i won't do that in Rhino).

I think the best solution is to use a java class that simple returns a Connection object. Just create that class and copyit together with the driver jar to the scriptlib dir.

jsherriff

Thanks for the research CBR - it's good to know what's going on. Interesting...the creation of an external class was what I was hoping to avoid.

If I go down that path, I have a secondary question that I haven't seen a clear answer for: what is the equivalent place to put the jar for runtime? I ran across this comment in an old birt commit log message:

"A new parameter called BIRT_VIEWER_SCRIPTLIB_DIR is added to web.xml. Similar as setting log directory in viewer, user can specify the location of his script libraries by using that parameter. If no value is specified, the default location birt/scriptlib will be used. When engine tries to load a script class (in script executor), it will look into all the JAR files in the script lib directory and its sub-directories."

"birt/scriptlib"? That's a bit vague. Do you know where that is?

CBR

Hi jsherriff,

when looking at the BIRT Viewer webapplication you will find a subdirectory birt/scriptlib where you can put your jar files to make them available to Rhino.
The eclipse report designer uses the BIRT Viewer application when you preview your report. You can find it in your plugins folder in the org.eclipse.birt.report.viewer_**** subfolder (when designing a report you should not use this scriptlib folder to make classes available, so its only when you deploy your report).

hope this helps

Etienne22

Hi,

Here is a rhino only solution. IT works by loading dynamically the jdbc driver. You need to put your jdbc.jar driver in the resource path

var SQL = {
driver: null,
connection : null,
loadDriver : function(pathToDriver, driverClassName) {
var f = reportContext.getResource(pathToDriver)
var u = f.toURI().toURL()
var sysloader = java.lang.ClassLoader.getSystemClassLoader()
var tabURL = new java.lang.reflect.Array.newInstance(u.getClass(), 1)
tabURL[0] = u
var classLoader = new java.net.URLClassLoader(tabURL)
var sysclass = classLoader.getClass()
var parameters = new java.lang.reflect.Array.newInstance(java.lang.Class, 1)
parameters[0] = u.getClass()
var method = sysclass.getDeclaredMethod("addURL",parameters)
method.setAccessible(true)
method.invoke(sysloader,tabURL)
this.driver = java.lang.ClassLoader.getSystemClassLoader().loadClass(driverClassName).newInstance()
},
connect : function(jdbcURL,database,user, password){
var prop = new java.util.Properties()
prop.setProperty('user', user)
prop.setProperty('password', password)
prop.setProperty('DatabaseName', database)
this.connection = this.driver.connect(jdbcURL,prop)
},
disconnect : function(){
this.connection.close();
},
query : function(queryStr){
var stmt = this.connection.createStatement()
var rs = stmt.executeQuery("select Top 10 id from JiraIssue");
var columnCount = rs.getMetaData().getColumnCount();
var v = []
while( rs.next() ){
var crow = []
for(var i = 1; i <= columnCount; i++) crow.push(rs.getObject(i));
v.push(crow);
}
rs.close() ;
stmt.close() ;
return v;
}
}

//example :

SQL.loadDriver("sqljdbc4.jar", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
SQL.connect('jdbc:sqlserver://d............', 'databasename', 'uname', 'password')
var v = SQL.query("select Top 10 id from JiraIssue")
SQL.disconnect()