Ruby DBI Database Access Tutorial

SQLite

SQLRelay

DBI application architecture

• DBI is independent of any database available in the background. Whether you use Oracle, MySQL, Informix, you can use DBI. The following architectural diagram clearly illustrates this point.

• Ruby DBI generally uses two layers:

database interface (DBI) layer. This layer is independent of the database and provides a set of public access methods that are not type-specific to the database server.

database driver (DBD) layer. This layer is dependent on the database, and different drivers provide access to different database engines. MySQL, PostgreSQL, InterBase, Oracle, etc., use different drivers. Each driver is responsible for interpreting requests from the DBI layer and mapping them to requests suitable for the given type of database server.

Installation

If you want to write Ruby scripts to access MySQL databases, you need to install the Ruby MySQL module first.
client-Install Mysql development package-get install mysql
client-sudo apt15-get install libmysqlclient
　
dev
yum install mysql-devel

Mac OS system needs to modify ~/.bash_profile or ~/Add the following code to the .profile file:

MYSQL=/usr/local/mysql/bin
export PATH=$PATH:$MYSQL
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

Or use a symbolic link:

sudo ln　-s　/usr/local/mysql/lib/libmysqlclient.18.dylib　/usr/lib/libmysqlclient.18.dylib

Install DBI using RubyGems (recommended)

RubyGems was approximately created in2003year11month, from Ruby 1.9version became part of the Ruby standard library. More details can be found at：Ruby RubyGems

Install dbi and dbd using gem-mysql：

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

Install using source code (Ruby version less than1.9using this method)

This module is a DBD, available from http://tmtm.org/downloads/mysql/ruby/ Download.

After downloading the latest package, unzip it into the directory and run the following command to install:

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

Then compile:

make

Get and install Ruby/DBI

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

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

Before starting the installation, please ensure that you have root privileges. Now, please install the following steps to install:

Step 1

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

or directly download the zip package and unzip it.

Step 2

to enter the directory ruby-dbi-master, use setup.rb script for configuration. The most commonly used configuration command is to not follow any parameters with the config parameter. The command defaults to installing all drivers.

ruby setup.rb config

More specifically, you can use --The with option lists the specific parts you want to use. For example, if you only want to configure the main 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 driver, install it using the following command:

ruby setup.rb setup
ruby setup.rb install

Database connection

Assuming we are using the MySQL database, please ensure the following before connecting to the database:

• 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 already viewed the MySQL tutorial and understood the basic operations of MySQL.

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

Online example

#!/usr/bin/ruby　-w
　
require "dbi"
　
begin
　　　　　# Connect to MySQL server
　　　　　dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",　
　　　　　　　　　　　　　　　　　　　　　　　"testuser", "test123")
　　　　　# Retrieve the 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 this script is run, the following results will be produced on a Linux machine.

Server version:　5.0.45

If a data source is provided when establishing the connection, then return the database handle (Database Handle) and save it to dbh for subsequent use, otherwise dbh will be set to the nil value,e.err and e::errstr respectively return error codes and error strings.

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

INSERT operation

When you want to create records in a database table, you need to use the INSERT operation.

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

using the do statement

For statements that do not return rows, you can call do Database handling method. This 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(20) NOT NULL,
　　　　　LAST_NAME CHAR(20),
　　　　　AGE INT,　　
　　　　　SEX CHAR(1);
　　　　　INCOME FLOAT)";

Similarly, you can execute SQL INSERT

Online example

#!/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 DBI's prepare and execute method to execute SQL statements in Ruby code.

The steps to create a record are as follows:

• prepare an SQL statement with an INSERT statement. This will be done by using prepare method to complete.

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

• Release the statement handle. This will be done by using finish API to complete.

• If everything goes well, then commit If this operation is not performed, you can rollback Complete the transaction.

The syntax for using these two methods is as follows:

Online example

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

