Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

MS Access empty results

by marvell (Pilgrim)
on Mar 10, 2004 at 14:55 UTC ( [id://335457]=perlquestion: print w/replies, xml ) Need Help??

marvell has asked for the wisdom of the Perl Monks concerning the following question:

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.

--
¤ Steve Marvell

Replies are listed 'Best First'.
Re: MS Access empty results
by Zero_Flop (Pilgrim) on Mar 11, 2004 at 06:44 UTC
    Hey Marvell,
    If all you are trying to do is export the data, you could connect to the db through OLE and call the ExportXML method. TAMTOWTDI.
Re: MS Access empty results
by matija (Priest) on Mar 10, 2004 at 22:44 UTC
    Honestly, my first thought would be to suspect a bug in the database implementation, or in the ODBC interface for that particular database.

    The workaround that I'd try would be to enumerate all the needed fields in the select, i.e.:

    select field1,field2,...fieldlast from table
    Perhaps by explicitly naming all the fields you can get what you need?

Re: MS Access empty results
by marvell (Pilgrim) on Mar 10, 2004 at 15:05 UTC
    truncation, bah!

    --
    Steve Marvell

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://335457]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (4)
As of 2024-03-29 00:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found