Re: where is the output

hi all,

Not sure it's the right group, but I've spent the afternoon googling and
trying on this.

In PHP (Apache Module)

I try pg_exec("COPY blah TO STDOUT WITH blah");
It runs for ever...

How can I get the output of COPY in PHP?

Copy_from is not an option because the goal is to get a CSV file.
Writing and re-reading a file doesn't seem to be an option either because
of safe_mode.

Any php gurus out there?

Regards,

--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp(at)pyrenet(dot)fr wrote:
> In PHP (Apache Module)
>
> I try pg_exec("COPY blah TO STDOUT WITH blah");
> It runs for ever...
>
> How can I get the output of COPY in PHP?

You need to use the API functions for copy. In C they are:

PQgetCopyData
PQputCopyData
PQputCopyEnd

I'm sure PHP has these functions, or perhaps the older versions:

PQgetline
PQputline
PQendcopy

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp(at)pyrenet(dot)fr wrote:
> Not sure it's the right group, but I've spent the afternoon googling and
> trying on this.
>
> In PHP (Apache Module)

pgsql-php might be more appropriate, or possibly a PHP mailing list.

> I try pg_exec("COPY blah TO STDOUT WITH blah");
> It runs for ever...
>
> How can I get the output of COPY in PHP?

At the libpq layer you'd use PQgetCopyData(); the only place I see
a call to that function in the PHP 5.1.1 source code is in the
pg_copy_to() function, which is for copying a table into an array.

> Copy_from is not an option because the goal is to get a CSV file.
> Writing and re-reading a file doesn't seem to be an option either because
> of safe_mode.

Could you use pg_copy_to() to read the table into an array and write
each row of the array as CSV? I'm not sure if the following is the
best way or even entirely correct, but it appears to work in PHP 5.1.1,
even with safe_mode enabled:

$rows = pg_copy_to($conn, "tablename");
$fh = fopen("php://output", "w");
foreach ($rows as $row) {
fputcsv($fh, explode("\t", rtrim($row)));
}
fclose($fh);

According to the PHP documentation fputcsv() is new as of 5.1.0RC1;
for earlier versions you could probably find a module to generate
CSV output.

--
Michael Fuhr

On Wed, 21 Dec 2005, Michael Fuhr wrote:

> Date: Wed, 21 Dec 2005 13:23:24 -0700
> From: Michael Fuhr <mike(at)fuhr(dot)org>
> To: ohp(at)pyrenet(dot)fr
> Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
> Subject: Re: [HACKERS] where is the output
>
> On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp(at)pyrenet(dot)fr wrote:
> > Not sure it's the right group, but I've spent the afternoon googling and
> > trying on this.
> >
> > In PHP (Apache Module)
>
> pgsql-php might be more appropriate, or possibly a PHP mailing list.
Sure!
But I'd still like to know in that case (the same goes for C I guess)
where stdout is.
Why isn't it connected by default to the input of whatever connected by
dbconnect?
>
> > I try pg_exec("COPY blah TO STDOUT WITH blah");
> > It runs for ever...
> >
> > How can I get the output of COPY in PHP?
>
> At the libpq layer you'd use PQgetCopyData(); the only place I see
> a call to that function in the PHP 5.1.1 source code is in the
> pg_copy_to() function, which is for copying a table into an array.
>
> > Copy_from is not an option because the goal is to get a CSV file.
> > Writing and re-reading a file doesn't seem to be an option either because
> > of safe_mode.
>
> Could you use pg_copy_to() to read the table into an array and write
> each row of the array as CSV? I'm not sure if the following is the
> best way or even entirely correct, but it appears to work in PHP 5.1.1,
> even with safe_mode enabled:
>
> $rows = pg_copy_to($conn, "tablename");
> $fh = fopen("php://output", "w");
> foreach ($rows as $row) {
> fputcsv($fh, explode("\t", rtrim($row)));
> }
> fclose($fh);
>
> According to the PHP documentation fputcsv() is new as of 5.1.0RC1;
> for earlier versions you could probably find a module to generate
> CSV output.
>
>
Thanks for the tip. I found a function that does it!
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: ohp(at)pyrenet(dot)fr
------------------------------------------------------------------------------
Make your life a dream, make your dream a reality. (St Exupery)

On Fri, Dec 23, 2005 at 02:19:09PM +0100, ohp(at)pyrenet(dot)fr wrote:
> But I'd still like to know in that case (the same goes for C I guess)
> where stdout is.
> Why isn't it connected by default to the input of whatever connected by
> dbconnect?

From the COPY documentation:

When STDIN or STDOUT is specified, data is transmitted via the
connection between the client and the server.

I think the names STDIN and STDOUT are causing the confusion: they're
just the server's names for the client/server connection and don't
have anything to do with the client's stdin and stdout. The client
writes data to and reads data from the connection using the API
functions for doing so; where the client gets or puts that data is
up to the application.

--
Michael Fuhr