Re: Prepared select

libpq doesn't have enought support to allow executing a prepared statement
in a named portal (current libpq only works wuth the unnamed portal). But
the V3 protocol have it. I solved this problem by adding the following
functions. They let you prepare a named statement, execute this statement in
a named portal, fetch from it and close it.

this is a temporary solution to wait for an official extension of libpq
(more call could be added to support completly the V3 protocol).

cyril

/*
* PQportalSetup
* Setup a portal to execute a prepared statement
*/
PGresult *
PQportalSetup(PGconn *conn,
const char *stmtName,
const char *portalName,
int nParams,
const char *const * paramValues,
const int *paramLengths,
const int *paramFormats,
int resultFormat)
{
int i;

if (!PQexecStart(conn))
return NULL;

if (!PQsendQueryStart(conn))
return NULL;

if (!stmtName)
{
printfPQExpBuffer(&conn->errorMessage,
libpq_gettext("statement name is a null pointer\n"));
return NULL;
}

/* This isn't gonna work on a 2.0 server */
if (PG_PROTOCOL_MAJOR(conn->pversion) < 3)
{
printfPQExpBuffer(&conn->errorMessage,
libpq_gettext("function requires at least protocol version 3.0\n"));
return 0;
}

/* construct the Bind message */
if (pqPutMsgStart('B', false, conn) < 0 ||
pqPuts(portalName, conn) < 0 ||
pqPuts(stmtName, conn) < 0)
goto sendFailed;
if (nParams > 0 && paramFormats)
{
if (pqPutInt(nParams, 2, conn) < 0)
goto sendFailed;
for (i = 0; i < nParams; i++)
{
if (pqPutInt(paramFormats[i], 2, conn) < 0)
goto sendFailed;
}
}
else
{
if (pqPutInt(0, 2, conn) < 0)
goto sendFailed;
}
if (pqPutInt(nParams, 2, conn) < 0)
goto sendFailed;
for (i = 0; i < nParams; i++)
{
if (paramValues && paramValues[i])
{
int nbytes;

if (paramFormats && paramFormats[i] != 0)
{
/* binary parameter */
nbytes = paramLengths[i];
}
else
{
/* text parameter, do not use paramLengths */
nbytes = strlen(paramValues[i]);
}
if (pqPutInt(nbytes, 4, conn) < 0 ||
pqPutnchar(paramValues[i], nbytes, conn) < 0)
goto sendFailed;
}
else
{
/* take the param as NULL */
if (pqPutInt(-1, 4, conn) < 0)
goto sendFailed;
}
}
if (pqPutInt(1, 2, conn) < 0 ||
pqPutInt(resultFormat, 2, conn))
goto sendFailed;
if (pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* construct the Sync message */
if (pqPutMsgStart('S', false, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* remember we are using extended query protocol */
conn->ext_query = true;

/*
* Give the data a push. In nonblock mode, don't complain if we're
* unable to send it all; PQgetResult() will do any additional
* flushing needed.
*/
if (pqFlush(conn) < 0)
goto sendFailed;

/* OK, it's launched! */
conn->asyncStatus = PGASYNC_BUSY;

return PQexecFinish(conn);

sendFailed:
pqHandleSendFailure(conn);
return NULL;
}

/*
* PQportalFetch
* Fetch next rows
*/
PGresult *
PQportalFetch(PGconn *conn,
const char *portalName,
int maxrows)
{
if (!PQexecStart(conn))
return NULL;

if (!PQsendQueryStart(conn))
return NULL;

/* This isn't gonna work on a 2.0 server */
if (PG_PROTOCOL_MAJOR(conn->pversion) < 3)
{
printfPQExpBuffer(&conn->errorMessage,
libpq_gettext("function requires at least protocol version 3.0\n"));
return 0;
}

/* construct the Describe Portal message */
if (pqPutMsgStart('D', false, conn) < 0 ||
pqPutc('P', conn) < 0 ||
pqPuts(portalName, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* construct the Execute message */
if (pqPutMsgStart('E', false, conn) < 0 ||
pqPuts(portalName, conn) < 0 ||
pqPutInt(maxrows, 4, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* construct the Sync message */
if (pqPutMsgStart('S', false, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* remember we are using extended query protocol */
conn->ext_query = true;

/*
* Give the data a push. In nonblock mode, don't complain if we're
* unable to send it all; PQgetResult() will do any additional
* flushing needed.
*/
if (pqFlush(conn) < 0)
goto sendFailed;

/* OK, it's launched! */
conn->asyncStatus = PGASYNC_BUSY;
return PQexecFinish(conn);

sendFailed:
pqHandleSendFailure(conn);
return NULL;
}

/*
* PQportalClose
* Close a named portal
* using protocol 3.0
*/
PGresult *
PQportalClose(PGconn *conn,
const char *portalName)
{
if (!PQexecStart(conn))
return NULL;

if (!PQsendQueryStart(conn))
return NULL;

/* This isn't gonna work on a 2.0 server */
if (PG_PROTOCOL_MAJOR(conn->pversion) < 3)
{
printfPQExpBuffer(&conn->errorMessage,
libpq_gettext("function requires at least protocol version 3.0\n"));
return 0;
}

/* construct the Close message */
if (pqPutMsgStart('C', false, conn) < 0 ||
pqPutc('P', conn) < 0 ||
pqPuts(portalName, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* construct the Sync message */
if (pqPutMsgStart('S', false, conn) < 0 ||
pqPutMsgEnd(conn) < 0)
goto sendFailed;

/* remember we are using extended query protocol */
conn->ext_query = true;

/*
* Give the data a push. In nonblock mode, don't complain if we're
* unable to send it all; PQgetResult() will do any additional
* flushing needed.
*/
if (pqFlush(conn) < 0)
goto sendFailed;

/* OK, it's launched! */
conn->asyncStatus = PGASYNC_BUSY;
return PQexecFinish(conn);

sendFailed:
pqHandleSendFailure(conn);
return NULL;
}

----- Original Message -----
From: "Robert Turnbull" <rturnbull(at)strategicmind(dot)com>
To: "Christoph Haller" <ch(at)rodos(dot)fzk(dot)de>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Monday, April 19, 2004 2:48 AM
Subject: Re: [HACKERS] Prepared select

> There are several production issues related to the proposed solution. For
> example, what happens when the result set exceeds the swap space of the
> server or client machine? My original question is how to get a cursor from
a
> prepared select so a subset of the result can be returned to the client
for
> processing. For your solution to work the SQL EXECUTE command needs the
> functionality of the SQL FETCH command.
>
>
> > >
> > >
> > > How can I use a prepared select statement as mentioned in the
> documentation=
> > > on SQL PREPARE. Preparing the statement is easy, the problem is using
> the =
> > > plan to get a cursor. My assumption is the SQL OPEN command is not
> document=
> > > ed or there is some other libpq API to make this happen.
> > >
> > > Thanks
> > >
> > >
> > >
> > I'm using libpq and lines like below are working:
> >
> > res = PQexec(conn,
> > "PREPARE plan001 ( integer , double precision , character ) AS SELECT
> a,b,d FROM foo WHERE a = $1 OR d > $2 OR b = $3");
> > ...
> > res = PQexec(conn, "EXECUTE plan001 ( 3 , 6.66 , 'whatever' ) ");
> >
> > HTH, pretty late reply - I know (but no one else did as far as I can
tell)
> >
> > Regards, Christoph
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>