in reply to Re^2: DBD::Pg copy issues
in thread DBD::Pg copy issues

If you already have the information inside perl here's an example. Be really careful to use only unix line endings - PostgreSQL's copy command is very particular about that.

# Start copying some data to PostgeSQL. Here, STDIN is relative to the
# script. So the PostgreSQL STDIN being mentioned is actually just the
# stuff being written over $dbh->func( ..., 'putline');
$dbh->func( 'COPY some_table FROM STDIN', 'putline' );

# Write whatever was read from $fh to PostgreSQL's COPY.
while ( <$fh> )
{
    chomp;
    $dbh->func( $_, 'putline' );
}

# Tell PostgreSQL that the COPY is over with.
$dbh->func( "\\.\n", 'putline' );

[download]

Replies are listed 'Best First'.
Re^4: DBD::Pg copy issues
by tradez (Pilgrim) on Sep 10, 2004 at 19:58 UTC
    K, I have this now 
       $dbh->func('COPY snapshot_$formName (name, value, collect_date, omp
+_id) from STDIN', 'putline');
    print "Started a line\n";
    foreach my $values (@form_out){
      #my $cmd = `rm -f $form_out`;
      my @values = split(/;/, $values);
      my @names = split(/ /, $ga->{'outputString'}{$formName});
      my $count = 0;
      $count = 0;
      foreach my $value (@values){
        chomp($value);
        if (!$names[$count]){
         print "$value element # $count does not exist in array\n";
        }
        my $name = $names[$count];
        chomp($name);
        $value =~ s/\s+/ / if $value;
        $value = 'Null' if (length($value) < '1');
        if ($value =~ /\w+/){
          $dbh->func("$name\t$value\t$date\t$ompId", 'putline');
        }else{
          $dbh->func("$name\tNull\t$date\t$ompId", 'putline');
        }
         $count++;
      }
    }
    $dbh->func( "\\.\n", 'putline' );
    print "ended that line\n";

    [download]
    And i am still not getting inserts at all into the DB. What is the way to see what errStr has on the DBH obj, i tried like it was a STH and it didn't work.


    Tradez
    "Never underestimate the predictability of stupidity"
    - Bullet Tooth Tony, Snatch (2001)
[reply]
[d/l]
      $dbh->func( 'COPY snapshot_$formName ( name, value, collect_date, omp_
+id ) FROM STDIN',
            'putline' );

      [download]

      There are three problems with preceding line.

      1. The first and most serious is that you used single quotes but appeared to expect $formName to interpolate into the string. Single quotes prevent this. You probably meant to use double quotes.
      2. The second is that 'value' is just a really poor name for a value. Its like calling a variable $variable. Use descriptive names for variables and table attributes. Its part of writing maintainable code.
      3. The 'name' name is typically a reserved word in SQL and you'll find that a lot of things play badly with schemas that use attributes of this name. I suggest you find something more appropriate like "collection_name."

        my $count = 0;
for ( ... ) {
    ... $names[ $count ]

    $count++;
}

        [download]

        It looks like somehow your indexes in @values are synchronized with the indexes in some @names list. When you manage the current index manually, you make it likely that it will be managed incorrectly. Let perl do this for you.

        foreach my $ix ( 0 .. $#names )
    my $value = $values[ $ix ];
    if ( ! $names[ $ix ] ) {
    }
}

        [download]

        Now here's what I think a reasonable re-write of your code would look like. I didn't change the table attribute name so that's still present in its likely-buggy form.

        $formName = ...;
@form_out = ...;
$ga = { ... };
$collect_date = ...;
$omp_id = ...;
my @value_names = split ' ', $ga->{ 'outputString'}{ $formName };
chomp @value_names;
my $table_name = "snapshot_$formName";

$dbh->func( "COPY $table_name ( name, value, collect_date, omp_id ) FR
+OM STDIN",
            "putline" );
print "Started a line.\n";

for my $packed_values ( @form_out )
{
    my @values = split /:/, $packed_values;
    my @values_to_use_ix = grep $value_names[ $_ ],
                           0 .. $#value_names;
    for my $ix ( @values_to_use_ix )
    {
        my $value = $values[ $ix ];
        my $value_name = $value_names[ $ix ];

        $value = 'NULL' if not defined $value;
        $value =~ s/^\s+//;
        $value =~ s/\s+$//;
        # The following line will remove any internal tabs which would
+ mess with
        # the column count.
        $value =~ s/\s+/ /g;
        $value = 'NULL' if not length $value;

        my $row = join( "\t",
                        $value_name,
                        $value,
                        $collect_date,
                        $omp_id );

        eval { $dbh->func( $row, 'putline' ) }
            or die "Couldn't write `$row' to $table_name: $@, " . $dbh
+->errstr;
    }
}

        [download]
[reply]
[d/l]
[select]
        Alright this is killling me now. Here is my new code and thank you so much for your help. This thing can't even get passed the first COPY statement without dying, and I can't get any type of error message back other then it failed! 
        
$dbh->func("COPY snapshot_$formName (name, value, collect_date, omp_id
+) from STDIN", "putline"
        or die "Couldn't write COPY STATEMENT to snapshot_$formName: $
+@, " . $dbh->errstr;
    print "Started a line\n";

    foreach my $values (@form_out){
      #my $cmd = `rm -f $form_out`;
      my @values = split(/;/, $values);
      my @names = split(/ /, $ga->{'outputString'}{$formName});
      my $count = 0;
      foreach my $ix (0 .. $#names ){
        my $value = $values[$ix];
        if (!$names[$count]){
         print "$value element # $count does not exist in array\n";
        }
        my $name = $names[$count];
        $value = 'NULL' if not defined $value;
        $value =~ s/^\s+//;
        $value =~ s/\s+$//;
        # The following line will remove any internal tabs which would
+ mess with
        # the column count.
        $value =~ s/\s+/ /g;
        $value = 'Null' if not length $value;
        my $row = join("\t",
                       $name,
                       $value,
                       $date,
                       $ompId);
        $dbh->func($row , 'putline' )
        or die "Couldn't write `$row' to snapshot_$formName: $@, " . $
+dbh->errstr;
         $count++;
      }
    }
    $dbh->func( "\\.\n", 'putline' );
    print "ended that line\n";
  }

        [download]


        Tradez
        "Never underestimate the predictability of stupidity"
        - Bullet Tooth Tony, Snatch (2001)
[reply]
[d/l]

In Section Seekers of Perl Wisdom