Tech Stuff‎ > ‎

Access MDB with Perl on Cygwin

I wanted to be able to access some MS Access .MDB databases from perl scripts under cygwin, for some data analysis. It seemed like it should have been reasonably straightforward, but turned out to be very frustrating. I did get it working in the end. It probably should work on Linux this way too.

Things I needed:

 * Cygwin basic installation, with cygrunsrv, perl, perl-DBI, g++, make, automake, autoconf, flex, bison, libtool, pkg-config, libglib2.0-devel git and some other dev tools. Make sure cygserver is running - run "/usr/bin/cygserver-config"

 * unixODBC 2.3.2 (2.3.4 doesn't work) built from source - use "./configure --enable-shared"

 * mdbtools from brianb's git repo revision 529534dae5ff57be9384a3dbde00c83680537579 (later revs don't work). Apply noundef.patch (attached to this page), then run "./autogen.sh --with-unixodbc=/usr/local --with-libiconv-prefix=/usr", then apply nolfl.patch, then "make install"

 * DBD::ODBC v1.23 - later versions don't get along with mdbtools! (requires gcc4, not 3.x!!) - ignore the warnings about tests not working with environment variables set. Note, with perl 5.14, there are a bunch of symbols that are undefined. Prefix all of them with 'PL_' to hackaround (could #define them in some head file, perhaps). http://markmail.org/message/35qwy64nc4zwd2ou#query:+page:1+mid:35qwy64nc4zwd2ou+state:results

Big gotcha!! something(?) expects unixODBC's config file "odbc.ini" to be in /etc , but it gets placed in /usr/local/etc by default. Probably can be worked around by setting the environment variable ODBCSYSINI, but it's easier to just make a symlink. Thanks to James Orr (and Google) for the hint.

odbcinst.ini contains a pointer to the odbc-mdb driver:

[MDBToolsODBC]
Description = MDB Tools ODBC drivers
Driver     = /usr/local/bin/cygmdbodbc-1.dll
Setup      =
FileUsage  = 1
CPTimeout  =
CPReuse    =


and odbc.ini defines the source(s):

[lotw]
Description = LotW users database
Driver      = MDBToolsODBC
Database    = /cygdrive/c/DXLab/DXView/Databases/LotW.mdb


A sample perl script:

#!/usr/bin/perl

use DBI;

my @drivers = DBI->available_drivers;
print "Drivers: ", join(", ", @drivers), "\n";

my @dsns = DBI->data_sources('ODBC');
print "ODBC sources: ", join(", ", @dsns), "\n";

my $dbh = DBI->connect("dbi:ODBC:lotw") or die "$DBI::errstr\n";

my $sql = qq/select * from Callsigns where CallSign = 'N6ML'/;
my $sth = $dbh->prepare($sql);
$sth->execute();
while (@row = $sth->fetchrow_array) {
    print join(", ", @row), "\n";
}





ċ
nolfl.patch
(0k)
iain MacDonnell,
Mar 10, 2012, 4:17 PM
ċ
noundef.patch
(1k)
iain MacDonnell,
Mar 10, 2012, 4:17 PM
Comments