Thread: TR: Re: TR: interface PERL and return results

TR: Re: TR: interface PERL and return results

From
"victor3.lopes@voila.fr"
Date:
Hello, anyone have a response of  my problem ?

---------- Entête Initiale  -----------

De    : "victor3.lopes@voila.fr" <victor3.lopes@voila.fr>
A     : "nandrews" <nandrews@investsystems.co.uk>
Copie : "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
Date  : Wed,  9 Oct 2002 13:38:40 +0200
Objet : Re: [INTERFACES] TR: interface PERL and return results

Bonjour (you speak french ? ;-) )

Thanks  for your response,
Indeed I confused two commands copy (libpq) and \copy (psql).

I have try two methods for charge my result.res in my table.

the first  method :

$res = $db->exec(" COPY temp FROM "full path\result.res ");

Unfortunately I did not succeed in using this method, I had always an
error ( While placing postgres in debug mode ).

DEBUG:  StartTransactionCommand
DEBUG:  query: COPY temp FROM '-- my path -- /result.res'
DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 :into <>
:isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
<> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()}
DEBUG:  ProcessUtility: COPY temp FROM '-- my path -- /result.res'
ERROR:  You must have Postgres superuser privilege to do a COPY directly to or from a
file.  Anyone can COPY to stdout or from stdin.  Psql's \copy command also works for
anyone.
....

I don't understand why this error because the superuser postgres (who had create for
launch the server) have the privilege
for access and read this file.

The second method :

$res = $db->exec('COPY temp FROM stdin');

open (FIC,'<result.res');
while (<FIC>) {
        $db->putline($_);
        }
$db->putline("\\.\n");
close(FIC);
print $res->resultStatus;

This method works with success, but i have some questions.

- print $res->resultStatus; print PGRES_COPY_IN
But i don't kown if  the action of copy achieve its goal or no. I post this question
because i has tested this case
after insert an row error on file and the response had the same.

Documentation indicates that one would have to be inserted a command $res =
$db->endcopy for synchronize the copy
instruction.  But  when i use this instruction at end of my code., the prog perl blocks
itself  and if i kill, the operation of copy
fails on postgres.
If  i omit this instruction the process works fine.  Must I use or not this instruction ?


Victor


>
>
> Bonjour,
>
> On Mon, 7 Oct 2002, [utf-8] victor3.lopes@voila.fr wrote:
>
> > Hello no one has a solution for my problem :-) ?
> >
> >
> > ---------- Entête Initiale  -----------
> >
> > De    : "victor3.lopes@voila.fr" <victor3.lopes@voila.fr>
> > A     : "pgsql-interfaces" <pgsql-interfaces@postgresql.org>
> > Copie :
> > Date  : Sat,  5 Oct 2002 23:31:40 +0200
> > Objet : interface PERL and return results
> >
> > Hello,
> > I use Perl Pg interface to join my postgresql database.
> >
> > I wan't  send the below query in perl :
> >
> > $res = $db->exec(' create table temp (....); \copy temp from result.res');
>
> The \copy is a psql command. You need to use just the word 'copy' without the
> leading '\'.
>
> Using:
>
>  COPY temp FROM /path/to/result.res
>
> will try and read result.res from a file on the server system. To load a file
> on your client system you should use COPY temp FROM STDIN and read the Pg
> manpage.
>
> Documentation:
>
> http://developer.postgresql.org/docs/postgres/sql-copy.html
>
> and
>
> man Pg
>
> search for 'copy' in the second of these to get a good starting point on the
> subject.
>
>
> --
> Nigel J. Andrews
> Director
>
> ---
> Logictree Systems Limited
> Computer Consultants
>
>
>
------------------------------------------

Faites un voeu et puis Voila ! www.voila.fr

------------------------------------------

Faites un voeu et puis Voila ! www.voila.fr



Re: TR: Re: TR: interface PERL and return results

From
rmunn@pobox.com
Date:
On Fri, Oct 11, 2002 at 12:29:40AM +0200, victor3.lopes@voila.fr wrote:
> Hello, anyone have a response of  my problem ?

Maybe. See below...
>
> I have try two methods for charge my result.res in my table.
>
> the first  method :
>
> $res = $db->exec(" COPY temp FROM "full path\result.res ");
>
> Unfortunately I did not succeed in using this method, I had always an
> error ( While placing postgres in debug mode ).
>
> DEBUG:  StartTransactionCommand
> DEBUG:  query: COPY temp FROM '-- my path -- /result.res'
> DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 :into <>
> :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
> <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
> :resultRelations ()}
> DEBUG:  ProcessUtility: COPY temp FROM '-- my path -- /result.res'
> ERROR:  You must have Postgres superuser privilege to do a COPY directly to or from a
> file.  Anyone can COPY to stdout or from stdin.  Psql's \copy command also works for
> anyone.
> ....
>
> I don't understand why this error because the superuser postgres (who had create for
> launch the server) have the privilege
> for access and read this file.

In your Perl script, what user and password did you use to connect to
the database? You need to *connect* as the Postgres superuser (which is
almost always the user named 'postgres').

If you are connected as user postgres then your COPY command should
work.

--
Robin Munn
rmunn@pobox.com

Re: TR: Re: TR: interface PERL and return results

From
Jean-Luc Lachance
Date:
Two things:

The file MUST be on the server where PostgreSQL is running.
The file MUST be accessible by the user postgres.



rmunn@pobox.com wrote:
> 
> On Fri, Oct 11, 2002 at 12:29:40AM +0200, victor3.lopes@voila.fr wrote:
> > Hello, anyone have a response of  my problem ?
> 
> Maybe. See below...
> >
> > I have try two methods for charge my result.res in my table.
> >
> > the first  method :
> >
> > $res = $db->exec(" COPY temp FROM "full path\result.res ");
> >
> > Unfortunately I did not succeed in using this method, I had always an
> > error ( While placing postgres in debug mode ).
> >
> > DEBUG:  StartTransactionCommand
> > DEBUG:  query: COPY temp FROM '-- my path -- /result.res'
> > DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation 0 :into <>
> > :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks false :rtable
> > <> :jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
> > :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
> > :resultRelations ()}
> > DEBUG:  ProcessUtility: COPY temp FROM '-- my path -- /result.res'
> > ERROR:  You must have Postgres superuser privilege to do a COPY directly to or from a
> > file.  Anyone can COPY to stdout or from stdin.  Psql's \copy command also works for
> > anyone.
> > ....
> >
> > I don't understand why this error because the superuser postgres (who had create for
> > launch the server) have the privilege
> > for access and read this file.
> 
> In your Perl script, what user and password did you use to connect to
> the database? You need to *connect* as the Postgres superuser (which is
> almost always the user named 'postgres').
> 
> If you are connected as user postgres then your COPY command should
> work.
> 
> --
> Robin Munn
> rmunn@pobox.com
> 
>   ------------------------------------------------------------------------
>    Part 1.2Type: application/pgp-signature