Working with a saved Access parameter query under (JDBC-)ODBC

Question

In my Java application I have to use data that comes from an Access 2010 database. I used the graphical query creator from Access to create the appropriate query and it works great.

Unfortunately, when I try to use a prepared statement with that query (in order to use a parameter) in my Java application I got an NPE

messageChildrenRequest.setString(1, blockId);
ResultSet result = messageChildrenRequest.executeQuery();

The NPE occurs when i set the parameter with setString() and my query is not execute but when i look with the debugger the statement is not null...

My query given by access is :

SELECT IRSIDD.[BLOCK ID], IRSIDD.[IDENTIFICATION CHIFFREE], IRSIDD.MSG_ID, MAIN.SUB_FIELD_ID, MAIN.ORDER, FIELD.[FIELD NAME], FIELD.TYPE, FIELD.[RC 'TYPE] "
FROM IRSIDD LEFT JOIN (MAIN LEFT JOIN FIELD ON MAIN.SUB_FIELD_ID = FIELD.[FIELD ID]) ON IRSIDD.[BLOCK ID] = MAIN.BLOCK_ID "
WHERE ((IRSIDD.[BLOCK ID])=?)

The StackTrace gives me :

Exception in thread "main" 
java.lang.NullPointerException
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.clearParameter(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(Unknown Source)
    at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(Unknown Source)

When I tried a very simple prepared statement :

SELECT * FROM table1 WHERE table1.id = ?

I didn't get any NPE when setting the parameter so I suspect that Access and java JDBC do not have the same way to deal with join.

Does someone already that kind of problem or can confirm that the structure of my query is the problem here?

I suggest adding a self-contained example with your code, and the stack trace. — Mena, Aug 26 '15 at 10:42
If you are saying that you got NPE at `statement.setString(1,"myParam")` that means `statement` is null at that line. — Vimal Bera, Aug 26 '15 at 10:45
Looks similar to [this question](http://stackoverflow.com/questions/20620345/inserting-data-into-a-access-database). What's `blockId` initialised to? — Edd, Aug 26 '15 at 12:02
blockId is "1100b" and not null. I checked that with the debugger — scoob27, Aug 26 '15 at 12:08
Just to be clear: You have a parameter query saved in Access and when you run that query in Access it prompts you for a parameter value and displays the results. Now you want to call that query from your Java application, pass the parameter value from the Java app to the saved query in Access, and then retrieve the results into a `ResultSet` in your Java application. Is that correct? — Gord Thompson, Aug 26 '15 at 19:35

score 0 · Answer 1 · answered Aug 26 '15 at 12:32

Connection connection = null;
CallableStatement callStmt = null;
String myParam = "test";
String statement = "SELECT * FROM table1 WHERE table1.id = ?";
try {
connection = DatabasePoolUtil.getDefaultConnection(); //Connects
callStmt = connection.prepareCall(statement);
callStmt.setString(1,myParam);
callStmt.execute();
}
catch (SQLException ex) {
// Do something
}
finally { // connection has to be closed
if (callStmt != null) {
 callStmt.close();
  }
 if (connection != null) {
 connection.close();
}
}

Gord Thompson · Answer 2 · 2015-08-27T20:45:43.300

The ODBC (and OLEDB) interfaces to an Access database expose different types of saved Access queries as either "Views" or "Stored Procedures":

Access query                     appears under ODBC/OLEDB as
-------------------------------  ---------------------------
Select query without parameters  View
Select query with parameters     Stored Procedure
Append query (INSERT)            Stored Procedure
Update query                     Stored Procedure
Delete query                     Stored Procedure

Since your Access saved query has parameters it will look like a Stored Procedure under ODBC and therefore you need to use a CallableStatement to work with it.

For example, given the following saved parameter query named [myParameterQuery] in Access

PARAMETERS specificID Long;
SELECT Table1.*
FROM Table1
WHERE (((Table1.ID)=[specificID]));

we need to use the following Java code to retrieve the row for ID=3:

String connectionString = "jdbc:odbc:"
        + "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
        + "DBQ=C:/Users/Public/32224442.accdb;";
try (Connection conn = DriverManager.getConnection(connectionString)) {
    try (CallableStatement cs = conn.prepareCall("{call myParameterQuery(?)}")) {
        cs.setInt(1, 3);  // set "specificID" parameter to 3
        try (ResultSet rs = cs.executeQuery()) {
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
} catch (Exception e) {
    System.err.println(e.getMessage());
    System.exit(0);
}

The corresponding C# code would be:

string myConnectionString =
        @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
        @"Dbq=C:\Users\Public\32224442.accdb;";
using (var con = new OdbcConnection(myConnectionString))
{
    con.Open();
    using (var cmd = new OdbcCommand("{CALL myParameterQuery (?)}", con))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add("?", OdbcType.Int).Value = 3;  // set "specificID" parameter to 3
        using (OdbcDataReader rdr = cmd.ExecuteReader())
        {
            rdr.Read();
            Console.WriteLine(rdr[0]);
        }
    }
}

Working with a saved Access parameter query under (JDBC-)ODBC

2 Answers2