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

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.

Improving the custom Android Gallery

I was looking for a custom gallery for displaying and selecting images on an Android phone, and while I found the sample code here to be a good start it needed some work before it was useful.

Before starting, it is worth pointing out that the Android API supplies a perfectly useful image gallery, and the StackOverflow thread how to pick a image from gallery (SD Card) for my app in android? has everything you need to get plug it into your own application.

If you require customised behaviour it isn’t too hard to get something rough working, so go to the original thread and create the ImageThumbnailsActivity and ViewImage classes, then configure the AndroidManifest.xml, res/layout/main.xml and missing res/values/strings.xml files.

The layout specifies a single GridView to hold the images, and the main ImageThumbnailsActivity activity performs two main jobs. First it loads all existing thumbnails and provides the code necessary to interact with the thumbnail if it is selected, and it has a nested adapter to manage the display of the thumbnails using a Cursor position into the GridView.

There are quite a few comments in the original posting indicating that the code is not quite right, and if you run it on a phone with a decent history of photos you’ll get some unusual behaviour. The two main issues are that firstly there are far more thumbnails displayed than there are images stored, and secondly that the images displayed are repeated as you scroll through the gallery.

The first issue can be resolved using the StackOverflow thread here and involves initially loading the full images rather than the thumbnail, but associating with the thumbnail when displaying on the grid. This means that we don’t try to show a thumbnail for an image that no longer exists or is incorrect for some other reason.

The second issue is a little more involved, but related to the fact that the nested ImageAdapter class will attempt to reuse View instances if possible (in the getView method), and the code provided assumes that if a View is returned to be reused that it is correct, which it is not. The GridView only creates as many View instances as required to fill the screen. Once you scroll to a new section it asks if you want to reuse the instances that have scrolled off the screen. Obviously this is an acceptable reuse of resources, but the reused instances will be showing the thumbnail for a different picture and will need to be updated.

As a matter of style, I also made the following changes:

  • Remove all of the System.gc() calls, they aren’t required.
  • close cursors
  • make variables local where applicable
  • as stated we load images from the MediaStore.Images.Media rather than the MediaStore.Images.Thumbnails but return the thumbnail for display.
  • specify the MediaStore.Images.Thumbnails.MICRO_KIND for display. The benefit here is that the images are the size we intend to show and eliminited scaling. Feel free to play with settings but I found this the most pleasing layout and matches the existing Android gallery

Finally, if you look up the thumbnail when the View is visible on the screen, the scrolling is slow and limited to a row at a time rather than being able to race past images they do not want. Since we specify the thumbnails as the smallest size, I chose to load and cache the android.graphics.Bitmap for the thumbnails so they are ready to display when required.

Just to Review

We’re doing this to provide a customised image gallery. The Android gallery is fully functional and should suit most needs, but if for example you also needed to display the file name or size, date or other information, this solution allows a custom View on the GridView.

Everything else remains the same, and the updated ImageThumbnailsActivity looks like this:

public class ImageThumbnailsActivity extends Activity
{
    private int count;
    /**
     * Caching this is a trade off in memory versus performance.
     * We deliberated use the smallest thumbnails so the size of each should be small (92x92x1 byte = about 10kb each)
     * but can be significant if there are thousands of images on the device.
     */
    private Bitmap[] windows;

    @Override
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.image_thumbnail);
        init_phone_image_grid();
    }

    private void init_phone_image_grid()
    {
        final String[] columns = { MediaStore.Images.Media._ID };
        final String orderBy = MediaStore.Images.Media._ID;
        Cursor imagecursor = managedQuery(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, columns, null, null, orderBy);
        int image_column_index = imagecursor.getColumnIndex(MediaStore.Images.Media._ID);
        this.count = imagecursor.getCount();
        this.windows = new Bitmap[this.count];
        for(int i=0;i<this.count;i++)
        {
            imagecursor.moveToPosition(i);
            int id = imagecursor.getInt(image_column_index);
            windows[i] = MediaStore.Images.Thumbnails.getThumbnail(getApplicationContext().getContentResolver(),
                    id, MediaStore.Images.Thumbnails.MICRO_KIND, null);
        }
        GridView imagegrid = (GridView) findViewById(R.id.PhoneImageGrid);
        imagegrid.setAdapter(new ImageAdapter(getApplicationContext()));
        imagegrid.setOnItemClickListener(new OnItemClickListener() {
            public void onItemClick(@SuppressWarnings("rawtypes") AdapterView parent, View v, int position, long id)
            {
                String[] columns = { MediaStore.Images.Media.DATA, MediaStore.Images.Media._ID };
                Cursor actualimagecursor = managedQuery(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, columns, null, null, null);
                final int dataColumnIndex = actualimagecursor.getColumnIndex(MediaStore.Images.Media.DATA);
                final int idColumnIndex = actualimagecursor.getColumnIndex(MediaStore.Images.Media._ID);
                actualimagecursor.moveToPosition(position);
                final String filename = actualimagecursor.getString(dataColumnIndex);
                final long imageId = actualimagecursor.getLong(idColumnIndex);
                final Intent intent = new Intent(ImageThumbnailsActivity.this, ViewImage.class);
                intent.putExtra("filename", filename);
                intent.putExtra("dataUid", imageId);
                actualimagecursor.close();
                startActivity(intent);
            }
        });
        imagecursor.close();
    }

    public class ImageAdapter extends BaseAdapter
    {
        private Context mContext;

        public ImageAdapter(Context c)
        {
            mContext = c;
        }

        @Override
        public int getCount()
        {
            return count;
        }

        @Override
        public Object getItem(int position)
        {
            return position;
        }

        @Override
        public long getItemId(int position)
        {
            return position;
        }

        @Override
        public View getView(int position, View convertView, ViewGroup parent)
        {
            ImageView i = (ImageView)convertView;
            if(i!=null)
            {
               i.setImageBitmap(windows[position]);
            }
            else
            {
                i = new ImageView(mContext.getApplicationContext());
                i.setImageBitmap(windows[position]);
                i.setLayoutParams(new GridView.LayoutParams(92, 92));
            }
            return i;
        }
    }
}

