Re: libpq and prepared statements progress for 8.0

[Abhijit Menon-Sen <ams(at)oryx(dot)com>]

At 2004-09-20 13:24:47 -0400, tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
> It depends on whether you think that PQprepare should bundle the
> Describe Statement operation or not.

I decided against bundling the two operations together. Here's a patch
to add PQprepare() and PQsendPrepare() in a fairly self-contained way.
Also attached is a test program à la testlibpq3.c that I used to test
the change. This should be all that's needed for DBD::Pg to prepare a
statement without pre-specifying types.

(I'll post a separate patch for PQdescribe() later. It's a little more
involved, and I need to fix a couple of bugs I found during testing.)

Any thoughts? Does this look good enough for 8.0?

-- ams

--- libpq-fe.h.1~	2004-10-05 18:14:07.885948042 +0530
+++ libpq-fe.h	2004-10-05 22:19:07.544034341 +0530
@@ -292,6 +292,9 @@ extern void PQinitSSL(int do_init);
 
 /* Simple synchronous query */
 extern PGresult *PQexec(PGconn *conn, const char *query);
+extern PGresult *PQprepare(PGconn *conn, const char *stmtName,
+						   const char *query, int nParams,
+						   const Oid *paramTypes);
 extern PGresult *PQexecParams(PGconn *conn,
 			 const char *command,
 			 int nParams,
@@ -309,6 +312,9 @@ extern PGresult *PQexecPrepared(PGconn *
 			   int resultFormat);
 
 /* Interface for multiple-result or asynchronous queries */
+extern PGresult *PQsendPrepare(PGconn *conn, const char *stmtName,
+							   const char *query, int nParams,
+							   const Oid *paramTypes);
 extern int	PQsendQuery(PGconn *conn, const char *query);
 extern int PQsendQueryParams(PGconn *conn,
 				  const char *command,

--- fe-exec.c.1~	2004-10-02 06:15:44.000000000 +0530
+++ fe-exec.c	2004-10-05 22:02:03.459149212 +0530
@@ -635,6 +635,69 @@ pqSaveParameterStatus(PGconn *conn, cons
 
 
 /*
+ * PQsendPrepare
+ *   Submit a Parse message, but don't wait for it to finish.
+ *
+ * Returns: 1 if successfully submitted
+ *          0 if error (conn->errorMessage is set)
+ */
+int
+PQsendPrepare(PGconn *conn,
+			  const char *stmtName, const char *query,
+			  int nParams, const Oid *paramTypes)
+{
+	if (PG_PROTOCOL_MAJOR(conn->pversion) < 3)
+	{
+		printfPQExpBuffer(&conn->errorMessage,
+						  libpq_gettext("function requires at least protocol version 3.0\n"));
+		return 0;
+	}
+
+	if (!PQsendQueryStart(conn))
+		return 0;
+
+	if (pqPutMsgStart('P', false, conn) < 0 ||
+		pqPuts(stmtName, conn) < 0 ||
+		pqPuts(query, conn) < 0)
+		goto sendFailed;
+
+	if (nParams > 0 && paramTypes)
+	{
+		int i;
+
+		if (pqPutInt(nParams, 2, conn) < 0)
+			goto sendFailed;
+		for (i = 0; i < nParams; i++)
+		{
+			if (pqPutInt(paramTypes[i], 4, conn) < 0)
+				goto sendFailed;
+		}
+	}
+	else
+	{
+		if (pqPutInt(0, 2, conn) < 0)
+			goto sendFailed;
+	}
+	if (pqPutMsgEnd(conn) < 0)
+		goto sendFailed;
+
+	if (pqPutMsgStart('S', false, conn) < 0 ||
+		pqPutMsgEnd(conn) < 0)
+		goto sendFailed;
+
+	conn->ext_query = true;
+	if (pqFlush(conn) < 0)
+		goto sendFailed;
+	conn->asyncStatus = PGASYNC_BUSY;
+	return 1;
+
+sendFailed:
+	pqHandleSendFailure(conn);
+	return 0;
+}
+
+
+/*
  * PQsendQuery
  *	 Submit a query, but don't wait for it to finish
  *
@@ -1145,6 +1208,28 @@ PQexec(PGconn *conn, const char *query)
 	return PQexecFinish(conn);
 }
 
+
+/*
+ * PQprepare
+ *    Creates a prepared statement by issuing a v3.0 parse message.
+ *
+ * Returns NULL if the query failed, and a new PGresult otherwise. The
+ * user is responsible for calling PQclient() on the result.
+ */
+
+PGresult *
+PQprepare(PGconn *conn,
+		  const char *stmtName, const char *query,
+		  int nParams, const Oid *paramTypes)
+{
+	if (!PQexecStart(conn))
+		return NULL;
+	if (!PQsendPrepare(conn, stmtName, query, nParams, paramTypes))
+		return NULL;
+	return PQexecFinish(conn);
+}
+
+
 /*
  * PQexecParams
  *		Like PQexec, but use protocol 3.0 so we can pass parameters

--- fe-protocol3.c.1~	2004-10-05 18:59:55.293092244 +0530
+++ fe-protocol3.c	2004-10-05 19:17:48.154807848 +0530
@@ -220,6 +220,11 @@ pqParseInput3(PGconn *conn)
 					conn->asyncStatus = PGASYNC_READY;
 					break;
 				case '1':		/* Parse Complete */
+					if (conn->result == NULL)
+						conn->result = PQmakeEmptyPGresult(conn,
+														   PGRES_COMMAND_OK);
+					conn->asyncStatus = PGASYNC_READY;
+					break;
 				case '2':		/* Bind Complete */
 				case '3':		/* Close Complete */
 					/* Nothing to do for these message types */

/*
 * Test program for PQprepare/PQdescribe.
 * Abhijit Menon-Sen <ams(at)oryx(dot)com>
 *
 * create table pqtest (a int, b text, c text);
 * insert into pqtest (a,b,c) values (1,'foo','bar');
 * insert into pqtest (a,b,c) values (1,'foo','baz');
 * insert into pqtest (a,b,c) values (2,'foo','barf');
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "libpq-fe.h"


int main(int argc, char *argv[])
{
    int i, a, b, c;
    PGconn *conn;
    PGresult *res;

    char *query = "select * from pqtest where a=$1 and b=$2";
    const char *values[2];

    conn = PQconnectdb("");
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Couldn't connect to the database: %s",
                PQerrorMessage(conn));
        PQfinish(conn);
        exit(-1);
    }

    res = PQprepare(conn, "prep", query, 0, NULL);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "PREPARE failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        exit(-1);
    }

    PQclear(res);
    values[0] = "1";
    values[1] = "foo";
    res = PQexecPrepared(conn, "prep", 2, values, NULL, NULL, 0);
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        fprintf(stderr, "EXECUTE failed: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
        exit(-1);
    }

    a = PQfnumber(res, "a");
    b = PQfnumber(res, "b");
    c = PQfnumber(res, "c");

    printf("Results:\n");
    for(i = 0; i < PQntuples(res); i++) {
        char *av = PQgetvalue(res, i, a);
        char *bv = PQgetvalue(res, i, b);
        char *cv = PQgetvalue(res, i, c);
        printf("  (a=%s)(b=%s)(c=%s)\n", av, bv, cv);
    }
    printf("%d rows\n", i);

    PQclear(res);
    PQfinish(conn);
    return 0;
}