Thread: running \copy through perl dbi ?

running \copy through perl dbi ?

From
David Gauthier
Date:
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 !

Re: running \copy through perl dbi ?

From
"David G. Johnston"
Date:
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.

Re: running \copy through perl dbi ?

From
Adrian Klaver
Date:
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




Re: running \copy through perl dbi ?

From
"David G. Johnston"
Date:
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.

Re: running \copy through perl dbi ?

From
Vincent Veyron
Date:
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



Re: running \copy through perl dbi ?

From
Vincent Veyron
Date:
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




Re: running \copy through perl dbi ?

From
Vincent Veyron
Date:
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>



Re: [EXT]Re: running \copy through perl dbi ?

From
"Johnson, Bruce E - (bjohnson)"
Date:

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

Re: [EXT]Re: running \copy through perl dbi ?

From
Adrian Klaver
Date:
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




Re: running \copy through perl dbi ?

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