A detailed tutorial on connecting to a database in a Ruby program _ruby topics

This section will show you how to access a database using Ruby. The Ruby DBI module provides a database-independent interface for Ruby scripts similar to the Perl DBI module.

DBI, the Database independent interface, represents a Ruby-independent interface to databases. DBI provides an abstraction layer between Ruby code and the underlying database, allowing you to simply implement database switching. It defines a series of methods, variables, and specifications that provide a consistent database interface that is independent 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, or Informix, you can use DBI. This is clearly illustrated in the following architectural diagram.

Ruby DBI General architecture uses two tiers:

• Database Interface (DBI) layer. This layer is independent of the database and provides a wide range of public access methods, using the database server type.
• Database driven (DBD) layer. The layer is dependent on the database, and different drivers provide access to different database engines. MySQL, PostgreSQL, InterBase, Oracle and so on, respectively, using a different driver. Each driver is responsible for interpreting requests from the DBI layer and mapping these requests to requests that apply to a given type of database server.

Prerequisite

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

The module is a DBD that can be downloaded from the http://www.tmtm.org/en/mysql/ruby/.
Get and install RUBY/DBI

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

http://rubyforge.org/projects/ruby-dbi/

Before you start the installation, make sure that you have root permissions. Now, install the following steps for installation:
Step 1

$ tar zxf dbi-0.2.0.tar.gz

Step 2

Enter the directory dbi-0.2.0 and configure using the SETUP.RB script in the directory. The most commonly used configuration command is the config parameter and does not follow any parameters. This command is configured to install all drivers by default.

$ Ruby setup.rb Config

More specifically, you can use the--with option to list the specific sections you want to use. For example, if you only want to configure the primary 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 set up the drive and install it using the following command:

$ ruby setup.rb Setup
$ ruby setup.rb Install

Database connection

Suppose we are using a MySQL database, before connecting to the database, make sure that:

• You have created a database TestDB.
• You have created table EMPLOYEE in TestDB.
• The table has fields first_name, last_name, age, SEX, and income.
• Set user ID "TestUser" and password "test123" to access TestDB
• The Ruby module DBI has been installed correctly on your machine.
• You have read the MySQL tutorial to understand the MySQL basics.

The following is an example of a "testdb" connection to the MySQL database:

#!/usr/bin/ruby-w
 
require "DBI"
 
begin
   # Connect to MySQL server
   DBH = Dbi.connect ("DBI:Mysql:TESTDB:localhost" ,
            "TestUser", "test123")
   # Gets the server version string, and displays
   row = Dbh.select_one ("Select VERSION ()")
   puts "server Version: "+ row[0]
rescue DBI::D atabaseerror => e
   puts" An error occurred "
   puts" error code:  #{e.e RR} "
   puts" Error message: #{e.errstr} "
ensure
   # Disconnect from server
   dbh.disconnect if DBH
end

When you run this script, the following results will be generated on the Linux machine.
Server version:5.0.45

If a connection is established with a data source, the database handle (DB Handle) is returned and saved to DBH for subsequent use, otherwise DBH is set to nil value, and E.err and E::errstr return the error code and error string, respectively.

Finally, before exiting this program, make sure that you close the database connection and release the resources.
INSERT Operation

INSERT operation is required when you want to create records in a database table.

Once a database connection is established, we can prepare to create a table using the Do method or the prepare and execute methods or create records inserted into a datasheet.
use do statement

Statements that do not return rows can be handled by calling the Do database processing method. The 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 () not NULL,
   last_ NAME Char, age
   INT, 
   SEX CHAR (1),
   income FLOAT) ");

Similarly, you can execute SQL INSERT statements to create records to insert into the EMPLOYEE table.

#!/usr/bin/ruby-w
 
require "DBI"
 
begin
   # Connect to MySQL server
   DBH = Dbi.connect ("DBI:Mysql:TESTDB:localhost" ,
            "TestUser", "test123")
   dbh.do ("INSERT into the EMPLOYEE (first_name,
          last_name, age
          ,
      SEX,
      Income)
     VALUES (' Mac ', ' Mohan ', ', ' M ', Watts) ')
   puts "record has been created"
   Dbh.commit rescue
DB I::D atabaseerror => e
   puts "An error occurred"
   puts "error code:  #{e.err}"
   puts "error message: # {e.errstr} '
   dbh.rollback
ensure
   # Disconnects from server
   dbh.disconnect if DBH
end

Using Prepare and Execute

You can use the DBI prepare and Execute methods to execute SQL statements in Ruby code.

The steps to create a record are as follows:

Prepares an SQL statement with an INSERT statement. This will be done by using the Prepare method.
Execute the SQL query and select all the results from the database. This is done by using the Execute method.
Releases the statement handle. This is done by using the finish API.
If all goes well, commit the operation, or you can rollback the transaction.