eclipse 3.7 on the new mac – 8 good features & 1 bad + my plugin list

I downloaded Eclipse 3.7/Indigo release.  Since I got my Mac so recently, I waited to install development tools and create a new Mac workspace until Indigo came out.  My first set of installs on a Mac had some extra unexpected surprises of course.  Installing Postgres didn’t go nearly as smoothly as Eclipse.  Check back for the blog post on that!

Downloading

I went with the JavaEE developer release since it has web functionality built in.  I confirmed I had a 64 bit machine by running uname -a.  I also finally realized the clue that I’ve clicked download.  It’s that the download icon turns into a progress icon or changes the icon to my most recent download.

Installing

When I selected the download, it automatically unzipped it to downloads.  I then dragged it to the applications folder and added Eclipse to the dock as described.  Somehow I downloaded Helios instead of Indigo and had to do it again.  Not sure how that happened since I downloaded on Indigo release day.  I’ll assume I was tired.

Eclipse itself

My plugin install list contains what I used in Eclipse 3.6 plus a number of others.  This time I used Marketplace Client to try to get the plugins.  It’s nice that you can browse plugins and get ideas for things to install that you didn’t know about.  It’s not much easier to install, but it wasn’t exactly hard before.

Plugin Purpose Marketplace Experience
Sysdeo Tomcat integration Listed, but no install link. Still must install by unzipping into plugins directory.
EclEmma Code Coverage Smooth – click and install
PMD Static analysis Listed, but no install link. Had to use install site link directly.
Subversive To access Subversion repositories Smooth – click and install
eGit To access Git repositories (or run your own locally) Smooth – click and install
Hibernate Tools JPA assistance Did not install. There was a conflict with the built into JPA perspective in the JEE version of Eclipse. While I usually hand create my entities, I like having the plugin available but this isn’t a big deal.  The built into one appears to do the same thing.  And more likely, I wouldn’t use them either.
Groovy Groovy project/editor and console Couldn’t find in Marketplace, but think it is there.  Installed from install site link.
Freemarker IDE Freemarker syntax highlighting and macro assistance. Didn’t look in Marketplace.  I didn’t know this existed, but JBoss supplies it at the same URL as Hibernate Tools and I found it by accident.  Since JForum (CodeRanch) uses Freemarker, this could be helpful.  Trying it for five minutes, the syntax highlighting made the install worth it.
m2Eclipse Maven Smooth – click and install

What I like

In Eclipse 3.6, there were only 4 features I liked enough to remark upon.  This time, there are twice as many!

  1. A few versions of Eclipse ago if you had m.method(one, two) and tried to delete the “method” followed by autocomplete, it left the parenthesis and parameters alone.  Since at least Eclipse 3.5, it would add an extra set of parens and parameter templates.  Horribly annoying.  In Eclipse 3.7, it goes back to the original behavior.  Very excited about this fix!  This wasn’t reproducable, I think it was luck.
  2. JPA annotation autocomplete got better.  This is a minor convenience.
  3. Secure storage – passwords are now stored encrypted on disk.   This does mean anyone who uses your computer/account can commit on your behalf.  Not a problem on my home computer.
  4. “Document proxy icons in Cocoa” – you can drag an icon in the title bar to another application.  Sounds like it has potential.
  5. You can open the same file in different editors at the same time.  This is nice because I sometimes like to be in the visual and XML views.
  6. Being able to filter the compiler settings preferences.  (That list has grown so long it is hard to find specific options.)
  7. Compiler setting to ignore unavoidable generics problems (when calling legacy code.)  This is nice because it avoids false positives.  There is a risk because you have to be extra careful in that space, but I’ll find that in unit test.
  8. Paste URL into JUnit view – useful for loading an Ant or Maven junit report XML file from a nightly build.

And my worst feature

  1. The extract method keyboard shortcut is gone!  I use this one a lot.  It was there on Mac Eclipse 3.6.   You can still use the menus, but that is less efficient.

Mac Stuff

Since I had downloaded Eclipse 3.6 for the Mac, I installed it as well to see what was a Mac issue and what was an Eclipse 3.7 issue.  Here’s what is not new in Eclipse 3.7, but was new to me.

  1. In Safari, control left/right arrow take you the beginning/end of the line.  Which is convenient because shift + control + arrow highlights the line.   (I learned today that the command/apple key does the same as control although it is more awkward to type.)  In Eclipse, command left/right arrows takes you to the beginning/end of the line but control does not. Luckily, command + arrow does work in Safari so I’ll be using that shortcut now.  (It would be nice if there was a standard across applications for this – pgadmin doesn’t work the same way as either of these and I haven’t found the keyboard shortcut there yet.)
  2. My integration tests went down from three minutes to seven seconds!  This isn’t a Mac thing – it’s a six year old machine vs brand new machine.  But still cool.  I didn’t know they could run so fast.
  3. I changed the Mac system preferences to turn on “Use all F1, F2, etc keys as standard function keys”.  Having to press “fn” to use the debugger was quite annoying. I use the special keys a lot less than the function keys overall.  And when I am changing the sound volume, I’m not in the middle of typing/debugging.  I wish I could change it on only one of my two keyboards.  (I’m using a standalone keyboard where I care about the function keys being reversed.)  Not important, but it would be cool if I could switch to use the built in keyboard without the function key.