Prepared select

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 documented or there is some other libpq API to make this happen.

Thanks

>
>
> 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

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
>
>
>

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
>