Microsoft Access Craziness

[Germano Bax]

I'm facing a pretty weird issue with Microsoft Access. I got the task of migrating data from MS Access to another database, but some data transformation is required, and the following issue is driving me crazy:

I'm running the following SQL statement on my JDBC code:

SELECT 
    fdgrp_cd, 
    fdgrp_desc  
FROM 
    fd_group

And reading with:

String code = rs.getString(1);
String description = rs.getString(2);

It should be simple and straightforward, but rs.getString(1) throws the exception java.sql.SQLException: No data found. rs.getString(2) reads the data without issues. The same SQL statement when run inside MS Access produces lots of results on both the first and second columns.

Back to JDBC, I changed the SQL statement to:

SELECT 
    fdgrp_cd,
    fdgrp_cd, -- YES, THE SAME COLUMN AGAIN!! 
    fdgrp_desc  
FROM 
    fd_group

Now I can read the values correctly, by skipping the first column (rs.getString(1) would throw me that No data found exception):

String code = rs.getString(2);
String description = rs.getString(3);

Is this a jdbc driver issue or a Ms Access issue? Please advise on what can be wrong. I wonder what else I might expect with this crappy database.

[David Newton]

What JDBC driver are you using? It'd also be better if you posted the actual code being used, just in case.

[Jeanne Boyarsky]

This is looking like a bug in the JDBC driver though.

[David Newton]

The JDBC-ODBC bridge has issues for sure, or at least did.

[Paul Sturrock]

Still does. Unfortunately it is (pretty much) the only option if you use Access.

[Jaikiran Pai]

Germano, have you tried switching the column order and see if that works:

SELECT 
    fdgrp_desc,
    fdgrp_cd
FROM 
    fd_group

and then:

String code = rs.getString(2);
String description = rs.getString(1);

I know, the column ordering shouldn't matter, but I'm just curious. And as Jeanne said, it does look like a driver issue.

[Germano Bax]

Hi, this is getting pretty funny...
I did what Jaikiran Pai suggested:

SELECT 
    fdgrp_desc,
    fdgrp_cd
FROM 
    fd_group

and then:

String code = rs.getString(2); // NO DATA AVAILABLE EXCEPTION
String description = rs.getString(1); // This works right

My "fix" is this one:

SELECT 
    fdgrp_desc,
    fdgrp_cd,
    fdgrp_cd
FROM 
    fd_group

and then:

String code = rs.getString(3); // This works right
String code = rs.getString(2); // NO DATA AVAILABLE EXCEPTION
String description = rs.getString(1); // This works right

Any query on any table of this database have the issue. Looks like it is related with the first column of each table. Luckily I'm able to perform my task by fetching the column twice, ignoring the first column and fetching from the second. I didn't face any other issues so far. Once I'm finished with this migration, I'll never look back... unless I decide to post this case on thedailywtf.com

Thanks guys.