Using ruby-pg and a patch for the rdoc

Ruby
1

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…

PostgreSQL: Documentation: 8.2: Command Execution Functions

  "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.
    • "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 C. Phone : (64)(3) 358 6639
Tait Electronics Fax : (64)(3) 359 4632
PO Box 1645 Christchurch Email : [email protected]
New Zealand