These two methods can be used to bind values to the SQL statement. Sometimes, the input values may not be provided in advance, in which case, bound values are used. Use question marks (?）to replace actual values, which are passed through the execute() API.

The following example creates two records in the EMPLOYEE table:

Online example

#!/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 INSERTs are used simultaneously, it is much more efficient to prepare a statement first and then execute it multiple times in a loop than to call do each time in a loop.

READ operation

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

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

steps to retrieve records:

• Prepare SQL queries based on the required conditions. This will be done by using prepare method to complete.

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

• Iteratively retrieve the results and output these results. This will be done by using fetch method to complete.

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

The following example queries all salaries (salary) over 1000 records.

Online 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
　　　　　# Disconnect from the server
　　　　　dbh.disconnect if dbh
end

This will produce the following result:

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 retrieve records from the database. If you are interested, you can check 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 example updates all records with SEX as 'M'. Here, we will increase the AGE of all males by one year. This will be divided into three steps:

• Prepare SQL queries based on the required conditions. This will be done by using prepare method to complete.

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

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

• If everything goes well, then commit If this operation is not performed, you can rollback Complete the transaction.

Online 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
　　　　　# Disconnect from the server
　　　　　dbh.disconnect if dbh
end

DELETE operation

When you want to delete records from the database, you need to use the DELETE operation. The following example deletes records from EMPLOYEE where AGE is over 20 records. The steps for this operation are as follows:

• Prepare SQL queries based on the required conditions. This will be done by using prepare method to complete.

• Execute SQL queries to delete the required records from the database. This will be done by using execute method to complete.

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

• If everything goes well, then commit If this operation is not performed, you can rollback Complete the transaction.

Online 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
　　　　　# Disconnect from the server
　　　　　dbh.disconnect if dbh
end

Execute the transaction

A transaction is a mechanism to ensure the consistency of transactions. A transaction should have the following four properties:

• Atomicity (Atomicity):The atomicity of a transaction means that the program contained in the transaction is considered as a logical unit of work in the database, and the data modification operations it performs must either be executed entirely or not executed at all.

• Consistency (Consistency):The consistency of a transaction refers to the fact that the database must be in a consistent state before and after the execution of a transaction. If the state of the database satisfies all the integrity constraints, then the database is consistent.

• Isolation (Isolation):The isolation of a transaction refers to the fact that concurrent transactions are isolated from each other, that is, the operations inside a transaction and the data being operated on must be locked up, and not seen by other transactions that attempt to modify it.

• Durability (Durability):The durability of a transaction means that when a system or medium fails, the updates that have been committed cannot be lost. That is, once a transaction is committed, the changes it makes to the data in the database should be permanent and withstand any database system failure. Durability is guaranteed through database backups and recovery.

DBI provides two methods for executing transactions. One is commit or rollback Method, used to commit or rollback transactions. There is also another 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 DBI's commit and rollback Method to explicitly commit or cancel a transaction:

Online example

dbh['AutoCommit'] = false  # Set auto-commit 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 transaction Method. This method is relatively simple because it requires a code block that constitutes the transaction statement.transaction method execution block, and then automatically call based on whether the block is executed successfully or not. commit or rollback:

Online example

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

Below is a call commit A simple example of the method.

dbh.commit

ROLLBACK operation

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

Below is a call rollback A simple example of the method.

dbh.rollback

Disconnect the database

To disconnect the database connection, please use the disconnect API.

dbh.disconnect

If the user closes the database connection through the disconnect method, DBI will roll back all incomplete transactions. However, you do not need to rely on any DBI implementation details, and your application can call commit or rollback explicitly.

Error Handling

There are many different sources of errors. For example, syntax errors when executing SQL statements, or connection failures, or calling the fetch method on a canceled or completed statement handle.

If a DBI method fails, DBI will throw an exception. DBI methods can throw any type of exception, but the two most important exception classes are DBI::InterfaceError and DBI::DatabaseError.

These classes of Exception objects have err,errstr and state Three attributes, which respectively represent the error number, a descriptive error string, and a standard error code. The specific descriptions are as follows:

• err：Return the integer representation of the error that occurred, or return nil if the DBD does not support it. nil. For example, Oracle DBD returns ORA-XXXX The numeric part of the error message.

• errstr：Return the string representation of the error that occurred.

• state：Return the SQLSTATE code of the error that occurred. SQLSTATE is a five-character-length string. Most DBDs do not support it and will return nil.

In the above examples, you have already 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
　　　　　# Disconnect from the server
　　　　　dbh.disconnect if dbh
end

To obtain debugging information about the script execution content during script execution, you can enable tracing. To do this, you must first download dbi/After calling the trace module, call the control tracing mode and output destination. trace method:

require 'dbi/trace"
..............
　
trace(mode, destination)

The value of mode can be 0(off),1,2 or 3, 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 handles. These methods are called through code blocks. The advantage of using code blocks with methods is that they provide handles as parameters to the code block, which are automatically cleared when the block ends. Here are some examples to help understand this concept.

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

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

• 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 will be executed automatically.

Example 1

DBI.connect It can also have a code block, passing the database handle to it, and it will automatically disconnect the handle at the end of the block.

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

Example 2

dbh.prepare It can also have a code block, passing the statement handle to it, and it will automatically call finish at the end of the block.

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

Example 3

dbh.execute It can also have a code block, passing the statement handle to it, and it will automatically call finish at the end of the block.

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

DBI transaction Methods can also have a code block, which has been explained in the previous chapter.

Functions and properties of specific drivers

DBI provides additional specific database functions to the database driver, which can be accessed by users through any Handle object. func methods to make the call.

Use []= or [] Methods can set or get attributes of the specific driver.

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

Online example

#!/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 result:

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