How to write scripts based on Ruby DBI -Issue#1

What Will I Learn?

The Ruby DBI module provides Ruby scripts with a database-independent interface, similar to the DBI Perl module. This tutorial describes how to write scripts based on Ruby DBI. In this issue we will provide tutorial about -

• Installation of Ruby and DBI module
• A simple DBI script
• Processing SQL queries
• Processing queries that do not return a result

Requirements

The Ruby DBI module includes code that implements the DBI general layer and a set of database-pecific drivers. You can probably install the Ruby DBI module using your distribution's package manager . For example, under Ubuntu, we can install this module simply by typing sudo apt-get install libdbi-ruby. Many of these drivers require the installation of other software.

For example, the MySQL database driver is written in Ruby and depends on the MySQL Ruby module, which is itself written in C and provides a bridge to the MySQL C client API. This means that if you want to write DBI scripts to access MySQL databases, you will need to install the Ruby MySQL module as well as the C API.

Difficulty

• Intermediate

Tutorial Contents

---

Installation:-

---

After installing the prerequisites described in the requirements section, you can install the DBI module that can be obtained from the following site: http://rubyforge.org/projects/ruby-dbi/ .

The DBI module is distributed as a tarball that you should decompress after downloading. For example, if the current version is 0.1.1 , the distribution archive can be decompressed with one of the following commands:

% tar zxf dbi-0.1.1.tar.gz
% gunzip < dbi-0.1.1.tar.gz | tar xf -

After unzipping the distribution archive, go to the root of the directory and configure it using the setup.rb script in this directory. The most general command to configure is the following (with no arguments after config):
% ruby setup.rb config

This command configures the distribution to install all the default drivers. To be more specific, pass the option --with that lists the modules of the distribution that you want to use. For example, to configure the main DBI module and the MySQL database driver, run the following command:
% ruby setup.rb config --with=dbi,dbd_mysql

After configuring the distribution, you need to build and install it:

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

You may have to run the installation command as the root user .

The rest of this document uses the following conventions:

"DBI Module" refers to the specific DBI driver for the MySQL database.
"MySQL Module Ruby" refers to the module on which DBD::MySQL is built (ie the module that provides the bridge with the client API C).

---

A simple DBI script:-

---

With the Ruby DBI module installed, you should be able to access your MySQL server from Ruby programs. We will assume in the rest of this article that the server is running on the localhost ( localhost ) and that you have access to a named database test by logging into an account with a username testuser and password testpass. You can set up this account by using the program mysql to log in as MySQL root and by running the following query:
mysql> GRANT ALL ON test.* TO 'testuser'@'localhost' IDENTIFIED BY 'testpass';

If the database test does not exist, create it with this query:
mysql> CREATE DATABASE test;

If you want to use a different host for the server, another name or password, or another database, simply replace with the appropriate values ​​in the scripts of the rest of this article.

The following script, simple.rb , is a short DBI program that connects to the server, retrieves and displays the server version, and disconnects.

#! / usr / bin / ruby ​​-w
# simple.rb - simple MySQL script using the Ruby DBI module
require "dbi"
begin
  # connection to the MySQL server
  dbh = DBI.connect ("DBI: Mysql: test: localhost", "testuser", "testpass")
  # retrieves the server version string and displays it
  row = dbh.select_one ("SELECT VERSION ()")
  puts "Server version:" + row [0]
rescue DBI :: DatabaseError => e
  puts "An error has occurred"
  puts "Error Code: # {e.err}"
  puts "Error Message: # {e.errstr}"
Ensure
  # server disconnect
  dbh.disconnect if dbh
end

The simple.rb script provides a general overview of DBI's basic concepts. The following discussion details the operation of the script, and the following sections present additional examples that expose some specific aspects of DBI programming.

simple.rb starts with a line require that loads the DBI module; without this line, the DBI methods would fail. The rest of the script is placed in a build begin/rescue/ensure:

• The block begin encapsulates the processing in the database.
• The clause rescue supports exceptions that might be waived; it obtains and displays information about errors.
• The clause ensure ensures that the script closes any open connections to the database server.

The method connect establishes the connection to the database server and returns a reference that is used to communicate with it. The first argument is the name of a Data Source Name (DSN ) that specifies the name of the driver (which is Mysql for MySQL), the name of the default database, and the host name of the server. The following two arguments are the username and password of the MySQL account used.

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

% ruby simple.rb
Server version: 5.1.14-beta-log

Errors cause exceptions to be thrown. Many types of exceptions can be thrown, but most of those related to database errors cause type exceptions to be thrown DatabaseError. The objects of this class of exceptions include the attributes err, errstr and state which represent the error number, an explanatory error message and SQLSTATE. simple.rb prints the number and error message for database exceptions and ignores other types of exceptions. If another type of exception is raised, it is passed to Ruby for processing.

The simple.rb script closes the connection to the server by calling the method disconnect. This is done in a clause ensure to ensure that the connection is closed even if an error occurs while processing the request.

---

Processing SQL queries:-

---

Ruby DBI offers many ways to execute queries. This section looks at some of them, but there are others.
Many examples use a named table people that has the following structure:

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

---

Processing queries that do not return a result:-

---

Queries that do not return rows can be executed by invoking do the database reference method . This method takes the query as a string and returns the number of rows affected by the query. The following example calls do several times to create the table people and populate it with a small set of data:

bh.do ("DROP TABLE IF EXISTS people")
dbh.do ("CREATE TABLE people (
          id INT UNSIGNED NOT NULL AUTO_INCREMENT,
          CHAR Name (20) NOT NULL,
          FLOAT size,
          PRIMARY KEY (id)) ")
rows = dbh.do ("INSERT INTO people (name, size)
               VALUES
                 ( 'Wanda', 160),
                 (Robert ', 190),
                 ( 'Phillipe', 182),
                 ( 'Sarah', 172) ")
puts "Number of inserted lines: # {rows}"

For the query INSERT, this script gets the number of rows affected and displays it to indicate the number of rows added to the table.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---