Load file from STDIN

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  command. There are similar commands in other database servers. MySQL uses the  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,  or make sure the file from which you're reading ends with the   characters.