Thread: Run external SQL file via Perl dbh
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
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
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..
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