tradez has asked for the wisdom of the Perl Monks concerning the following question:

Hello fellow monks! Been a while but I am back in the monk game with a new question for my favorite monastery. I am coding a script to take HUGE ammounts of data and place it in a postgres database, more specifically a 4 column table within it, in a simple structure. I have been toying with the "copy" functionality from SQL and I have found it to work incredibly well from the command line, and extremely fast. However, when attempting to do this from a perl script via DBI, i get some strange errors. Please consider the following 

my $copySQL = "copy snapshot_$formName 
(name, value, collect_date, omp_id)
                   from ?"
my $sth = $dbh->prepare($copySQL);
$sth->execute($db_input);

[download]
FYI, $db_input is a variable created in the foreach loop that creates the input file, it is explicit in its file location, and again this exact SQL format works perfectly when logged into psql. The errors I get look like this: 
DBD::Pg::st execute failed: ERROR:  missing data for column "collect_d
+ate" at ./getSnapshot.pl line 180, <FORM_OUT> line 103.
Couldn't execute copy snapshot_cco (name, value, collect_date, omp_id)
+ FROM '/tmp/snapshot_mil-newberlin1_cco.input' statement: 7 at ./getS
+napshot.pl line 180, <FORM_OUT> line 103.

[download]
The file I am inputting looks like this: 
csno,100,20040908,32
alarm1.equip[1]    y    20040908    32
alarm1.equip[10]    y    20040908    32
alarm1.equip[11]    y    20040908    32

[download]
The truly strange thing about this is that it fails on line 102 or 103 everytime, the first 100 or so make it through just fine. So whattyah say brethren, can yah help me out?


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

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

    Don't use prepare/execute. Use $dbh->func( ..., 'putline' ). DBD::Pg documents this. If this staill fails for you then attempt this using the standard psql client. If it still fails then there's something wrong with your data.

    $db_input = "/tmp/snapshot_mil-newberlin1_cco.input";
$formName = ...;
$dbh->func( "COPY snapshot_$formName (name, value, collect_date, omp_i
+d) FROM '$db_input'",
            "putline" );

    [download]
[reply]
[d/l]
[select]
      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")

      [download]
      is what i got, where am i wrong?


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

        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]
[reply]
[d/l]

Back to Seekers of Perl Wisdom