Using ruby-pg and a patch for the rdoc..

[John Carter]

So I've use SQL a fair bit in a C++ environment....

Now I just learning ruby-pg.

The doc/postgres.html mentions an Insert_table method. It doesn't seem to exist anymore.

The main interface seems to be...

* call-seq:
* conn.exec(sql [, params, result_format ] ) -> PGresult
*
* Sends SQL query request specified by _sql_ to PostgreSQL.
* Returns a PGresult instance on success.
* On failure, it raises a PGError exception.
*
* +params+ is an optional 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.

Which I find a little confusing.

I see in the code there is also block_given form I think invokes block
for each row returned.

I'm a little unclear as to what the param form does and why/when you would use it.

Is it faster? Is it only for input to sql or output from sql or both?

What is the default result format?

Hmm.

This seems to be required reading to make head or tail of whats going on...

http://www.postgresql.org/docs/8.2/static/libpq-exec.html


"The primary advantage of PQexecParams over PQexec is that
parameter values may be separated from the command string, thus
avoiding the need for tedious and error-prone quoting and
escaping.

Unlike PQexec, PQexecParams 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."

Hmm. Digging in the code...

if(NIL_P(in_res_fmt)) {
resultFormat = 0;
}
else {
resultFormat = NUM2INT(in_res_fmt);
}
I guess the default is 0 text format. I wonder why? Wouldn't that be a lot slower?

Here is a patch for that comment block...

diff -u pg.c~ pg.c
--- pg.c~ 2008-03-19 05:44:07.000000000 +1300
+++ pg.c 2008-04-16 14:18:57.000000000 +1200
@@ -824,11 +824,30 @@
/*
* call-seq:
* conn.exec(sql [, params, result_format ] ) -> PGresult
+ * or
+ * conn.exec(sql [, params, result_format ] ) do |row|
+ * end
*
* Sends SQL query request specified by _sql_ to PostgreSQL.
* Returns a PGresult instance on success.
* On failure, it raises a PGError exception.
*
+ * This is implemented in terms of the libpq PQexec unless params is
+ * non-nil, then by PQexecParams.
+ * See http://www.postgresql.org/docs/8.2/static/libpq-exec.html for more details
+ *
+ * "The primary advantage of PQexecParams over PQexec is that
+ * parameter values may be separated from the command string, thus
+ * avoiding the need for tedious and error-prone quoting and
+ * escaping.
+ *
+ * Unlike PQexec, PQexecParams 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."
+ *
+ *
* +params+ is an optional array of the bind parameters for the SQL query.
* Each element of the +params+ array may be either:
* a hash of the form:
@@ -851,6 +870,8 @@
*
* The optional +result_format+ should be 0 for text results, 1
* for binary.
+ *
+ * The default +result_format+ is 0, text.
*/
static VALUE
pgconn_exec(argc, argv, self)



John Carter Phone : (64)(3) 358 6639
Tait Electronics Fax : (64)(3) 359 4632
PO Box 1645 Christchurch Email : (e-mail address removed)
New Zealand