Method: PG::Connection#exec_params

Defined in:

ext/pg_connection.c

#exec_params(sql, params[, result_format ]) ⇒ PG::Result #exec_params(sql, params[, result_format ]) {|pg_result| ... } ⇒ Object

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   => Fixnum (oid of type of bind parameter)
   :format => Fixnum (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, $1, $2, 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.

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.

Overloads:

• #exec_params(sql, params[, result_format ]) ⇒ PG::Result

  Returns:

  • (PG::Result)
• #exec_params(sql, params[, result_format ]) {|pg_result| ... } ⇒ Object

  Yields:

  • (pg_result)

# File 'ext/pg_connection.c', line 918

static VALUE
pgconn_exec_params( int argc, VALUE *argv, VALUE self )
{
	PGconn *conn = pg_get_pgconn(self);
	PGresult *result = NULL;
	VALUE rb_pgresult;
	VALUE command, params, in_res_fmt;
	VALUE param, param_type, param_value, param_format;
	VALUE param_value_tmp;
	VALUE sym_type, sym_value, sym_format;
	VALUE gc_array;
	int i=0;
	int nParams;
	Oid *paramTypes;
	char ** paramValues;
	int *paramLengths;
	int *paramFormats;
	int resultFormat;

	rb_scan_args(argc, argv, "12", &command, &params, &in_res_fmt);

	/*
	 * Handle the edge-case where the caller is coming from #exec, but passed an explict +nil+
	 * for the second parameter.
	 */
	if ( NIL_P(params) ) {
		return pgconn_exec( 1, argv, self );
		}

	Check_Type(params, T_ARRAY);

	if ( NIL_P(in_res_fmt) ) {
		resultFormat = 0;
	}
	else {
		resultFormat = NUM2INT(in_res_fmt);
	}

	gc_array = rb_ary_new();
	rb_gc_register_address(&gc_array);

	sym_type = ID2SYM(rb_intern("type"));
	sym_value = ID2SYM(rb_intern("value"));
	sym_format = ID2SYM(rb_intern("format"));
	nParams = (int)RARRAY_LEN(params);
	paramTypes = ALLOC_N(Oid, nParams);
	paramValues = ALLOC_N(char *, nParams);
	paramLengths = ALLOC_N(int, nParams);
	paramFormats = ALLOC_N(int, nParams);

	for ( i = 0; i < nParams; i++ ) {
		param = rb_ary_entry(params, i);
		if (TYPE(param) == T_HASH) {
			param_type = rb_hash_aref(param, sym_type);
			param_value_tmp = rb_hash_aref(param, sym_value);
			if(param_value_tmp == Qnil)
				param_value = param_value_tmp;
			else
				param_value = rb_obj_as_string(param_value_tmp);
			param_format = rb_hash_aref(param, sym_format);
		}
		else {
			param_type = Qnil;
			if(param == Qnil)
				param_value = param;
			else
				param_value = rb_obj_as_string(param);
			param_format = Qnil;
		}

		if(param_type == Qnil)
			paramTypes[i] = 0;
		else
			paramTypes[i] = NUM2INT(param_type);

		if(param_value == Qnil) {
			paramValues[i] = NULL;
			paramLengths[i] = 0;
		}
		else {
			Check_Type(param_value, T_STRING);
			/* make sure param_value doesn't get freed by the GC */
			rb_ary_push(gc_array, param_value);
			paramValues[i] = StringValuePtr(param_value);
			paramLengths[i] = (int)RSTRING_LEN(param_value);
		}

		if(param_format == Qnil)
			paramFormats[i] = 0;
		else
			paramFormats[i] = NUM2INT(param_format);
	}

	result = gvl_PQexecParams(conn, StringValuePtr(command), nParams, paramTypes,
		(const char * const *)paramValues, paramLengths, paramFormats, resultFormat);

	rb_gc_unregister_address(&gc_array);

	xfree(paramTypes);
	xfree(paramValues);
	xfree(paramLengths);
	xfree(paramFormats);

	rb_pgresult = pg_new_result(result, self);
	pg_result_check(rb_pgresult);

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

	return rb_pgresult;
}