wood burning stoves*
The moose likes JDBC and the fly likes Java 7: MS Excel select results in Null Pointer Exception with JdbcRowSetImpl Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java 7: MS Excel select results in Null Pointer Exception with JdbcRowSetImpl" Watch "Java 7: MS Excel select results in Null Pointer Exception with JdbcRowSetImpl" New topic
Author

Java 7: MS Excel select results in Null Pointer Exception with JdbcRowSetImpl

Peter Heide
Ranch Hand

Joined: Nov 04, 2006
Posts: 31
I would like to make a select statement on an MS 2002 Excel Table using JDK 7 under Windows XP 32 Bit.

I have an Excel file named Test.xls with a Sheet named Table. The Sheet is our Table and has two columns ID and Name:



In Windows Settings I created a data source named EXCEL_TEST which is connected to the Excel file.

The following code works fine under Java 6:



The output is the content of the Excel file:



Now I am using Java 7 JDK, with the following code. I am trying to use the new JDBC features described in the book Java 7 New Features Cookbook from Richard M. Reese and Jennifer L. Reese in chapter Using the RowSetFactory:




It gives me the following error:



I think that the JdbcRowSetImpl class is not a part of the JDK 7. It could be in a JDBC Driver 4.1 from Microsoft. According to my knowledge there is no such driver from Microsoft realesed so far.

Any Ideas?







SCJA
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

This is only a guess from reading the stack trace, but it looks as if there is a property your not setting it is expecting.
Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

Peter Heide wrote:


I think that the JdbcRowSetImpl class is not a part of the JDK 7. It could be in a JDBC Driver 4.1 from Microsoft. According to my knowledge there is no such driver from Microsoft realesed so far.

Any Ideas?



I got the same results you did. I got curious about why it failed. Your code worked fine when used on real databases, but the jdbc-odbc driver fails with Excel data sources. (I found that this driver is widely hated. There are alternatives, but I didn't investigate those)

jdbcRowSetImpl IS part of JDK 7, but the source of it is only available in openJDK for reasons I do not understand. To see the actual source, you have to look at the openJDK (http://grepcode.com/file/repository.grepcode.com/java/root/jdk/openjdk/6-b14/com/sun/rowset/JdbcRowSetImpl.java).
I grabbed that class and debugged it. Your code fails when JdbcRowSetImpl.java tries to call its own setProperties() method and the following two blocks don't work with Excel, apparently:



when I commented those two blocks out of my version of JdbcRowSetImpl, the query to excel worked! So, I guess what you would want to do is create your own class that extends
JdbcRowSetImpl and provide it a setProperties() method that does not try to set QueryTimeout or maxFieldSize. You could call that class PeterHeidesJdbcRowSetImpl .

since you have to change RowSetFactoryImpl to be able to call PeterHeidesJdbcRowSetImpl and RowSetFactoryImpl is "final"
You would have to just forget the factory and replace in your code:

with:


I don't know the implications of breaking the factory paradigm here or relying on or what may break later by overriding, but I found it interesting.
Peter Heide
Ranch Hand

Joined: Nov 04, 2006
Posts: 31
Thank you Tim for you deep analyses.

It seems that the vendor driver support for Java 7 and JDBC 4.1 is not yet really good.

We tried MS Access Database, but got the same error.

Then we decided to evaluate the Apache Derby Database, which I think is Open Source and was used in the Java 7 New Features Cookbook.

From the Oracle web pages where we downloaded the Java 7 JDK, there are already JDK 7 Demos and Samples available. They are having example code for the Derby database and the Derby download link.

They open a DB, creating a Table, filling it using SQL statements and drop the table again. We included the following code and it worked:



 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Java 7: MS Excel select results in Null Pointer Exception with JdbcRowSetImpl
 
Similar Threads
jdbc problem
Creating JAR
ERROR: java.sql.SQLException: No data found
java.sql.SQLException: No suitable driver
NPE