Fail extracting table from .mdb file using DBI module

Discussion in 'Perl Misc' started by MoshiachNow, Feb 10, 2008.

  1. MoshiachNow

    MoshiachNow Guest

    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
    }
    MoshiachNow, Feb 10, 2008
    #1
    1. Advertising

  2. MoshiachNow

    Uri Guttman Guest

    >>>>> "M" == MoshiachNow <> 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.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 ---------
    Uri Guttman, Feb 10, 2008
    #2
    1. Advertising

  3. MoshiachNow

    Guest

    MoshiachNow <> 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.
    , Feb 11, 2008
    #3
  4. MoshiachNow

    MoshiachNow Guest

    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
    MoshiachNow, Feb 11, 2008
    #4
  5. MoshiachNow

    David Combs Guest

    In article <>,
    Uri Guttman <> 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
    David Combs, Mar 5, 2008
    #5
  6. MoshiachNow

    Uri Guttman Guest

    >>>>> "DC" == David Combs <> writes:

    DC> In article <>,
    DC> Uri Guttman <> 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 ------ -------- 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 ---------
    Uri Guttman, Mar 5, 2008
    #6
  7. MoshiachNow

    Guest

    Jim Gibson <> wrote:
    > In article <fqml1a$blo$>, David Combs
    > <> wrote:
    >
    > > In article <>,
    > > Uri Guttman <> 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.
    , Mar 5, 2008
    #7
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UGhpbA==?=

    Faster access to MDB? MS Access MDB and ASP

    =?Utf-8?B?UGhpbA==?=, Jan 26, 2005, in forum: ASP .Net
    Replies:
    8
    Views:
    469
    Kevin Spencer
    Jan 27, 2005
  2. Byron Hopp
    Replies:
    6
    Views:
    644
    Paul Clement
    Jan 11, 2006
  3. John  Schult

    MDB to MDB Event Notification

    John Schult, May 4, 2005, in forum: Java
    Replies:
    0
    Views:
    467
    John Schult
    May 4, 2005
  4. Wenjie

    if (f() != FAIL) or if (FAIL != f())?

    Wenjie, Jul 28, 2003, in forum: C Programming
    Replies:
    3
    Views:
    438
    E. Robert Tisdale
    Jul 31, 2003
  5. Tim Haynes
    Replies:
    3
    Views:
    140
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page