Load file from STDIN

From ClassDBI

Loading from STDIN

If you're going to populate a DB with many rows from a script, doing single insert commands one after another can be very expensive and time-consuming. One way to optimize this is to tell the database server to load directly from an input source like STDIN or a physical file.

This example shows how to use Postgresql's COPY command. There are similar commands in other database servers. MySQL uses the LOAD command.

Pass this routine the table name and a live filehandle like this.

My::Foo->load_sql_file( $table, $filehandle );

   sub load_sql_file {
      my ( $class, $table, $file_handle ) = @_;

      my $dbh = $class->db_Main;
      my $sth = $dbh->prepare( "COPY " . $table .  " FROM STDIN" );
      $sth->execute() or die "Unable to execute query: $! ",$sth->errstr;

      while ( <$file_handle> ) {
          my $ret = $dbh->func( $_, 'putline' );
      }
      $dbh->disconnect;
  }

The application must explicitly send the two characters \. to indicate to the backend that it has finished sending its data. You can either perform another func, $dbh->func( "\\\.", 'putline' ); or make sure the file from which you're reading ends with the \. characters.