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

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 -

• Methods taking code blocks
• Details about the connection to the server
• Error handling and debugging

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

---

Methods taking code blocks:-

---

Reference creation methods can be invoked with code blocks. When executed in this way, they send the reference to the code block as a parameter and automatically clean the reference when the block has been executed:

• DBI.connect generates a reference to the database on which it calls disconnect, if necessary, at the end of the block.
• dbh.prepare generates a reference to a request that it calls finish when the block is terminated. In the block, you must call execute to execute the query.
• dbh.execute is similar except that you do not invoke execute in the block; the reference to the query is executed automatically.

The following example illustrates the use of code blocks with each of these reference creation methods:

# connect can take a block of code, pass it to the reference to the
# database and automatically disconnect the reference to the
# end of the block
DBI.connect ("DBI: Mysql: test: localhost", "testuser", "testpass") do | dbh |
  # prepare can take a block of code, pass it the reference to
  # the query and automatically call 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 block of code, pass it the reference to
  # the query and automatically call finish at the end of the block
  dbh.execute ("SHOW DATABASES") do | sth |
    puts "Databases:" + sth.fetch_all.join (",")
  end
end

There is also a method transaction that accepts code blocks.

---

Details about the connection to the server:-

---

The simple.rb previously seen script connects to the server using the DBI method connect as follows: xdbh = DBI.connect ("DBI: Mysql: test: localhost", "testuser", "testpass")

The first argument to connect is the name of the data source (or DSN for Data Source Name); it identifies the type of connection to be made. The other two parameters are the username and password of your MySQL account.

The DSN can be given in one of these formats:

DBI driver_name
DBI driver_name: db_name: hostname
DBI driver_name: param = val; param = val ...

The DSN always starts with DBI or dbi (in upper or lower case, but not with mixed case) and the name of the driver. For MySQL, the name of the driver is Mysql, and it is best to always use this case. It is stated in the DBI specification that the case in the driver name does not matter, but this is not always the case until DBI versions as recent as the 0.0.18. For other drivers, you will need to use the appropriate driver name.

DBI (or dbi) and the name of the driver should always be given in the DSN. If nothing follows the name of the driver, the driver can (I think) try to connect using a base name and a default host. The second format requires two values, a base name and a host name, separated by two periods ( :) . The third format allows a list of parameters to be specified after the second pair of points (which is necessary), in param = value semicolon-separated ( ; ) format . The following DSNs are all equivalent:

DBI: mysql: test: localhost
DBI: mysql: host = localhost; database = test
DBI: mysql: database = test; host = localhost

The DSN syntax that uses the format param=value is the most flexible because it allows you to specify the parameters in any order. It also allows to pass specific parameters to the driver, if it accepts such parameters. For MySQL, many of these parameters match the parameters of the C API function mysql_real_connect:

• host = host_name: the host on which the MySQL server is running.
• database = base_name: the name of the database.
• port = port_number: the TCP / IP port number, for connections that are not made on localhost.
• flag = name: flag to rise.

MySQL programs can read the options from a configuration file as described in the MySQL Reference Manual. Two DSN settings allow Ruby DBI scripts to use this capability:

• mysql_read_default_file = file_name: Read the options only from this configuration file.
• mysql_read_default_group = group_name: Read the options from the group option [group_name]
• (and from the group [client] ifgroup_namedifferent fromclient`).

If no option is passed, the configuration files are not used. If only mysql_read_default_group passed, the options are read from the standard configuration file (such as .my.cnf in your home directory or /etc/my.cnf in Unix). The following example shows how to connect using all the options in the [client]standard configuration file group :

dsn = "DBI: Mysql: mysql_read_default_group = client"
dbh = DBI.connect (dsn, nil, nil)

Other DSN options:

• mysql_compression = {0 | 1}: Enable or disable compression in the client / server protocol. By default, prohibits compression.
• mysql_client_found_rows = {0 | 1}: By default, MySQL returns the number of rows changed for queries that modify rows. You can use mysql_client_found_rows to ask the server to get back from the list, if they were modified or not. For example, by default, the following query returns a number of changed lines of 0 because no value has changed in the lines: UPDATE t SET id = id ;. With mysql_client_found_rows = 1, the number of lines will be equal to the number of rows in the table.

---

Error handling and debugging:-

---

If a DBI method fails, DBI throws an exception. DBI methods can throw a number of exceptions, but
or database operations, the appropriate class for exceptions is DatabaseError. Objects of this class have three attributes called err, errstr and state which represent the error number, a description string and an error code "standard". For MySQL, these values ​​correspond to the return values ​​of the C API functions mysql_errno(), mysql_error() and mysql_sqlstate(). When an exception occurs, you can retrieve these values ​​as follows:

rescue DBI :: DatabaseError => e
  puts "An error has occurred"
  puts "Error Code: # {e.err}"
  puts "Error Message: # {e.errstr}"
  puts "SQLSTATE of the error: # {e.state}"

If your version of the MySQL Ruby modules is old and does not provide the SQLSTATE information, e.state returns nil.
To get debugging information about what your script does when it runs, you can enable tracing. To do this, you must load the module dbi/trace:
require "dbi/trace"

The module dbi/trace is not automatically loaded by the module dbi because it is dependent on version 0.3.3 or later of the AspectR module, which may not be present on your machine.
The module dbi/trace provides a method trace that controls the trace mode and destination of the output:
trace(mode, destination)

The value mode can be 0 (off), 1 , 2, or 3, and the destination must be an object IO. The default values ​​are 2 and STDERR.

trace can be invoked as a class method to affect all later references, or as an object method to take effect on the reference of a driver, a database, or a query. When invoked as an object method, any object derived from this object also inherits the trace configuration. For example, if you enable traces on a reference to a database, references to queries created from now on will inherit the same trace configuration.

Curriculum

Here is a related tutorial which help to understand this whole programming procedure better -

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

---

Posted on Utopian.io - Rewarding Open Source Contributors

---