Thread: Support for prepared queries

Support for prepared queries

From
"Stephen"
Date:
Hi,

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

Thanks, Stephen



Re: Support for prepared queries

From
Robby Russell
Date:
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@commandprompt.com | Telephone: (503) 222.2783


Re: Support for prepared queries

From
Martin Marques
Date:
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@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telemática
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------


Re: Support for prepared queries

From
Martin Marques
Date:
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@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telemática
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------


Re: Support for prepared queries

From
"scott.marlowe"
Date:
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.


Re: Support for prepared queries

From
Christopher Kings-Lynne
Date:
>> 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


Re: Support for prepared queries

From
ljb
Date:
jleelim@xxxxxx.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.

Re: Support for prepared queries

From
"Stephen"
Date:
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.marlowe@ihs.com> wrote in message
news:Pine.LNX.4.33.0310241623570.26036-100000@css120.ihs.com...
> 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
>



Re: Support for prepared queries

From
"scott.marlowe"
Date:
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.marlowe@ihs.com> wrote in message
> news:Pine.LNX.4.33.0310241623570.26036-100000@css120.ihs.com...
> > 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
>
>


Re: Support for prepared queries

From
"scott.marlowe"
Date:
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.marlowe@ihs.com> wrote in message
> > news:Pine.LNX.4.33.0310241623570.26036-100000@css120.ihs.com...
> > > 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
>
>


Re: Support for prepared queries

From
Bruce Momjian
Date:
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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Support for prepared queries

From
Christopher Kings-Lynne
Date:
>>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





Re: Support for prepared queries

From
"scott.marlowe"
Date:
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?


Re: Support for prepared queries

From
Bruce Momjian
Date:
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@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

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