Re^2: DBD::Pg copy issues

[tradez]

Ok, but this still fails. Also, i notice in the documentation that "\." must be sent, how does one go about sending that?

print INPUTFILE "\.";
    my $copySQL = "copy snapshot_$formName (name, value, collect_date,
+ omp_id)
                   from '$db_input'";
    $dbh->func("COPY snapshot_$formName (name, value, collect_date, om
+p_id) FROM '$db_input'",
                "putline")

is what i got, where am i wrong?

[diotalevi]

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' );

[tradez]

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";

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.

[diotalevi]

$dbh->func( 'COPY snapshot_$formName ( name, value, collect_date, omp_
+id ) FROM STDIN',
            'putline' );

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++;
   }
   

   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 ] ) {
       }
   }
   

   ---

   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;
       }
   }