Re: dos Dbase -> pg table

The problem is that the INSERT statment generated by the script is more 16K
long. You must have some very large memo fields! This a pgsql limitation,
but I think it has been increased(?) in recent versions? What version are
you on?

I don't know of an easy workaround, but I can think of two options:

1) write a custom version of the script for this file which first does an
INSERT on some of the fields, followed by an UPDATE to put the rest in.
Hopefully you have a unique key in the file for the where clause of UPDATE
portion. Remember to do a VACCUM at the end to recover space no longer
used; since the space used by INSERT will not be reused when you issue UPDATE.

2) rewrite the portion of script which generates INSERT to instead create
an tempfile with data in a format expected by "COPY FROM <tempfile>".
Then, once the tempfile has all your data, issue "COPY FROM <tempfile>".
http://looking-glass.usask.ca:82/users-lounge/docs/7.0/user/sql-copy.htm

If a 16K limitation on SQL statements will be a problem with your
applications, you might consider making a change to source code and
re-installing; this is out of my league.

Frank

At 02:24 PM 1/3/02 -0300, Fernando M. Maresca wrote:
>Tnx a lot, Frank.
>Very usefull. So sorry about this new question. I run the script over a
.dbf of about 4000 records in 30 columns.
>when I run the script in this dbf this is the output:
>
>home/fmaresca/DBFCONV/SUBSCRIB.DBF - "subscrib"
>DBD::Pg::db do failed: PQsendQuery() -- query is too long. Maximum length
is 16382
>Database handle destroyed without explicit disconnect.
>
>Any idea? workaround?
>Tnx,
>Fernando.
>
>
>
>On Thu, Jan 03, 2002 at 10:33:52AM -0500, Frank Bax wrote:
>> install DBI, DBD:pg, DBD:XBase, then run this script (after customising the
>> $dir, $dbf and $dbp lines):
>>
>> #!/usr/bin/perl -w
>> use strict;
>> use File::Basename; # for basename() function
>> use DBI; use DBD::XBase; use DBD::Pg;
>> my $dir = '/home/fbax/DBFCONV';
>> my $dbf = DBI->connect("dbi:XBase:$dir", {RaiseError => 1} );
>> my $dbp = DBI->connect("dbi:Pg:dbname=fbax", "fbax", "", {RaiseError =>
1} );
>> while (my $fname = <$dir/*.DBF>) {
>> &DBF2PG ($dbf, $dbp, $fname, basename(substr($fname, 0,
length($fname)-4)));
>> }
>> $dbf->disconnect;
>> $dbp->disconnect;
>>
>> sub DBF2PG {
>> (my $dbf, my $dbp, my $fname, my $table) = @_;
>> $table = lc("\"$table\"");
>> print "$fname - $table\n";
>> open (PIPE, "dbfdump --info $fname |") or die "Can't open $fname: $!";
>> my $sql = "CREATE TABLE $table ";
>> my $sep = "(";
>> while( <PIPE> ) {
>> chomp;
>> if (/^[0-9]+\./) { # line starts with number.
>> # print "$_\n";
>> my @stru = split; # stru contains field,type,len,dec
>> $sql .= $sep.' "'.lc($stru[1]).'"';
>> if ($stru[2] eq 'D') {
>> $sql .= " date";
>> } elsif ($stru[2] eq 'L') {
>> $sql .= " boolean";
>> } elsif ($stru[2] eq 'M') {
>> $sql .= " text";
>> } elsif ($stru[2] eq 'G') {
>> $sql .= " text";
>> } elsif ($stru[2] eq 'C' && $stru[3] eq 1) {
>> $sql .= " char";
>> } elsif ($stru[2] eq 'C') {
>> $sql .= " varchar($stru[3])";
>> } elsif ($stru[2] eq 'N' && $stru[4] eq 0 && $stru[3] < 5) {
>> $sql .= " int2";
>> } elsif ($stru[2] eq 'N' && $stru[4] eq 0 && $stru[3] < 10) {
>> $sql .= " int4";
>> } elsif ($stru[2] eq 'N' && $stru[4] eq 0) {
>> $sql .= " int8";
>> } elsif ($stru[2] eq 'N') {
>> $sql .= " numeric($stru[3],$stru[4])";
>> } else {
>> $sql .= " $stru[2].$stru[3].$stru[4]";
>> }
>> $sep = ',';
>> }
>> }
>> close (PIPE);
>> $sql .= ' );';
>> $dbp->{RaiseError} = 0;
>> $dbp->do( "DROP TABLE $table" );
>> $dbp->{RaiseError} = 1;
>> $dbp->do( $sql );
>>
>> my $sth = $dbf->prepare(" SELECT * FROM ".basename($fname) );
>> $sth->execute;
>> while (my @row = $sth->fetchrow_array()) {
>> $sql = "INSERT INTO $table VALUES ";
>> $sep = "(";
>> foreach my $fld (@row) {
>> $sql .= "$sep ".$dbp->quote($fld);
>> $sep = ",";
>> }
>> $sql .= ' );';
>> $dbp->do( $sql );
>> }
>> $sth->finish;
>> }
>>
>>
>> At 08:44 AM 1/3/02 -0300, Fernando M. Maresca wrote:
>> >Hello everybody,
>> >
>> >There is out there a proggy called dbf2pg (like dbf2mysql) that makes a
>> good job, but it can not deal with MEMO
>> >fields. AHHGGG!!!
>> >So, somebody knows a program that can deal with MEMO fields in (DOS) .dbf
>> files, or, at least, a program that can
>> >cut off memo fields of a .dbf file from command line in *nix?
>> >The idea is to build a script (well, I wrote it and run ok if there are no
>> MEMOs in source .dbf) that cron.ically
>> >updates a table from a dbase file.
>> >Tnx, regards
>> >
>> >
>> >--
>> >Fernando M. Maresca
>> >
>> >Monitoring Station S.A.
>> >48 nº 812 La Plata (1900) BA - ARG
>> >Tel/Fax: (54) 221 425-3355
>> >ICQ: 101304086
>> >
>> >---------------------------(end of broadcast)---------------------------
>> >TIP 5: Have you checked our extensive FAQ?
>> >
>> >http://www.postgresql.org/users-lounge/docs/faq.html
>> >
>>
>
>--
>Fernando M. Maresca
>
>Monitoring Station S.A.
>48 nº 812 La Plata (1900) BA - ARG
>Tel/Fax: (54) 221 425-3355
>ICQ: 101304086
>