ResultSet row count via .getRow() giving wrong answer with JDBC-ODBC and MS Access

Question

I have a HAM radio database and I'm trying to count the number of countries contacted per band for a given month (March 2014 in this example). My SQL statement works perfectly well in the MS Access Database tool, but from my Java program I get only a partial result. (I see an incorrect rowcount.)

I've been struggling with this for a few days now and can't get it to work.

Here is my SQL:

SELECT count(*) AS cdxcc, dxcc, rfband 
FROM (SELECT  dxcc,ldate,rfband
FROM logbook) AS tmp 
where mid(logbook.ldate,4)='03.2014'
GROUP BY dxcc,rfband ORDER BY dxcc;

In MsAccess (the windows program), I get these correct results:

cdxcc   dxcc        rfband
5       Canada      15m
3       Canada      20m
2       England     40m
18      England     80m
1       Germany     80m
4       Poland      20m
1       Scotland    80m
1       Sweden      20m
1       USA         12m
12      USA         15m
1       Wales       60m

However, in my Java program, I only get back as many rows as per the 'cdxcc' count in the first record, which in this case is 5. So I'm getting just the first 5 rows, and after that a SQL/java error ('ResultSet is closed').

For a different month, the first row might have a count of 1, and I get just 1 record back. As a debug, I found this handy code here on stackoverflow, and it confirms 5, which is wrong. The correct answer should be 11 for this example:

if (rs.last()) {
  rowcount = rs.getRow();
  rs.beforeFirst(); 
}

Does anyone recognize this problem and know a solution?

indeed the query was too complicated, this works as well, but unfortuately with the same results:

SELECT count(*) AS cdxcc,dxcc,rfband,ldate FROM logbook AS tmp where mid(ldate,4)='03.2014' GROUP BY dxcc,rfband,ldate ORDER BY dxcc;

Unfortunately with the same wrong result.

I have a lot of other SQL statements in my code and they all work fine, it's just this one. The java code is here (try-catch and other non-essential code omitted):

private static final String accessDBURLPrefix = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=";
private static final String accessDBURLSuffix = ";DriverID=22;READONLY=false}";
String currdb=accessDBpath + logbookdb; // path and filename of .mdb file
private Statement stm = null;
private ResultSet rs=null;
private Connection dbconn = null;
String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
Class.forName(dbDriver);
String dbURL = accessDBURLPrefix + currdb + accessDBURLSuffix;
dbconn = DriverManager.getConnection(dbURL,"","");
stm = dbconn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,     ResultSet.CONCUR_READ_ONLY);

// This function is called by a JSP page that contains the aforementioned query
public boolean dbQuery(String q) {
    rs = stm.executeQuery(q);

    int rowcount = 0;

    if (rs.last()) {
          rowcount = rs.getRow();
          rs.beforeFirst(); 
    }
    System.out.println("Rowcount="+ rowcount);
    return true;
}

I'm not an SQL expert, but plenty of insert,update and read queries in my code, accessing the same database, all seem to work just fine. No concurrency required, single user only.

That query seems to be overly complicated. Why do you have the internal SELECT? It doesn't create a set of rows that is different in any way from `logbook` itself. Just replace the whole part from the internal select through `AS tmp` with `logbook`. If that doesn't work, please show your java code. — RealSkeptic, Jul 30 '15 at 15:31
Please [edit] your question to show us some Java code for an [MCVE](http://stackoverflow.com/help/mcve) (Minimum, Complete, and Verifiable Example) that recreates your issue. Without more information we can only guess as to what the problem might be. — Gord Thompson, Jul 30 '15 at 17:51
@jpw - `if (rs.last()) {` will move to the last row if possible, and then enter the loop if that method returns `true` (which indicates that the ResultSet is not empty). — Gord Thompson, Jul 31 '15 at 03:34
@GordThompson You're absolutely correct of course. I misread the code and was thinking is `isLast()`. — jpw, Jul 31 '15 at 11:49
Thanks for the clarification. I've downloaded the UCanAccess files, but not sure how to incorporate it in my project. Problem is that I'm NOT using Eclipse or Netbeans, it's just a text editor (notepad++). I've copied the new jar files into WEB-INF/classes, but getting the following runtime error: *** Could not connect to database: No suitable driver found for jdbc:ucanaccess:myDatabase.mdb. Do I need to add an 'import' statement in my java class? My compile.bat file already contains '-d "WEB-INF\classes" ' — mljm, Jul 31 '15 at 16:04

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

I was able to recreate your issue using the JDBC-ODBC Bridge and the Access ODBC driver. It appears to be an incompatibility between the two, I tried the exact same code using the UCanAccess JDBC driver and I got the correct result ("Rowcount=11").

The JDBC-ODBC Bridge is obsolete and has been removed from Java 8 so you should consider switching to UCanAccess anyway. For more information see

Manipulating an Access database from Java without ODBC

edited May 23 '17 at 11:43

Community

1
1

answered Jul 31 '15 at 03:32

Gord Thompson

98,607
26
164
342

Thanks very much for taking the time to check this @Gord Thompson! All I needed to do (after the code changes) was copy the UcanAccess jar files into the tomcat LIB folder, and it works! – mljm Aug 01 '15 at 10:08

ResultSet row count via .getRow() giving wrong answer with JDBC-ODBC and MS Access

1 Answers1