Обсуждение: running \copy through perl dbi ?
Hi:
I'm trying to run a PG client side "\copy" command from a perl script. I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
ERROR: syntax error at or near "\"
I can do this with a command line approach, attaching to the DB then run using...
-c "\copy ...". But I was wondering if there is a more elegant way to do this, maybe some sort of legal SQL command that Perl/DBI would swallow ???
Thanks in Advance !
On Fri, Dec 8, 2023 at 8:45 AM David Gauthier <dfgpostgres@gmail.com> wrote:
I'm trying to run a PG client side "\copy" command from a perl script. I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...ERROR: syntax error at or near "\"
More accurately it is the psql application's meta-command. There is no such thing as a generic client side command.
I can do this with a command line approach, attaching to the DB then run using...-c "\copy ...". But I was wondering if there is a more elegant way to do this, maybe some sort of legal SQL command that Perl/DBI would swallow ???
The SQL command is just "COPY". Search for how your driver exposes working with that command/protocol.
David J.
On 12/8/23 07:45, David Gauthier wrote:
> Hi:
>
> I'm trying to run a PG client side "\copy" command from a perl script.
> I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then
> run using...
> -c "\copy ...". But I was wondering if there is a more elegant way to
> do this, maybe some sort of legal SQL command that Perl/DBI would
> swallow ???
Per David Johnston post:
https://metacpan.org/pod/DBD::Pg#COPY-support
>
> Thanks in Advance !
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Dec 8, 2023 at 9:01 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/8/23 07:45, David Gauthier wrote:
> Hi:
>
> I'm trying to run a PG client side "\copy" command from a perl script.
> I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then
> run using...
> -c "\copy ...". But I was wondering if there is a more elegant way to
> do this, maybe some sort of legal SQL command that Perl/DBI would
> swallow ???
Per David Johnston post:
https://metacpan.org/pod/DBD::Pg#COPY-support
Right. Since the copy api isn't really a standard thing in the SQL world you cannot usually use generic database APIs, you need to use driver-specific interfaces.
David J.
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.com> wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
Hi David,
This works for me :
#create file
my $sql = qq {\\copy ( $sub_query ) to '$location' with null as '' delimiter ';' csv header } ;
my $db_name = 'xxxxxx' ;
my @args = ( 'psql', '-c', $sql, $db_name ) ;
system( @args ) == 0 or die "Bad copy: $?" ;
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.com> wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then run
> using...
Duh! I just realized that what I proposed with system() is a command line approach.
As David Johnston mentionned, you can use the SQL COPY command.
However, you need then to deal with permissions so that the server may write the file, so I wonder what approach is
cleaner?
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.com> wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then run
> using...
Duh! I just realized that what I proposed with system() is a command line approach.
As David Johnston mentionned, you can use the SQL COPY command.
However, you need then to deal with permissions so that the server may write the file, so I wonder what approach is the
mostelegant?
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance
--
vv.lists <vv.lists@wanadoo.fr>
On Dec 10, 2023, at 10:41 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:External Email
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.com> wrote:
I'm trying to run a PG client side "\copy" command from a perl script. I
tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
ERROR: syntax error at or near "\"
I can do this with a command line approach, attaching to the DB then run
using...
Duh! I just realized that what I proposed with system() is a command line approach.
As David Johnston mentionned, you can use the SQL COPY command.
One thing to remember with the Perl DBI is that you can use a string variable in the $dbh->do() command.
Perl uses 2 different string variable delimiters:
1) ‘ ‘ , which is exactly what you enter $s= ‘\copy * from foo as json’; will send that to the database without the need for escaping anything (unless you need to enter an actual ‘ in the command, in which case method two is better)
2) “ “ , which allows for declared perl variables to be substituted in the string:$table=‘foo’;$type=‘json’;$cmd=‘\copy’;$s= “$cmd * from $table as $type”;
Concatenation (periods between strings) works as well: $s = ‘\copy ‘.”* from foo as json”;
Then $dbh->do($s); will work in alll three cases.
Been using perl and DBI for (does quick math, ulp!) over 20 years now wrangling a lot of things like this.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
On 12/10/23 10:34, Johnson, Bruce E - (bjohnson) wrote: > >> On Dec 10, 2023, at 10:41 AM, Vincent Veyron <vv.lists@wanadoo.fr >> <mailto:vv.lists@wanadoo.fr>> wrote: >> >> External Email >> >> On Fri, 8 Dec 2023 10:45:28 -0500 >> >> Duh! I just realized that what I proposed with system() is a command >> line approach. >> >> As David Johnston mentionned, you can use the SQL COPY command. > > One thing to remember with the Perl DBI is that you can use a string > variable in the $dbh->do() command. > > Perl uses 2 different string variable delimiters: > > 1) ‘ ‘ , which is exactly what you enter $s= ‘\copy * from foo as > json’; will send that to the database without the need for escaping > anything (unless you need to enter an actual ‘ in the command, in which > case method two is better) The issue is \copy is not a SQl statement, it is a psql(Postgres command line client) meta-command. This means it can only be used in psql. > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs > -- Adrian Klaver adrian.klaver@aklaver.com
\copy in psql just wraps PostgreSQL's COPY FROM STDIN.
if you are trying to do it from your own client program it is trivial to change to that call instead.
On Mon, Dec 11, 2023 at 4:09 PM Vincent Veyron <vincent.veyron@libremen.org> wrote:
On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier <dfgpostgres@gmail.com> wrote:
>
> I'm trying to run a PG client side "\copy" command from a perl script. I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR: syntax error at or near "\"
>
> I can do this with a command line approach, attaching to the DB then run
> using...
Duh! I just realized that what I proposed with system() is a command line approach.
As David Johnston mentionned, you can use the SQL COPY command.
However, you need then to deal with permissions so that the server may write the file, so I wonder what approach is cleaner?
I wouldn't do COPY FROM FILE in that case. I would do COPY FROM STDIN and hten write the data.
Here's the general docs in the DBD::Pg module: https://metacpan.org/pod/DBD::Pg#COPY-support
The general approach is to COPY FROM STDIN and then use pg_putcopydata for each row, and finally pg_putcopyend to close out this. It's not too different from what psql does in the background.
--
Bien à vous, Vincent Veyron
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.