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

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 -

• Transaction Support
• Access to specific driver capabilities
• Other DBI treats

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.

• You can obtain the Ruby DBI module as well as specification documents from the RubyForge DBI site: http://rubyforge.org/projects/ruby-dbi/ .
• The Ruby AspectR module must be installed if you want to use the module dbi/trace that provides traces of the DBI executions. You can get AspectR from its SourceForge site: http://aspectr.sourceforge.net/ .
• The Ruby homepage provides a lot of information about Ruby himself: http://www.ruby-lang.org/ .
• MySQL can be obtained at: http://www.mysql.com/ .

Difficulty

• Intermediate

Tutorial Contents

---

Transaction Support:-

---

DBI provides an abstraction of transactions. However, the availability of this abstraction is conditioned by the support of transactions by the database engine and the DBD implementation of this abstraction in the driver. For the MySQL driver, this abstraction does not work before version 0.0.19 , so you must perform the transactions explicitly using the queries that control the auto-commit level, commits, and rollbacks. For example :

dbh.do ("SET AUTOCOMMIT = 0")
dbh.do ( "BEGIN")
... queries that make up the transaction ...
dbh.do ( "COMMIT")

For versions 0.0.19 and later, you can use transaction abstraction with MySQL. An aspect of this abstraction allows you to set the auto-commit level that assigns AutoCommit the reference attribute to the database:

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

When auto-commit is disabled (when given the value false), transactions can be performed in two ways. The following examples illustrate these two approaches, using the tablecompte in which funds are transferred from one person to another:

The first approach uses DBI methods commit and rollback explicitly confirms or cancels 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 "the transaction failed"
  dbh.rollback
end
dbh ['AutoCommit'] = true

The second approach uses the method transaction. This is simpler because this method supports the block of code that contains the transactions that make up the transaction. The method transaction executes the block and then calls commi tor rollback automatically depending on whether the block succeeded or failed:

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
dbh ['AutoCommit'] = true

---

Access to specific driver capabilities:-

---

DBI provides a database reference method functhat pilots can call to make base-dependent features available. For example, the MySQL C API provides a function mysql_insert_id() that returns the last value of AUTO_INCREMENT a connection. The Ruby MySQL module provides a bridge to this function via its basic reference method insert_id and DBD::Mysql, in turn, provides access insert_idthrough the mechanism of the DBI function func.

The first argument of func is the name of the database-specific method that you want to use; the other arguments are those required by the method. The method insert_id does not require any other arguments, so to recover the AUTO_INCREMENT most recent one, follow these steps:

dbh.do ("INSERT INTO people (name, size) VALUES ('Mike', 70.5)")
id = dbh.func (: insert_id)
puts "ID of new records: # {id}"

Other specific methods supported by DBD::Mysql are:

dbh.func (: createdb, dbname) Creates a new database
dbh.func (: dropdb, dbname) Clears a database
dbh.func (: reload) Performs a reload operation
dbh.func (: shutdown) Stops the server

The methods createdbanddropdb` are not available unless your MySQL client library is derived from an older version of MySQL 4 (they correspond to the functions the Ruby MySQL module no longer supports since version 4 of MySQL).

As of DBI 0.1.1 , a number of other methods func are available. They correspond to several functions of the MySQL C API:

String = dbh.func (: client_info)
Fixnum = dbh.func (: client_version)
String = dbh.func (: host_info)
String = dbh.func (: info)
Fixnum = dbh.func (: proto_info)
String = dbh.func (: server_info)
String = dbh.func (: stat)
Fixnum = dbh.func (: thread_id)

In some cases, using specific driver functions may offer benefits, even if there is another way to do the same thing. For example, the value returned by the function insert_id of DBD::Mysql can be obtained by executing the query SELECT LAST_INSERT_ID(). Both return the same value in most cases. However, the call to insert_id is more efficient because it returns a value that is stored on the client side and can be accessed without executing a query. This benefit in efficiency has a cost: you have to pay more attention to the way you use this function. Its value is reset after each executed query so you must access it after each query that generates a valueAUTO_INCREMENTbut before you execute any other query. When with him,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 values AUTO_INCREMENT.

---

Other DBI treats:-

---

The module DBI::Utils contains some interesting methods:

DBI::Utils::measure takes a block of code and measures the time needed to execute it. You can use this method to measure the execution time of a query as follows:

elapsed = DBI :: Utils :: measure do
  dbh.do (stmt)
end
puts "Query: # {stmt}"
puts "Elapsed time: # {elapsed}"

The module DBI::Utils::TableFormatter includes a method ascii for displaying the contents of a result. The first argument is an array of column names, and the second is an array of column objects. To view the contents of the table people, do the following:

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 result is as follows:

id	name	size
1	Wanda	160
2	Robert	190
3	Phillipe	182
4	Sarah	172

he module DBI::Utils::XMLFormatter includes the methods row and table to display rows of a result or a whole result in the form of XML. This makes trivial XML generation for a given result. The following example implements the method table:
BI :: Utils :: XMLFormatter.table (dbh.select_all ("SELECT * FROM people"))

The result is as follows:

<? xml version = "1.0" encoding = "UTF-8"?>
<Rows>
<Row>
  <Id> 1 </ id>
  <Name> Wanda </ name>
  <Height> 160 </ height>
</ Row>
<Row>
  <Id> 2 </ id>
  <Name> Robert </ name>
  <Height> 190 </ height>
</ Row>
<Row>
  <Id> 3 </ id>
  <Name> Phillipe </ name>
  <Height> 182 </ height>
</ Row>
<Row>
  <Id> 4 </ id>
  <Name> Sarah </ name>
  <Height> 172 </ height>
</ Row>
</ Rows>

Methods ascii and table support additional arguments providing better control over the output format and destination.

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
• How to write scripts based on Ruby DBI -Issue#3

---

Posted on Utopian.io - Rewarding Open Source Contributors

---