Postgres Pro
Downloads
Home   >   mailing lists

Thread: Run external SQL file via Perl dbh

Run external SQL file via Perl dbh

From
David Williams
Date:
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
externalSQL from a Per database handle? 

Best regards
David

Re: Run external SQL file via Perl dbh

From
Chris Travers
Date:
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
externalSQL 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

Re: Run external SQL file via Perl dbh

From
Toby Corkindale
Date:
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
externalSQL 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..

Re: Run external SQL file via Perl dbh

From
Greg Williamson
Date:
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

Re: Run external SQL file via Perl dbh

From
Chris Travers
Date:
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