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.