pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Support for prepared queries
15+ messages / 7 participants
[nested] [flat]

* Support for prepared queries
@ 2003-10-21 05:58  Stephen <jleelim@xxxxxx.com>
  0 siblings, 3 replies; 15+ messages in thread

From: Stephen @ 2003-10-21 05:58 UTC (permalink / raw)
  To: pgsql-php

Hi,

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

Thanks, Stephen





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-22 00:58  ljb <ljb220@mindspring.com>
  parent: Stephen <jleelim@xxxxxx.com>
  2 siblings, 0 replies; 15+ messages in thread

From: ljb @ 2003-10-22 00:58 UTC (permalink / raw)
  To: pgsql-php

jleelim@xxxxxx.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.



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-24 18:02  Robby Russell <rrussell@commandprompt.com>
  parent: Stephen <jleelim@xxxxxx.com>
  2 siblings, 2 replies; 15+ messages in thread

From: Robby Russell @ 2003-10-24 18:02 UTC (permalink / raw)
  To: Stephen <jleelim@xxxxxx.com>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-24 21:47  Martin Marques <martin@bugs.unl.edu.ar>
  parent: Stephen <jleelim@xxxxxx.com>
  2 siblings, 0 replies; 15+ messages in thread

From: Martin Marques @ 2003-10-24 21:47 UTC (permalink / raw)
  To: Stephen <jleelim@xxxxxx.com>; pgsql-php

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
-----------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-24 21:50  Martin Marques <martin@bugs.unl.edu.ar>
  parent: Robby Russell <rrussell@commandprompt.com>
  1 sibling, 1 reply; 15+ messages in thread

From: Martin Marques @ 2003-10-24 21:50 UTC (permalink / raw)
  To: Robby Russell <rrussell@commandprompt.com>; Stephen <jleelim@xxxxxx.com>; +Cc: pgsql-php

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
-----------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-24 22:30  scott.marlowe <scott.marlowe@ihs.com>
  parent: Martin Marques <martin@bugs.unl.edu.ar>
  0 siblings, 1 reply; 15+ messages in thread

From: scott.marlowe @ 2003-10-24 22:30 UTC (permalink / raw)
  To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: Robby Russell <rrussell@commandprompt.com>; Stephen <jleelim@xxxxxx.com>; pgsql-php

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.




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-26 08:20  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Robby Russell <rrussell@commandprompt.com>
  1 sibling, 0 replies; 15+ messages in thread

From: Christopher Kings-Lynne @ 2003-10-26 08:20 UTC (permalink / raw)
  To: Robby Russell <rrussell@commandprompt.com>; +Cc: Stephen <jleelim@xxxxxx.com>; pgsql-php


>> 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




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-10-31 16:35  Stephen <jleelim@xxxxxx.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 15+ messages in thread

From: Stephen @ 2003-10-31 16:35 UTC (permalink / raw)
  To: pgsql-php

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
>





^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-02 01:30  ljb <ljb220@mindspring.com>
  parent: Stephen <jleelim@xxxxxx.com>
  1 sibling, 0 replies; 15+ messages in thread

From: ljb @ 2003-11-02 01:30 UTC (permalink / raw)
  To: pgsql-php

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.



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-04 21:22  scott.marlowe <scott.marlowe@ihs.com>
  parent: Stephen <jleelim@xxxxxx.com>
  1 sibling, 2 replies; 15+ messages in thread

From: scott.marlowe @ 2003-11-04 21:22 UTC (permalink / raw)
  To: Stephen <jleelim@xxxxxx.com>; +Cc: pgsql-php

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
> 
> 




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-05 15:38  scott.marlowe <scott.marlowe@ihs.com>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 15+ messages in thread

From: scott.marlowe @ 2003-11-05 15:38 UTC (permalink / raw)
  To: Stephen <jleelim@xxxxxx.com>; +Cc: pgsql-php

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
> 
> 




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-06 22:53  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 2 replies; 15+ messages in thread

From: Bruce Momjian @ 2003-11-06 22:53 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Stephen <jleelim@xxxxxx.com>; pgsql-php

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



^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-07 02:31  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 1 reply; 15+ messages in thread

From: Christopher Kings-Lynne @ 2003-11-07 02:31 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; Stephen <jleelim@xxxxxx.com>; pgsql-php


>>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







^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-07 16:44  scott.marlowe <scott.marlowe@ihs.com>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 15+ messages in thread

From: scott.marlowe @ 2003-11-07 16:44 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Stephen <jleelim@xxxxxx.com>; pgsql-php

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?




^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Support for prepared queries
@ 2003-11-12 03:52  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 15+ messages in thread

From: Bruce Momjian @ 2003-11-12 03:52 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; Stephen <jleelim@xxxxxx.com>; pgsql-php


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



^ permalink  raw  reply  [nested|flat] 15+ messages in thread


end of thread, other threads:[~2003-11-12 03:52 UTC | newest]

Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-21 05:58 Support for prepared queries Stephen <jleelim@xxxxxx.com>
2003-10-22 00:58 ` ljb <ljb220@mindspring.com>
2003-10-24 18:02 ` Robby Russell <rrussell@commandprompt.com>
2003-10-24 21:50   ` Martin Marques <martin@bugs.unl.edu.ar>
2003-10-24 22:30     ` scott.marlowe <scott.marlowe@ihs.com>
2003-10-31 16:35       ` Stephen <jleelim@xxxxxx.com>
2003-11-02 01:30         ` ljb <ljb220@mindspring.com>
2003-11-04 21:22         ` scott.marlowe <scott.marlowe@ihs.com>
2003-11-05 15:38           ` scott.marlowe <scott.marlowe@ihs.com>
2003-11-06 22:53           ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-11-07 02:31             ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-11-12 03:52               ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-11-07 16:44             ` scott.marlowe <scott.marlowe@ihs.com>
2003-10-26 08:20   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-24 21:47 ` Martin Marques <martin@bugs.unl.edu.ar>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox