Thread: 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")); returnNULL;} /* 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 { /* textparameter, 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 anyadditional * 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 anyadditional * 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 anyadditional * 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@strategicmind.com> To: "Christoph Haller" <ch@rodos.fzk.de> Cc: <pgsql-hackers@postgresql.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 >