pg_exec_prepared — Execute a pre-prepared SQL statement on the server
pg_exec_preparedconn
statementName
resultFormats
argFormats
arg...
pg_exec_prepared
executes a pre-prepared SQL
named statement on the server and returns a result handle. This command allows
binding arguments to SQL statement parameters without quoting issues, and
supports sending and receiving raw binary data.
conn
The handle of the connection on which to execute the prepared command.
statementName
The name of the pre-prepared statement.
resultFormats
The format that the query results are expected in. Use T
or TEXT
for text (ASCII) format results,
and B
or BINARY
for binary format
results. If this parameter is an empty string, TEXT
is
assumed.
argFormats
The formats that the query parameters are sent in. This parameter can be an
empty string, a single word, or a list. If this parameter is an empty
string, all query parameters are sent in text (ASCII) format. If this
parameter is a single word, it specifies the format for all query
parameters. Use T
(or TEXT
) for text
format, and B
(or BINARY
) for binary
format. If this parameter is a list, it must contain a single word
(T
or TEXT
or B
or
BINARY
) specifying the format for each query parameter.
arg...
Zero or more arguments to pass to the prepared query.
The first argument will replace $1
in the
prepared query, the second argument will replace $2
,
etc. The arguments will be interpreted as text or binary data
according to the argFormats
argument.
Returns a result handle which can be used with pg_result to obtain the results of the command.
A Tcl error will be thrown if an error occurs communicating with the database. Note that no Tcl error will be thrown if an invalid query is successfully sent to the server and a response successfully received. The result status must be checked after a normal return.
The statement must be prepared with an SQL PREPARE command, for example:
PREPARE statementName (INTEGER) AS SELECT * FROM mytable WHERE ID > $1
This can be sent to the database with pg_exec
or pg_execute
(but note that $1
must
be escaped for Tcl as \$1
).
Prepared statements last until the end of a session.
The command syntax of pg_exec_prepared
supports mixed
text and binary result columns, but the underlying
PostgreSQL library
(libpq) does not currently support this.
Therefore, all result columns must be text format, or all columns must be
binary format.
There is no support for passing NULL value arguments to prepared statements.
Be sure to free the result handle with
pg_result -clear
when you are done with it.
This command uses or emulates the PostgreSQL
libpq
function PQexecPrepared
.
This is a new command, and should be considered experimental. The command may change and backwards compatibility is not assured.
First, create a prepared a statement called sel1 which takes a single integer parameter and returns two values from the corresponding record in a database:
pg_execute $conn "PREPARE sel1 (INTEGER) AS SELECT cost, itemname FROM mytable WHERE partnum = \$1"
Now we can use that prepared statement to fetch the values from a record:
set res [pg_exec_prepared $conn sel1 TEXT TEXT $partnumber]
One TEXT argument, $partnumber, is sent to the prepared query and replaces $1. One TEXT result is expected back. The result handle res is accessed using pg_result just like a result handle from pg_exec.
Other examples can be found in Section 5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures” and Section 5.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures”.