Ruby Database Access - DBI tutorial

Ruby Database Access - DBI tutorial

DBI application architecture

The DBI is independent of any database available in the background. W hether you're using Oracle, MySQL, Informix, you can use DBI. The following architectural diagram clearly illustrates this point.

Ruby DBI's general architecture uses two layers:

• Database Interface (DBI) layer. This layer is database-independent and provides a range of public access methods that use no database server type.
• Database-driven (DBD) layer. T he layer is dependent on the database, and different drivers provide access to different database engines. M ySQL, PostgreSQL, InterBase, Oracle, and more use different drivers. Each driver is responsible for interpreting requests from the DBI layer and mapping them to requests that apply to a given type of database server.

Prerequisite

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

The module is a DBD that can be downloaded from http://www.tmtm.org/en/mysql/ruby/ the database.

Get and install Ruby/DBI

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

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

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

Step 1

$ tar zxf dbi-0.2.0.tar.gz

Step 2

Go to directory dbi-0.2.0 and configure it in the directory using the setup.rb script. T he most common configuration command is the config parameter, which is not followed by any parameters. The command is configured by default to install all drivers.

$ ruby setup.rb config

More specifically, you can use the --with option to list specific sections that you want to use. For example, if you only want to configure the main DBI module and MySQL DBD layer drivers, 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 commands:

$ ruby setup.rb setup
$ ruby setup.rb install

The database connection

Assuming we're 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 the password "test123" to access the TESTDB
• Ruby module DBI has been properly installed on your machine.
• You've seen the MySQL tutorial to understand the basics of MySQL.

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

#!/usr/bin/ruby -w

require "dbi"

begin
     # 连接到 MySQL 服务器
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", 
                       "testuser", "test123")
     # 获取服务器版本字符串，并显示
     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
     # 断开与服务器的连接
     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 is returned and saved to dbh for subsequent use, otherwise dbh is set to nil values, e.err and e::errstr return error codes and error strings, respectively.

Finally, before exiting this program, be sure to close the database connection and free up resources.

INSERT operation

Insert operations are required when you want to create records in a database table.

Once a database connection is established, we are ready to create tables or records inserted into data tables using the do or prepare and execute methods.

Use the do statement

Statements that do not return rows can be handled by calling the do database. The method has a statement string argument and returns the number of rows that the statement affects.

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 SQL INSERT statements to create records inserted into the EMPLOYEE table.

#!/usr/bin/ruby -w

require "dbi"

begin
     # 连接到 MySQL 服务器
     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
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

Use prepare and execute

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

Here's how to create a record:

• Prepare a SQL statement with an INSERT statement. This will be done by using the prepare method.
• Perform SQL queries to select all the results from the database. This will be done by using the execute method.
• Release the statement handle. This will be done by using the finish API.
• If all goes well, commit does so, otherwise you can rollback to complete the deal.

Here's the syntax for using both methods:

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

Both methods can be used to pass bind values to SQL statements. S ometimes the value entered may not be given in advance, in which case the binding value is used. U se a question mark ? Instead of the actual value, 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
     # 连接到 MySQL 服务器
     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
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

If you use 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 to call do each time through a loop.

READ operation

READ operations on any database are those that obtain useful information from the database.

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

Here's how to get the record:

• Prepare SQL queries based on the conditions you want. This will be done by using the prepare method.
• Perform SQL queries to select all the results from the database. This will be done by using the execute method.
• Get the results one by one and output them. This will be done by using the fetch method.
• Release the statement handle. This will be done by using the finish method.

The following example queries all records with salaries of more than 1000 from the EMPLOYEE table.

#!/usr/bin/ruby -w

require "dbi"

begin
     # 连接到 MySQL 服务器
     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
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

This results in the following:

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 ways to get records from a database, and if you're interested, you can check out ruby DBI Read operations.

Update action

AN UPDATE operation on any database is the updating of one or more existing records in the database. T he following instance updates all records with SEX as 'M'. H ere, we will increase the AGE for all men by one year. This is divided into three steps:

• Prepare SQL queries based on the conditions you want. This will be done by using the prepare method.
• Perform SQL queries to select all the results from the database. This will be done by using the execute method.
• Release the statement handle. This will be done by using the finish method.
• If all goes well, commit does so, otherwise you can rollback to complete the deal.

#!/usr/bin/ruby -w

require "dbi"

begin
     # 连接到 MySQL 服务器
     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
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

DELETE operation

When you want to delete records from the database, you need to use the DELETE operation. T he following instance removes all records with AGE exceeding 20 from THE. Here's how to do it:

• Prepare SQL queries based on the conditions you want. This will be done by using the prepare method.
• Perform SQL queries to remove the required records from the database. This will be done by using the execute method.
• Release the statement handle. This will be done by using the finish method.
• If all goes well, commit does so, otherwise you can rollback to complete the deal.

