6.33. Ruby Database access-DBI tutorial

6.33.1. 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.

6.33.2. 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 

6.33.3. 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.

6.33.4. 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 

6.33.5. 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.

6.33.6. 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 

6.33.7. 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 

6.33.8. 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 

6.33.9. 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 

6.33.10. 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 

6.33.11. 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 .

6.33.12. 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 .

6.33.13. 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 

6.33.14. 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