YouTipRuby Database Access
# Ruby Database Access – DBI Tutorial
This section will explain how to use Ruby to access databases. The _Ruby DBI_ module provides a database-independent interface for Ruby scripts, similar to the Perl DBI module.
DBI stands for Database Independent Interface, representing Ruby's database-independent interface. DBI provides an abstraction layer between Ruby code and the underlying database, allowing you to easily switch between databases. It defines a series of methods, variables, and specifications, providing a consistent, database-independent 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 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:
* The Database Interface (DBI) layer. This layer is database-independent and provides a set of common access methods, regardless of the database server type.
* The Database Driver (DBD) layer. This layer is database-dependent, with different drivers providing access to different database engines. MySQL, PostgreSQL, InterBase, Oracle, etc., each use a different driver. Each driver is responsible for interpreting requests from the DBI layer and mapping them to requests suitable for the given type of database server.
If you want to write a Ruby script to access a MySQL database, you need to install the Ruby MySQL module first.
### Installing the MySQL Development Package
```bash
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev
yum install mysql-devel
For Mac OS systems, you need to modify the `~/.bash_profile` or `~/.profile` file and add the following code:
```bash
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:
```bash
sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
### Installing DBI with RubyGems (Recommended)
RubyGems was created around November 2003 and has been part of the Ruby standard library since Ruby 1.9. For more details, see: (#)
Use `gem` to install `dbi` and `dbd-mysql`:
```bash
sudo gem install dbi
sudo gem install mysql
sudo gem install dbd-mysql
### Installing from Source (For Ruby versions less than 1.9)
This module is a DBD and can be downloaded from [http://tmtm.org/downloads/mysql/ruby/](http://tmtm.org/downloads/mysql/ruby/).
After downloading the latest package, extract it, enter the directory, and execute the following commands to install:
```bash
ruby extconf.rb
or
```bash
ruby extconf.rb --with-mysql-dir=/usr/local/mysql
or
```bash
ruby extconf.rb --with-mysql-config
Then compile:
```bash
make
**Obtaining and Installing Ruby/DBI**
You can download and install the Ruby DBI module from the following link:
[https://github.com/erikh/ruby-dbi](https://github.com/erikh/ruby-dbi)
Before starting the installation, please ensure you have root privileges. Now, follow the steps below to install:
**Step 1**
```bash
git clone https://github.com/erikh/ruby-dbi.git
Or download the zip package directly and extract it.
**Step 2**
Enter the directory `ruby-dbi-master` and use the `setup.rb` script for configuration. The most common configuration command is the `config` parameter without any arguments. This command configures the installation of all drivers by default.
```bash
ruby setup.rb config
More specifically, you can use the `--with` option to list 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:
```bash
ruby setup.rb config --with=dbi,dbd_mysql
**Step 3**
The final step is to build the driver and install it using the following commands:
```bash
ruby setup.rb setup
ruby setup.rb install
Assuming we are using a MySQL database, before connecting to the database, please ensure:
* You have created a database named `TESTDB`.
* You have created a table named `EMPLOYEE` in `TESTDB`.
* The table has fields `FIRST_NAME`, `LAST_NAME`, `AGE`, `SEX`, and `INCOME`.
* You have set up a user ID "testuser" with password "test123" to access `TESTDB`.
* The Ruby DBI module is correctly installed on your machine.
* You have read the MySQL tutorial and understand the basic MySQL operations.
Here is an example of connecting to the MySQL database "TESTDB":
## Example
```ruby
require "dbi"
begin
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row
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 this script is run, it will produce the following result on a Linux machine:
Server version: 5.0.45
If the connection is established with a data source, a database handle is returned and stored in **dbh** for subsequent use. Otherwise, **dbh** will be set to `nil`. `_e.err_` and `_e.errstr_` return the error code and error string, respectively.
Finally, before exiting this program, please ensure you close the database connection and release resources.
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 create tables or insert records into tables using the **do** method or the **prepare** and **execute** methods.
## Using the `do` Statement
Statements that do not return rows can be executed by calling the **do** database handle method. This method takes a statement string parameter and returns the number of rows affected by that statement.
```ruby
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 in the EMPLOYEE table.
## Example
```ruby
require "dbi"
begin
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
## Using `prepare` and `execute`
You can use DBI's _prepare_ and _execute_ methods to execute SQL statements in Ruby code.
The steps for creating records are as follows:
* Prepare the SQL statement with an INSERT statement. This is done using the **prepare** method.
* Execute the SQL query to select all results from the database. This is done using the **execute** method.
* Release the statement handle. This is done using the **finish** API.
* If everything goes smoothly, **commit** the operation; otherwise, you can **rollback** to complete the transaction.
Here is the syntax for using these two methods:
## Example
```ruby
sth = dbh.prepare(statement)
sth.execute
... zero or more SQL operations ...
sth.finish
These two methods can be used to pass **bind** values to SQL statements. Sometimes the input values may not be known in advance. In such cases, bind values are used. Use question marks (**?**) in place of the actual values, which are passed via the `execute()` API.
The following example creates two records in the EMPLOYEE table:
## Example
```ruby
require "dbi"
begin
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 are performing multiple INSERTs, it is much more efficient to prepare a statement once and execute it multiple times in a loop than to call `do` repeatedly in a loop.
A READ operation on any database refers to fetching useful information from the database.
Once a database connection is established, we can prepare to query the database. We can use the **do** method or the **prepare** and **execute** methods to fetch values from database tables.
The steps for fetching records are as follows:
* Prepare the SQL query based on the desired conditions. This is done using the **prepare** method.
* Execute the SQL query to select all results from the database. This is done using the **execute** method.
* Fetch the results one by one and output them. This is done using the **fetch** method.
* Release the statement handle. This is done using the **finish** method.
The following example queries all records from the EMPLOYEE table where the salary (INCOME) is greater than 1000.
## Example
```ruby
require "dbi"
begin
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 : %sn", row, row
printf "Age: %d, Sex : %sn", row, row
printf "Salary :%d nn", row
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 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 more methods for fetching records from the database. If you are interested, you can check [**Ruby DBI Read Operations**](#).
An UPDATE operation on any database refers to updating one or more existing records in the database. The following example updates all records where SEX is 'M'. Here, we will increase the AGE of all males by one year. This will be done in three steps:
* Prepare the SQL query based on the desired conditions. This is done using the **prepare** method.
* Execute the SQL query to select all results from the database. This is done using the **execute** method.
* Release the statement handle. This is done using the **finish** method.
* If everything goes smoothly, **commit** the operation; otherwise, you can **rollback** to complete the transaction.
## Example
```ruby
require "dbi"
begin
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
When you want to delete records from the database, you need to use the DELETE operation. The following example deletes all records from EMPLOYEE where AGE is greater than 20. The steps for this operation are:
* Prepare the SQL query based on the desired conditions. This is done using the **prepare** method.
* Execute the SQL query to delete the required records from the database. This is done using the **execute** method.
* Release the statement handle. This is done using the **finish** method.
* If everything goes smoothly, **commit** the operation; otherwise, you can **rollback** to complete the transaction.
## Example
```ruby
require "dbi"
begin
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
A transaction is a mechanism to ensure the consistency of transactions. Transactions should have the following four properties:
* **Atomicity:** The atomicity of a transaction means that the program contained in the transaction acts as a logical unit of work for the database. The data modification operations it performs are either all executed or not executed at all.
* **Consistency:** The consistency of a transaction means that the database must be in a consistent state before and after the transaction execution. If the database state satisfies all integrity constraints, the database is said to be consistent.
* **Isolation:** The isolation of a transaction means that concurrent transactions are isolated from each other. That is, the operations within a transaction and the data being operated on must be locked and not visible to other transactions attempting to modify them.
* **Durability:** The durability of a transaction means that when a system or media failure occurs, the updates of committed transactions are guaranteed not to be lost. That is, once a transaction is committed, its changes to the data in the database should be permanent and withstand any database system failure. Durability is ensured through database backups and recovery.
DBI provides two methods for executing transactions. One is the _commit_ or _rollback_ method, used to commit or roll back a transaction. The other 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 DBI's _commit_ and _rollback_ methods to explicitly commit or cancel a transaction:
## Example
```ruby
dbh['AutoCommit'] = 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 simpler because it requires a code block containing the statements that make up the transaction. The _transaction_ method executes the block and then automatically calls _commit_ or _rollback_ depending on whether the block executed successfully:
## Example
```ruby
dbh['AutoCommit'] = 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 is an operation that indicates the database has completed its changes, after which all changes are irreversible.
Here is a simple example of calling the **commit** method.
```ruby
dbh.commit
If you are not satisfied with one or several changes and want to completely revert them, use the **rollback** method.
Here is a simple example of calling the **rollback** method.
```ruby
dbh.rollback
To disconnect from the database, use the `disconnect` API.
```ruby
dbh.disconnect
If the user closes the database connection via the `disconnect` method, DBI will roll back all uncommitted transactions. However, it is good practice for your application to explicitly call `commit` or `rollback` without relying on any DBI implementation details.
There are many different sources of errors. For example, syntax errors when executing SQL statements, connection failures, or calling the `fetch` method on a statement handle that has already been cancelled or completed.
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_.
Exception objects of these classes have three attributes: _err_, _errstr_, and _state_, representing the error number, a descriptive error string, and a standard error code, respectively. The attributes are described as follows:
* **err:** Returns the integer representation of the error that occurred. If the DBD does not support it, it returns _nil_. For example, the Oracle DBD returns the numeric part of the _ORA-XXXX_ error message.
* **errstr:** Returns the string representation of the error that occurred.
* **state:** Returns the SQLSTATE code of the error that occurred. SQLSTATE is a five-character string. Most DBDs do not support it, so they return nil.
You have already seen the following code in the examples above:
```ruby
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
To obtain debugging information about the script's execution content, you can enable tracing. To do this, you must first download the `dbi/trace` module, then call the _trace_ method to control the tracing mode and output destination:
```ruby
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, respectively.
There are several ways to create handles. These methods are called via code blocks. The advantage of using code blocks with methods is that they provide the handle as an argument to the block and automatically clean up the handle when the block terminates. Here are some examples to help understand this concept:
* **DBI.connect:** This method generates a database handle. It is recommended to call _disconnect_ at the end of the block to disconnect from the database.
* **dbh.prepare:** This method generates a statement handle. It is recommended to call _finish_ at the end of the block. Inside 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 require calling the `execute` method inside the block. The statement handle is executed automatically.
### Example 1
**DBI.connect** can take a code block, passing the database handle to it, and automatically disconnects the handle at the end of the block.
```ruby
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") do |dbh|
### Example 2
**dbh.prepare** can take a code block, passing the statement handle to it, and automatically calls `finish` at the end of the block.
```ruby
dbh.prepare("SHOW DATABASES") do |sth|
sth.execute
puts "Databases: " + sth.fetch_all.join(", ")
end
### Example 3
**dbh.execute** can take a code block, passing the statement handle to it, and automatically calls `finish` at the end of the block.
```ruby
dbh.execute("SHOW DATABASES") do |sth|
puts "Databases: " + sth.fetch_all.join(", ")
end
The DBI _transaction_ method can also take a code block, as explained in the previous section.
DBI allows database drivers to provide additional database-specific functions, which can be called by users through the _func_ method of any Handle object.
You can set or get specific driver attributes using the **[]=** or **[]** methods.
DBD::Mysql implements the following driver-specific functions:
| No. | Function & Description |
| --- | --- |
| 1 | **dbh.func(:createdb, db_name)** Creates a new database. |
| 2 | **dbh.func(:dropdb, db_name)** Drops a database. |
| 3 | **dbh.func(:reload)** Executes a reload operation. |
| 4 | **dbh.func(:shutdown)** Shuts down the server. |
| 5 | **dbh.func(:insert_id) => Fixnum** Returns the most recent AUTO_INCREMENT value for this connection. |
| 6 | **dbh.func(:client_info) => String** Returns MySQL client information based on the version. |
| 7 | **dbh.func(:client_version) => Fixnum** Returns client information based on the version. This is similar to :client_info, but it returns a fixnum instead of a string. |
| 8 | **dbh.func(:host_info) => String** Returns host information. |
| 9 | **dbh.func(:proto_info) => Fixnum** Returns the protocol used for communication. |
| 10 | **dbh.func(:server_info) => String** Returns MySQL server-side information based on the version. |
| 11 | **dbh.func(:stat) => String** Returns the current status of the database. |
| 12 | **dbh.func(:thread_id) => Fixnum** Returns the ID of the current thread. |
## Example
```ruby
require "dbi"
begin
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