From 9d9cfb1ad75b60f4e9258078d7f483ea123dc7b2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 18 Oct 2004 22:00:42 +0000 Subject: [PATCH] Add PQprepare/PQsendPrepared functions to libpq to support preparing statements without necessarily specifying the datatypes of their parameters. Abhijit Menon-Sen with some help from Tom Lane. --- doc/src/sgml/libpq.sgml | 135 ++++++++++++++++++++++++---- src/interfaces/libpq/exports.txt | 3 + src/interfaces/libpq/fe-exec.c | 120 +++++++++++++++++++++++-- src/interfaces/libpq/fe-protocol3.c | 13 ++- src/interfaces/libpq/libpq-fe.h | 8 +- src/interfaces/libpq/libpq-int.h | 13 ++- 6 files changed, 265 insertions(+), 27 deletions(-) diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 97a6ab5e71..d0c59424fc 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -1,5 +1,5 @@ @@ -1055,8 +1055,9 @@ PGresult *PQexec(PGconn *conn, const char *command); out-of-memory conditions or serious errors such as inability to send the command to the server. If a null pointer is returned, it - should be treated like a PGRES_FATAL_ERROR result. Use - PQerrorMessage to get more information about the error. + should be treated like a PGRES_FATAL_ERROR result. + Use PQerrorMessage to get more information + about such errors. @@ -1144,6 +1145,81 @@ than one nonempty command.) This is a limitation of the underlying protocol, but has some usefulness as an extra defense against SQL-injection attacks. + + + +PQpreparePQprepare + + + Submits a request to create a prepared statement with the + given parameters, and waits for completion. + +PGresult *PQprepare(PGconn *conn, + const char *stmtName, + const char *query, + int nParams, + const Oid *paramTypes); + + + + +PQprepare creates a prepared statement for later execution with +PQexecPrepared. +This feature allows commands +that will be used repeatedly to be parsed and planned just once, rather +than each time they are executed. +PQprepare is supported only in protocol 3.0 and later +connections; it will fail when using protocol 2.0. + + + +The function creates a prepared statement named stmtName +from the query string, which must contain a single SQL command. +stmtName may be "" to create an unnamed statement, +in which case any pre-existing unnamed statement is automatically replaced; +otherwise it is an error if the statement name is already defined in the +current session. +If any parameters are used, they are referred +to in the query as $1, $2, etc. +nParams is the number of parameters for which types are +pre-specified in the array paramTypes[]. (The array pointer +may be NULL when nParams is zero.) +paramTypes[] specifies, by OID, the data types to be assigned to +the parameter symbols. If paramTypes is NULL, +or any particular element in the array is zero, the server assigns a data type +to the parameter symbol in the same way it would do for an untyped literal +string. Also, the query may use parameter symbols with numbers higher than +nParams; data types will be inferred for these symbols as +well. + + + +As with PQexec, the result is normally a +PGresult object whose contents indicate server-side +success or failure. A null result indicates out-of-memory or inability to +send the command at all. +Use PQerrorMessage to get more information +about such errors. + + + +At present, there is no way to determine the actual datatype inferred for +any parameters whose types are not specified in paramTypes[]. +This is a libpq omission that will probably be rectified +in a future release. + + + + + +Prepared statements for use with PQexecPrepared can also be +created by executing SQL PREPARE statements. (But +PQprepare is more flexible since it does not require +parameter types to be pre-specified.) Also, although there is no +libpq function for deleting a prepared statement, +the SQL DEALLOCATE statement can be used for that purpose. + + @@ -1166,7 +1242,8 @@ PGresult *PQexecPrepared(PGconn *conn, 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 +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 @@ -1182,13 +1259,6 @@ 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. @@ -2270,10 +2340,15 @@ 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 are also PQsendQueryParams and -PQsendQueryPrepared, which can be used with -PQgetResult to duplicate the functionality of -PQexecParams and PQexecPrepared +There are also +PQsendQueryParams, +PQsendPrepare, and +PQsendQueryPrepared, +which can be used with PQgetResult to duplicate the +functionality of +PQexecParams, +PQprepare, and +PQexecPrepared respectively. @@ -2325,6 +2400,33 @@ int PQsendQueryParams(PGconn *conn, + +PQsendPreparePQsendPrepare + + + Sends a request to create a prepared statement with the given + parameters, without waiting for completion. + +int PQsendPrepare(PGconn *conn, + const char *stmtName, + const char *query, + int nParams, + const Oid *paramTypes); + + + This is an asynchronous version of PQprepare: it + returns 1 if it was able to dispatch the request, and 0 if not. + After a successful call, call PQgetResult + to determine whether the server successfully created the prepared + statement. + The function's parameters are handled identically to + PQprepare. Like + PQprepare, it will not work on 2.0-protocol + connections. + + + + PQsendQueryPreparedPQsendQueryPrepared @@ -2358,7 +2460,8 @@ int PQsendQueryPrepared(PGconn *conn, Waits for the next result from a prior PQsendQuery, - PQsendQueryParams, or + PQsendQueryParams, + PQsendPrepare, 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/exports.txt b/src/interfaces/libpq/exports.txt index d6532155a3..e14a8bb58a 100644 --- a/src/interfaces/libpq/exports.txt +++ b/src/interfaces/libpq/exports.txt @@ -1,3 +1,4 @@ +# $PostgreSQL: pgsql/src/interfaces/libpq/exports.txt,v 1.2 2004/10/18 22:00:42 tgl Exp $ # Functions to be exported by libpq DLLs PQconnectdb 1 PQsetdbLogin 2 @@ -116,3 +117,5 @@ PQgetssl 114 pg_char_to_encoding 115 pg_valid_server_encoding 116 pqsignal 117 +PQprepare 118 +PQsendPrepare 119 diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.c index 5e19d78b37..fff9746e33 100644 --- a/src/interfaces/libpq/fe-exec.c +++ b/src/interfaces/libpq/fe-exec.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/interfaces/libpq/fe-exec.c,v 1.163 2004/10/16 22:52:53 tgl Exp $ + * $PostgreSQL: pgsql/src/interfaces/libpq/fe-exec.c,v 1.164 2004/10/18 22:00:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -664,7 +664,7 @@ PQsendQuery(PGconn *conn, const char *query) } /* remember we are using simple query protocol */ - conn->ext_query = false; + conn->queryclass = PGQUERY_SIMPLE; /* * Give the data a push. In nonblock mode, don't complain if we're @@ -717,6 +717,94 @@ PQsendQueryParams(PGconn *conn, resultFormat); } +/* + * 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 (!PQsendQueryStart(conn)) + return 0; + + if (!stmtName) + { + printfPQExpBuffer(&conn->errorMessage, + libpq_gettext("statement name is a null pointer\n")); + return 0; + } + + if (!query) + { + printfPQExpBuffer(&conn->errorMessage, + libpq_gettext("command string is a null pointer\n")); + return 0; + } + + /* 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 Parse message */ + 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; + + /* construct the Sync message */ + if (pqPutMsgStart('S', false, conn) < 0 || + pqPutMsgEnd(conn) < 0) + goto sendFailed; + + /* remember we are doing just a Parse */ + conn->queryclass = PGQUERY_PREPARE; + + /* + * 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 1; + +sendFailed: + pqHandleSendFailure(conn); + return 0; +} + /* * PQsendQueryPrepared * Like PQsendQuery, but execute a previously prepared statement, @@ -921,7 +1009,7 @@ PQsendQueryGuts(PGconn *conn, goto sendFailed; /* remember we are using extended query protocol */ - conn->ext_query = true; + conn->queryclass = PGQUERY_EXTENDED; /* * Give the data a push. In nonblock mode, don't complain if we're @@ -1134,7 +1222,6 @@ PQgetResult(PGconn *conn) * The user is responsible for freeing the PGresult via PQclear() * when done with it. */ - PGresult * PQexec(PGconn *conn, const char *query) { @@ -1168,6 +1255,29 @@ PQexecParams(PGconn *conn, return PQexecFinish(conn); } +/* + * PQprepare + * Creates a prepared statement by issuing a v3.0 parse message. + * + * If the query was not even sent, return NULL; conn->errorMessage is set to + * a relevant message. + * If the query was sent, a new PGresult is returned (which could indicate + * either success or failure). + * The user is responsible for freeing the PGresult via PQclear() + * when done with it. + */ +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); +} + /* * PQexecPrepared * Like PQexec, but execute a previously prepared statement, @@ -1451,7 +1561,7 @@ PQputCopyEnd(PGconn *conn, const char *errormsg) * If we sent the COPY command in extended-query mode, we must * issue a Sync as well. */ - if (conn->ext_query) + if (conn->queryclass != PGQUERY_SIMPLE) { if (pqPutMsgStart('S', false, conn) < 0 || pqPutMsgEnd(conn) < 0) diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c index cec7a67205..af88ddc8f4 100644 --- a/src/interfaces/libpq/fe-protocol3.c +++ b/src/interfaces/libpq/fe-protocol3.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/interfaces/libpq/fe-protocol3.c,v 1.18 2004/10/16 22:52:54 tgl Exp $ + * $PostgreSQL: pgsql/src/interfaces/libpq/fe-protocol3.c,v 1.19 2004/10/18 22:00:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -220,6 +220,15 @@ pqParseInput3(PGconn *conn) conn->asyncStatus = PGASYNC_READY; break; case '1': /* Parse Complete */ + /* If we're doing PQprepare, we're done; else ignore */ + if (conn->queryclass == PGQUERY_PREPARE) + { + 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 */ @@ -1118,7 +1127,7 @@ pqEndcopy3(PGconn *conn) * If we sent the COPY command in extended-query mode, we must * issue a Sync as well. */ - if (conn->ext_query) + if (conn->queryclass != PGQUERY_SIMPLE) { if (pqPutMsgStart('S', false, conn) < 0 || pqPutMsgEnd(conn) < 0) diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h index 55e288b417..1fba91bde4 100644 --- a/src/interfaces/libpq/libpq-fe.h +++ b/src/interfaces/libpq/libpq-fe.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/interfaces/libpq/libpq-fe.h,v 1.111 2004/10/16 22:52:55 tgl Exp $ + * $PostgreSQL: pgsql/src/interfaces/libpq/libpq-fe.h,v 1.112 2004/10/18 22:00:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -304,6 +304,9 @@ extern PGresult *PQexecParams(PGconn *conn, const int *paramLengths, const int *paramFormats, int resultFormat); +extern PGresult *PQprepare(PGconn *conn, const char *stmtName, + const char *query, int nParams, + const Oid *paramTypes); extern PGresult *PQexecPrepared(PGconn *conn, const char *stmtName, int nParams, @@ -322,6 +325,9 @@ extern int PQsendQueryParams(PGconn *conn, const int *paramLengths, const int *paramFormats, int resultFormat); +extern int PQsendPrepare(PGconn *conn, const char *stmtName, + const char *query, int nParams, + const Oid *paramTypes); extern int PQsendQueryPrepared(PGconn *conn, const char *stmtName, int nParams, diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h index e0992c4103..d1fab1395b 100644 --- a/src/interfaces/libpq/libpq-int.h +++ b/src/interfaces/libpq/libpq-int.h @@ -12,7 +12,7 @@ * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/interfaces/libpq/libpq-int.h,v 1.94 2004/10/16 22:52:55 tgl Exp $ + * $PostgreSQL: pgsql/src/interfaces/libpq/libpq-int.h,v 1.95 2004/10/18 22:00:42 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -185,6 +185,14 @@ typedef enum PGASYNC_COPY_OUT /* Copy Out data transfer in progress */ } PGAsyncStatusType; +/* PGQueryClass tracks which query protocol we are now executing */ +typedef enum +{ + PGQUERY_SIMPLE, /* simple Query protocol (PQexec) */ + PGQUERY_EXTENDED, /* full Extended protocol (PQexecParams) */ + PGQUERY_PREPARE /* Parse only (PQprepare) */ +} PGQueryClass; + /* PGSetenvStatusType defines the state of the PQSetenv state machine */ /* (this is used only for 2.0-protocol connections) */ typedef enum @@ -264,10 +272,9 @@ struct pg_conn PGAsyncStatusType asyncStatus; PGTransactionStatusType xactStatus; /* note: xactStatus never changes to ACTIVE */ + PGQueryClass queryclass; bool nonblocking; /* whether this connection is using * nonblock sending semantics */ - bool ext_query; /* was our last query sent with extended - * query protocol? */ char copy_is_binary; /* 1 = copy binary, 0 = copy text */ int copy_already_done; /* # bytes already returned in * COPY OUT */ -- 2.49.0