Fail extracting table from .mdb file using DBI module


M

MoshiachNow

HI,

The following sub extracts data nicely from all tables,just one table
comes up empty.
Will appreciate ideas on possible issues in the code.
thanks
=======================
sub exportMDB {
my $database = shift;
my $driver = "Microsoft Access Driver (*.mdb)";
print "$database\n";
print "---------------------------------\n\n";
my $dsn = "dbi:ODBC:driver=$driver;dbq=$database";
my $dbh = DBI->connect("$dsn") or warn "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 "\n$table :\n";
print "--------\n";
my $sql = "select * from $table";

# Prepare the SQL query for execution
my $sth = $dbh->prepare($sql) or warn "Couldn't prepare
statement:$DBI::errstr; stopped";

# Execute the query
$sth->execute() or warn "Couldn't execute statement:
$DBI::errstr; stopped";

# Fetch each row and print it
while ( my (@row) = $sth->fetchrow_array() ) {
print "$_\t" foreach (@row);
print "\n";
}
}
}
$dbh->disconnect(); # Disconnect from
the database
}
 
Ad

Advertisements

U

Uri Guttman

M> The following sub extracts data nicely from all tables,just one table
M> comes up empty.
M> Will appreciate ideas on possible issues in the code.
M> thanks

i don't think i can help with the db issue but here are some general
coding comments.

M> =======================
M> sub exportMDB {
M> my $database = shift;
M> my $driver = "Microsoft Access Driver (*.mdb)";

that is a fixed value so assign it outside the sub if it is called more
than once.
M> print "$database\n";
M> print "---------------------------------\n\n";

you can use a single print call for that. either pass it a list of thise
strings, or make then a single string or use a here document. all are
cleaner and faster than 2 (or more) calls to print

M> my $dsn = "dbi:ODBC:driver=$driver;dbq=$database";
M> my $dbh = DBI->connect("$dsn") or warn "Couldn't open database:


don't quote single variables like $dsn as it is useless and may actually
cause bugs.


M> my $sth = $dbh->table_info( "", "", "", "TABLE" );

you should comment lines like that since you ignore several
args. explain why you make this call and why you chose this list of
args.

M> while ( my ($catalog, $schema, $table, $type) = $sth-M> if ($table) {

you have no else clause on that if. so reverse the if and do the next
loop. this is best done with a simple statement modifier

next unless $table ;

otherwise you fall through to the rest of the code which needs no block
so you save an indent, the expensive braces and all those wasted
pixels.

M> print "\n$table :\n";
M> print "--------\n";

multi print call again. bad habit you should break.

M> my $sql = "select * from $table";

M> # Prepare the SQL query for execution
M> my $sth = $dbh->prepare($sql) or warn "Couldn't prepare
M> statement:$DBI::errstr; stopped";

wrap long lines like that. general style calls for lines < 80 or so. i
don't want to start a war over long code lines.

M> # Execute the query
M> $sth->execute() or warn "Couldn't execute statement:
M> $DBI::errstr; stopped";

M> # Fetch each row and print it
M> while ( my (@row) = $sth->fetchrow_array() ) {
M> print "$_\t" foreach (@row);

that calls print each time in the loop. map is usually better when you
want output for print

print map "$_\t", @row ;

M> print "\n";

combine that with the previous print:

print map( "$_\t", @row ), "\n";

uri
 
X

xhoster

MoshiachNow said:
HI,

The following sub extracts data nicely from all tables,just one table
comes up empty.

With or without warnings/errors/messages?
Will appreciate ideas on possible issues in the code.
thanks
=======================
sub exportMDB {
my $database = shift;
my $driver = "Microsoft Access Driver (*.mdb)";
print "$database\n";
print "---------------------------------\n\n";
my $dsn = "dbi:ODBC:driver=$driver;dbq=$database";
my $dbh = DBI->connect("$dsn") or warn "Couldn't open database:
$DBI::errstr; stopped";

If you say "stopped", you should probably actually stop. Using die
instead of warn would accomplish that. Or just setting RaiseError.
my $sth = $dbh->table_info( "", "", "", "TABLE" );

while ( my ($catalog, $schema, $table, $type) = $sth-
if ($table) {
print "\n$table :\n";
print "--------\n";
my $sql = "select * from $table";

# Prepare the SQL query for execution
my $sth = $dbh->prepare($sql) or warn "Couldn't prepare
statement:$DBI::errstr; stopped";

# Execute the query
$sth->execute() or warn "Couldn't execute statement:
$DBI::errstr; stopped";

# Fetch each row and print it
while ( my (@row) = $sth->fetchrow_array() ) {
print "$_\t" foreach (@row);
print "\n";
}

You don't check fetchrow_array for errors after it returns the empty
list.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
 
M

MoshiachNow

Thanks to Uri for valuable code comments.However,this code I have just
cut&pasted from web ...

Xho - I do not get any errors from the code.

Still I have one table empty ,and no ideas how to continue.
thanks
 
D

David Combs

....
M> my $dsn = "dbi:ODBC:driver=$driver;dbq=$database";
M> my $dbh = DBI->connect("$dsn") or warn "Couldn't open database:


don't quote single variables like $dsn as it is useless and may actually
cause bugs.

Interesting. How so?

Thanks,

David
 
U

Uri Guttman

DC> In article <[email protected]>,

DC> ...
M> my $dsn = "dbi:ODBC:driver=$driver;dbq=$database";
M> my $dbh = DBI->connect("$dsn") or warn "Couldn't open database:
DC> Interesting. How so?

google for many postings about why unneeded quoting can be bad and
slow. no need to repeat it again.

uri
 
Ad

Advertisements

X

xhoster

Jim Gibson said:
See 'perldoc -q quoting' "What's wrong with always quoting "$vars"?"

OK. So? Do we use ++ on DSNs? Do we use references for DSNs?

Always quoting "$vars" is different than quoting "$vars" at least once.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top