This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes Microsoft Access Craziness Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Microsoft Access Craziness" Watch "Microsoft Access Craziness" New topic
Author

Microsoft Access Craziness

Germano Bax
Greenhorn

Joined: Sep 04, 2010
Posts: 3
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:



And reading with:


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:



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



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
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

What JDBC driver are you using? It'd also be better if you posted the actual code being used, just in case.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

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

Joined: Apr 14, 2004
Posts: 10336

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10145
    
165

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



and then:


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


[My Blog] [JavaRanch Journal]
Germano Bax
Greenhorn

Joined: Sep 04, 2010
Posts: 3
Hi, this is getting pretty funny...
I did what Jaikiran Pai suggested:



and then:


My "fix" is this one:



and then:


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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Microsoft Access Craziness