64-bit Solution for "2-bit" Perl Problem

I'm not a Perl programming by trade, I just know enough to help me in my day to day activities. That said, database development / interaction with Perl is not a day to day activity, but it's something I feel I should know at a base level for the few times I've needed it in the past. To that end, I have a simple script that illustrates database instantiation, creation, population and some data manipulations with Perl.

I mainly use Windows, so the database for my little experiment was Microsoft Access as that's the most common database found on Windows; usually part of an enterprise Microsoft Office installation. I also have a view to cross platform capabilities, so I made my script check the operating system ($^O eq "MSWin32") and if not Windows, use MySQL as the database.

<digression>This presents some issues with database creation - creating tables has a different SQL syntax between Microsoft Access and MySQL so again an operating system check is required for some activites. For example, both have the concept of auto-increment, but MySQL is AUTO_INCREMENT, Access is AUTOINCREMENT. And while MySQL needs a type definition (INT AUTO_INCREMENT), Access believes AUTOINCREMENT is explicitly an integer type of its own, so will fail with INT AUTOINCREMENT.</digression>

My script:

1. Instantiates - make a database (if it doesn't already exist)
2. Creates - create the tables / schema in the database
3. Populates - adds some data to the database
4. Manipulates - does some SQL INSERT, UPDATE, DELETE, SELECT queries

I couldn't find a way to programmatically instantiate a database in MySQL - the database needs to exist for my script to use it. It's easy enough to do with some simple MySQL commands (with operations for a test example emphasized over secure best practices):

$ mysql -u root -p

mysql> create database PerlTest;
mysql> grant usage on PerlTest.* to perluser@localhost identified by 'password';
mysql> grant all privileges on PerlTest.* to perluser@localhost;

I did find a way to programmatically instantiate a Microsoft Access database in Perl. It relies on Win32::OLE, standard with Strawberry Perl - my Perl of choice for Windows.

use Cwd;
use Win32::OLE;
use Win32::OLE::Const;

my $dbname = getcwd . '/' . 'PerlTest.accdb';
my $props = Win32::OLE::Const->Load('Microsoft DAO')
my $dbLangGeneral = $props->{dbLangGeneral};

my $Access = Win32::OLE->new('Access.Application', 'Quit');
my $Workspace = $Access->DBEngine->CreateWorkspace('', 'Admin', '');
my $Database = $Workspace->CreateDatabase($dbname, $dbLangGeneral);

It's at this point I should mention I did this years ago on Windows XP (32-bit) with Strawberry Perl 5.10.1 (32-bit). I've since upgraded to Windows 7 (64-bit) and Strawberry Perl 5.18.1 (64-bit) and along the way, the script "broke".

I decided for no particular reason the other day to see what was wrong. I was sure it was a 32/64 bit issue with the instantiate code. On Windows 7 64-bit, I could instantiate the database with Strawberry 32-bit, but not connect to it for steps 2 through 4. With Strawberry 64-bit, I couldn't instantiate the database, but could do steps 2 through 4 on an existing database.

It turns out, there isn't a 64-bit version of "Microsoft DAO" and instead, you should rely on "Microsoft Office 14.0 Access Database Engine Object" for similar functionality. Changing the line to read:

my $props = Win32::OLE::Const->Load('Microsoft Office 14.0 Access Database Engine Object')

did the trick!

Next step - testing this all with SQLite.