Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9d9cfb1

Browse files
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 parentb3fe6bc commit9d9cfb1

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+
constchar*stmtName,constchar*query,
730+
intnParams,constOid*paramTypes)
731+
{
732+
if (!PQsendQueryStart(conn))
733+
return0;
734+
735+
if (!stmtName)
736+
{
737+
printfPQExpBuffer(&conn->errorMessage,
738+
libpq_gettext("statement name is a null pointer\n"));
739+
return0;
740+
}
741+
742+
if (!query)
743+
{
744+
printfPQExpBuffer(&conn->errorMessage,
745+
libpq_gettext("command string is a null pointer\n"));
746+
return0;
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+
return0;
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+
gotosendFailed;
762+
763+
if (nParams>0&&paramTypes)
764+
{
765+
inti;
766+
767+
if (pqPutInt(nParams,2,conn)<0)
768+
gotosendFailed;
769+
for (i=0;i<nParams;i++)
770+
{
771+
if (pqPutInt(paramTypes[i],4,conn)<0)
772+
gotosendFailed;
773+
}
774+
}
775+
else
776+
{
777+
if (pqPutInt(0,2,conn)<0)
778+
gotosendFailed;
779+
}
780+
if (pqPutMsgEnd(conn)<0)
781+
gotosendFailed;
782+
783+
/* construct the Sync message */
784+
if (pqPutMsgStart('S', false,conn)<0||
785+
pqPutMsgEnd(conn)<0)
786+
gotosendFailed;
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+
gotosendFailed;
798+
799+
/* OK, it's launched! */
800+
conn->asyncStatus=PGASYNC_BUSY;
801+
return1;
802+
803+
sendFailed:
804+
pqHandleSendFailure(conn);
805+
return0;
806+
}
807+
720808
/*
721809
* PQsendQueryPrepared
722810
*Like PQsendQuery, but execute a previously prepared statement,
@@ -921,7 +1009,7 @@ PQsendQueryGuts(PGconn *conn,
9211009
gotosendFailed;
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,constchar*query)
11401227
{
@@ -1168,6 +1255,29 @@ PQexecParams(PGconn *conn,
11681255
returnPQexecFinish(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+
constchar*stmtName,constchar*query,
1272+
intnParams,constOid*paramTypes)
1273+
{
1274+
if (!PQexecStart(conn))
1275+
returnNULL;
1276+
if (!PQsendPrepare(conn,stmtName,query,nParams,paramTypes))
1277+
returnNULL;
1278+
returnPQexecFinish(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)
[8]ページ先頭
©2009-2025 Movatter.jp