Like a geocache, this is another post meant to help somebody who stumbles across it in a future Google search. (Or as an external reminder for me, when I forget how I did this six months from now.)

I've liked here-documents since the days of shell programming. Ruby has good support for here docs with variable interpolation. For example, if I want to construct a SQL query, I can do this:

def build_query(customer_id)
    select * 
     from customer
   where id = #{customer_id}

Disclaimer: Don't do this if customer_id comes from user input!

Recently, I wanted a way to build inserts using a matching number of column names and placeholders.

def build_query
    insert into #{table} ( #{columns()} ) values ( #{column_placeholders()} )

In this case, columns and column_placeholders were both functions.

One oddity I ran into is the combination of here documents and block syntax. RubyDBI lets you pass a block when executing a query, the same way you would pass a block to File::open(). The block gets a "statement handle", which gets cleaned up when the block completes.

  dbh.execute(query) { |sth| 
    sth.fetch() { |row|
      # do something with the row

Combining these two lets you write something that looks like SQL invading Ruby:

  dbh.execute(<<-STMT) { |sth|
      select distinct customer, business_unit_id, business_unit_key_name
       from problem_ticket_lz
       order by customer
    sth.fetch { |row|
      print "#{row[1]}\t#{row[0]}\t#{row[2]}\n"

This looks pretty good overall, but take a look at how the block opening interacts with the here doc. The here doc appears to be line-oriented, so it always begins on the line after the <<-STMT token. On the other hand, the block open follows the function, so the here doc gets lexically interpolated in the middle of the block, even though it has no syntactic relation to the block. No real gripe, just an oddity.