The following are the syntax for using these two methods:

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

Both of these methods can be used to pass the bind value to the SQL statement. Sometimes the value that is entered may not be given beforehand, in which case the bound value is used. Use the question mark (?) instead of the actual value, and the actual value is passed through the Execute () API.

The following example creates two records in the EMPLOYEE table:

#!/usr/bin/ruby-w
 
require "DBI"
 
begin
   # Connect to 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 ', ' Poul ', ', ' M ', 2300)
   sth.execute (' Zara ', ' Ali ', ', ' F ', 1000)
   sth.finish
   Dbh.commit
   puts "record has been created" rescue DBI
::D atabaseerror => 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 at the same time, it is much more efficient to prepare a statement and then execute it multiple times in a loop than through loops each time you call do.
READ Operation

A READ operation on any database refers to obtaining useful information from the database.

Once the database connection is established, we can prepare to query the database. We can use the Do method or the prepare and execute methods to get a value from a database table.

The steps for getting records are as follows:

• Prepare the SQL query based on the conditions you want. This will be done by using the Prepare method.
• Execute the SQL query and select all the results from the database. This is done by using the Execute method.
• Get the results individually and output the results. This is done by using the Fetch method.
• Releases the statement handle. This is done by using the Finish method.

The following example queries the EMPLOYEE table for records that have more than 1000 of all wages (salary).

#!/usr/bin/ruby-w
 
require "DBI"
 
begin
   # Connect to 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 "Name:%s, last Name:%s\n", row[0], row[1]
    printf "Age:%d, Sex:%s\n", row[2], row[3]
    printf "s Alary:%d \ n \ row[4]
   end
   sth.finish
rescue DBI::D atabaseerror => e
   puts "An error occurred" C17/>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 results:

Name:mac, last Name:mohan
age:20, Sex:m salary:2000-A-Name:john
, last
 
Name:poul
Ag e:25, sex:m
salary:2300

There are many ways to get records from a database, and if you're interested, you can view the Ruby DBI Read operation.
Update operation

An update operation on any database refers to updating one or more existing records in the database. The following instance updates all records for SEX ' M '. Here, we will increase the age of all males by one year. This will be divided into three steps:

• Prepare the SQL query based on the conditions you want. This will be done by using the Prepare method.
• Execute the SQL query and select all the results from the database. This is done by using the Execute method.
• Releases the statement handle. This is done by using the Finish method.

If all goes well, commit the operation, or you can rollback the transaction.

#!/usr/bin/ruby-w
 
require "DBI"
 
begin
   # Connect to 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::D atabaseerror => 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

The delete operation is required when you want to delete records from the database. The following instance deletes all records with age over 20 from the EMPLOYEE. The steps for this operation are as follows:

• Prepare the SQL query based on the conditions you want. This will be done by using the Prepare method.
• Execute the SQL query to delete the required records from the database. This is done by using the Execute method.
• Releases the statement handle. This is done by using the Finish method.
• If all goes well, commit the operation, or you can rollback the transaction.

#!/usr/bin/ruby-w
 
require "DBI"
 
begin
   # Connect to MySQL server
   DBH = Dbi.connect ("DBI:Mysql:TESTDB:localhost" ,
            "TestUser", "test123")
   sth = Dbh.prepare ("DELETE from EMPLOYEE
            WHERE age >?")
   Sth.execute (m)
   sth.finish
   dbh.commit
rescue DBI::D atabaseerror => 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 a transaction

Transactions are a mechanism for ensuring the consistency of transactions. Transactions should have the following four properties:

• Atomicity (atomicity): The atomicity of a transaction means that the program contained in the transaction acts as a logical unit of work for the database, and that it does all of the data modification operations or is not executed at all.
• Consistency (consistency): The consistency of a transaction means that the database must be in a consistent state before and after a transaction is executed. If the state of the database satisfies all the integrity constraints, the database is said to be consistent.
• Isolation (Isolation): The isolation of a transaction means that concurrent transactions are isolated from each other, that is, the operations within a transaction and the data being manipulated must be blocked from being seen by other transactions attempting to modify them.
• Persistence (Durability): The persistence of a transaction means ensuring that updates to committed transactions cannot be lost when a system or media failure occurs. That is, once a transaction is committed, its changes to the data in the database should be permanent and can withstand any database system failure. Persistence is guaranteed through database backup and recovery.

DBI provides two ways to perform transactions. One is a commit or rollback method for committing or rolling back a transaction. There is also a transaction method that can be used to implement transactions. Let's go through the two simple ways to implement transactions:
Method I

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

dbh[' autocommit ' = false # setting is automatically submitted to false.
Begin
 Dbh.do ("update employee set age = age+1
     WHERE first_name = ' John '")
 dbh.do ("update employee set age = AG E+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 simple because it requires a block of code that consists of a transaction statement. The transaction method executes the block, and then automatically invokes the commit or rollback according to whether the block was executed successfully:

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

A Commit is an operation that identifies a database that has been changed, and all changes are not recoverable after this operation.

The following is a simple instance that invokes the Commit method.

Dbh.commit

ROLLBACK operation

If you are dissatisfied with one or more of the changes, and you want to fully recover the changes, use the Rollback method.

The following is a simple instance of calling the rollback method.

Dbh.rollback

Disconnecting a database

To disconnect your database, use the Disconnect API.

Dbh.disconnect

If the user closes the database connection through the Disconnect method, DBI rolls back all outstanding transactions. However, you do not need to rely on any DBI implementation details, and your application can make a good explicit invocation of commit or rollback.
Handling Errors

There are many different sources of error. For example, the syntax error when executing an SQL statement, or the connection failure, or the Fetch method is invoked on a canceled or completed statement handle.

If a DBI method fails, DBI throws an exception. The DBI method throws any type of exception, but the two most important exception classes are Dbi::interfaceerror and DBI::D atabaseerror.

The Exception objects of these classes have the Err, ERRSTR, and state three attributes, and the table represents the error number, a descriptive error string, and a standard error code. The attributes are specified as follows:

• ERR: Returns an integer representation of the error that occurred and returns nil if the DBD does not support it. For example, Oracle DBD returns the number portion of a ora-xxxx error message.
• Errstr: Returns a string representation of the error that occurred.
• State: Returns the SQLSTATE code for the error that occurred. SQLSTATE is a five character length string. Most of the DBD does not support it, so it will return to nil.

In the example above you have looked at the following code:

Rescue DBI::D atabaseerror => e
   puts "An error occurred"
   puts "error code:  #{e.err}"
   puts "error Message: #{e.errstr} "
   dbh.rollback
ensure
   # Disconnect from server
   dbh.disconnect if DBH
end

You can enable tracing to obtain debugging information about script execution content while the script is executing. To do this, you must first download the Dbi/trace module, and then call the trace method that controls the trace mode and the output destination:

Copy Code code as follows:

Require "Dbi/trace"
..............

Trace (mode, destination)

The value of mode can be 0 (off), 1, 2, or 3,destination value should be an IO object. The default values are 2 and STDERR respectively.
Method's code block

There are some ways to create handles. These methods are invoked through a code block. The advantage of using code blocks with methods is that they provide a handle as an argument for a block of code that automatically clears the handle when the block terminates. Here are some examples to help you understand this concept.

• Dbi.connect: This method generates a database handle, it is recommended to call disconnect at the end of the block to disconnect the database.
• Dbh.prepare: This method generates a statement handle and recommends that finish be called at the end of the block. Within a 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 need to call the Execute method within the block. The statement handle is automatically executed.

Instance 1

Dbi.connect can take a block of code, pass the database handle to it, and automatically disconnect the handle at the end of the block.
DBH = Dbi.connect ("DBI:Mysql:TESTDB:localhost",
"TestUser", "test123") do |dbh|
Instance 2

Dbh.prepare can take a block of code, pass a statement handle to it, and automatically call finish at the end of a block.

Dbh.prepare ("Show DATABASES") do |sth|
    Sth.execute
    puts "Databases:" + sth.fetch_all.join (",")
end

Instance 3

Dbh.execute can take a block of code, pass a statement handle to it, and automatically call finish at the end of a block.

Dbh.execute ("Show DATABASES") do |sth|
  Puts "Databases:" + sth.fetch_all.join (",") End

The DBI transaction method can also carry a block of code, as explained in the previous section.
Functions and properties for specific drivers

DBI allows the database driver to provide additional functions for specific databases that can be invoked by the user through the Func method of any Handle object.

Use the []= or] method to set or get properties for a particular driver.

Dbd::mysql implements the following specific driver functions:

Instance

#!/usr/bin/ruby
 
require "DBI"
begin
  # Connect to MySQL server
  DBH = Dbi.connect ("DBI:Mysql:TESTDB:localhost",
            "TestUser", "test123")
  Puts Dbh.func (: Client_info) puts Dbh.func (: client_version) puts Dbh.func (:
  host_info) puts Dbh.func (:
  Proto_info) puts Dbh.func (: Server_info) puts Dbh.func (: thread_id) puts Dbh.func
  (: stat)
Rescue DBI::D atabaseerror => e
  puts "An error occurred"
  puts "error code:  #{e.err}"
  puts "error message : #{e.errstr} "
ensure
  dbh.disconnect if DBH
end

This will produce the following results:

5.0.45
50045
Localhost via UNIX socket
5.0.45
150621
uptime:384981 threads:1 questions:1101078 slow queries:4 \
opens:324 Flush tables:1 Open tables:64 \
queries per second avg:2.860