CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
export from database to dbf

 



aesalus1976
New User

Oct 28, 2008, 3:50 AM

Post #1 of 4 (5119 views)
export from database to dbf Can't Post

Hallo all. I am trying create cgi script with export tables from databases to excel and dbf. Excel no problem:
my $xls = Spreadsheet::WriteExcel::FromDB->read($db, $tb);
$xls->write_xls("/tmp/$tb.xls");
but I have problem with dbf files. I already tried pg2xbase (done with error message: pg2xbase: symbol lookup error: pg2xbase: undefined symbol: _ZN7xbXBaseC1Ev). Then I try xbase.pm module but it is little bit complicated for me. I do not know how assign names of columns from database and also appropriate data values. I understand to general syntax of xbase.pm:
my $newtable = XBase->create("name" => "copy.dbf",
"field_names" => [ "ID", "MSG" ],
"field_types" => [ "N", "C" ],
"field_lengths" => [ 6, 40 ],
"field_decimals" => [ 0, undef ]);
, also I know how found out names of columns ( $name[$i] = "\"$sth->{NAME}->[$i]\"";) but the whole complex is out of me. Please help my.


aesalus1976
New User

Nov 3, 2008, 2:36 AM

Post #2 of 4 (5075 views)
Re: [aesalus1976] export from database to dbf [In reply to] Can't Post

Hallo all. OK, I'm create some perl code which export data from postgres to dbf file. Please this is first and raw coded version. It is working, but not exactly what I want. The is some "internal limit" in XBase for number of lines in dbf file (script create file with 96559 lines (seems like excel limit) and finish with error message: DBD::Pg::st fetchrow_arrayref failed: no statement executing). Any idea how solve it?

#!/usr/bin/perl -w
use XBase;
use DBI;
use Time::Local;
#configuration (for now only)
$db = 'database name';
$login = 'user name';
$passwd = 'password';
$tb = 'table name';

#login to database
my $dbh = DBI->connect("DBI:Pg:dbname=$db", "$login", "$passwd");
if (!$dbh) {
print "ERR: Couldn't open connection: ".$DBI::errstr."\n";
}
#setup and execute query
my $tabsth = $dbh->type_info_all();
my $query1 = qq{select * from $tb };
my $sth = $dbh->prepare($query1);
$sth->execute;
my $col_names = $sth->{NAME};
my $col_types = $sth->{TYPE};
my $i=0;
#extract names of columns to the array
foreach my $c_name (@$col_names) {
$, = ",";
$nm .= $$col_names[$i] . ', ';
$i++;
}
@nm = split(',', $nm);
@nm1 = @nm[0 .. $#nm-1];
#create empty dbf file
my $newtable = XBase->create("name" => "new.dbf",
"field_names" => [ @nm1 ],
"field_types" => [ ], #problem, pg return some num. values
"field_lengths" => [ ],
"field_decimals" => [ ]);
#open dbf file
my $table = new XBase "copy.dbf" or die XBase->errstr;
#add some records
while ($sth->fetchrow_arrayref) {
my $last = $table->last_record;
$table->set_record($last+1, $sth->fetchrow_array);
}


FishMonger
Veteran / Moderator

Nov 3, 2008, 8:34 AM

Post #3 of 4 (5053 views)
Re: [aesalus1976] export from database to dbf [In reply to] Can't Post

Please use the code tags when posing blocks of code. The code tags will use a different font and retain the code indentation which will make it easier for us to read.

Instead of using the -w switch, it's better to use the warnings pragma as well as the strict pragma.

Instead of the if (!$dbh) { block use an or die statement on the connect statement and enable RaiseError.

Your method of building/assigning the @nm and @nm1 arrays is very messy and inefficient.

Your while loop is not initialized properly and the $table->set_record call should be passed the row that was fetched in the begining of the loop, not a separate fetch_array call.

Here is a cleaned up version, but it's incomplete.

Code
#!/usr/bin/perl 

use warnings;
use strict;
use XBase;
use DBI;
use Time::Local;

#configuration (for now only)
$db = 'database name';
$login = 'user name';
$passwd = 'password';
$tb = 'table name';

#login to database
my $dbh = DBI->connect("DBI:Pg:dbname=$db", $login, $passwd,
{ RaiseError => 1 })
or die "ERR: Couldn't open connection: " . $DBI::errstr."\n";


#setup and execute query
my $sth = $dbh->prepare("select * from $tb");
$sth->execute;

my @names = @{$sth->{NAME}};

#my @types = @{$sth->{TYPE}}; # returns integer
my @types = map { scalar $dbh->type_info($_)->{TYPE_NAME} } @{ $sth->{TYPE} }; # returns name

# @types needs to be translated into single character data types that are used by XBase
# The field types are specified by one letter strings (C, N, L, D, ...).
# If you set some value as undefined, create will make it into some reasonable default.

#create empty dbf file
my $newtable = XBase->create("name" => "new.dbf",
"field_names" => [ @names ],
"field_types" => [ @types ], # see above note

# the DBI doc should show how to retrieve this info
"field_lengths" => [ ],
"field_decimals" => [ ],
);

#open dbf file
my $table = new XBase "copy.dbf" or die XBase->errstr;

#add some records
while (my @values = $sth->fetchrow_array) {
my $last = $table->last_record;
$table->set_record($last+1, @values);
}



aesalus1976
New User

Nov 10, 2008, 11:24 PM

Post #4 of 4 (4818 views)
Re: [FishMonger] export from database to dbf [In reply to] Can't Post

Thanks you a lot, now it is a much more better and faster. My code was wery raw. I am not native in perl, but I am still learning. Thanks a lot again.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives