Ruby Database Access - DBI Tutorial

This chapter will guide you on how to access databases using Ruby. The Ruby DBI module provides a database-independent interface for Ruby scripts, similar to the Perl DBI module.

DBI stands for Database Independent Interface, representing a database-independent interface for Ruby. DBI provides an abstraction layer between Ruby code and the underlying database, allowing for easy database switching. It defines a set of methods, variables, and conventions that provide a consistent database interface regardless of the database.

DBI can interact with the following:

• ADO (ActiveX Data Objects)
• DB2
• Frontbase
• mSQL
• MySQL
• ODBC
• Oracle
• OCI8 (Oracle)
• PostgreSQL
• Proxy/Server
• SQLite
• SQLRelay

DBI Application Architecture

DBI is independent of any database available in the background. Whether you are using Oracle, MySQL, Informix, you can use DBI. The architecture diagram below clearly illustrates this.

The general architecture of Ruby DBI uses two layers:

• Database Interface (DBI) layer. This layer is database-independent and provides a set of common access methods that are not specific to any database server.
• Database Driver (DBD) layer. This layer is database-dependent. Different drivers provide access to different database engines. MySQL, PostgreSQL, InterBase, Oracle, etc., use different drivers. Each driver is responsible for interpreting requests from the DBI layer and mapping these requests to the appropriate requests for the given type of database server.

Installation

If you want to write Ruby scripts to access a MySQL database, you need to install the Ruby MySQL module first.

Installing MySQL Development Package

# Ubuntu
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev

# Centos
yum install mysql-devel

For Mac OS, you need to modify the ~/.bash_profile or ~/.profile file and add the following code:

MYSQL=/usr/local/mysql/bin
export PATH=$PATH:$MYSQL
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

Alternatively, use a symbolic link:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

Installing DBI using RubyGems (Recommended)

RubyGems was created around November 2003 and became part of the Ruby standard library from Ruby 1.9 onwards. For more details, see Ruby RubyGems.

Install dbi and dbd-mysql using gem:

sudo gem install dbi
sudo gem install mysql
sudo gem install dbd-mysql

Installing from Source (For Ruby versions less than 1.9)

This module is a DBD and can be downloaded from http://tmtm.org/downloads/mysql/ruby/.

After downloading the latest package, unzip and enter the directory, then run the following commands to install:

ruby extconf.rb

or

ruby extconf.rb --with-mysql-dir=/usr/local/mysql

or

ruby extconf.rb --with-mysql-config

Then compile:

make

Getting and Installing Ruby/DBI

You can download and install the Ruby DBI module from the following link:

https://github.com/erikh/ruby-dbi

Before starting the installation, ensure you have root privileges. Now, follow the steps below to install:

Step 1

git clone https://github.com/erikh/ruby-dbi.git

Alternatively, download the zip package and unzip it.

Step 2

Enter the directory ruby-dbi-master and use the setup.rb script for configuration. The most common configuration command is to run config without any parameters. This command defaults to configuring all drivers.

ruby setup.rb config

For more specific configurations, you can use the --with option to list the specific parts you want to use. For example, if you only want to configure the main DBI module and the MySQL DBD layer driver, enter the following command:

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

Step 3

The final step is to build the drivers using the following commands:

ruby setup.rb setup
ruby setup.rb install

Database Connection

Assuming we are using a MySQL database, before connecting to the database, ensure that:

• You have created a database TESTDB.
• You have created a table EMPLOYEE in TESTDB.
• The table has fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
• The user ID "testuser" and password "test123" are set to access TESTDB.
• The Ruby DBI module is correctly installed on your machine.
• You have read the MySQL tutorial and understand the basics of MySQL operations.

Here is an example of connecting to the MySQL database "TESTDB":

Example

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     # Get the 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 the server
     dbh.disconnect if dbh
end

When this script is run, it will produce the following result on a Linux machine:

Server version: 5.0.45

If the connection is made with a data source, it returns a database handle (Database Handle) and saves it in dbh for subsequent use; otherwise, dbh is set to nil, and e.err and e::errstr return the error code and error string, respectively.

Finally, before exiting the program, ensure that the database connection is closed and resources are freed.

INSERT Operation

When you want to create records in a database table, you need to use the INSERT operation.

Once the database connection is established, we can prepare to create tables or records in the table using the do method or the prepare and execute methods.

Using do Statement

Statements that do not return rows can be executed by calling the do database handle method. This method takes a statement string parameter and returns the number of rows affected by the statement.

dbh.do("DROP TABLE IF EXISTS EMPLOYEE")
dbh.do("CREATE TABLE EMPLOYEE (
     FIRST_NAME  CHAR(20) NOT NULL,
     LAST_NAME  CHAR(20),
     AGE INT,  
     SEX CHAR(1),
     INCOME FLOAT )" );

Similarly, you can execute the SQL INSERT statement to create records in the EMPLOYEE table.

Example

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME,
                   LAST_NAME, 
                   AGE, 
         SEX, 
         INCOME)
          VALUES ('Mac', 'Mohan', 20, 'M', 2000)" )
     puts "Record has been created"
     dbh.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # Disconnect from the server
     dbh.disconnect if dbh
end

Using prepare and execute

You can use DBI's prepare and execute methods to execute SQL statements in Ruby code.

The steps to create records are as follows:

• Prepare an SQL statement with the INSERT statement. This is done using the prepare method.
• Execute the SQL query to select all results from the database. This is done using the execute method.
• Release the statement handle. This is done using the finish API.
• If everything goes smoothly, commit the operation; otherwise, you can rollback the transaction.

Here is the syntax for using these methods:

Example

sth = dbh.prepare(statement)
sth.execute
   ... zero or more SQL operations ...
sth.finish

These methods can be used to pass bind values to the SQL statement. Sometimes the input values may not be pre-given, in which case bind values are used. Use question marks (?) in place of the actual values, and pass the actual values through the execute() API.

The following example creates two records in the EMPLOYEE table:

Example

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME,
                   LAST_NAME, 
                   AGE, 
         SEX, 
         INCOME)
                   VALUES (?, ?, ?, ?, ?)" )
     sth.execute('John', 'Doe', 30, 'M', 5000)
     sth.execute('Jane', 'Doe', 25, 'F', 4500)
     sth.finish
     dbh.commit
     puts "Records created"
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # Disconnect from the server
     dbh.disconnect if dbh
end

sth.execute('John', 'Poul', 25, 'M', 2300)
sth.execute('Zara', 'Ali', 17, 'F', 1000)
sth.finish
dbh.commit
puts "Record has been created"
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code:    #{e.err}"
puts "Error message: #{e.errstr}"
dbh.rollback
ensure
# Disconnect from the server
dbh.disconnect if dbh
end

If you are using multiple INSERTs, it is more efficient to prepare a statement once and then execute it multiple times in a loop than to call do each time through the loop.

READ Operation

The READ operation on any database means to fetch some useful information from the database.

Once the database connection is established, we are ready to query the database. We can use either do method or prepare and execute methods to fetch values from the database tables.

The steps for fetching records are as follows:

• Prepare an SQL query based on the required conditions. This will be done using the prepare method.

• Execute the SQL query to select all results from the database. This will be done using the execute method.

• Fetch the results one by one and output them. This will be done using the fetch method.

• Release the statement handle. This will be done using the finish method.

The following example fetches all the records from the EMPLOYEE table where the salary is greater than 1000.

