Run external SQL file via Perl dbh

On Sat, Jun 9, 2012 at 3:29 PM, David Williams
<mobiusinversion@gmail.com> wrote:

Hi There,

I'd like to be able to run the contents of an external SQL file from Perl.  Something akin to:

   $dbh->do( '\i /home/david/run_me.sql' );

However this fails, and I assume that is because the \i is a client command. Is there a way to run the contents of an external SQL from a Per database handle?

What we do in LedgerSMB is something like:

system("psql -f /home/david/run_me.sql > /tmp/dblog 2>&1") after
setting appropriate environment variables.

But if you want to do this server-side there is always pl/perlU....

Best Wishes,
Chris Travers

On 10/06/12 08:29, David Williams wrote:

Hi There,

I'd like to be able to run the contents of an external SQL file from Perl. Something akin to:

$dbh->do( '\i /home/david/run_me.sql' );

However this fails, and I assume that is because the \i is a client command. Is there a way to run the contents of an external SQL from a Per database handle?

How about something like

use File::Slurp;
use Try::Tiny;

try {
$dbh->being_work;
$dbh->do($_) for split(/;/, read_file('run_me.sql'))
$dbh->commit;
}
catch {
say "Failed SQL: $_";
$dbh->rollback;
};

That will break if you have any semi-colons inside quoted strings, but
you could improve the splitting to cope with that..

Toby --

A small point:

<...>

How about something like

use File::Slurp;
use Try::Tiny;

try {
  $dbh->being_work;

$dbh-begin_work;

<...>

Clarification for any who might be unfamiliar with the perl tools.

Greg Williamson

On Mon, Jun 11, 2012 at 7:37 PM, Greg Williamson
<gwilliamson39@yahoo.com> wrote:

Toby --

A small point:

<...>

As a brief note, the semicolon splitting issue was why we went with
invoking psql via system(). We found that was vastly simpler than
trying to handle all corner cases ourselves.

Best Wishes,
Chris Travers