Perl and SQLite - Follow Through, On and Up

As follow through to my previous post, I did some investigation into SQLite and got that working in my little database proof of concept script.

As follow-on to my digression in that post, SQLite adds yet another wrinkle in the AUTO_INCREMENT saga I used as a primary key in each of my tables. These are the strings I found to work:

Microsoft Access

ID AUTOINCREMENT NOT NULL PRIMARY KEY

MySQL

ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY

SQLite

ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

As follow-up to my statement about not finding a way to programmatically instantiate a MySQL database, I did some searching and found the following in the 'perldoc' for DBD::mysql - I have yet to try it.

$rc = $drh->func('createdb', $database, $host, $user, $password, 'admin');

...

createdb
    Creates the database $dbname. Equivalent to "m(y)sqladmin create $dbname".

And finally, the topic of this post. I found this neat little Perl extension for SQLite and thought I might give it a try.

Learning from my last post, I checked my SQLite version and it was 32-bit, so I switched my Perl to Strawberry 5.18.1 32-bit on Windows 7 x64 and followed the steps outlined in that post. It worked a treat first time! So I ported the executable extension to my other machine and tried it and it failed.

Knowing my other machine was 64-bit for everything I switched Perl back to Strawberry 5.18.1 64-bit on my original machine and failure there too. It makes sense; I compiled a 32-bit Perl extension for a 32-bit version of SQLite - it stands to reason it would need a 32-bit version of Perl to run it. But I use 64-bit Perl normally.

Easy enough solution, I grabbed the sources for SQLite from their download page and compiled myself with the 64-bit gcc from Strawberry Perl 5.18.1 64-bit. This incidentally was the easiest build I've done - no warnings, no errors, built first time.

gcc shell.c sqlite3.c -o sqlite3.exe -lpthread

I redid the extension compile with 64-bit Perl and all worked again - this time all with 64-bit.

Since I did this a few times (due to my own mistake), I created a Perl package with a Makefile.PL to easily rebuild the SQLite Perl extension whenever / wherever needed.

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.