Detailed tutorial on connecting to the database in Ruby program, ruby Database

Detailed tutorial on connecting to the database in Ruby program, ruby Database

This section describes how to use Ruby to access databases. The Ruby DBI module provides database-independent interfaces similar to the Perl DBI module for Ruby scripts.

DBI is the Database independent interface, which represents the Ruby-independent Database interface. DBI provides an abstraction layer between Ruby code and underlying databases, allowing you to easily switch databases. It defines a series of methods, variables, and specifications, and provides a database-independent consistent interface.

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 on the backend. Whether you are using Oracle, MySQL, or 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 independent from the database and provides a series of public access methods. The method is used regardless of the database server type.
• Database Driver (DBD) layer. This layer depends on databases. Different drivers provide access to different database engines. MySQL, PostgreSQL, InterBase, and Oracle use different drivers. Each driver is responsible for interpreting the requests from the DBI layer and ing these requests to the requests of the database server of the given type.

Prerequisites

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

This module is a DBD that can be downloaded from the http://www.tmtm.org/en/mysql/ruby.
Obtain and install Ruby/DBI

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

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

Before starting installation, make sure that you have the root permission. Now, install the following steps:
Step 1

$ tar zxf dbi-0.2.0.tar.gz

Step 2

Go to the directory dbi-0.2.0 and use the setup. rb script in the directory for configuration. The most common configuration command is not followed by any parameters after the config parameter. By default, this command is used to install all drivers.

$ ruby setup.rb config

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

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

Step 3

The last step is to create a drive and run the following command to install it:

$ 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 named TESTDB.
• You have created the table "EMPLOYEE" in TESTDB.
• This strap has the following fields: FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
• Set the user ID "testuser" and password "test123" to access TESTDB.
• You have installed the Ruby module DBI correctly on your machine.
• You have read the MySQL tutorial and understood basic MySQL operations.

The following is an instance connecting to the MySQL database "TESTDB:

#! / usr / bin / ruby -w
 
require "dbi"
 
begin
    # Connect to MySQL server
    dbh = DBI.connect ("DBI: Mysql: TESTDB: localhost",
             "testuser", "test123")
    # Get server version string and display
    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 you run this script, the following results will be generated on the Linux machine.
Server version: 5.0.45

If a connection has a data source, the Database Handle is returned and saved to dbh for later use. Otherwise, dbh is set to nil value, e. err and e: errstr Return Error Codes and error strings respectively.

Finally, before exiting this program, make sure to close the database connection and release resources.
INSERT operation

To create a record in a database table, you must use the INSERT operation.

Once a database connection is established, we can use the do method, the prepare method, and the execute method to create a table or create a record inserted into the data table.
Use do statements

Statements that do not return rows can be processed by calling the do database processing method. This method carries 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 a record and INSERT it 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 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

Use prepare and execute

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

To create a record, follow these steps:

Prepare an SQL statement with an INSERT statement. This will be done by using the prepare method.
Execute the SQL query and select all results from the database. This will be done by using the execute method.
Release statement handle. This will be done by using the finish API.
If everything goes smoothly, commit this operation; otherwise, you can roll back to complete the transaction.

The syntax for using these two methods is as follows:

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

These two methods can be used to pass the bind value to the SQL statement. Sometimes the entered value may not be provided in advance. In this case, the bound value is used. Use question mark (?) Replace the actual value. The actual value is passed through the execute () API.

The following instance 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', 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 multiple INSERT statements are used at the same time, it is much more efficient to prepare a statement and then execute it multiple times in a loop than to call do each time through a loop.
READ operation

READ operations on any database refer to obtaining useful information from the database.

Once a database connection is established, we can prepare to query the database. We can use the do, prepare, and execute methods to obtain values from the database table.

To obtain a record, follow these steps:

• Prepare an SQL query based on the required conditions. This will be done by using the prepare method.
• Execute the SQL query and select all results from the database. This will be done by using the execute method.
• Obtain results one by one and output these results. This will be done by using the fetch method.
• Release statement handle. This will be done by using the finish method.

