Tuesday, November 09, 2010

IReport No suitable driver found

I knew I had checked for everything and still some of my Jasper reports were unable to run from Jasper:
Caused by: java.sql.SQLException: No suitable driver found for jdbc:...

In my case it turned out to be a class loader problem. For some reason (custom class loaders?) the Netbeans GUI is not able to find the JDBC driver. Interesting enough this does not happen for all reports and probably there are situations (specific xml tags inside jrxml?) where the bug shows up.

Some proposed solutions

Sometimes it just works copying your diver jar file into the iReport lib folder. Below are examples in OSX and Windows for my version of iReport (Your folders might differ slightly, that is why I am providing a folder list). The specific driver failing in my case was the sqlite driver.

In Mac OSX
Nestor-Urquizas-MacBook-Pro:~ nestor$ ls -l /Applications/iReport.app/Contents/Resources/ireport/platform9/lib 
total 7240
-rw-r--r--  1 nestor  admin   255928 Jul 20 11:29 boot.jar
-rwxr-xr-x  1 nestor  admin    14731 Jul 20 11:29 nbexec
-rw-r--r--  1 nestor  admin    93696 Jul 20 11:29 nbexec.exe
-rw-r--r--  1 nestor  admin    23345 Jul 20 11:29 org-openide-modules.jar
-rw-r--r--  1 nestor  admin   626203 Jul 20 11:29 org-openide-util.jar
-rw-r--r--  1 nestor  admin  2684154 Oct 28 20:32 sqlitejdbc-

In Windows
C:\>dir "C:\Program Files\Jaspersoft\iReport-3.7.4\platform9\lib"
 Volume in drive C has no label.
 Volume Serial Number is DC32-F12F

 Directory of C:\Program Files\Jaspersoft\iReport-3.7.4\platform9\lib

11/09/2010  03:03 PM    <DIR>          .
11/09/2010  03:03 PM    <DIR>          ..
07/20/2010  10:37 AM           255,928 boot.jar
07/20/2010  10:37 AM            14,731 nbexec
07/20/2010  10:37 AM            97,792 nbexec.exe
07/20/2010  10:37 AM            93,696 nbexec.exe_original
07/20/2010  10:37 AM            23,345 org-openide-modules.jar
07/20/2010  10:37 AM           626,203 org-openide-util.jar
10/28/2010  07:32 PM         2,684,154 sqlitejdbc-
But sometimes it does not. Some people (especially on Widnows and linux) report dropping the jar in the $JAVA_HOME/lib/ext/

A perhaps better solution

Why these hacks? What is really going on?

The answer to this question will need to be found in the specific environment and the way iReport (netbeans) work. If you are running a Unix/Linux/OSX syste you can use lsof to find out what is going on and get the issue resolved.

My test case would be a report which uses for example sqlserver and a subreport which uses sqlite. You might notice that the subreport cannot render and sometimes iReport will complain about no suitable driver while others simply the report comes back blank (which is even worst we agree). Follow the below steps and let me know if it does work for you.
  1. Remove from preferences classpath any driver jar file
  2. Run the master report
  3. Check with "lsof|grep sqlite" that ireport did not open a sqlite file (you need to look for the java entries or you could use option -p to get a faster response just for the ireport process). If it lists any sqlite file open just delete the file. In OSX iReport opens a temp file like /private/var/folders/_6/_1d763x107sffygx2l8rr3vw0000gn/T/libsqlitejdbc-1619728097364591810.lib so be aware you are not looking only for jar extension. Even though that file is temporary it might have a caching impact. Sometimes I could even see previous classpath definitions that were already removed which is clearly coming from some internal caching. It is very important that lsof shows no sqlite files open and that you get an error close to the below:
    java.sql.SQLException: No suitable driver found for jdbc:sqlite:/path/to/mysql.db
  4. Now that we know we have a system with no caching restart ireport
  5. Go to preferences and in the classpath add the jar file
  6. The result of lsof command will be showing the newly added jar and then you think you got it: if the jar is referenced iReport should have it in the classpath indeed. Not so fast my friend, classloaders work in misterious ways ;-) Unfortunately it will fail again.
  7. Delete the main report jasper file and restart iReport.
  8. Edit the main report with something like:
  9. Now when you run it you get the driver loaded indeed. What did we do? We just forced the loading of the driver needed by the subreport from the main report. Note that in OSX as I said before you get something like /private/var/folders/_6/_1d763x107sffygx2l8rr3vw0000gn/T/sqlite-3.7.2-libsqlitejdbc.jnilib from lsof instead of the jar driver. Optimizations? Who knows but that caching game really takes the life out of any mortal


Deryl Spielman said...

In iReport you can select Tools-->Options-->Classpath tab and choose jars to be on the classpath.

Nestor Urquiza said...

Of course! that is the way we all do it but that was not enough this time.

If it ever happens to you that specifying yoir jar is not enough then this workaround will help. This is just another class loader problem to be added on the stack.

huda said...

Thank you very much , I tried your solution by copying the driver into the lib folder and finally it is working. thank you very much

Huda Youssef

Nestor Urquiza said...

@huda Glad it helped!