Example

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     sth = dbh.prepare("SELECT * FROM EMPLOYEE 
                        WHERE INCOME > ?")
     sth.execute(1000)

     sth.fetch do |row|
        printf "First Name: %s, Last Name : %s\n", row[0], row[1]
        printf "Age: %d, Sex : %s\n", row[2], row[3]
        printf "Salary :%d \n\n", row[4]
     end
     sth.finish
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
ensure
     # Disconnect from the server
     dbh.disconnect if dbh
end

This will produce the following result:

First Name: Mac, Last Name : Mohan
Age: 20, Sex : M
Salary :2000

First Name: John, Last Name : Poul
Age: 25, Sex : M
Salary :2300

There are many methods to fetch records from the database, and if you are interested, you can check Ruby DBI Read Operations.

Update Operation

The UPDATE operation on any database means to update one or more records already available in the database. The following example updates all records with SEX as 'M'. Here, we will increase the AGE of all males by one year. This involves the following steps:

• Prepare an SQL query based on the required conditions. This will be done using the prepare method.

• Execute the SQL query to select all results from the database. This will be done using the execute method.

• Release the statement handle. This will be done using the finish method.

• If everything goes fine, then commit this operation; otherwise, you can roll back the complete transaction.

Example

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1
                        WHERE SEX = ?")
     sth.execute('M')
     sth.finish
     dbh.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # Disconnect from the server
     dbh.disconnect if dbh
end

DELETE Operation

When you want to delete records from a database, you need to perform a DELETE operation. The following example deletes all records from EMPLOYEE where AGE is more than 20. The steps for a DELETE operation are as follows:

• Prepare an SQL query based on the required conditions. This will be done using the prepare method.

• Execute the SQL query to delete the required records from the database. This will be done using the execute method.

• Release the statement handle. This will be done using the finish method.

• If everything goes fine, then commit this operation; otherwise, you can roll back the complete transaction.

Example

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     sth = dbh.prepare("DELETE FROM EMPLOYEE 
                        WHERE AGE > ?")
     sth.execute(20)
     sth.finish
     dbh.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # Disconnect from the server
     dbh.disconnect if dbh
end

Performing Transactions

Transactions are a mechanism that ensures data consistency. Transactions have the following four properties:

• Atomicity: Atomicity of a transaction means that it should either be completed in all its parts or none at all.

• Consistency: Consistency in a transaction means that the database should be consistent before the transaction starts and after it ends.

• Isolation: Isolation of a transaction means that the intermediate state of a transaction is invisible to other transactions.

• Durability: Durability means that once a transaction is committed, it will remain so, even in the event of system failure.

DBI provides two methods to perform transactions. One is using commit or rollback methods to explicitly commit or cancel the transaction. The other is using the transaction method, which can be used to implement transactions. Let's introduce these two simple methods to perform transactions:

Method I

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

Example

dbh['AutoCommit'] = false # Set AutoCommit to false.
   begin
     dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 
             WHERE FIRST_NAME = 'John'")
     dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 
             WHERE FIRST_NAME = 'Zara'")
     dbh.commit
   rescue
     puts "transaction failed"
     dbh.rollback
   end
   dbh['AutoCommit'] = true

Method II

The second method uses the transaction method. This method is relatively simpler because it requires a block containing the statements that make up the transaction. The transaction method executes the block and then calls commit or rollback automatically depending on whether the block succeeds or fails:

Example

dbh['AutoCommit'] = false # Set AutoCommit to false
   dbh.transaction do |dbh|
     dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 
             WHERE FIRST_NAME = 'John'")
     dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 
             WHERE FIRST_NAME = 'Zara'")
   end
   dbh['AutoCommit'] = true

COMMIT Operation

The COMMIT operation is an operation that indicates that the database has finished making changes, and after this operation, no changes can be reverted.

Here is a simple example calling the commit method.

dbh.commit

ROLLBACK Operation

If you are not satisfied with one or more of the changes and you want to revert back those changes completely, then use the rollback method.

Here is a simple example calling the rollback method.

dbh.rollback

Disconnecting the Database

To disconnect the database connection, use the disconnect API.

dbh.disconnect

If the user closes the database connection by using the disconnect method, DBI rolls back any uncommitted transaction. However, instead of depending on any DBI implementation details, your application should call either commit or rollback explicitly.

Handling Errors

There are many different sources of errors. For example, a syntax error in an SQL statement, a connection failure, or calling the fetch method for an already canceled or finished statement handle.

sth.execute('John', 'Poul', 25, 'M', 2300)
sth.execute('Zara', 'Ali', 17, 'F', 1000)
sth.finish
dbh.commit
puts "Record has been created"
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code:    #{e.err}"
puts "Error message: #{e.errstr}"
dbh.rollback
ensure
# Disconnect from the server
dbh.disconnect if dbh
end

If a DBI method fails, DBI throws an exception. DBI methods can throw any type of exception, but the two most important exception classes are DBI::InterfaceError and DBI::DatabaseError.

These classes' Exception objects have three attributes: err, errstr, and state, which respectively represent the error number, a descriptive error string, and a standard error code. The attributes are described as follows:

• err: Returns the integer representation of the error that occurred, or nil if the DBD does not support it. For example, Oracle DBD returns the numeric part of the ORA-XXXX error message.

• errstr: Returns the string representation of the error that occurred.

• state: Returns the SQLSTATE code for the error. SQLSTATE is a five-character string. Most DBDs do not support it, so it returns nil.

In the example above, you have seen the following code:

rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # Disconnect from the server
     dbh.disconnect if dbh
end

To obtain debugging information about script execution, you can enable tracing. To do this, you must first download the dbi/trace module, then call the trace method to control the tracing mode and output destination:

require "dbi/trace"
..............

trace(mode, destination)

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

Method Code Blocks

Some methods create handles. These methods are called with a code block. The advantage of using a code block with a method is that they provide the handle as an argument to the block and automatically clean up the handle when the block terminates. Below are some examples to help understand this concept.

• DBI.connect: This method generates a database handle, and it is recommended to call disconnect at the end of the block to disconnect from the database.

• dbh.prepare: This method generates a statement handle, and it is recommended to call finish at the end of the block. Within the block, you must call the execute method to execute the statement.

• dbh.execute: This method is similar to dbh.prepare, but dbh.execute does not require calling the execute method within the block. The statement handle is automatically executed.

Example 1

DBI.connect can take a code block, passing it the database handle, and it will automatically disconnect the handle at the end of the block.

dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                  "testuser", "test123") do |dbh|

Example 2

dbh.prepare can take a code block, passing it the statement handle, and it will automatically call finish at the end of the block.

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

Example 3

dbh.execute can take a code block, passing it the statement handle, and it will automatically call finish at the end of the block.

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

The DBI transaction method can also take a code block, which has been explained in the previous section.

Specific Driver Functions and Attributes

DBI allows database drivers to provide additional database-specific functions, which can be called by the user via the func method of any Handle object.

Specific driver attributes can be set or retrieved using the []= or [] methods.

DBD::Mysql implements the following driver-specific functions: