|
Posted by MoshiachNow on February 10, 2008, 12:53 am
Please log in for more thread options
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
}
|
|
Posted by Uri Guttman on February 10, 2008, 1:14 am
Please log in for more thread options
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 ------ uri@stemsystems.com -------- 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 ---------
|
|
Posted by David Combs on March 5, 2008, 12:24 pm
Please log in for more thread options
...
>
> 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
|
|
Posted by Uri Guttman on March 5, 2008, 12:44 pm
Please log in for more thread options
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 ------ uri@stemsystems.com -------- 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 ---------
|
|
Posted by Jim Gibson on March 5, 2008, 12:50 pm
Please log in for more thread options
>
> ...
>
> >
> > 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"?"
--
Jim Gibson
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com
|
| Similar Threads | Posted | | Formatting a single line without effecting the entire table with the Text::Table module | March 10, 2005, 11:18 am |
| Extracting encrypted in a table on DB2 with Perl | December 9, 2004, 11:32 pm |
| Re: Extracting links from a html table | May 19, 2008, 5:40 pm |
| Extracting .jpg from mbox file with MIME::Base64 | February 3, 2005, 10:40 pm |
| help: newbie extracting lines from large file. | April 16, 2006, 8:57 pm |
| Parsing table in rtf file | December 29, 2007, 11:17 pm |
| Insert Log file into oracle table | September 4, 2006, 6:04 am |
| How to parse text file into hash table | April 24, 2007, 1:30 pm |
| Reset <> without having it fail once? | August 11, 2004, 8:51 am |
| integer fail ... | July 28, 2005, 3:35 am |
|
XML Sitemap