Skip to content

Commit 9d9cfb1

Browse files
tglsfdctglsfdc
committed
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.
1 parent b3fe6bc commit 9d9cfb1

File tree

6 files changed

+265
-27
lines changed

6 files changed

+265
-27
lines changed

‎doc/src/sgml/libpq.sgml

Lines changed: 119 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.165 2004/10/01 17:34:17 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.166 2004/10/18 22:00:41 tgl Exp $
33
-->
44

55
<chapter id="libpq">
@@ -1055,8 +1055,9 @@ PGresult *PQexec(PGconn *conn, const char *command);
10551055
out-of-memory conditions or serious errors such as inability
10561056
to send the command to the server.
10571057
If a null pointer is returned, it
1058-
should be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result. Use
1059-
<function>PQerrorMessage</function> to get more information about the error.
1058+
should be treated like a <symbol>PGRES_FATAL_ERROR</symbol> result.
1059+
Use <function>PQerrorMessage</function> to get more information
1060+
about such errors.
10601061
</para>
10611062
</listitem>
10621063
</varlistentry>
@@ -1144,6 +1145,81 @@ than one nonempty command.) This is a limitation of the underlying protocol,
11441145
but has some usefulness as an extra defense against SQL-injection attacks.
11451146
</para>
11461147

1148+
<para>
1149+
<variablelist>
1150+
<varlistentry>
1151+
<term><function>PQprepare</function><indexterm><primary>PQprepare</></></term>
1152+
<listitem>
1153+
<para>
1154+
Submits a request to create a prepared statement with the
1155+
given parameters, and waits for completion.
1156+
<synopsis>
1157+
PGresult *PQprepare(PGconn *conn,
1158+
const char *stmtName,
1159+
const char *query,
1160+
int nParams,
1161+
const Oid *paramTypes);
1162+
</synopsis>
1163+
</para>
1164+
1165+
<para>
1166+
<function>PQprepare</> creates a prepared statement for later execution with
1167+
<function>PQexecPrepared</>.
1168+
This feature allows commands
1169+
that will be used repeatedly to be parsed and planned just once, rather
1170+
than each time they are executed.
1171+
<function>PQprepare</> is supported only in protocol 3.0 and later
1172+
connections; it will fail when using protocol 2.0.
1173+
</para>
1174+
1175+
<para>
1176+
The function creates a prepared statement named <parameter>stmtName</>
1177+
from the <parameter>query</> string, which must contain a single SQL command.
1178+
<parameter>stmtName</> may be <literal>""</> to create an unnamed statement,
1179+
in which case any pre-existing unnamed statement is automatically replaced;
1180+
otherwise it is an error if the statement name is already defined in the
1181+
current session.
1182+
If any parameters are used, they are referred
1183+
to in the query as <literal>$1</>, <literal>$2</>, etc.
1184+
<parameter>nParams</> is the number of parameters for which types are
1185+
pre-specified in the array <parameter>paramTypes[]</>. (The array pointer
1186+
may be <symbol>NULL</symbol> when <parameter>nParams</> is zero.)
1187+
<parameter>paramTypes[]</> specifies, by OID, the data types to be assigned to
1188+
the parameter symbols. If <parameter>paramTypes</> is <symbol>NULL</symbol>,
1189+
or any particular element in the array is zero, the server assigns a data type
1190+
to the parameter symbol in the same way it would do for an untyped literal
1191+
string. Also, the query may use parameter symbols with numbers higher than
1192+
<parameter>nParams</>; data types will be inferred for these symbols as
1193+
well.
1194+
</para>
1195+
1196+
<para>
1197+
As with <function>PQexec</>, the result is normally a
1198+
<structname>PGresult</structname> object whose contents indicate server-side
1199+
success or failure. A null result indicates out-of-memory or inability to
1200+
send the command at all.
1201+
Use <function>PQerrorMessage</function> to get more information
1202+
about such errors.
1203+
</para>
1204+
1205+
<para>
1206+
At present, there is no way to determine the actual datatype inferred for
1207+
any parameters whose types are not specified in <parameter>paramTypes[]</>.
1208+
This is a <application>libpq</> omission that will probably be rectified
1209+
in a future release.
1210+
</para>
1211+
</listitem>
1212+
</varlistentry>
1213+
</variablelist>
1214+
1215+
Prepared statements for use with <function>PQexecPrepared</> can also be
1216+
created by executing SQL <command>PREPARE</> statements. (But
1217+
<function>PQprepare</> is more flexible since it does not require
1218+
parameter types to be pre-specified.) Also, although there is no
1219+
<application>libpq</> function for deleting a prepared statement,
1220+
the SQL <command>DEALLOCATE</> statement can be used for that purpose.
1221+
</para>
1222+
11471223
<para>
11481224
<variablelist>
11491225
<varlistentry>
@@ -1166,7 +1242,8 @@ PGresult *PQexecPrepared(PGconn *conn,
11661242
<para>
11671243
<function>PQexecPrepared</> is like <function>PQexecParams</>, but the
11681244
command to be executed is specified by naming a previously-prepared
1169-
statement, instead of giving a query string. This feature allows commands
1245+
statement, instead of giving a query string.
1246+
This feature allows commands
11701247
that will be used repeatedly to be parsed and planned just once, rather
11711248
than each time they are executed.
11721249
<function>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).
11821259
</listitem>
11831260
</varlistentry>
11841261
</variablelist>
1185-
1186-
Presently, prepared statements for use with <function>PQexecPrepared</>
1187-
must be set up by executing an SQL <command>PREPARE</> command,
1188-
which is typically sent with <function>PQexec</> (though any of
1189-
<application>libpq</>'s query-submission functions may be used).
1190-
A lower-level interface for preparing statements may be offered in a
1191-
future release.
11921262
</para>
11931263

11941264
<para>
@@ -2270,10 +2340,15 @@ discarded by <function>PQexec</function>.
22702340
Applications that do not like these limitations can instead use the
22712341
underlying functions that <function>PQexec</function> is built from:
22722342
<function>PQsendQuery</function> and <function>PQgetResult</function>.
2273-
There are also <function>PQsendQueryParams</function> and
2274-
<function>PQsendQueryPrepared</function>, which can be used with
2275-
<function>PQgetResult</function> to duplicate the functionality of
2276-
<function>PQexecParams</function> and <function>PQexecPrepared</function>
2343+
There are also
2344+
<function>PQsendQueryParams</function>,
2345+
<function>PQsendPrepare</function>, and
2346+
<function>PQsendQueryPrepared</function>,
2347+
which can be used with <function>PQgetResult</function> to duplicate the
2348+
functionality of
2349+
<function>PQexecParams</function>,
2350+
<function>PQprepare</function>, and
2351+
<function>PQexecPrepared</function>
22772352
respectively.
22782353

22792354
<variablelist>
@@ -2325,6 +2400,33 @@ int PQsendQueryParams(PGconn *conn,
23252400
</listitem>
23262401
</varlistentry>
23272402

2403+
<varlistentry>
2404+
<term><function>PQsendPrepare</><indexterm><primary>PQsendPrepare</></></term>
2405+
<listitem>
2406+
<para>
2407+
Sends a request to create a prepared statement with the given
2408+
parameters, without waiting for completion.
2409+
<synopsis>
2410+
int PQsendPrepare(PGconn *conn,
2411+
const char *stmtName,
2412+
const char *query,
2413+
int nParams,
2414+
const Oid *paramTypes);
2415+
</synopsis>
2416+
2417+
This is an asynchronous version of <function>PQprepare</>: it
2418+
returns 1 if it was able to dispatch the request, and 0 if not.
2419+
After a successful call, call <function>PQgetResult</function>
2420+
to determine whether the server successfully created the prepared
2421+
statement.
2422+
The function's parameters are handled identically to
2423+
<function>PQprepare</function>. Like
2424+
<function>PQprepare</function>, it will not work on 2.0-protocol
2425+
connections.
2426+
</para>
2427+
</listitem>
2428+
</varlistentry>
2429+
23282430
<varlistentry>
23292431
<term><function>PQsendQueryPrepared</function><indexterm><primary>PQsendQueryPrepared</></></term>
23302432
<listitem>
@@ -2358,7 +2460,8 @@ int PQsendQueryPrepared(PGconn *conn,
23582460
<para>
23592461
Waits for the next result from a prior
23602462
<function>PQsendQuery</function>,
2361-
<function>PQsendQueryParams</function>, or
2463+
<function>PQsendQueryParams</function>,
2464+
<function>PQsendPrepare</function>, or
23622465
<function>PQsendQueryPrepared</function> call,
23632466
and returns it. A null pointer is returned when the command is complete
23642467
and there will be no more results.

‎src/interfaces/libpq/exports.txt

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
# $PostgreSQL: pgsql/src/interfaces/libpq/exports.txt,v 1.2 2004/10/18 22:00:42 tgl Exp $
12
# Functions to be exported by libpq DLLs
23
PQconnectdb 1
34
PQsetdbLogin 2
@@ -116,3 +117,5 @@ PQgetssl 114
116117
pg_char_to_encoding 115
117118
pg_valid_server_encoding 116
118119
pqsignal 117
120+
PQprepare 118
121+
PQsendPrepare 119

‎src/interfaces/libpq/fe-exec.c

Lines changed: 115 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/interfaces/libpq/fe-exec.c,v 1.163 2004/10/16 22:52:53 tgl Exp $
11+
* $PostgreSQL: pgsql/src/interfaces/libpq/fe-exec.c,v 1.164 2004/10/18 22:00:42 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -664,7 +664,7 @@ PQsendQuery(PGconn *conn, const char *query)
664664
}
665665

666666
/* remember we are using simple query protocol */
667-
conn->ext_query = false;
667+
conn->queryclass = PGQUERY_SIMPLE;
668668

669669
/*
670670
* Give the data a push. In nonblock mode, don't complain if we're
@@ -717,6 +717,94 @@ PQsendQueryParams(PGconn *conn,
717717
resultFormat);
718718
}
719719

720+
/*
721+
* PQsendPrepare
722+
* Submit a Parse message, but don't wait for it to finish
723+
*
724+
* Returns: 1 if successfully submitted
725+
* 0 if error (conn->errorMessage is set)
726+
*/
727+
int
728+
PQsendPrepare(PGconn *conn,
729+
const char *stmtName, const char *query,
730+
int nParams, const Oid *paramTypes)
731+
{
732+
if (!PQsendQueryStart(conn))
733+
return 0;
734+
735+
if (!stmtName)
736+
{
737+
printfPQExpBuffer(&conn->errorMessage,
738+
libpq_gettext("statement name is a null pointer\n"));
739+
return 0;
740+
}
741+
742+
if (!query)
743+
{
744+
printfPQExpBuffer(&conn->errorMessage,
745+
libpq_gettext("command string is a null pointer\n"));
746+
return 0;
747+
}
748+
749+
/* This isn't gonna work on a 2.0 server */
750+
if (PG_PROTOCOL_MAJOR(conn->pversion) < 3)
751+
{
752+
printfPQExpBuffer(&conn->errorMessage,
753+
libpq_gettext("function requires at least protocol version 3.0\n"));
754+
return 0;
755+
}
756+
757+
/* construct the Parse message */
758+
if (pqPutMsgStart('P', false, conn) < 0 ||
759+
pqPuts(stmtName, conn) < 0 ||
760+
pqPuts(query, conn) < 0)
761+
goto sendFailed;
762+
763+
if (nParams > 0 && paramTypes)
764+
{
765+
int i;
766+
767+
if (pqPutInt(nParams, 2, conn) < 0)
768+
goto sendFailed;
769+
for (i = 0; i < nParams; i++)
770+
{
771+
if (pqPutInt(paramTypes[i], 4, conn) < 0)
772+
goto sendFailed;
773+
}
774+
}
775+
else
776+
{
777+
if (pqPutInt(0, 2, conn) < 0)
778+
goto sendFailed;
779+
}
780+
if (pqPutMsgEnd(conn) < 0)
781+
goto sendFailed;
782+
783+
/* construct the Sync message */
784+
if (pqPutMsgStart('S', false, conn) < 0 ||
785+
pqPutMsgEnd(conn) < 0)
786+
goto sendFailed;
787+
788+
/* remember we are doing just a Parse */
789+
conn->queryclass = PGQUERY_PREPARE;
790+
791+
/*
792+
* Give the data a push. In nonblock mode, don't complain if we're
793+
* unable to send it all; PQgetResult() will do any additional
794+
* flushing needed.
795+
*/
796+
if (pqFlush(conn) < 0)
797+
goto sendFailed;
798+
799+
/* OK, it's launched! */
800+
conn->asyncStatus = PGASYNC_BUSY;
801+
return 1;
802+
803+
sendFailed:
804+
pqHandleSendFailure(conn);
805+
return 0;
806+
}
807+
720808
/*
721809
* PQsendQueryPrepared
722810
* Like PQsendQuery, but execute a previously prepared statement,
@@ -921,7 +1009,7 @@ PQsendQueryGuts(PGconn *conn,
9211009
goto sendFailed;
9221010

9231011
/* remember we are using extended query protocol */
924-
conn->ext_query = true;
1012+
conn->queryclass = PGQUERY_EXTENDED;
9251013

9261014
/*
9271015
* Give the data a push. In nonblock mode, don't complain if we're
@@ -1134,7 +1222,6 @@ PQgetResult(PGconn *conn)
11341222
* The user is responsible for freeing the PGresult via PQclear()
11351223
* when done with it.
11361224
*/
1137-
11381225
PGresult *
11391226
PQexec(PGconn *conn, const char *query)
11401227
{
@@ -1168,6 +1255,29 @@ PQexecParams(PGconn *conn,
11681255
return PQexecFinish(conn);
11691256
}
11701257

1258+
/*
1259+
* PQprepare
1260+
* Creates a prepared statement by issuing a v3.0 parse message.
1261+
*
1262+
* If the query was not even sent, return NULL; conn->errorMessage is set to
1263+
* a relevant message.
1264+
* If the query was sent, a new PGresult is returned (which could indicate
1265+
* either success or failure).
1266+
* The user is responsible for freeing the PGresult via PQclear()
1267+
* when done with it.
1268+
*/
1269+
PGresult *
1270+
PQprepare(PGconn *conn,
1271+
const char *stmtName, const char *query,
1272+
int nParams, const Oid *paramTypes)
1273+
{
1274+
if (!PQexecStart(conn))
1275+
return NULL;
1276+
if (!PQsendPrepare(conn, stmtName, query, nParams, paramTypes))
1277+
return NULL;
1278+
return PQexecFinish(conn);
1279+
}
1280+
11711281
/*
11721282
* PQexecPrepared
11731283
* Like PQexec, but execute a previously prepared statement,
@@ -1451,7 +1561,7 @@ PQputCopyEnd(PGconn *conn, const char *errormsg)
14511561
* If we sent the COPY command in extended-query mode, we must
14521562
* issue a Sync as well.
14531563
*/
1454-
if (conn->ext_query)
1564+
if (conn->queryclass != PGQUERY_SIMPLE)
14551565
{
14561566
if (pqPutMsgStart('S', false, conn) < 0 ||
14571567
pqPutMsgEnd(conn) < 0)

0 commit comments

Comments
 (0)