java.sql.SQLException: Invalid handle

Question

I'm trying to teach myself how to connect to a msaccess database in java. I have set up a class to access the database as follows

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

public abstract class AccessDBConnect2 {
    public static Connection connect(){
        String fileName = "C:/Users/Bridget/Documents/EmployeeSys.accdb";
        Connection con = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
            con = DriverManager.getConnection(url,"","");
        } catch (Exception e) {
            // Handle exceptions    ...
            System.out.println(e.toString());
            System.out.println("A problem accessing the database");
            e.printStackTrace();
        } finally {
            try { if(con!=null) {con.close();} } catch (Exception e) {}
        }
        return con;
    }
public static void closeConnection(Connection conn){
    try{
        conn.close();
    }catch (Exception e){

    }
}

Then I have my code which is just trying to select everything from the table. I have created the table in msAccess and the code seems to get through the connect method in the above code without any problems, indicating it is finding the database and accessing it somewhat. The problem happens when I call the prepareStatement using the connection, i.e. code line:

stm = conn.prepareStatement(sql);

The full code is:

import java.sql.*;
public class Program2{
public static void main(String[] args) {
        try{
            // Load the JDBC driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

        // Establishing db connection
        Connection conn = AccessDBConnect.connect();

        // Displaying all records from employee file
        System.out.println("Display records of all employees");
        display(conn);

        // Closing the connection
        AccessDBConnect.closeConnection(conn);
    }catch (Exception e){
        System.out.println("Error");
    }
}

// Display details of all employees
public static void display(Connection conn){
    PreparedStatement stm = null;
    // SQL statement
    String sql = "SELECT * FROM Employee";
    ResultSet rs;
    try {
        stm = conn.prepareStatement(sql);   // Prepare the SQL statement
        rs = stm.executeQuery();            // Execture the SQL statement

        // Navigate through the ResultSet and print
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String gender = rs.getString("gender");
            String address = rs.getString("address");

            System.out.println("ID: \t \t" + id);
            System.out.println("Name: \t \t" + name);
            System.out.println("Gender: \t" + gender);
            System.out.println("Address: \t" + address);
            System.out.println(" ");
        }

    // Closing the resultSet
    rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public void test(){
    int a = "hello";
}

}

score 2 · Accepted Answer · edited May 23 '17 at 12:24

2

You are receiving the error because when you try to call .prepareStatement the connection is closed. Your AccessDBConnect2 class contains a finally block that closes the connection before it returns. Fix that class so it leaves the connection open.

By the way, the JDBC-ODBC Bridge has been removed from Java 8 and is effectively obsolete. You might be interested in this alternative:

Manipulating an Access database from Java without ODBC

edited May 23 '17 at 12:24

Community

1
1

answered Nov 28 '14 at 21:53

Gord Thompson

98,607
26
164
342

Thank you so much, the finally was the problem. This was such an obvious mistake, just the type that will drive you crazy for hours and hours. I will have a look at the alternative to the JDBC-ODBC in Java 8. Thanks again – Bridget Nov 30 '14 at 16:00

kbbucks · Answer 2 · 2014-11-28T20:34:25.970

-1

I've removed the obviously incorrect answer :) another possibility:

I would think the issue is in your connection to the database, try changing 'C:/Users/Bridget/Documents/EmployeeSys.accdb'
to
'C:\\Users\Bridget\Documents\EmployeeSys.accdb'

edited Nov 28 '14 at 20:34

answered Nov 24 '14 at 17:15

kbbucks

98
8

No, "Values can be retrieved using either the index number of the column or the name of the column." (ref: [here](https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html)) – Gord Thompson Nov 24 '14 at 17:41
Thanks for your help but my problem is before I get to this statement. It is at the first statement inside the try block: stm = conn.prepareStatement(sql); – Bridget Nov 25 '14 at 18:58
sorry that was a bit of a lazy answer above!, I should've known that - I would think the issue is in your connection to the database, try changing 'C:/Users/Bridget/Documents/EmployeeSys.accdb' to 'C:\\Users\Bridget\Documents\EmployeeSys.accdb' – kbbucks Nov 28 '14 at 20:31
Sorry, strike two. Paths with forward slashes like `C:/Users/ ...` work just fine. – Gord Thompson Nov 28 '14 at 21:54
Time for me to find a new hobby it seems... :) – kbbucks Nov 29 '14 at 11:16

java.sql.SQLException: Invalid handle

2 Answers2