PostgreSQL Stored Procedures & PEAR::DB

[newms]

I'm trying to execute a PostgreSQL stored procedure using the PEAR:😃B interface to keep my db connections abstract. Here's a snipped of my code: (my sp name is "get_last_name", it takes no params and returns a recordset consisting of a single column "last_name")

---

require_once("DB.php");

$dbh = DB::connect("pgsql://$dbuser:$dbpassword@$dbhost/$dbname");

$dbh->setFetchMode(DB_FETCHMODE_ASSOC);

$strSqlText = "EXEC get_last_name";

$result = $dbh->query($strSqlText);

while ($row = $result->fetchRow()) {
print $row["last_name"] . "<br>";
}

---

When I run it, I get "Call to undefined function: fetchrow()..."

I have also tried $dbh->execute($strSqlText) and a sql statement of "BEGIN get_last_name" and neither of those worked.

I swapped the sql statement with "SELECT * from tbl_customers" and the code works perfectly, so I know it's probably not a simple typo to blame.

To prevent flames of RTM and RTFM I have checked the PostrgreSQL site, the PHP manual, the PEAR docs, etc. but I can't find anything that points me in the right direction. If I'm a total retard and I'm missing something obvious, I'll happily take the verbal abuse just to get this thing working! :-)

[Anon]

Hrm. There is no EXEC command in postgres!

Try this instead:

$strSqlText = "SELECT get_last_name()";

Trying BEGIN get_last_name is totally off base. BEGIN is for starting a transaction, not calling a function.

I seriously suggest that you test your procedures from within psql before trying to use them from PHP.

The other thing that is going to bite you is that stored procedures in Postgres cannot yet return recordsets. The best you can do is return an open cursor. Bad, I know, but we're working on it... You might just have to use the SELECT * FROM ... directly.

Chris

[newms]

Yes, I admit I was shooting around in the
dark trying to make this work.

Thank you, Chris - I really appreciate your help.