Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Fail extracting table from .mdb file using DBI module

Reply
Thread Tools

Fail extracting table from .mdb file using DBI module

 
 
MoshiachNow
Guest
Posts: n/a
 
      02-10-2008
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
}
 
Reply With Quote
 
 
 
 
Uri Guttman
Guest
Posts: n/a
 
      02-10-2008
>>>>> "M" == MoshiachNow <(E-Mail Removed)> writes:

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-
>> fetchrow_array() ) {

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

--
Uri Guttman ------ http://www.velocityreviews.com/forums/(E-Mail Removed) -------- http://www.sysarch.com --
----- Perl Architecture, Development, Training, Support, Code Review ------
----------- Search or Offer Perl Jobs ----- http://jobs.perl.org ---------
--------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com ---------
 
Reply With Quote
 
 
 
 
xhoster@gmail.com
Guest
Posts: n/a
 
      02-11-2008
MoshiachNow <(E-Mail Removed)> wrote:
> 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-
> >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";
> }


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.
 
Reply With Quote
 
MoshiachNow
Guest
Posts: n/a
 
      02-11-2008
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
 
Reply With Quote
 
David Combs
Guest
Posts: n/a
 
      03-05-2008
In article <(E-Mail Removed)>,
Uri Guttman <(E-Mail Removed)> wrote:

....

>
> 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


 
Reply With Quote
 
Uri Guttman
Guest
Posts: n/a
 
      03-05-2008
>>>>> "DC" == David Combs <(E-Mail Removed)> writes:

DC> In article <(E-Mail Removed)>,
DC> Uri Guttman <(E-Mail Removed)> wrote:

DC> ...

>>

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.


DC> Interesting. How so?

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

uri

--
Uri Guttman ------ (E-Mail Removed) -------- http://www.sysarch.com --
----- Perl Architecture, Development, Training, Support, Code Review ------
----------- Search or Offer Perl Jobs ----- http://jobs.perl.org ---------
--------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com ---------
 
Reply With Quote
 
xhoster@gmail.com
Guest
Posts: n/a
 
      03-05-2008
Jim Gibson <(E-Mail Removed)> wrote:
> In article <fqml1a$blo$(E-Mail Removed)>, David Combs
> <(E-Mail Removed)> wrote:
>
> > In article <(E-Mail Removed)>,
> > Uri Guttman <(E-Mail Removed)> wrote:
> >
> > ...
> >
> > >
> > > 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?

>
> 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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
DBI Mysql storing DBI:binary, trouble with character 26 Jerome Hauss Ruby 0 10-13-2004 03:04 PM
DBI and DBI::Oracle packages configuration ulloa Perl 1 07-22-2004 05:52 PM
DBI problem : How can I load quickly one huge table with DBI ??. Tim Haynes Perl Misc 3 09-13-2003 03:43 AM
if (f() != FAIL) or if (FAIL != f())? Wenjie C Programming 3 07-31-2003 09:54 PM
Mason, DBI, and DBI::Pg Asby Perl Misc 0 07-24-2003 09:04 PM



Advertisments