Null pointer error while running JDBC code on another machine

[Marsoni Hutao]

Hi,

I'm writing a Java Swing app in Eclipse. I've exported as a runnable jar. And when I run it. Everything is working fine. So I've sent the same JAR to my second machine. And When I ran it, to my Surprise, this therw a null pointer Exception.

Below is a piece of my code.

try {
            String dburl = path.getDBUrl();

            System.out.println(dburl);

            textArea.append(dburl + "\n");
            System.out.println(path.getSystemId + " s id \n");
            textArea.append(path.getSystemId + "\n");

            // connect to Excel
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
            Connection myConn = DriverManager.getConnection(dburl);
            String queryString = "select sum(Pages) as totalUnitsCount,sum(TotalErrors) as totalError, Sum(IIF(Type Like 'Formatting Error ',(TotalErrors),0)) as FormsattingCount, Sum(IIF([Type] like 'X Ref Error ',(TotalErrors),0)) as [X ref CountErrs],"
                    + "Sum(IIF(((Type Like 'Formatting Error ' and Critical<>0)),(Critical),0)) as FormsattingErrorCritical,"
                    + "Sum(IIF(((Type Like 'X Ref Error ' and Critical<>0)),(Critical),0)) as XRefErrorCritical,"
                    + "Count(IIF(((Type Like 'Formatting Error ' and NonCritical<>0)),1,null)) as FormsattingErrorNonCritical,"
                    + "Count(IIF(((Type Like 'X Ref Error ' and NonCritical<>0)),1,null)) as XRefErrorNonCritical"
                    + "  from [Quality Sheet$]";

            statement = myConn.prepareStatement(queryString);
            resultSet = statement.executeQuery();
            ResultSetMetaData rsMetaData = resultSet.getMetaData();
            System.out.println(rsMetaData.getColumnCount());

This is working fine on my Machine, But in my second machine it is throwing the exception in the below line.

    Connection myConn = DriverManager.getConnection(dburl);
Here is my SetTheExcelPath file

public class SetTheExcelSrcPath {
    String getSystemId = System.getProperty("user.name");

    public String getDBUrl() {
        return "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\Users\\" + getSystemId
                + "\\Desktop\\Quality Sheets\\quality_template.xlsx;";
    }

}

Please let me know where Am I going wrong and how Can I fix this.

Here I'm not getting Exception on my machine, it is getting thrown on another machine, I would have debugged if the issue was on my Machine.

Here is my stack trace

java.lang.NullPointerException at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:453) at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at Src.Files.TestExcel.(TestExcel.java:39) at Src.Files.ReportsGeneratorGUI$3.actionPerformed(ReportsGeneratorGUI.java:91) at javax.swing.AbstractButton.fireActionPerformed(Unknown Source) at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source) at javax.swing.DefaultButtonModel.setPressed(Unknown Source) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source) at java.awt.Component.processMouseEvent(Unknown Source) at javax.swing.JComponent.processMouseEvent(Unknown Source) at java.awt.Component.processEvent(Unknown Source) at java.awt.Container.processEvent(Unknown Source) at java.awt.Component.dispatchEventImpl(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source) at java.awt.Container.dispatchEventImpl(Unknown Source) at java.awt.Window.dispatchEventImpl(Unknown Source) at java.awt.Component.dispatchEvent(Unknown Source) at java.awt.EventQueue.dispatchEventImpl(Unknown Source) at java.awt.EventQueue.access$500(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.awt.EventQueue$3.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.awt.EventQueue$4.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source) at java.awt.EventQueue.dispatchEvent(Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.pumpEvents(Unknown Source) at java.awt.EventDispatchThread.run(Unknown Source)

Thanks

[Michelle Nicholes]

I hate to state the obvious but its saying it cant find the file.  

I know this is probably not the answer you were looking for but I always used the JFilechooser to select a file so I knew I got the URL correct.
There are tons of examples on the net on how to use JFilechooser.

But if this is a config file.  I always used the getRecource

		URL url = PersonList.class.getResource("/Files/data.txt");
		File f = null;
		try 
		{
			f = new File(url.toURI());
		} 
		catch (URISyntaxException e) 
		{
			e.printStackTrace();
		}

And there are examples for getRecource on the net as well.

[Michelle Nicholes]

My Bad, I just remembered your doing jdbc.odbc connection.  Its sounds like you have not setup the odbc part on the second machine.

http://www.dreamincode.net/forums/topic/207385-java-to-mysql-connection-using-the-jdbc-odbc-bridge/

[Michelle Nicholes]

Another possibility is that maybe the driver is missing on the second machine

[Michelle Nicholes]

I found this example.   Notice it defines DriverID.   Will do some research to see what that does

import java.sql.Connection;
import java.sql.DriverManager;

public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:/data.xls;"
        + "DriverID=22;READONLY=false";
    Connection con = DriverManager.getConnection(myDB, "", "");
  }
}

