Ruby Database access-DBI tutorial

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. The following architecture diagram clearly illustrates this.

The general architecture of Ruby DBI uses two layers:

• Database Interface (DBI) layer. This layer is independent of the database and provides a series of public access methods, regardless of the type of database server.

• Database driven (DBD) layer. This layer is dependent on the database, and different drivers provide access to different database engines. MySQL, PostgreSQL, InterBase, Oracle and so on use different drivers. Each driver is responsible for interpreting requests from the DBI layer and mapping themto requests applicable to a given type of database server.

Installation

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

# Ubuntusudoapt-getinstallmysql-clientsudoapt-getinstalllibmysqlclient15-dev#
Centosyuminstallmysql-devel

MYSQL=/usr/local/mysql/binexportPATH=$PATH:$MYSQLexportDYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

sudoln-s/usr/local/mysql/lib/libmysqlclient.18.dylib/usr/lib/libmysqlclient.18.dylib

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

ruby extconf.rb

or

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

or

ruby extconf.rb --with-mysql-config

make

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

ruby setup.rb config

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

ruby setup.rb setup
ruby setup.rb install

Database connection

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

• You have created a database TESTDB.

• You have created the table EMPLOYEE in TESTDB.

• The table has fields FIRST_NAME 、 LAST_NAME , AGE, SEX and INCOME.

• Set the user ID “testuser” and password “test123” to access TESTDB

• The Ruby module DBI has been installed correctly on your machine.

• You have seen the MySQL tutorials and understood the basics of MySQL.

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

Example

#!/usr/bin/ruby -w

require "dbi"

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

When you run this script, the following results will be produced on the Linux machine.

Server version: 5.0.45

If the connection is established with a data source, the database handle (Database Handle) is returned and saved to dbh for later use, otherwise``dbh`` will be set to nil value e.err and e::errstr returns the error code and the error string, respectively.

Finally, before quitting the program, be sure to close the database connection and release resources.

INSERT operation

The INSERT operation is needed when you want to create a record in a database table.

Once we have established a database connection, we are ready to use the do method or prepare and execute method to create a table or create a record that is inserted into a data table.

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 )" );

#!/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
     # server disconnect
     dbh.disconnect if dbh
end

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

#!/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
     # server disconnect
     dbh.disconnect if dbh
end

READ operation

For any database READ operation refers to getting useful information from the database.

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

The steps to obtain a record are as follows:

• Prepare the SQL query based on the required conditions. This will be done byusing the prepare method to do it.

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

• Get the results one by one and output them. This will be done by using the fetch method to do it.

• Release the statement handle. This will be done by using the finish method to do it.

The following example is derived from EMPLOYEE query all the records with salary over 1000 in the table.

Example

#!/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
     # server disconnect
     dbh.disconnect if dbh
end

This will produce 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 get records from the database, and if you are interested, you can check the Ruby DBI Read operation.

Update operation

For any database UPDATE an operation refers to updating one or more existing records in the database. The following example updates all records with a SEX of’M’. Here, we will increase the AGE of all men by one year. This will be divided into three steps:

• Prepare the SQL query based on the required conditions. This will be done byusing the prepare method to do it.

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

• Release the statement handle. This will be done by using the finish method to do it.

• If all goes well, then commit this operation, otherwise you can rollback close the deal.

Example

#!/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
     # server disconnect
     dbh.disconnect if dbh
end

DELETE operation

When you want to delete records from the database, you need to use DELETE operation. The following example is derived from EMPLOYEE delete all records with an AGE greater than 20 in. The steps for this operation are as follows:

• Prepare the SQL query based on the required conditions. This will be done byusing the prepare method to do it.

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

• Release the statement handle. This will be done by using the finish method to do it.

• If all goes well, then commit this operation, otherwise you can rollback close the deal.

Example

#!/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
     # server disconnect
     dbh.disconnect if dbh
end

Execute a transaction

A transaction is a mechanism that ensures transaction consistency. A transaction should have the following four attributes:

• Atomicity: the atomicity of a transaction means that the program contained in the transaction, as the logical unit of work of the database, either performs all or no modifications to the data.

• Consistency: the consistency of a transaction means that the database must be in a consistent state before and after the execution of a transaction. Ifthe state of the database satisfies all integrity constraints, the databaseis said to be consistent.

• Isolation: transaction isolation means that concurrent transactions are isolated from each other, that is, operations within a transaction and data being operated must be blocked from being seen by other transactions that attempt to modify.

• Durability: transaction persistence means ensuring that updates to committedtransactions are not lost in the event of a system or media failure. That is, once a transaction is committed, its change to the data in the database should be permanent and can withstand any database system failure. Persistence is guaranteed by database backup and recovery.

DBI provides two ways to perform transactions. One is commit or rollback method to commit or roll back the transaction. And the other is``transaction`` method that can be used to implement a transaction. Next, let’s introduce these two simple ways to implement transactions:

dbh['AutoCommit'] = false # Set automatic submission 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

dbh['AutoCommit'] = false # Set automatic submission 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 completed changes, after which all changes are unrecoverable.

Here is a call to commit a simple example of the.

dbh.commit

ROLLBACK operation

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

Here is a call to rollback a simple example of the.

dbh.rollback

Disconnect the database

To disconnect the database, use the disconnect API .

dbh.disconnect

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

Handling error

There are many different sources of errors. Such as a syntax error when executing a SQL statement, or a connection failure, or a call to a cancelledor completed statement handle fetch method.

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 include err 、 errstr and state the sub table represents 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 ifDBD does not support it nil . For example, Oracle DBD returns ORA-XXXX the numeric part of the error message.

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

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

In the above example, 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
     # server disconnect
     dbh.disconnect if dbh
end

To get debugging information about what the script is executing when it is executed, you can enable tracing. To do this, you must first download dbi/trace module, and then call the trace methods:

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

trace(mode, destination)

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

Code block for method

There are some ways to create handles. These methods are called through the code block. The advantage of using code blocks with methods is that they provide a handle to the code block as a parameter, and the handle is automatically cleared when the block terminates. Here are some examples to help you understand this concept.

• DBI.connect :This method generates a database handle, which is recommended to be called at the end of the block disconnect to disconnect the database.

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

• dbh.execute :This method is similar to that of dbh.prepare similar, but dbh.execute . There is no need to call within the block execute method. The statement handle executes automatically.

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

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

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

Functions and properties of a specific driver

DBI allows the database driver to provide additional database-specific functions that can be used by the user through any Handle object’s func method is called.

Use []= or [] method can set or get the properties of a specific driver.

The following DBD::Mysql driver-specific functions are implemented:

#!/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 will produce 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