Category Archives: JDBC

Connecting to MS Access Files via JDBC in 64-bit Java

Posted on by
28
In 32-bit Java, the technique for connecting to a Microsoft Access file via JDBC connection is well-known and available as part of the Sun JDK. There are a number of errors, though, if you attempt to use a 64-bit version of Java that are not as well documented. This article points out some of those issues and a working strategy for how to successfully connect to a Microsoft Access file via JDBC in 64-bit Java.

1. Review of the 32-bit Connection Strategy

The well-known syntax for connecting to an Microsoft Access file via JDBC is as follows: 

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

If you are using the Sun JDK, then the driver will be available in the classpath automatically. Notice, I make sure to close my connection object in a finally block as all good JDBC developers know to do.

2. Errors in the 64-bit World

Attempting to run the proceeding code returns the following error when using a 64-bit JDK:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

If you would prefer to stick with the 32-bit version of the driver, there are two options available:

  • Use the 64-bit Java installation but run java.exe with the “-D32” flag.
  • Use a 32-bit Java installation

Both of these solutions limit the amount of memory a Java application can use.

3. Adapting JDBC to 64-bit Java

If you would prefer to use the 64-bit Java, there is a solution, although for some users this may require removing Microsoft Office.

Step #1: Download the Microsoft Access Database Engine 2010 Redistributable, specifically the AccessDatabaseEngine_x64.exe file.

Step #2: Install the Microsoft Access Database Engine. If you are running a 32-bit version of Microsoft Office, you will likely get the following error when you try to install it:

You cannot install the 64-bit version of Office 2010 because you have 32-bit Office products installed.

At this point, you must decide whether or not to uninstall the 32-bit version of Microsoft Office. Newer versions of Office, such as 2010, often contain both 32-bit and 64-bit versions on the installation DVD, although the 32-bit version is used by default when installed via the AutoRun process. In this case, you would first uninstall the 32-bit version of Office. Restart the computer. Then, ignore the AutoRun on the DVD and manually open the setup executable in the x64 directory. After a 64-bit Office version is installed, continue with the Microsoft Access Database Engine installation.

Note: If you are installing a recent 64-bit version of Microsoft Office, you may be able to skip installing the Microsoft Access Database Engine, as it is often included in the Office installation.

If a 64-bit version of Office is not available, then you will unable to run the 32-bit version of Microsoft Office alongside the 64-bit Microsoft Access Database Engine, and must decide which is more important.

Step #3 Update the JDBC code to use the following revised connection string as follows:

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

After making these changes, you should be able to connect to your Access database in 64-bit Java.

Live from TSSJS – Tips for Hibernate with Patrycja

Posted on by
1

This morning I’m live blogging from TheServerSide Java Symposium 2011 at the breakout “Anti-Patterns and Best Practices for Hibernate” presented by Patrycja Wegrzynowicz.

Making Good Software
Patrycja’s primary goal is to “understand what makes good software good and bad software bad” and she is working to formalize these concepts.  She would like to develop automated code tools that can detect ahead of time whether or not code contains anti-patterns and potential performance issues.

For those not familiar with the concept, an anti-pattern is an obvious or common strategy which leads to degraded or counter-intuitive performance.

Is Java Fat
Patrycja asks the question “Is Java fat/slow?”.  She claims the language is not, in fact, slow, but that some of the ways in which we program objects is slow, which I agree with strongly.  For example, I have seen computational entensive code that improved in memory/speed by large degrees by relying on primitive variables, rather than more complex objects, in part because “new” is considered expensive in Java.

Automated Code Analysis
Requires large knowledge base.  Patrycja has been researching semantic code query system which focusses on behavior, not just structure.

Conclusion
Patrycja says that proper usage of hibernate is tricky.  Transactional issues and correctness of code under various scenarios.  Best solution may be combination of Hibernate and JDBC.

I liked that fact that she stepped back and indicate Hibernate, by itself, isn’t perfect.  I agree with her take that the best systems are often hybrid systems that take advantage of the ‘low hanging fruit’ of both worlds, and avoid the pitfalls of each.

Live from TSSJS – PostgreSQL with Tom

Posted on by
1

Live blogging from TheServerSide Java Symposium with Tom Kincaid at his vendor presentation “Introduction to PostgreSQL for Development and Deployment”.  Tom spends a lot of time contrasing Postgres with MySQL, and commenting how since Oracle’s aquisition of MySQL, the licensing of MySQL is now much more restrictive. Tom says the licensing of Postgres is basically “Do what you want with the code but don’t bother us”.

1. Why has it not the most adopted open source DB?
Tom talks about some of the limitation of Postgres that prevented it from becoming one of the most adopted DBMS software including:

  • Early versions were Linux only with Windows support coming later
  • Installation was difficult
  • Default configuration was not neccessarily the best, and was designed for widest platform adoption
  • Lacked bundled distribution tools

Tom points out that originally, developers “first 20 mins” of using the product was often frustrating and would turn people off to the software.

2. Today’s Postgres
Postgres has made a number of changes in the last few years to grow Postgres has a more developer-friendly and easier-to-use product.  It also has been extended to work with all major IDEs, object-relational mapping tools such as JDBC, ODBC drivers, and stored procedures based in Java.  They have also spent time improving the GUI tools to compete with other major DBMS providers.  The GUI also shows performance tools and query plans.

Conclusion
The vendor sessions were abridged compared to the normal sessions so there was a lot Tom did not get to.  He did peak my interest in learning more about Postgres, now that it has grown to a more substational platform.  Tom’s belief is that everyone should use Postgres given its power and hands-off licensing, especially compared to the direction MySQL may be going.