pg_exec_params

pg_exec_params
Prev	4.3. Query Execution Commands	Next

pg_exec_params — Execute a parameterized SQL command on the server

Synopsis

pg_exec_params conn commandString resultFormats argFormats argTypes ?param...?

Description

pg_exec_params sends a command to the PostgreSQL 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 command.
commandString: The SQL command to parse, bind arguments to, and execute. Argument place-holders are indicated as $1, $2, etc.
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.
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.
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

Be sure to brace-quote or escape the parameter place-holders 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 as query parameters.

Be sure to free the returned 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.

pg_exec also supports parameterized queries, but only TEXT parameter and result types. If all your parameters are TEXT type, and your result is TEXT type, it is simpler to use pg_exec instead.

Prev	Up	Next
pg_select	Home	pg_exec_prepared