I have a simple script which converts access tables to XML.
use XML::Simple;
use Data::Dumper;
use DBI;
my $config = XMLin('c:\website\config.xml', forcearray => qr/table/ )
or die ("unable to open config file");
my ($access_database) = @{$config->{database}};
my ($results_directory) = @{$config->{results_directory}};
my $dsn = "driver=Microsoft Access Driver (*.mdb);dbq=$access_database
+";
my $driver = "dbi:ODBC:$dsn";
my $username = '';
my $password = '';
my $dbopt = { PrintError => 0,
RaiseError => 0 };
open (LOG,'>c:\website\log.txt') or die "log $!";
my $dbh = DBI->connect($driver, $username, $password, $dbopt)
or die "Error connecting: $DBI::errstr";
for $extract (@{$config->{extract}}) {
my $results_file = $extract->{file};
my @results;
my $ref;
for $table (@{$extract->{table}}) {
my $sql = "SELECT * FROM $table";
my $sth = $dbh->prepare($sql)
or die "Unable to prepare ($sql): ", $dbh->errstr;;
$sth->execute
or die "Unable to execute ($sql): ", $sth->errstr;
$ref->{$table} = $sth->fetchall_arrayref({});
}
my $results = XMLout($ref,rootname=>'root', noattr=>1);
print LOG Dumper $ref;
print LOG $results;
print LOG "\n\n";
local *RESXML;
open (RESXML, ">$results_directory\\$results_file") or
die ("Unable to open results file $results_directory\\$results_fil
+e: $!");
print RESXML $results;
}
I can't hand out the database, but I can tell you it's pretty simple with a few numeric, date, memo, and text fields.
The problem is that some result sets are empty, even though the database table is populated.
So, I did a little test. I wrote a small script which did nothing more
than select * from accommodation. That didn't work. I got no rows in
my results set at all.
If I did select count(*) from accommodation, I got the count of the
rows.</p?
If I did select <field> from accommodation, that worked fine.
I am more stumped that stumpy the stumpy dwarf.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.