pg_exec_params

pg_exec_params — Parse and execute a parameterized SQL statement

Synopsis

pg_exec_params conn commandString resultFormats argFormats argTypes arg...

Description

pg_exec_params sends an SQL command to the server with parameters to be bound to place-holders in the command, and returns a result handle. This is similar to pg_exec_prepared, but doesn't use a pre-prepared statement, and if you want to use binary parameters you must also provide the type OIDs. By separating parameters from the SQL command string, 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 prepared command.

commandString

The SQL command to parse, bind arguments to, and execute. Argument placeholders are indicated as $1, $2, etc.

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.

argTypes

A list of PostgreSQL Type OIDs for the query parameter arguments. This list must either be empty, or contain one entry for each query parameter. If the list is empty, all arguments are treated as untyped literal strings, and all argument formats must be text. If the list is non-empty, each zero entry results in the corresponding text format argument being treated as an untyped literal string. Each non-zero entry is the type OID for the corresponding binary format argument. To get type OIDs, query the pg_type table.

arg...

Zero or more arguments to bind to query parameters. 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.

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

Be sure to brace-quote or escape the parameter placeholders such as $1 in the SQL command string to protect them from Tcl variable expansion.

The command syntax of pg_exec_params 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 was added in pgtclng-1.5.1 and in pgintcl-2.1.0.

This command uses or emulates the PostgreSQL libpq function PQexecParams.

Caution

This is a new command, and should be considered experimental. The command may change and backwards compatibility is not assured.