#!/usr/bin/ruby -w

require "dbi"

begin
     # 连接到 MySQL 服务器
     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
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

The transaction is executed

Transactions are a mechanism for ensuring transaction consistency. The transaction should have four properties:

• Atomicity: Atomicity of a transaction refers to a program contained in a transaction that acts as a logical unit of work for a database and does either all or no data modification operations.
• Consistency: Transaction consistency means that the database must be in a consistency state both before and after a transaction is executed. I f the state of the database meets all integrity constraints, the database is consistent.
• Isolation: Isolation of a transaction means that a transaction is isolated from each other, that is, the operation within a transaction and the data being operated must be blocked from being seen by other transactions attempting to modify it.
• Persistence: The persistence of a transaction means that when a system or media fails, ensure that updates to the committed transaction are not lost. T hat is, once a transaction is committed, its changes to the data in the database should be permanent and withstand any database system failure. Persistence is guaranteed through database backup and recovery.

DBI provides two ways to execute transactions. O ne is the commit or rollback method, which commits or rolls back transactions. A nother is the transaction method, which can be used to implement transactions. Let's look at these two simple ways to implement transactions:

Method I

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

   dbh['AutoCommit'] = false # 设置自动提交为 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. T his approach is relatively simple because it requires a block of code that makes up the transaction statement. The transaction method executes the block, and then automatically calls commit or rollback depending on whether the block executes successfully:

   dbh['AutoCommit'] = false # 设置自动提交为 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 action that identifies that a database has completed changes, after which all changes are not recoverable.

Here is a simple example of calling the commit method.

     dbh.commit

ROLLBACK operation

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

Here is a simple example of calling the rollback method.

     dbh.rollback

Disconnect the database

To disconnect the database, use the disconnect API.

    dbh.disconnect

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

Handling errors

There are many different sources of error. For example, a syntax error when executing a SQL statement, or a failed connection, or a fetch method is called on a statement handle that has been canceled or completed.

If a DBI method fails, the 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 err, errstr, and state properties, and the sub-tables represent error numbers, a descriptive error string, and a standard error code. The properties are specified below:

• err: Returns the integer notation of the error that occurred, and returns nil if DBD is not supported. For example, Oracle DBD returns the numeric portion of the ORA-XXXX error message.
• errstr: Returns the string notation of the error that occurred.
• State: Returns the SQLSTATE code for the error that occurred. S QLSTATE is a five-character string. Most DBDs do not support it, so nil is returned.

In the example above, you've 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
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

In order to get debugging information about what the script executes when it executes, you can enable tracing. To do this, you must first download the dbi/trace module and then call the trace method that controls the trace 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 defaults are 2 and STDERR, respectively.

The block of code for the method

There are some ways to create handles. T hese methods are called through blocks of code. T he advantage of using blocks of code with methods is that they provide a handle to the block as an argument, which is automatically cleared when the block terminates. Here are some examples to help you understand the concept.

• DBI.connect: This method generates a database handle and recommends calling disconnect at the end of the block to break the database.
• dbh.prepare: This method generates a statement handle that recommends calling 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 need to call the execute method within a block. Statement handles are executed automatically.

Instance 1

DBI.connect can have a block of code that passes a database handle to it and automatically breaks the handle at the end of the block.

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

Instance 2

dbh.prepare can have a block of code that passes a statement handle to it and automatically calls 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.execute can have a block of code that passes a statement handle to it and automatically calls 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 come with a block of code, as explained in the previous section.

Functions and properties of a particular driver

DBI lets the database driver provide additional database-specific functions that can be called by the user through the func method of any Handle object.

You []= get [] a specific driver by using the method of . .

DBD:: Mysql implements functions for the following specific drivers:

序号	函数 & 描述
1	dbh.func(:createdb, db_name) 创建一个新的数据库。
2	dbh.func(:dropdb, db_name) 删除一个数据库。
3	dbh.func(:reload) 执行重新加载操作。
4	dbh.func(:shutdown) 关闭服务器。
5	dbh.func(:insert_id) => Fixnum 返回该连接的最近 AUTO_INCREMENT 值。
6	dbh.func(:client_info) => String 根据版本返回 MySQL 客户端信息。
7	dbh.func(:client_version) => Fixnum 根据版本返回客户端信息。这与 :client_info 类似，但是它会返回一个 fixnum，而不是返回字符串。
8	dbh.func(:host_info) => String 返回主机信息。
9	dbh.func(:proto_info) => Fixnum 返回用于通信的协议。
10	dbh.func(:server_info) => String 根据版本返回 MySQL 服务器端信息。
11	dbh.func(:stat) => Stringb> 返回数据库的当前状态。
12	dbh.func(:thread_id) => Fixnum 返回当前线程的 ID。

#!/usr/bin/ruby

require "dbi"
begin
   # 连接到 MySQL 服务器
   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 results in the following:

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