#! /usr/bin/perl -w
#
# 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 xml 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, DATA_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_array() )
    {
        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; stopped";

            # 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();
}