From 0be731ad4441bcb8d2aea809c08c37a4a5d831ce Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 13 Aug 2003 16:29:03 +0000 Subject: [PATCH] Add PQexecPrepared() and PQsendQueryPrepared() functions, to allow libpq users to perform Bind/Execute of previously prepared statements. Per yesterday's discussion, this offers enough performance improvement to justify bending the 'no new features during beta' rule. --- doc/src/sgml/libpq.sgml | 91 +++++++++++- src/interfaces/libpq/blibpqdll.def | 4 + src/interfaces/libpq/fe-exec.c | 223 +++++++++++++++++++++-------- src/interfaces/libpq/libpq-fe.h | 16 ++- src/interfaces/libpq/libpqdll.def | 2 + 5 files changed, 266 insertions(+), 70 deletions(-) diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index dc395d46c35..8284d4b5f8d 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -1,5 +1,5 @@ @@ -1090,6 +1090,53 @@ than one nonempty command.) This is a limitation of the underlying protocol, but has some usefulness as an extra defense against SQL-injection attacks. + + + +PQexecPrepared + + + Sends a request to execute a prepared statement with given + parameters, and waits for the result. + +PGresult *PQexecPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); + + + + +PQexecPrepared is like PQexecParams, but the +command to be executed is specified by naming a previously-prepared +statement, instead of giving a query string. This feature allows commands +that will be used repeatedly to be parsed and planned just once, rather +than each time they are executed. +PQexecPrepared is supported only in protocol 3.0 and later +connections; it will fail when using protocol 2.0. + + + +The parameters are identical to PQexecParams, except that the +name of a prepared statement is given instead of a query string, and the +paramTypes[] parameter is not present (it is not needed since +the prepared statement's parameter types were determined when it was created). + + + + + +Presently, prepared statements for use with PQexecPrepared +must be set up by executing an SQL PREPARE command, +which is typically sent with PQexec (though any of +libpq's query-submission functions may be used). +A lower-level interface for preparing statements may be offered in a +future release. + + The PGresult structure encapsulates the result returned by the server. @@ -1775,7 +1822,7 @@ SQL commands are fed to your database. Note that it is not necessary nor correct to do escaping when a data value is passed as a separate parameter in PQexecParams or -PQsendQueryParams. +its sibling routines. size_t PQescapeString (char *to, const char *from, size_t length); @@ -1961,9 +2008,11 @@ discarded by PQexec. Applications that do not like these limitations can instead use the underlying functions that PQexec is built from: PQsendQuery and PQgetResult. -There is also PQsendQueryParams, which can be -used with PQgetResult to duplicate the functionality -of PQexecParams. +There are also PQsendQueryParams and +PQsendQueryPrepared, which can be used with +PQgetResult to duplicate the functionality of +PQexecParams and PQexecPrepared +respectively. @@ -2014,13 +2063,41 @@ int PQsendQueryParams(PGconn *conn, + +PQsendQueryPrepared + + + Sends a request to execute a prepared statement with given + parameters, without waiting for the result(s). + +int PQsendQueryPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char * const *paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); + + + This is similar to PQsendQueryParams, but the + command to be executed is specified by naming a previously-prepared + statement, instead of giving a query string. + The function's parameters are handled identically to + PQexecPrepared. Like + PQexecPrepared, it will not work on 2.0-protocol + connections. + + + + PQgetResult Waits for the next result from a prior - PQsendQuery or - PQsendQueryParams, + PQsendQuery, + PQsendQueryParams, or + PQsendQueryPrepared call, and returns it. A null pointer is returned when the command is complete and there will be no more results. diff --git a/src/interfaces/libpq/blibpqdll.def b/src/interfaces/libpq/blibpqdll.def index ff85e9cdfc8..eb78e770f8a 100644 --- a/src/interfaces/libpq/blibpqdll.def +++ b/src/interfaces/libpq/blibpqdll.def @@ -111,6 +111,8 @@ EXPORTS _PQftable @ 107 _PQftablecol @ 108 _PQfformat @ 109 + _PQexecPrepared @ 110 + _PQsendQueryPrepared @ 111 ; Aliases for MS compatible names PQconnectdb = _PQconnectdb @@ -222,3 +224,5 @@ EXPORTS PQftable = _PQftable PQftablecol = _PQftablecol PQfformat = _PQfformat + PQexecPrepared = _PQexecPrepared + PQsendQueryPrepared = _PQsendQueryPrepared diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index 350561d7267..0e2cd6a8a76 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v 1.143 2003/08/04 02:40:16 momjian Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v 1.144 2003/08/13 16:29:03 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -44,6 +44,15 @@ char *const pgresStatus[] = { static bool PQsendQueryStart(PGconn *conn); +static int PQsendQueryGuts(PGconn *conn, + const char *command, + const char *stmtName, + int nParams, + const Oid *paramTypes, + const char *const * paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); static void parseInput(PGconn *conn); static bool PQexecStart(PGconn *conn); static PGresult *PQexecFinish(PGconn *conn); @@ -668,58 +677,160 @@ PQsendQueryParams(PGconn *conn, const int *paramFormats, int resultFormat) { - int i; + if (!PQsendQueryStart(conn)) + return 0; + + if (!command) + { + printfPQExpBuffer(&conn->errorMessage, + libpq_gettext("command string is a null pointer\n")); + return 0; + } + + return PQsendQueryGuts(conn, + command, + "", /* use unnamed statement */ + nParams, + paramTypes, + paramValues, + paramLengths, + paramFormats, + resultFormat); +} +/* + * PQsendQueryPrepared + * Like PQsendQuery, but execute a previously prepared statement, + * using 3.0 protocol so we can pass parameters + */ +int +PQsendQueryPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char *const * paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat) +{ if (!PQsendQueryStart(conn)) return 0; - /* This isn't gonna work on a 2.0 server */ - if (PG_PROTOCOL_MAJOR(conn->pversion) < 3) + if (!stmtName) { printfPQExpBuffer(&conn->errorMessage, - libpq_gettext("function requires at least 3.0 protocol\n")); + libpq_gettext("statement name is a null pointer\n")); return 0; } - if (!command) + return PQsendQueryGuts(conn, + NULL, /* no command to parse */ + stmtName, + nParams, + NULL, /* no param types */ + paramValues, + paramLengths, + paramFormats, + resultFormat); +} + +/* + * Common startup code for PQsendQuery and sibling routines + */ +static bool +PQsendQueryStart(PGconn *conn) +{ + if (!conn) + return false; + + /* clear the error string */ + resetPQExpBuffer(&conn->errorMessage); + + /* Don't try to send if we know there's no live connection. */ + if (conn->status != CONNECTION_OK) { printfPQExpBuffer(&conn->errorMessage, - libpq_gettext("command string is a null pointer\n")); + libpq_gettext("no connection to the server\n")); + return false; + } + /* Can't send while already busy, either. */ + if (conn->asyncStatus != PGASYNC_IDLE) + { + printfPQExpBuffer(&conn->errorMessage, + libpq_gettext("another command is already in progress\n")); + return false; + } + + /* initialize async result-accumulation state */ + conn->result = NULL; + conn->curTuple = NULL; + + /* ready to send command message */ + return true; +} + +/* + * PQsendQueryGuts + * Common code for 3.0-protocol query sending + * PQsendQueryStart should be done already + * + * command may be NULL to indicate we use an already-prepared statement + */ +static int +PQsendQueryGuts(PGconn *conn, + const char *command, + const char *stmtName, + int nParams, + const Oid *paramTypes, + const char *const * paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat) +{ + int i; + + /* 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 3.0 protocol\n")); return 0; } /* - * We will send Parse, Bind, Describe Portal, Execute, Sync, using - * unnamed statement and portal. + * We will send Parse (if needed), Bind, Describe Portal, Execute, Sync, + * using specified statement name and the unnamed portal. */ - /* construct the Parse message */ - if (pqPutMsgStart('P', false, conn) < 0 || - pqPuts("", conn) < 0 || - pqPuts(command, conn) < 0) - goto sendFailed; - if (nParams > 0 && paramTypes) + if (command) { - if (pqPutInt(nParams, 2, conn) < 0) + /* construct the Parse message */ + if (pqPutMsgStart('P', false, conn) < 0 || + pqPuts(stmtName, conn) < 0 || + pqPuts(command, conn) < 0) goto sendFailed; - for (i = 0; i < nParams; i++) + if (nParams > 0 && paramTypes) { - if (pqPutInt(paramTypes[i], 4, conn) < 0) + 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) + else + { + if (pqPutInt(0, 2, conn) < 0) + goto sendFailed; + } + if (pqPutMsgEnd(conn) < 0) goto sendFailed; } - if (pqPutMsgEnd(conn) < 0) - goto sendFailed; /* construct the Bind message */ if (pqPutMsgStart('B', false, conn) < 0 || pqPuts("", conn) < 0 || - pqPuts("", conn) < 0) + pqPuts(stmtName, conn) < 0) goto sendFailed; if (nParams > 0 && paramFormats) { @@ -807,41 +918,6 @@ sendFailed: return 0; } -/* - * Common startup code for PQsendQuery and PQsendQueryParams - */ -static bool -PQsendQueryStart(PGconn *conn) -{ - if (!conn) - return false; - - /* clear the error string */ - resetPQExpBuffer(&conn->errorMessage); - - /* Don't try to send if we know there's no live connection. */ - if (conn->status != CONNECTION_OK) - { - printfPQExpBuffer(&conn->errorMessage, - libpq_gettext("no connection to the server\n")); - return false; - } - /* Can't send while already busy, either. */ - if (conn->asyncStatus != PGASYNC_IDLE) - { - printfPQExpBuffer(&conn->errorMessage, - libpq_gettext("another command is already in progress\n")); - return false; - } - - /* initialize async result-accumulation state */ - conn->result = NULL; - conn->curTuple = NULL; - - /* ready to send command message */ - return true; -} - /* * pqHandleSendFailure: try to clean up after failure to send command. * @@ -1071,7 +1147,30 @@ PQexecParams(PGconn *conn, } /* - * Common code for PQexec and PQexecParams: prepare to send command + * PQexecPrepared + * Like PQexec, but execute a previously prepared statement, + * using 3.0 protocol so we can pass parameters + */ +PGresult * +PQexecPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char *const * paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat) +{ + if (!PQexecStart(conn)) + return NULL; + if (!PQsendQueryPrepared(conn, stmtName, + nParams, paramValues, paramLengths, + paramFormats, resultFormat)) + return NULL; + return PQexecFinish(conn); +} + +/* + * Common code for PQexec and sibling routines: prepare to send command */ static bool PQexecStart(PGconn *conn) @@ -1139,7 +1238,7 @@ PQexecStart(PGconn *conn) } /* - * Common code for PQexec and PQexecParams: wait for command result + * Common code for PQexec and sibling routines: wait for command result */ static PGresult * PQexecFinish(PGconn *conn) diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h index eb64a5ac585..6843bb7e981 100644 --- a/src/interfaces/libpq/libpq-fe.h +++ b/src/interfaces/libpq/libpq-fe.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: libpq-fe.h,v 1.97 2003/08/08 21:42:55 momjian Exp $ + * $Id: libpq-fe.h,v 1.98 2003/08/13 16:29:03 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -286,6 +286,13 @@ extern PGresult *PQexecParams(PGconn *conn, const int *paramLengths, const int *paramFormats, int resultFormat); +extern PGresult *PQexecPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char *const * paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); /* Interface for multiple-result or asynchronous queries */ extern int PQsendQuery(PGconn *conn, const char *query); @@ -297,6 +304,13 @@ extern int PQsendQueryParams(PGconn *conn, const int *paramLengths, const int *paramFormats, int resultFormat); +extern int PQsendQueryPrepared(PGconn *conn, + const char *stmtName, + int nParams, + const char *const * paramValues, + const int *paramLengths, + const int *paramFormats, + int resultFormat); extern PGresult *PQgetResult(PGconn *conn); /* Routines for managing an asynchronous query */ diff --git a/src/interfaces/libpq/libpqdll.def b/src/interfaces/libpq/libpqdll.def index 8ff902f5218..4973ddd7138 100644 --- a/src/interfaces/libpq/libpqdll.def +++ b/src/interfaces/libpq/libpqdll.def @@ -111,3 +111,5 @@ EXPORTS PQftable @ 107 PQftablecol @ 108 PQfformat @ 109 + PQexecPrepared @ 110 + PQsendQueryPrepared @ 111 -- 2.39.5