... 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 solutionsSometimes 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-22.214.171.124.jar
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-126.96.36.199.jarBut sometimes it does not. Some people (especially on Widnows and linux) report dropping the jar in the $JAVA_HOME/lib/ext/
A perhaps better solutionWhy 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.
- Remove from preferences classpath any driver jar file
- Run the master report
- 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
- Now that we know we have a system with no caching restart ireport
- Go to preferences and in the classpath add the jar file
- 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.
- Delete the main report jasper file and restart iReport.
- Edit the main report with something like:
- 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