From ef0de01b62abcaaa72d85c64d27adcbc5945e157 Mon Sep 17 00:00:00 2001
From: Wez Furlong <wez@php.net>
Date: Thu, 13 Jan 2005 01:59:39 +0000
Subject: Take a blind stab at implementing scrollable cursors for pgsql. We
 allocate a unique cursor name for each statement, so that we don't interfere
 with other open statement handles on the same dbh.

Note, however, that we force a new transaction for each open scrollable cursor
(postgres requires cursors to be used inside a transaction).  This is okay,
except for the case where a scrollable cursor is opened, an update is made and
the cursor is closed; closing the cursor commits the transaction that was begun
when it was opened.

It might well be better to avoid the transaction in PDO and force the user to
be aware of the requirements of cursors and explicitly initiate the transaction
themselves.

This is all untested code; it compiles and looks like it will work, but I
encourage someone with a real postgres setup to actually sit down and try to
use it.
---
 ext/pdo_pgsql/pgsql_statement.c | 60 ++++++++++++++++++++++++++++++++++++-----
 1 file changed, 54 insertions(+), 6 deletions(-)

(limited to 'ext/pdo_pgsql/pgsql_statement.c')

diff --git a/ext/pdo_pgsql/pgsql_statement.c b/ext/pdo_pgsql/pgsql_statement.c
index 2220cd986c..ce20503f9c 100644
--- a/ext/pdo_pgsql/pgsql_statement.c
+++ b/ext/pdo_pgsql/pgsql_statement.c
@@ -41,6 +41,20 @@ static int pgsql_stmt_dtor(pdo_stmt_t *stmt TSRMLS_DC)
 		PQclear(S->result);
 		S->result = NULL;
 	}
+
+	if (S->cursor_name) {
+		pdo_pgsql_db_handle *H = S->H;
+		char *q = NULL;
+		spprintf(&q, 0, "CLOSE %s", S->cursor_name);
+		PGresult *res = PQexec(H->server, q);
+		efree(q);
+		if (res) PQclear(res);
+		res = PQexec(H->server, "COMMIT");
+		if (res) PQclear(res);
+		efree(S->cursor_name);
+		S->cursor_name = NULL;
+	}
+	
 	if(S->cols) {
 		efree(S->cols);
 		S->cols = NULL;
@@ -64,7 +78,14 @@ static int pgsql_stmt_execute(pdo_stmt_t *stmt TSRMLS_DC)
 		}
 	}
 
-	S->result = PQexec(H->server, stmt->active_query_string);
+	if (S->cursor_name) {
+		char *q = NULL;
+		spprintf(&q, 0, "DECLARE %s FOR %s", S->cursor_name, stmt->active_query_string);
+		S->result = PQexec(H->server, q);
+		efree(q);
+	} else {
+		S->result = PQexec(H->server, stmt->active_query_string);
+	}
 	status = PQresultStatus(S->result);
 
 	if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK) {
@@ -77,7 +98,6 @@ static int pgsql_stmt_execute(pdo_stmt_t *stmt TSRMLS_DC)
 		S->cols = ecalloc(stmt->column_count, sizeof(pdo_pgsql_column));
 	}
 
-
 	if (status == PGRES_COMMAND_OK) {
 		stmt->row_count = (long)atoi(PQcmdTuples(S->result));
 	} else {
@@ -98,11 +118,39 @@ static int pgsql_stmt_fetch(pdo_stmt_t *stmt,
 {
 	pdo_pgsql_stmt *S = (pdo_pgsql_stmt*)stmt->driver_data;
 
-	if (S->current_row < stmt->row_count) {
-		S->current_row++;
-		return 1;
+	if (S->cursor_name) {
+		char *ori_str = NULL;
+		char *q = NULL;
+		ExecStatusType status;
+
+		switch (ori) {
+			case PDO_FETCH_ORI_NEXT: 	ori_str = "NEXT"; break;
+			case PDO_FETCH_ORI_PRIOR:	ori_str = "PRIOR"; break;
+			case PDO_FETCH_ORI_REL:		ori_str = "RELATIVE"; break;
+		}
+		if (!ori_str) {
+			return 0;
+		}
+		
+		spprintf(&q, 0, "FETCH %s %d FROM %s", ori_str, offset, S->cursor_name);
+		S->result = PQexec(S->H->server, q);
+		status = PQresultStatus(S->result);
+
+		if (status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK) {
+			pdo_pgsql_error_stmt(stmt, status);
+			return 0;
+		}
+
+		S->current_row = 1;
+		return 1;	
+		
 	} else {
-		return 0;
+		if (S->current_row < stmt->row_count) {
+			S->current_row++;
+			return 1;
+		} else {
+			return 0;
+		}
 	}
 }
 
-- 
cgit v1.2.1