Using the Ruby DBI Module

Table of Contents

• Introduction
• Prerequisites
• Installation
• A Simple DBI Script
• Processing Queries
  • Processing Queries that Return No Result Set
  • Processing Queries that Return a Result Set
  • Quoting, Placeholders, and Parameter Binding
  • Query Metadata
• Methods That Take Code Blocks
• More on Connecting to the Server
• Error Handling and Debugging
• Transaction Support
• Accessing Driver-Specific Capabilities
• Other DBI Goodies
• Resources

The Ruby DBI module provides a database-independent interface for Ruby scripts similar to that of the Perl DBI module. This article describes how to write Ruby DBI-based scripts. It is an adjunct to and not a substitute for the DBI specification documents. Be sure to read the specifications. For more information, see the "Resources" section.

The general architecture for Ruby DBI uses two layers:

• The database interface (DBI) layer. This layer is database independent and provides a set of common access methods that are used the same way regardless of the type of database server with which you're communicating.
  
• The database driver (DBD) layer. This layer is database dependent; different drivers provide access to different database engines. There is one driver for MySQL, another for PostgreSQL, another for InterBase, another for Oracle, and so forth. Each driver interprets requests from the DBI layer and maps them onto requests appropriate for a given type of database server.
  

The Ruby DBI module includes the code that implements the general DBI layer, as well as a set of DBD-level drivers. Many of these drivers require that you have additional software installed. For example, the database driver for MySQL is written in Ruby and provides a binding to the Ruby MySQL module, which itself is written in C and provides a binding to the MySQL C client API. This means that if you want to write DBI scripts to access MySQL databases, you'll need to have both the Ruby MySQL module and the C API installed. For further information on the Ruby MySQL module, see the article referenced in the "Resources" section. Here, I will assume that the MySQL module is installed and available for use by DBI.

Installation

After you have satisfied the prerequisites described in the previous section, you can install the Ruby DBI module, which may be obtained from the following site:

   http://ruby-dbi.sourceforge.net/

   % tar zxf ruby-dbi-all-0.0.19.tar.gz
   % gunzip < ruby-dbi-all-0.0.19.tar.gz | tar xf -

   % ruby setup.rb config

   % ruby setup.rb config --with=dbi,dbd_mysql

   % ruby setup.rb setup
   % ruby setup.rb install

The rest of this article uses the following notational conventions:

• "DBI module" refers collectively to the DBI layer as well as the DBD-level drivers, unless context indicates that only the database independent layer is meant.
  
• "DBD::Mysql" refers to the MySQL-specific database driver for DBI.
  
• "Ruby MySQL module" refers to the module on which DBD::Mysql is built.
  

With the Ruby DBI module installed, you should be able to access your MySQL server from within Ruby programs. Assume for purposes of this article that the server is running on the local host and that you have access to a database named test by connecting using an account that has a username and password of testuser and testpass. You can set up this account by using the mysql program to connect to the server as the MySQL root user and issuing the following statement:

   mysql> GRANT ALL ON test.* TO 'testuser'@'localhost' IDENTIFIED BY 'testpass';

   mysql> CREATE DATABASE test;

The following script, simple.rb, is a short DBI program that just connects to the server, retrieves and displays the server version, and disconnects. You can download the script from the link listed in the "Resources" section, or use a text editor to create it directly:

   # simple.rb - simple MySQL script using Ruby DBI module

   require "dbi"

   begin
       # connect to the MySQL server
       dbh = DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")
       # get server version string and display it
       row = dbh.select_one("SELECT VERSION()")
       puts "Server version: " + row[0]
   rescue DBI::DatabaseError => e
       puts "An error occurred"

       puts "Error code: #{e.err}"
       puts "Error message: #{e.errstr}"
   ensure
       # disconnect from server
       dbh.disconnect if dbh
   end

simple.rb begins with a require line that pulls in the DBI module; without that line, DBI methods will fail. The rest of the script is placed within a begin/rescue/ensure construct:

• The begin block handles all the database processing.
  
• The rescue block handles any exceptions that occur; it obtains and displays error information.
  
• The ensure block make sure that the script closes any open connection to the database server.
  