The following instance queries all records whose salaries exceed 1000 from 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 ("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 produces the following results:

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 also many ways to retrieve records from the database. If you are interested, you can view the Ruby DBI Read operation.
Update operation

The UPDATE operation on any database refers to updating one or more existing records in the database. The following instance updates all records whose SEX is 'M. Here, we will increase the AGE of all male by one year. There are three steps:

• Prepare an SQL query based on the required conditions. This will be done by using the prepare method.
• Execute the SQL query and select all results from the database. This will be done by using the execute method.
• Release statement handle. This will be done by using the finish method.

If everything goes smoothly, commit this operation; otherwise, you can roll back to complete 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 :: 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

DELETE is required when you want to DELETE records from the database. The following instance deletes all records whose AGE exceeds 20 from EMPLOYEE. The procedure is as follows:

• Prepare an SQL query based on the required conditions. This will be done by using the prepare method.
• Execute an SQL query to delete the required records from the database. This will be done by using the execute method.
• Release statement handle. This will be done by using the finish method.
• If everything goes smoothly, commit this operation; otherwise, you can roll back to complete 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 (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

Execute transactions

Transactions are a mechanism to ensure transaction consistency. Transactions should have the following four attributes:

• Atomicity: the Atomicity of a transaction refers to the fact that the program contained in a transaction acts as the logical unit of work of the database, and all the data modification operations it performs are either performed, or not at all.
• Consistency: Transaction Consistency means that the database must be consistent before and after a transaction is executed. If the database status meets all integrity constraints, the database is consistent.
• Isolation: the Isolation of transactions means that concurrent transactions are isolated from each other, that is, operations within a transaction and the data being operated must be blocked, it is not seen by other transactions that attempt to make changes.
• Durability: Transaction Persistence means that when the system or media fails, the updates of committed transactions cannot be lost. That is, once a transaction is committed, its changes to the data in the database should be permanent and can withstand any database system faults. Durability is ensured through database backup and recovery.

DBI provides two transaction execution methods. A commit or rollback method is used to commit or roll back a transaction. Another method is the transaction method, which can be used to implement transactions. Next we will introduce these two simple methods for implementing transactions:
Method I

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

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 simple because it requires a code block that contains a transaction statement. The transaction method executes the block and automatically calls commit or rollback based on whether the block is successfully executed:

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

Commit is an operation that identifies that the database has been changed. After this operation, all the changes cannot be recovered.

The following is a simple example of calling the commit method.

dbh.commit

ROLLBACK operation

If you are not satisfied with one or more changes and want to completely restore them, use the rollback method.

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

dbh.rollback

Disconnect a database

To disconnect a database, use the disconnect API.

dbh.disconnect

If the user closes the database connection through the disconnect method, DBI rolls back all unfinished transactions. However, your application can call commit or rollback explicitly without relying on any implementation details of DBI.
Handling error

There are many different error sources. For example, when an SQL statement is executed, the syntax is incorrect, the connection fails, or the fetch method is called for 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: DatabaseError.

The Exception objects of these classes have three attributes: err, errstr, and state. Table sharding represents the error code, a descriptive error string, and a standard error code. The attributes are described as follows:

• Err: returns the integer representation of the error. If DBD is not supported, nil is returned. For example, Oracle DBD returns the numeric section of the ORA-XXXX error message.
• Errstr: returns the string representation of the error.
• State: return the SQLSTATE code of the error. SQLSTATE is a five-character string. Most dbds do not support it, So nil is returned.

In the above example, you have read 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 content during script execution, you can enable tracking. To do this, you must first download the dbi/trace module and then call the trace method to control the trace mode and output destination:

Copy codeThe Code is as follows:
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.
Code block of the Method

There are some methods to create a handle. These methods are called through code blocks. The advantage of using code blocks with methods is that they provide a handle as a parameter for the code block and automatically clear the handle when the block ends. Below are some examples to help you understand this concept.

• DBI. connect: This method generates a database handle. We recommend that you call disconnect at the end of the block to disconnect the database.
• Dbh. prepare: This method generates a statement handle. We recommend that you call finish at the end of the block. Within the block, you must call the execute method to execute the statement.
• Dbh.exe cute: This method is similar to dbh. prepare, but dbh.exe cute does not need to call the execute method in the block. The statement handle is automatically executed.

Instance 1

DBI. connect can contain a code block, which transmits a database handle to it and automatically disconnects the handle at the end of the block.
Dbh = DBI. connect ("DBI: Mysql: TESTDB: localhost ",
"Testuser", "test123") do | dbh |
Instance 2

Dbh. prepare can carry a code block, pass the statement handle to it, 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

Instance 3

Dbh.exe cute can contain a code block, pass the statement handle to it, and 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 contain a code block, which has been described in the previous chapter.
Functions and attributes of a specific driver

DBI allows the database driver to provide additional functions for a specific database. These functions can be called by the user through the func method of any Handle object.

You can use the [] = or [] method to set or obtain attributes of a specific driver.

DBD: Mysql implements the following 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 :: DatabaseError => e
   puts "An error occurred"
   puts "Error code: # {e.err}"
   puts "Error message: # {e.errstr}"
ensure
   dbh.disconnect if dbh
end

This produces the following results:

5.0.45
50045
Localhost via UNIX socket
10
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