pg_exec_prepared

pg_exec_prepared
Prev	4.3. Query Execution Commands	Next

pg_exec_prepared — Execute a pre-prepared SQL command on the server

Synopsis

pg_exec_prepared conn statementName resultFormats argFormats ?param...?

Description

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.

Arguments

conn: The handle of the connection on which to execute the 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 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 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.
param...: Zero or more parameter values to bind to command string place-holders. The first parameter will replace $1 in the prepared query, the second parameter will replace $2, etc. The parameters will be interpreted as text or binary data according to the argFormats argument.

Return Value

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.

Notes

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 persist 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 returned result handle with pg_result -clear when you are done with it.

This command uses or emulates the PostgreSQL libpq function PQexecPrepared.

Caution

The original Pgtcl implementation has a command with the same name but incompatible arguments.

Example

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”.

Prev	Up	Next
pg_exec_params	Home	pg_describe_cursor