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

[Peter Heide]

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:

ID	Name
1	Tana
2	Peter
3	Robert
4	Erik
5	Julian
6	Michael

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:

import java.sql.*;

public class JDBCAccess {

	public static void main(String[] args) {
		JDBCAccess jdbcAccess = new JDBCAccess();
		jdbcAccess.run();
	}

	public void run() {
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// javasubname ist der bei der Datenquelle festgelgte subname
			Connection connection = DriverManager.getConnection("jdbc:odbc:EXCEL_TEST", "", "");
				Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery
			("SELECT * FROM [Table$]");
			System.out.println("ID, Name: ");
			while (result.next()) {
				String sID = result.getString("ID");
				String sVorname = result.getString("Name");

				System.out.println(sID + " " + sVorname);
			}
			result.close();
			statement.close();
			connection.close();
		}
		catch(java.lang.ClassNotFoundException e) {
			System.out.println("JDBC-ODBC-Driver not found");
		}
		catch(java.sql.SQLException e) {
			System.out.println("Error on database"
					+ e.getMessage());
		}
	}
}

The output is the content of the Excel file:

ID, Name: 
1.0 Tana
2.0 Peter
3.0 Robert
4.0 Erik
5.0 Julian
6.0 Michael

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:

package Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class RowSetInterface {

	public static void main(String[] args) {

		String username = "";
		String password = "";
		String dbUrl = "jdbc:odbc:EXCEL_TEST";
		String command = "SELECT * FROM [Table$]";

		RowSetFactory rowSetFactory = null;
		JdbcRowSet rowSet;
		try {

			rowSetFactory = RowSetProvider.newFactory(
					"com.sun.rowset.RowSetFactoryImpl", null);
			rowSet = rowSetFactory.createJdbcRowSet();
			rowSet.setUrl(dbUrl);
			rowSet.setUsername(username);
			rowSet.setPassword(password);

			rowSet.setCommand(command);
			rowSet.execute();

			while (rowSet.next()) {
				System.out.println(rowSet.getInt("ID") + "---"
						+ rowSet.getString("Name"));
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}

	}
}

It gives me the following error:

Exception in thread "main" java.lang.NullPointerException
	at com.sun.rowset.JdbcRowSetImpl.setProperties(Unknown Source)
	at com.sun.rowset.JdbcRowSetImpl.execute(Unknown Source)
	at Test.RowSetInterface.main(RowSetInterface.java:33)

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?

[Wendy Gibbons]

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]

Peter Heide wrote:

Exception in thread "main" java.lang.NullPointerException
	at com.sun.rowset.JdbcRowSetImpl.setProperties(Unknown Source)
	at com.sun.rowset.JdbcRowSetImpl.execute(Unknown Source)
	at Test.RowSetInterface.main(RowSetInterface.java:33)

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:

      try {
            ps.setMaxFieldSize(getMaxFieldSize());
        } catch (SQLException ex) {
            System.err.println(jdbcResBundle.handleGetObject("jdbcrowsetimpl.setmaxfieldsize").toString() +
                                ex.getLocalizedMessage());
        }

        try {
            ps.setQueryTimeout(getQueryTimeout());
        } catch (SQLException ex) {
           System.err.println(jdbcResBundle.handleGetObject("jdbcrowsetimpl.setquerytimeout").toString() +
                                ex.getLocalizedMessage());
        }

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:

  rowSet = rowSetFactory.createJdbcRowSet();
 

with:

   rowSet = new PeterHeidesJdbcRowSetImpl();		

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]

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:

 		RowSetFactory rowSetFactory = null; 
  		JdbcRowSet rowSet; 

  		try {

  			conn.commit();
  			rowSetFactory = RowSetProvider.newFactory(
  					"com.sun.rowset.RowSetFactoryImpl", null);
  			rowSet = rowSetFactory.createJdbcRowSet(); 
  			
  			rowSet.setUrl(protocol + dbName + ";create=false"); 
  			rowSet.setUsername(username); 
  			rowSet.setPassword(password); 

  			rowSet.setCommand("SELECT num, addr FROM location ORDER BY num"); 
  			rowSet.execute(); 

  			while (rowSet.next()) {
  				System.out.println("Hello --- "
  						+ rowSet.getString("addr"));
  			}

  		} catch (SQLException e) {
  			e.printStackTrace();

  		}