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.

Apple OS X Lion Review

Posted on by
12

Shortly after downloading and installing Apple’s new Mac OS X Lion this morning, I started compiling a list of what I liked and what I did not like. My excitement began to fade as I realized there was a lot I was not going to enjoy about Apple’s new OS. After spending the morning with OS X Lion, I present a review that highlights both the good and bad of the new OS, along with tips to revert some new, possibly unwelcome features.

The Good

1) Launchpad: Launchpad is a new user interface component, which allows you to view the Applications on your computer in an iOS-like fashion. While I am not sure I will use this new interface, I like that Apple is trying to bridge the Desktop OS X and Mobile iOS worlds with a more consistent interface.
2) Mission Control: Although this feature is a bit recycled from Exposé, I do enjoy the new look and organization of the application overview.

3) Easy Access to DVD Media: When OS X Lion was first announced, there was a lot of uproar about the digital-only release. There were many who worried about how to do a fresh install of Lion on a new hard drive and the instruction, purportedly from Steve Jobs, that they install an old version of Mac OS then upgrade to Lion was met with a lot of online criticism. Most of those issues have since disappeared as Apple has provided a method, possibly unsupported, for creating a DVD image of the downloaded Lion installation. The steps are extremely easy and I had the DVD created within minutes of downloading. I tested the DVD on another Mac in my household, as the OS X license permits up to 5 installations, and the DVD worked without issues.

4) Full-screen Applications: The ability to load applications in full-screen was first introduced in iLife about a year ago and has now been extended throughout the OS. As someone who values screen real estate, the new feature is a welcome addition, especially if you are using an 11″ MacBook Air.

5) New Coat of Paint: The overall graphical interface for Lion is a lot more crisp and appealing, succeeding with that ‘lickable’ aspect Apple is known for.

6) Java Support: Like the DVD media issue, there were a lot of rumors predicting the end of Java on OS X Lion that have turned out to be false. Although there is some evidence to suggest Apple will be taking a backseat to integrating Java with the OS in the future, they have already posted a Java 1.6 release available as a separate download. They have also added an automatic download feature that detects when Java is required and automatically installs it. For example, the first time I started Eclipse, Java 1.6.0_26 (64-bit) was retrieved and installed within seconds. In short, Java is still very much alive and well in OS X Lion despite rumors to the contrary.

The Bad

1) Microsoft Office 2004 Dead: OS X Lion removes Rosetta compatibility, which means older applications such as Word, Excel, or PowerPoint 2004 will no longer load. In fact, Lion highlights such applications as shown in this screenshot. I am actually a big fan of Office 2004, as I have used more recent versions and been turned off by the annoying ribbon interface. Office 2004 was one of the best versions of Office and is now completely dead in Lion.

2) Slow: Ever since I started is up, OS X Lion has had an unreasonably slow user interface, especially in terms of responsiveness. As early as the login screen, I had to wait 10+ seconds after selecting my username to enter my password. My computer may not be fresh off the assembly line but it is still a somewhat new high-end MacBook Pro, so I’m shocked at the user interface delay in loading and using programs. Even loading System Preferences and the OS X Updater took a lot of time.

The reason for these delays may be that Spotlight runs a re-index of the hard-drive immediately following installation, but I’ll have to wait until it is finished to know for sure. Initially, Spotlight reported “4 weeks remaining” but that has since changed to 7 hours, which is better, but still a lot of time to wait. The extra process is also causing the temperature to spike and it practically burns my hand to type right now. It’s like taking a new sports car out on the road but not being able to go above 5 miles per hour, while the seat slowly burns your legs. It certainly diminishes the initial user experience after installation and should have been scheduled to occur at a later time.

Update [7/22/2011]: After Spotlight finished re-indexing the hard-drive and Time Machine ran its multi-gigabyte backup, the computer is running much better again. I still notice some impact lag when typing passwords and launching certain applications, but I can’t say for sure if this was much different than before I upgraded. In retrospect, I still contend Spotlight should have postponed its re-index of the hard-drive as this created a poor user experience immediately following installation.

3) Unnatural scrolling: For some reason, Apple decided to reverse the scroll direction. Up is down, down is up, left is right, etc. According to the documentation is this a more ‘natural’ approach, but from a user interface perspective reversing such a now-key part of the user interface is ridiculous. Luckily there is a way to disable this ‘feature’. Open System Preferences, select Trackpad, and uncheck the option for Scroll direction: natural. On older Macs, Apple presents this in a different manner, and to disable it, uncheck When using gestures to scroll or navigate, move content in the direction of finger movement

4) Launchpad + Parallels: One area where I found this particularly troublesome is if you have Parallels installed, it lists dozens, or even hundreds, of Parallels applications in Launchpad, often outweighing the number of OS X applications. This may not bother a lot of people, especially those that keep Parallels open at all times, but was extremely frustrating for myself. These items can be removed from Launchpad by opening Parallels and selecting the virtual machine. Then from the menu bar then select Virtual Machine, click Configure, select Applications, then uncheck Share Windows applications with Mac.

5) iChat Single View: iChat has been updated to automatically merge multiple buddy lists into one. Some people may like this feature, but I found annoying that it was enabled by default. Lion also overwrote many of my default preferences, such as hiding the hundreds of Offline Buddies on my list. None of these are killer issues, but did annoy me the first time I loaded iChat. To disable it, select Preferences from the menu bar, click General, and uncheck Show all my accounts in one list.

6) iChat won’t save account passwords: I’m having an issue that every time I start iChat I need to enter passwords for each of my accounts, despite having previously saved them in my keychain before the upgrade as well as checking the box to save them in my keychain now. I haven’t heard anyone else report this issue yet, but something seems buggy here.

Update [7/22/2011]: One website suggested my iChat preferences had been corrupted. The fix is to navigate to ‘/Users//Library/Preferences’ and delete all com.apple.iChat* files. You must then log out for the fix to work and re-setup iChat the next time you log in. I’m pleased to report this solution does work and I can now open and close iChat without having to reenter my passwords each time it starts up. Has anyone else installed OS X Lion, only to have their iChat preferences corrupted?

Some users have reported similar issues with Calendar passwords after upgrading to OS X Lion in which the solution was also to manually delete user Library files.

7) Adobe CS2 Dead: As Adobe has pointed out CS2 and earlier versions of its products do not function in OS X Lion due to the same Rosetta issue that disabled Microsoft Office 2004. More recent versions, such as CS3/CS4/CS5 still have a number of issues as Adobe spells out in its release notes.

Some components of Photoshop, such as Droplets, the automation creation tool, were sloppily implemented in PowerPC even in recent Intel-based versions of Photoshop. Adobe has released an update for CS5, although it is not clear if they will be updating CS3 or CS4, but given Adobe’s update history for older versions of it’s product, it seems unlikely.

Conclusion

Overall, I’m about 35% happy with OS X Lion. While I like the streamlined interface, new Mail client, and increased feature set, I can’t help but think they needed to spend more time on this. The erasing of many of my existing settings throughout the OS and applications, as well as needing to wait hours for the computer to finish running Spotlight before I can use it without burning my hands, leaves a bitter taste in my mouth. The biggest showstopping feature is the lack of support for Microsoft Office 2004, as I consider this version to be superior to many of the newer versions. More of my impressions will follow as I get more familiar with the many new features of this OS.

Improving the custom Android Gallery

Posted on by
4

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;
        }
    }
}