simple.rb uses the database handle to call select_one, a method that sends a query to the server and returns the first row of the result as an array. The SELECT VERSION() query returns a single value, so the version string is available as row[0], the first (and only) element of the array. When you run the script, the result looks something like this:

   % ruby simple.rb

   Server version: 4.0.13-log

simple.rb terminates the connection to the server by invoking the disconnect method. This is done in an ensure block to make sure that connection termination occurs even if an error occurs during query processing.

Processing Queries

Ruby DBI provides many ways to execute statements. This section discusses a few of them, but there are others.

Many of the examples use a table named people that has the following structure:

   CREATE TABLE people
   (
       id INT UNSIGNED NOT NULL AUTO_INCREMENT,    # ID number
       name CHAR(20) NOT NULL,                     # name
       height FLOAT,                               # height in inches
       PRIMARY KEY (id)
   );

Statements that do not return rows may be issued by invoking the do database handle method. This method takes a query string argument and returns a count of the number of rows affected by the query. The following example uses do several times to create the people table and populate it with a small data set:

   dbh.do("DROP TABLE IF EXISTS people")
   dbh.do("CREATE TABLE people (
           id INT UNSIGNED NOT NULL AUTO_INCREMENT,
           PRIMARY KEY (id),
           name CHAR(20) NOT NULL,
           height FLOAT)")
   rows = dbh.do("INSERT INTO people (name,height)
           VALUES('Wanda',62.5),('Robert',75),('Phillip',71.5),('Sarah',68)")
   printf "%d rows were inserted\n", rows

Statements such as SELECT or SHOW return rows. To process such a statement, send it to the server for execution, retrieve any rows in the result set that it generates, and dispose of the result set.

One way to do this is to call prepare to generate a statement handle. Use that handle to execute the statement and fetch its results, and then free the result set:

   sth = dbh.prepare(statement)
   sth.execute
   ... fetch rows ...
   sth.finish

   sth = dbh.execute(statement)
   ... fetch rows ...
   sth.finish

   sth = dbh.execute("SELECT * FROM people")
   while row = sth.fetch do
       printf "ID: %d, Name: %s, Height: %.1f\n", row[0], row[1], row[2]
   end
   sth.finish

   sth = dbh.execute("SELECT * FROM people")
   sth.fetch do |row|
       printf "ID: %d, Name: %s, Height: %.1f\n", row[0], row[1], row[2]
   end
   sth.finish

   sth = dbh.execute("SELECT * FROM people")
   sth.each do |row|
       printf "ID: %d, Name: %s, Height: %.1f\n", row[0], row[1], row[2]
   end
   sth.finish

• You can use a row object with by_index or by_field to access column values by number or by name:
  

     val = row.by_index(2)
     val = row.by_field("height")
  

• Column values also can be accessed by index or name using array notation:
  

     val = row[2]
     val = row["height"]
  

• An iterator method, each_with_name, produces each column value along with the column name:
  

     sth = dbh.execute("SELECT * FROM people")
     sth.each do |row|
         row.each_with_name do |val, name|
             printf "%s: %s, ", name, val.to_s
         end
         print "\n"
  
     end
     sth.finish
  

• DBI::Row objects also have a column_names method that returns an array containing the names for each column. field_names is an alias for column_names.
  

   sth = dbh.execute("SELECT * FROM people")
   while row = sth.fetch_hash do
       printf "ID: %d, Name: %s, Height: %.1f\n",
               row["id"], row["name"], row["height"]
   end
   sth.finish

   sth = dbh.execute("SELECT * FROM people")
   sth.fetch_hash do |row|
       printf "ID: %d, Name: %s, Height: %.1f\n",
               row["id"], row["name"], row["height"]
   end
   sth.finish

   row = dbh.select_one(statement)
   rows = dbh.select_all(statement)

The MySQL driver examines the metadata for the result set and uses it to coerce row values to the corresponding Ruby data type. (This means, for example,, that id, name, and height values retrieved from the people table will be returned as Fixnum, String, and Float objects.) However, be aware that if a column value is NULL, it will be represented as nil in the result set and its type will be NilClass. Also note that this coercion behavior does not appear to be mandated by the DBI specification and may not be performed by all drivers.

Quoting, Placeholders, and Parameter Binding

Ruby DBI provides a placeholder mechanism that allows you to avoid including data values literally in a query string. Instead, you use special markers within the statement to indicate where the data values go. When you execute the statement, you provide values to be bound to the placeholders. DBI substitutes the values into the statement where the placeholders appear, performing any quoting of string values and escaping of special characters as necessary. This makes it easy to construct statements without having to know whether or not the values contain special characters, and without having to do any quote processing yourself. The placeholder mechanism also properly handles NULL values; just provide nil as a data value and it will be placed into the statement as an unquoted NULL value.

The following example illustrates how this works. Suppose you want to add a new row to the people table for someone named Na'il (a name that includes a quote), who is 76 inches tall. To indicate where the data values go in the INSERT statement, use '?' placeholder markers (without any surrounding quotes), and provide the data values as additional arguments to do following the statement:

   dbh.do("INSERT INTO people (id, name, height) VALUES(?, ?, ?)",
           nil, "Na'il", 76)

   INSERT INTO people (id,name,height) VALUES(NULL,'Na\'il',76)

   # prepare statement for use within insert loop
   sth = dbh.prepare("INSERT INTO people (id, name, height) VALUES(?, ?, ?)")

   # read each line from file, split into values, and insert into database
   f = File.open("people.txt", "r")
   f.each_line do |line|
       name, height = line.chomp.split("\t")
       sth.execute(nil, name, height)
   end
   f.close

To use placeholders for SELECT statements, the proper strategy depends on whether you prepare the statement first:

• If you invoke prepare to obtain a statement handle, use that handle to call execute and pass it the data values to be bound to the placeholders:
  

     sth = dbh.prepare("SELECT * FROM people WHERE name = ?")
     sth.execute("Na'il")
     sth.fetch do |row|
         printf "ID: %d, Name: %s, Height: %.1f\n", row[0], row[1], row[2]
     end
     sth.finish
  
  

• If you don't use prepare, the first argument to execute is the statement and the following arguments are the data values:
  

     sth = dbh.execute("SELECT * FROM people WHERE name = ?", "Na'il")
     sth.fetch do |row|
         printf "ID: %d, Name: %s, Height: %.1f\n", row[0], row[1], row[2]
     end
     sth.finish
  
  

The quote method performs quoting and escaping of a data value and returns the result. This can be useful for constructing statements to be executed by other programs. For example, if you want to read the data file people.txt and convert it to a set of INSERT statements that can be processed by a program such as the mysql command-line client, do this:

   # read each line from file, split into values, and write INSERT statement
   f = File.open("people.txt", "r")
   f.each_line do |line|
       name, height = line.chomp.split("\t")
       printf "INSERT INTO people (id, name, height) VALUES(%s, %s, %s);\n",
               dbh.quote(nil), dbh.quote(name), dbh.quote(height)
   end
   f.close

For statements that return no result set, such as INSERT or DELETE, do returns a count of the number of rows processed.

For statements that return rows, such as SELECT, you can use the statement handle after invoking execute to get row and column counts or information about each of the columns in the result set:

• The row and column counts are not available directly. To get the row count, either count the rows as you fetch them, or fetch them into a data structure and see how many elements it contains. To get the column count, you can determine it from the number of column names, available as sth.column_names.size.
  
• The column_info method returns information about each column.
  

   sth = dbh.execute(query)

   puts "Query: " + query
   if sth.column_names.size == 0 then
       puts "Query has no result set"

       printf "Number of rows affected: %d\n", sth.rows
   else
       puts "Query has a result set"
       rows = sth.fetch_all
       printf "Number of rows: %d\n", rows.size
       printf "Number of columns: %d\n", sth.column_names.size
       sth.column_info.each_with_index do |info, i|
           printf "--- Column %d (%s) ---\n", i, info.name
           printf "precision:  %s\n", info.precision
           printf "scale:      %s\n", info.scale
       end
   end
   sth.finish

Some handle-creating methods may be invoked with a code block. When executed this way, they provide the handle to the code block as its parameter, and automatically clean up the handle when the block terminates:

• DBI.connect generates a database handle, for which it calls disconnect if necessary at the end of the block.
  
• dbh.prepare generates a statement handle, for which it calls finish at the end of the block. Within the block, you must invoke execute to execute the statement.
  
• dbh.execute is similar except you don't invoke execute within the block; the statement handle is automatically executed.
  

   # connect can take a code block, passes the database handle to it, 
   # and automatically disconnects the handle at the end of the block

   DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass") do |dbh|

       # prepare can take a code block, passes the statement handle
       # to it, and automatically calls finish at the end of the block

       dbh.prepare("SHOW DATABASES") do |sth|
           sth.execute
           puts "Databases: " + sth.fetch_all.join(", ")
       end

       # execute can take a code block, passes the statement handle
       # to it, and automatically calls finish at the end of the block

       dbh.execute("SHOW DATABASES") do |sth|
           puts "Databases: " + sth.fetch_all.join(", ")
       end
   end

The simple.rb script shown earlier connects to the server using the DBI connect method as follows:

   dbh = DBI.connect("dbi:Mysql:test:localhost", "testuser", "testpass")

The DSN can be given in any of the following formats:

   dbi:driver_name
   dbi:driver_name:db_name:host_name
   dbi:driver_name:key=val;key=val...

dbi (or DBI) and the driver name must always be given in the DSN. If nothing follows the driver name, the driver may (I think) attempt to connect using a default database and host name. The MySQL driver tries to require a database name to be specified, so the first format cannot be used for MySQL programming with DBI; you must use one of the other formats. The second format requires two values, a database name and hostname separated by a colon. The third format allows a list of parameter assignments to be specified in param=value format separated by semicolons. The following DSNs are all equivalent:

   dbi:Mysql:test:localhost
   dbi:Mysql:host=localhost;database=test
   dbi:Mysql:database=test;host=localhost

If a DBI method fails, DBI raises an exception. DBI methods may raise any of several types of exception, but for database-related operations, the relevant exception class is DatabaseError. Exception objects of this class have three attributes named err, errstr, and state. The DBI documentation isn't specific on the meaning of these attributes, but they appear to represent the error number, a descriptive error string, and some kind of "standard" error code. The MySQL driver at the moment supplies only the error string, although it's easy to patch the driver to supply the error number as well. Assuming that both values are available, you can obtain them when an exception occurs as follows:

   rescue DBI::DatabaseError => e
       puts "An error occurred"

       puts "Error code: #{e.err}"
       puts "Error message: #{e.errstr}"

   require "dbi/trace"

The dbi/trace module provides a trace method that controls the trace mode and output destination:

   trace(mode, destination)

trace may be invoked as a class method to affect all subsequently created handles, or as an object method for individual driver, database, or statement handles. When invoked as an object method, any other objects subsequently derived from that object also inherit the trace setting. For example, if you enable tracing on a database handle, statement handles created from it are given the same trace setting.

Transaction Support

DBI provides a transaction abstraction. However, availability of the abstraction depends on transaction support in your database engine, and on a DBD-level implementation of the abstraction in your driver. For the MySQL driver, this abstraction is not functional prior to DBI 0.0.19, so you must perform transactions by explicitly using statements that control the auto-commit level, commits, and rollbacks. For example:

   dbh.do("SET AUTOCOMMIT=0")
   dbh.do("BEGIN")

   ... statements that make up the transaction ...
   dbh.do("COMMIT")

   dbh['AutoCommit'] = true
   dbh['AutoCommit'] = false

• The first approach uses DBI's commit and rollback methods to explicitly commit or cancel the transaction:
  

     dbh['AutoCommit'] = false
     begin
         dbh.do("UPDATE account SET balance = balance - 50
                 WHERE name = 'bill'")
         dbh.do("UPDATE account SET balance = balance + 50
                 WHERE name = 'bob'")
         dbh.commit
     rescue
         puts "transaction failed"
         dbh.rollback
     end
  

• The second approach uses the transaction method. This is simpler, because it takes a code block containing the statements that make up the transaction. The transaction method executes the block, then invokes commit or rollback automatically, depending on whether the block succeeds or fails:
  

     dbh['AutoCommit'] = false
     dbh.transaction do |dbh|
         dbh.do("UPDATE account SET balance = balance - 50
                 WHERE name = 'bill'")
         dbh.do("UPDATE account SET balance = balance + 50
                 WHERE name = 'bob'")
     end
  

DBI provides a func method that drivers can use to make database-dependent features available. For example, the MySQL C API provides a mysql_insert_id() function that returns the most recent AUTO_INCREMENT value for a connection. The Ruby MySQL module provides a binding to this function via its insert_id database handle method, and DBD::Mysql in turn provides access to insert_id via the DBI func mechanism.

The first argument to func is the name of the database-specific method you want to use; the following arguments are those required by the method, if any. The insert_id method requires no additional arguments, so to access the most recent AUTO_INCREMENT value, do this:

   dbh.do("INSERT INTO people (name,height) VALUES('Mike',70.5)")
   id = dbh.func(:insert_id)
   puts "ID for new record is: " + id.to_s

   dbh.func(:createdb, db_name) Create a new database
   dbh.func(:dropdb, db_name)   Drop a database
   dbh.func(:reload)            Perform a reload operation
   dbh.func(:shutdown)          Shut down the server

In some cases, use of a driver-specific function may offer specific advantages, even if there is another way to accomplish the same thing. For example, the value returned by the insert_id function of DBD::Mysql can be obtained by issuing a SELECT LAST_INSERT_ID() query. Both return the same value in most cases. However, the insert_id function is more efficient because it returns a value that is stored on the client side and can be accessed without issuing another query. This efficiency benefit comes at the cost of greater care in how you use the function. Its value is reset for each query executed, so you must access it after issuing the query that generates an AUTO_INCREMENT value but before issuing any other query. By contrast, the value of LAST_INSERT_ID() is stored on the server side and is more persistent; it is not reset by other queries except those that also generate AUTO_INCREMENT values.

Other DBI Goodies

The DBI::Utils module includes a few interesting methods:

• DBI::Utils::measure takes a code block and measures how long it takes to execute the code within the block. You can use this method to measure the wallclock time for execution of a statement as follows:
  

     elapsed = DBI::Utils::measure do
         dbh.do(query)
     end
     puts "Query: " + query
     puts "Elapsed time: " + elapsed.to_s
  

• The DBI::Utils::TableFormatter module has an ascii method for displaying the contents of a result set. The first argument is an array of column names, and the second is an array of row objects. To display the contents of the people table, do this:
  

     sth = dbh.execute("SELECT * FROM people")
     rows = sth.fetch_all
     col_names = sth.column_names
     sth.finish
     DBI::Utils::TableFormatter.ascii(col_names, rows)
  

  The resulting output is:
  

     +----+---------+--------+
     | id | name    | height |
     +----+---------+--------+
     | 1  | Wanda   | 62.5   |
     | 2  | Robert  | 75.0   |
     | 3  | Phillip | 71.5   |
     | 4  | Sarah   | 68.0   |
     +----+---------+--------+
  

• The DBI::Utils::XMLFormatter module has row and table methods for displaying individual result set rows or an entire result set as XML. This makes it easy to generate XML output from query results. The following example demonstrates the table method:
  

     DBI::Utils::XMLFormatter.table(dbh.select_all("SELECT * FROM people"))
  

  The resulting output is:
  

     <?xml version="1.0" encoding="UTF-8" ?>
  
     <rows>
     <row>
       <id>1</id>
       <name>Wanda</name>
       <height>62.5</height>
  
     </row>
     <row>
       <id>2</id>
       <name>Robert</name>
       <height>75.0</height>
  
     </row>
     <row>
       <id>3</id>
       <name>Phillip</name>
       <height>71.5</height>
  
     </row>
     <row>
       <id>4</id>
       <name>Sarah</name>
       <height>68.0</height>
  
     </row>
     </rows>
  

The scripts that are used for examples in this article can be downloaded from the following location:

   http://www.kitebird.com/articles/

You may find the following additional resources helpful for using Ruby DBI:

• You can get the Ruby DBI module and specification documents from the DBI SourceForge site:
  

     http://ruby-dbi.sourceforge.net/
  
  

• The AspectR Ruby module must be installed if you want to use the dbi/trace module that provides DBI execution tracing. You can get AspectR at its SourceForge site:
  

     http://aspectr.sourceforge.net/
  
  

• The Ruby home page provides general information about Ruby itself:
  

     http://www.ruby-lang.org/
  
  

• MySQL may be obtained at:
  

  
     http://www.mysql.com/