Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

MS Access empty results

by marvell (Pilgrim)
on Mar 10, 2004 at 14:55 UTC ( #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?

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2022-06-25 17:36 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (83 votes). Check out past polls.