The story so far:
Many thanks to all of the monks who came up with answers to my original post. I went with the DBI / DBD::ODBC approach because it was closest to my original idea and I had all of the components already installed.
The code below lacks any real error handling and doesn't log any error/status information. I will be doing that later.
Be gentle with your comments!
Inman
#! /usr/bin/perl -w
#
# Daniel Inman
#
# Dump access databases to XML using DBI and DBD::ODBC
use strict;
use warnings;
use File::Find;
use DBI;
use IO::File;
use XML::Writer;
use Cwd;
#use Data::Dumper;
my $findpath = $ARGV[0] ? $ARGV[0] : '.';
my $output;
my $xmlwriter;
my $driver = "Microsoft Access Driver (*.mdb)";
# scan all files looking for *.mdb
find({ wanted => \&processDatabase}, $findpath);
# Process MDB files
# Produce and XML file in the same directory with the same name but xm
+l extension.
sub processDatabase
{
return unless $File::Find::name =~ /([^\/]*\.mdb)/i;
my $database = cwd . "/$1";
my $xmlfile = $database;
$database =~ s!/!\\!g;
$xmlfile =~ s/\.mdb$/.xml/i;
$output = new IO::File(">$xmlfile");
$xmlwriter = new XML::Writer(OUTPUT => $output, DATA_MODE => 1, DA
+TA_INDENT => 4);
$xmlwriter->xmlDecl("UTF-8");
$xmlwriter->startTag ("Database", "path"=>"$database");
exportDatabase ($database);
$xmlwriter->endTag ("Database");
$xmlwriter->end();
$output->close();
}
# Export the database
sub exportDatabase
{
my $database = shift;
print "Exporting: $database\n";
my $dsn = "dbi:ODBC:driver=$driver;dbq=$database";
my $dbh = DBI->connect("$dsn")
or die "Couldn't open database: $DBI::errstr; stopped";
my $sth = $dbh->table_info( "", "", "", "TABLE" );
while ( my ($catalog, $schema, $table, $type) = $sth->fetchrow_arr
+ay() )
{
if ($table)
{
print "Exporting $table\n";
my $sql = "select * from $table";
# Prepare the SQL query for execution
my $sth = $dbh->prepare ("$sql")
or die "Couldn't prepare statement:$DBI::errstr; stopp
+ed";
# Execute the query
$sth->execute() or die "Couldn't execute statement: $DBI::
+errstr; stopped";
$xmlwriter->startTag ("Table", "name"=>"$table");
# Fetch each row and print it
while ( my (@row) = $sth->fetchrow_array() )
{
$xmlwriter->startTag ("Row");
foreach (@row)
{
$xmlwriter->dataElement ("Column", $_);
}
$xmlwriter->endTag ("Row");
}
$xmlwriter->endTag ("Table");
}
}
# Disconnect from the database
$dbh->disconnect();
}