Re: Support for prepared queries

Hi,

Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x ? If
not, when will prepared queries be supported?

Thanks, Stephen

jleelim(at)xxxxxx(dot)com wrote:
> Hi,
>
> Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x ? If
> not, when will prepared queries be supported?

Yes, in the sense that you can send PREPARE and EXECUTE statements to the
server using PHP's pg_query(). They work like any other SQL statements; PHP
doesn't do anything special for them. If you're asking about the
low-level prepared query protocol used by libpq's PQexecPrepared(), that
doesn't exist until PostgreSQL-7.4, so the answer is no.

Stephen wrote:
> Hi,
>
> Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x ? If
> not, when will prepared queries be supported?
>
> Thanks, Stephen
>

You might want to look at PEAR db. I think it comes with php standard
now... so pear.php.net.

-Robby

--
Robby Russell, | Sr. Administrator / Lead Programmer
Command Prompt, Inc. | http://www.commandprompt.com
rrussell(at)commandprompt(dot)com | Telephone: (503) 222.2783

El Mar 21 Oct 2003 02:58, Stephen escribió:
> Hi,
>
> Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x ? If
> not, when will prepared queries be supported?

No it doesn't. I tried to contact the developers of the PGSQL extension, but
had no feed back. :-(

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

El Vie 24 Oct 2003 15:02, Robby Russell escribió:
> Stephen wrote:
> > Hi,
> >
> > Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x ?
If
> > not, when will prepared queries be supported?
> >
> > Thanks, Stephen
> >
>
> You might want to look at PEAR db. I think it comes with php standard
> now... so pear.php.net.

Those are not prepared queries (at least in the sence of prepare -> execute).
For prepared queries you have to know how to talk to libpq, and that's
something that is done from the pgsql ext.

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués | mmarques(at)unl(dot)edu(dot)ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------

On Fri, 24 Oct 2003, Martin Marques wrote:

> El Vie 24 Oct 2003 15:02, Robby Russell escribió:
> > Stephen wrote:
> > > Hi,
> > >
> > > Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x ?
> If
> > > not, when will prepared queries be supported?
> > >
> > > Thanks, Stephen
> > >
> >
> > You might want to look at PEAR db. I think it comes with php standard
> > now... so pear.php.net.
>
> Those are not prepared queries (at least in the sence of prepare -> execute).
> For prepared queries you have to know how to talk to libpq, and that's
> something that is done from the pgsql ext.

This hunk of code works fine on my php 4.3.2 / postgresql 7.3.4 box:

<?php
$conn = pg_connect("dbname=marl8412 user=marl8412");
$a = pg_query($conn,"prepare test (int4) as select * from accounts where
aid= $1");
$res = pg_query($conn,"execute test (45)");
$row = pg_fetch_row($res);
print implode(" ",array_values($row))."<BR>";
?>

so yes, you can use a prepared queries in PHP. But, they won't live
across connections I don't think. Or at least I'm pretty sure you can't
count on them living from one page to the next. but if you're gonna have
a page where you run the same select with different select parameters over
and over it might be a win.

>> Does anyone know if PHP supports prepared queries for PostgreSQL 7.3.x
>> ? If
>> not, when will prepared queries be supported?

Yes they are. Read the docs regarding the 'PREPARE' and 'EXECUTE'
commands in the Reference Manual. There is not special PHP support
required for this.

Chris

Any idea how the prepared query can be made to save across PHP invocations?
How about using pg_pconnect (persistent connection), will it stay prepared
when PHP comes again and reuse the connection?

Stephen

""scott.marlowe"" <scott(dot)marlowe(at)ihs(dot)com> wrote in message
news:Pine(dot)LNX(dot)4(dot)33(dot)0310241623570(dot)26036-100000(at)css120(dot)ihs(dot)com(dot)(dot)(dot)
> On Fri, 24 Oct 2003, Martin Marques wrote:
>
> > El Vie 24 Oct 2003 15:02, Robby Russell escribi:
> > > Stephen wrote:
> > > > Hi,
> > > >
> > > > Does anyone know if PHP supports prepared queries for PostgreSQL
7.3.x ?
> > If
> > > > not, when will prepared queries be supported?
> > > >
> > > > Thanks, Stephen
> > > >
> > >
> > > You might want to look at PEAR db. I think it comes with php standard
> > > now... so pear.php.net.
> >
> > Those are not prepared queries (at least in the sence of prepare ->
execute).
> > For prepared queries you have to know how to talk to libpq, and that's
> > something that is done from the pgsql ext.
>
> This hunk of code works fine on my php 4.3.2 / postgresql 7.3.4 box:
>
> <?php
> $conn = pg_connect("dbname=marl8412 user=marl8412");
> $a = pg_query($conn,"prepare test (int4) as select * from accounts where
> aid= $1");
> $res = pg_query($conn,"execute test (45)");
> $row = pg_fetch_row($res);
> print implode(" ",array_values($row))."<BR>";
> ?>
>
> so yes, you can use a prepared queries in PHP. But, they won't live
> across connections I don't think. Or at least I'm pretty sure you can't
> count on them living from one page to the next. but if you're gonna have
> a page where you run the same select with different select parameters over
> and over it might be a win.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

jleelim(at)xxxxxx(dot)com wrote:
> Any idea how the prepared query can be made to save across PHP invocations?
> How about using pg_pconnect (persistent connection), will it stay prepared
> when PHP comes again and reuse the connection?

Maybe use a procedure-language function instead of a prepared query?
Persistent connections won't help; the prepared query may still be valid
but only for that web-server child process.

No, in fact in PHP5 php now purposely resets the connection between pages.

You'd have to write some kind of pooling solution to get that kind of
behaviour. I don't know of anyone working on pooling in PHP.

On Fri, 31 Oct 2003, Stephen wrote:

> Any idea how the prepared query can be made to save across PHP invocations?
> How about using pg_pconnect (persistent connection), will it stay prepared
> when PHP comes again and reuse the connection?
>
> Stephen
>
>
> ""scott.marlowe"" <scott(dot)marlowe(at)ihs(dot)com> wrote in message
> news:Pine(dot)LNX(dot)4(dot)33(dot)0310241623570(dot)26036-100000(at)css120(dot)ihs(dot)com(dot)(dot)(dot)
> > On Fri, 24 Oct 2003, Martin Marques wrote:
> >
> > > El Vie 24 Oct 2003 15:02, Robby Russell escribió:
> > > > Stephen wrote:
> > > > > Hi,
> > > > >
> > > > > Does anyone know if PHP supports prepared queries for PostgreSQL
> 7.3.x ?
> > > If
> > > > > not, when will prepared queries be supported?
> > > > >
> > > > > Thanks, Stephen
> > > > >
> > > >
> > > > You might want to look at PEAR db. I think it comes with php standard
> > > > now... so pear.php.net.
> > >
> > > Those are not prepared queries (at least in the sence of prepare ->
> execute).
> > > For prepared queries you have to know how to talk to libpq, and that's
> > > something that is done from the pgsql ext.
> >
> > This hunk of code works fine on my php 4.3.2 / postgresql 7.3.4 box:
> >
> > <?php
> > $conn = pg_connect("dbname=marl8412 user=marl8412");
> > $a = pg_query($conn,"prepare test (int4) as select * from accounts where
> > aid= $1");
> > $res = pg_query($conn,"execute test (45)");
> > $row = pg_fetch_row($res);
> > print implode(" ",array_values($row))."<BR>";
> > ?>
> >
> > so yes, you can use a prepared queries in PHP. But, they won't live
> > across connections I don't think. Or at least I'm pretty sure you can't
> > count on them living from one page to the next. but if you're gonna have
> > a page where you run the same select with different select parameters over
> > and over it might be a win.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>

Hey, I found this on sourceforge:

http://sqlrelay.sourceforge.net/

No clue how well it works, but I might give it a try.

On Tue, 4 Nov 2003, scott.marlowe wrote:

> No, in fact in PHP5 php now purposely resets the connection between pages.
>
> You'd have to write some kind of pooling solution to get that kind of
> behaviour. I don't know of anyone working on pooling in PHP.
>
> On Fri, 31 Oct 2003, Stephen wrote:
>
> > Any idea how the prepared query can be made to save across PHP invocations?
> > How about using pg_pconnect (persistent connection), will it stay prepared
> > when PHP comes again and reuse the connection?
> >
> > Stephen
> >
> >
> > ""scott.marlowe"" <scott(dot)marlowe(at)ihs(dot)com> wrote in message
> > news:Pine(dot)LNX(dot)4(dot)33(dot)0310241623570(dot)26036-100000(at)css120(dot)ihs(dot)com(dot)(dot)(dot)
> > > On Fri, 24 Oct 2003, Martin Marques wrote:
> > >
> > > > El Vie 24 Oct 2003 15:02, Robby Russell escribió:
> > > > > Stephen wrote:
> > > > > > Hi,
> > > > > >
> > > > > > Does anyone know if PHP supports prepared queries for PostgreSQL
> > 7.3.x ?
> > > > If
> > > > > > not, when will prepared queries be supported?
> > > > > >
> > > > > > Thanks, Stephen
> > > > > >
> > > > >
> > > > > You might want to look at PEAR db. I think it comes with php standard
> > > > > now... so pear.php.net.
> > > >
> > > > Those are not prepared queries (at least in the sence of prepare ->
> > execute).
> > > > For prepared queries you have to know how to talk to libpq, and that's
> > > > something that is done from the pgsql ext.
> > >
> > > This hunk of code works fine on my php 4.3.2 / postgresql 7.3.4 box:
> > >
> > > <?php
> > > $conn = pg_connect("dbname=marl8412 user=marl8412");
> > > $a = pg_query($conn,"prepare test (int4) as select * from accounts where
> > > aid= $1");
> > > $res = pg_query($conn,"execute test (45)");
> > > $row = pg_fetch_row($res);
> > > print implode(" ",array_values($row))."<BR>";
> > > ?>
> > >
> > > so yes, you can use a prepared queries in PHP. But, they won't live
> > > across connections I don't think. Or at least I'm pretty sure you can't
> > > count on them living from one page to the next. but if you're gonna have
> > > a page where you run the same select with different select parameters over
> > > and over it might be a win.
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an index scan if your
> > > joining column's datatypes do not match
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>

scott.marlowe wrote:
> No, in fact in PHP5 php now purposely resets the connection between pages.
>
> You'd have to write some kind of pooling solution to get that kind of
> behaviour. I don't know of anyone working on pooling in PHP.

Thought PHP5 does RESET ALL, that doesn't affect prepared queries, only
SET variables.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

>>You'd have to write some kind of pooling solution to get that kind of
>>behaviour. I don't know of anyone working on pooling in PHP.
>
>
> Thought PHP5 does RESET ALL, that doesn't affect prepared queries, only
> SET variables.

I vote for a new command:

RESET SESSION;

Basically, we make that connection reset everythign - SET variables,
open cursors, etc. I don't think we want to clobber prepared
queries...i dunno.

And we can add stuff to it without needing pool managers to change how
they do things.

Chris

On Thu, 6 Nov 2003, Bruce Momjian wrote:

> scott.marlowe wrote:
> > No, in fact in PHP5 php now purposely resets the connection between pages.
> >
> > You'd have to write some kind of pooling solution to get that kind of
> > behaviour. I don't know of anyone working on pooling in PHP.
>
> Thought PHP5 does RESET ALL, that doesn't affect prepared queries, only
> SET variables.

But since your not guaranteeds your original connection in PHP, might
there still be some issues?

Should this be a TODO item?

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

Christopher Kings-Lynne wrote:
>
> >>You'd have to write some kind of pooling solution to get that kind of
> >>behaviour. I don't know of anyone working on pooling in PHP.
> >
> >
> > Thought PHP5 does RESET ALL, that doesn't affect prepared queries, only
> > SET variables.
>
> I vote for a new command:
>
> RESET SESSION;
>
> Basically, we make that connection reset everythign - SET variables,
> open cursors, etc. I don't think we want to clobber prepared
> queries...i dunno.
>
> And we can add stuff to it without needing pool managers to change how
> they do things.
>
> Chris
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073