[Marsoni Hutao]

for debugging I've added the below code in the file.

		try {
			try {
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
			} catch (InstantiationException | IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (ClassNotFoundException e) {
			textArea.append("Where is your Oracle JDBC Driver?\n");
			e.printStackTrace();
			return;
		}
		textArea.append("JDBC ODBC Driver Registered!\n");

		Connection myConn;
		try {
			myConn = DriverManager.getConnection(dburl, "", "");
		} catch (SQLException e) {
			textArea.append("Connection Failed! Check output console\n");
			e.printStackTrace();
			return;
		}

		if (myConn != null) {
			textArea.append("You made it, take control your database now!\n");
		} else {
			textArea.append("Failed to make connection!\n");
		}

When I run this in my machine, I get

JDBC ODBC Driver Registered!
You made it, take control your database now!

When I do the same on the second machine, it gives

JDBC ODBC Driver Registered!

But not returning the connection status.  

[Dave Tolls]

myConn will never be null.
It cannot.
If getConnection fails it will throw an exception.

I would check your logs.

[Marsoni Hutao]

Dave Tolls wrote:myConn will never be null.
It cannot.
If getConnection fails it will throw an exception.

Thanks for that quick point  

I would check your logs.

where can I find them in the target system?  

[Marsoni Hutao]

Dave Tolls wrote:
I would check your logs.

Here is my log output

java.lang.NullPointerException
       at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:453)
       at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153)
       at java.sql.DriverManager.getConnection(Unknown Source)
       at java.sql.DriverManager.getConnection(Unknown Source)
       at Src.Files.TestExcel.TestMyExcel(TestExcel.java:51)
       at Src.Files.ReportsGeneratorGUI$3.actionPerformed(ReportsGeneratorGUI.j
ava:93)
       at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
       at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
       at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
       at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
       at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Sour
ce)
       at java.awt.Component.processMouseEvent(Unknown Source)
       at javax.swing.JComponent.processMouseEvent(Unknown Source)
       at java.awt.Component.processEvent(Unknown Source)
       at java.awt.Container.processEvent(Unknown Source)
       at java.awt.Component.dispatchEventImpl(Unknown Source)
       at java.awt.Container.dispatchEventImpl(Unknown Source)
       at java.awt.Component.dispatchEvent(Unknown Source)
       at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
       at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
       at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
       at java.awt.Container.dispatchEventImpl(Unknown Source)
       at java.awt.Window.dispatchEventImpl(Unknown Source)
       at java.awt.Component.dispatchEvent(Unknown Source)
       at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
       at java.awt.EventQueue.access$500(U

[Dave Tolls]

It is at this point that I say that the JDBC-ODBC bridge driver was never intended for real use.
It is buggy.
It has appalling error handling.
And so on...

It's why it was removed, as people kept falling into the trap of actually trying to use it (usually with Access).

Anyway, if you have to use it (and I doubt there's any decent free Excel ODBC ones out there) I suppose you're stuck with it.

My guess is that the file doesn't exist, or is not able to be opened, or is already open, or some ODBC magic is missing?

[Roel De Nijs]

Any chance you are using a different JDK on your second machine?

According to this Oracle article about the status of the JDBC-ODBC Bridge, it will be removed in JDK8. So that might be a reason why it doesn't work on the first machine (running JDK7 or prior) and not on the other one (running JDK8).

[Roel De Nijs]

Because it seems the JdbcOdbcDriver is loaded (otherwise Class.forName would throw a ClassNotFoundException), you are probably running JDK7 (or prior) on both machines. Because I can't find the source for the JdbcOdbcDriver (it is not included in the sources for JDK7 nor JDK6), I decompiled the version of JDK7 and then I searched for the initialize() method. Although this method is pretty useless in its decompiled version, I noticed you can enable tracing to get more information about what's going on and what's going wrong.

This Oracle article about JDBC-ODBC Bridge Driver mentions clearly how you can enable tracing.

9.3.6     Debugging
The Bridge provides extensive tracing when DriverManager tracing is enabled. The following line of code enables tracing and sends it to standard out:

    java.sql.DriverManager.setLogStream(java.lang.System.out);

So I would add this line to the main method and see if this additional output shows what's wrong on your second machine

java.sql.DriverManager.setLogStream(java.lang.System.out);

Hope it helps!
Kind regards,
Roel