Well first off, JDBC and ODBC are not compatible. JDBC was Java's answer to ODBC, and they fill the same niche, but you can't connect to an ODBC data source using JDBC and vice versa. Now there IS a jdbc-odbc bridge in Java, but that is a compatibility hack, not a native connector (The translation will go JDBC->ODBC->(Target Database) rather than JDBC->(Target Database))
ODBC is simply a standardized interface to access data from different databases. You install the ODBC to (whatever database) driver on your machine, then create an ODBC datasource, and then you can connect to that datasource via odbc rather than having to configure a native driver for the original database. Or if the database is ODBC capable, you can connect to it directly by importing the driver.
The benefit is that you can write all your code to be odbc compatible and it'll work with only small modifications regardless of what the real database type is.
The negative is a performance hit, and occasional weird behaviour as odbc fails to translate some database specific action in exactly the right way. Support varies. If you're using ODBC to connect to some legacy database, you may have a lot of work to do, but if you're using it to connect to an access database, then it'll work flawlessly.
The process that happens on connection is relatively simple. You send your "standardized" query, the JDBC or ODBC driver translates it into the native framework you're trying to access, and then submits the query. If the database returns anything, the process is reversed.
If you're having problems, I'd check the driver you're using. If you import a bad driver with java, nothing is going to work right. The first thing you need to do is log the error. If you're using an ODBC datasource, you can just turn logging on in the properties (look to the driver documentation to find out the correct settings; they vary).
If you're using JDBC, you're probably going to have to catch and log the SQLException's yourself.
Solved the problem now. Recording here in case this of use to others.
The issue was much simpler than it first appeared. The problem was that the application used a mixture of named and unnamed datasources (DSN / DSN-Less).
It was not apparent to me that any named connections were used until I set up the application for debugging in Visual studio. Here is a rough guide to debugging the application in visual studio (Except I used HTTP based website, rather than file system):
http://www.codeproject.com/Articles/28792/Debugging-Classic-ASP-VBScript-in-Visual-Studio-20
Following creating the required DSN, there were some further exceptions being thrown regarding default values in database columns. This was due to a MySQL setting that can be changed in the my.ini file.
http://bugs.mysql.com/bug.php?id=14306
C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini
# Set the SQL mode to strict
# sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode=""
Best Answer
Well, I figured it out. Leaving the answer here and a couple other places for future generations:
The system was missing the "secret" prerequisite of having Visual C++ 2010 Redistributable x86 installed (not the x64, that one it had). Fixed the problem instantly.