Cursors and PHP - Mailing list pgsql-general

From Steve Manes
Subject Cursors and PHP
Date
Msg-id 40814ADC.6030503@magpie.com
Whole thread Raw
Responses Re: Cursors and PHP
Re: Cursors and PHP
List pgsql-general
I found very little information on the web about how to return a cursor
from plpgsql to PHP using PEAR::DB.  Here's what I managed to tack
together.  It works but I want to make sure I'm not overlooking the
obvious or doing something boneheaded here, like setting myself up for a
database crash under load.  Here's an example:

========================
PLPGSQL:

CREATE OR REPLACE FUNCTION get_user_data(REFCURSOR, INTEGER)
  RETURNS REFCURSOR AS '

DECLARE
     v_cursor    ALIAS FOR $1;
     v_user_id   ALIAS FOR $2;

BEGIN

     OPEN v_cursor FOR
       SELECT *
       FROM users
       WHERE user_id = v_user_id;

     RETURN v_cursor;

END;

' LANGUAGE 'plpgsql';

==========================
PHP:

     $cursor = 'my_user';

     $query = "SELECT get_user_data('$cursor', $user_id);FETCH ALL IN
$cursor;";

     $res =& $dbh->query($query);

     if (DB::isError($res)) {
      ... process the error
     }

     $row = $res->fetchRow(DB_FETCHMODE_ASSOC);

     $dbh->query("CLOSE $cursor;");

     $res->free();

     return $row;
============================

Question #1: Is this the way to do it?

Question #2: I'm using a persistent database connection.  To avoid
leaving an open cursor laying around I make another call to close the
cursor after the result set is fetched.  Do I need to do this or will
the cursor be closed implicitly by Apache/PHP after the page is delivered?



pgsql-general by date:

Previous
From: Jan Wieck
Date:
Subject: Re: [HACKERS] Remove MySQL Tools from Source?
Next
From: Bruno Wolff III
Date:
Subject: Re: build contrib