Method: PG::Connection#exec_params

Defined in:
ext/pg_connection.c

#exec_params(sql, params[, result_format [, type_map ]]) ⇒ nil #exec_params(sql, params[, result_format [, type_map ]]) {|pg_result| ... } ⇒ Object Also known as: async_exec_params

Sends SQL query request specified by sql to PostgreSQL using placeholders for parameters.

Returns a PG::Result instance on success. On failure, it raises a PG::Error.

params is an array of the bind parameters for the SQL query. Each element of the params array may be either:

a hash of the form:
  {:value  => String (value of bind parameter)
   :type   => Integer (oid of type of bind parameter)
   :format => Integer (0 for text, 1 for binary)
  }
or, it may be a String. If it is a string, that is equivalent to the hash:
  { :value => <string value>, :type => 0, :format => 0 }

PostgreSQL bind parameters are represented as $1, $2, $3, etc., inside the SQL query. The 0th element of the params array is bound to $1, the 1st element is bound to $2, etc. nil is treated as NULL.

If the types are not specified, they will be inferred by PostgreSQL. Instead of specifying type oids, it’s recommended to simply add explicit casts in the query to ensure that the right type is used.

For example: “SELECT $1::int”

The optional result_format should be 0 for text results, 1 for binary.

type_map can be a PG::TypeMap derivation (such as PG::BasicTypeMapForQueries). This will type cast the params from various Ruby types before transmission based on the encoders defined by the type map. When a type encoder is used the format and oid of a given bind parameter are retrieved from the encoder instead out of the hash form described above.

If the optional code block is given, it will be passed result as an argument, and the PG::Result object will automatically be cleared when the block terminates. In this instance, conn.exec returns the value of the block.

The primary advantage of #exec_params over #exec is that parameter values can be separated from the command string, thus avoiding the need for tedious and error-prone quoting and escaping. Unlike #exec, #exec_params allows at most one SQL command in the given string. (There can be semicolons in it, but not more than one nonempty command.) This is a limitation of the underlying protocol, but has some usefulness as an extra defense against SQL-injection attacks.

See also corresponding libpq function.

Overloads:

  • #exec_params(sql, params[, result_format [, type_map ]]) ⇒ nil

    Returns:

    • (nil)
  • #exec_params(sql, params[, result_format [, type_map ]]) {|pg_result| ... } ⇒ Object

    Yields:

    • (pg_result) 


3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
 
# File 'ext/pg_connection.c', line 3340

static VALUE
pgconn_async_exec_params(int argc, VALUE *argv, VALUE self)
{
	VALUE rb_pgresult = Qnil;

	pgconn_discard_results( self );
	/* If called with no or nil parameters, use PQsendQuery for compatibility */
	if ( argc == 1 || (argc >= 2 && argc <= 4 && NIL_P(argv[1]) )) {
		pg_deprecated(3, ("forwarding async_exec_params to async_exec is deprecated"));
		pgconn_send_query( argc, argv, self );
	} else {
		pgconn_send_query_params( argc, argv, self );
	}
	rb_pgresult = pgconn_async_get_last_result( self );

	if ( rb_block_given_p() ) {
		return rb_ensure( rb_yield, rb_pgresult, pg_result_clear, rb_pgresult );
	}
	return rb_pgresult;
}