Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re^2: DBD::Pg copy issues

by tradez (Pilgrim)
on Sep 10, 2004 at 17:50 UTC ( #390144=note: print w/replies, xml ) Need Help??

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

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?

"Never underestimate the predictability of stupidity"
- Bullet Tooth Tony, Snatch (2001)

Replies are listed 'Best First'.
Re^3: DBD::Pg copy issues
by diotalevi (Canon) on Sep 10, 2004 at 18:14 UTC

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

      "Never underestimate the predictability of stupidity"
      - Bullet Tooth Tony, Snatch (2001)
        $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; } }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://390144]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2017-05-29 01:36 GMT
Find Nodes?
    Voting Booth?