• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?






 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 820
IntelliJ IDE VI Editor Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 31
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:



 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic