This chapter shows you how to use Ruby to access a database. Like the Perl DBI module, the Ruby DBI module offers a database-independent interface for Ruby scripts.\

Database Independent Interface for Ruby, or DBI for short, offers an abstraction layer between the underlying database and the Ruby code, making it simple to transition between database implementations. Regardless of the database being used, it specifies a collection of variables, rules, and procedures that offer a standardized database interface.

Accessing Databases in Ruby with DBI

The following can be interfaced with using DBI:

  • ADO (ActiveX Data Objects)
  • DB2
  • Frontbase
  • mySQL
  • MySQL
  • ODBC
  • Oracle
  • OCI8 (Oracle)
  • PostgreSQL
  • Proxy/Server
  • SQLite
  • SQLRelay

DBI Application Structure

Any database that is accessible in the backend has no bearing on DBI. Regardless of whether you are working with Oracle, MySQL, Informix, etc., you can use DBI. The architecture diagram that follows makes this very evident.

Two layers make up Ruby DBI’s general architecture.

  • The DBI layer is the database interface. Regardless of the sort of database server you’re interacting with, this layer offers a consistent set of access methods that are database independent.
  • The DBD layer is the database driver. Different drivers grant access to various database engines, making this layer database dependent. One driver exists for MySQL, another for PostgreSQL, another for Oracle, and so on. Each driver translates DBI layer requests into requests that are suitable for a certain kind of database server.

Essential Requirements

You must have the Ruby MySQL module installed in order to develop Ruby scripts that access MySQL databases.

As previously mentioned, this module functions as a DBD and is available for download from https://www.tmtm.org/en/mysql/ruby/

Setting up Ruby/DBI

Install the Ruby DBI library via the RubyGems package manager.

gem install dbi

Make sure you have root access before beginning this installation. Now, take the actions listed below.

Step 1

$ tar zxf dbi-0.2.0.tar.gz

Step 2: Navigate to the distribution directory dbi-0.2.0 and use the setup.rb script located there to configure it. This is how the most basic configuration command appears, with the config parameter followed by no arguments. The distribution is set up to install all drivers by default with this command.

$ ruby setup.rb config

Customize your DBI installation by specifying drivers using the --with option. For example, ruby setup.rb config --with=dbi,dbd-mysql installs only the core DBI module and the MySQL driver.

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

Step 3: The last step is to use the following commands to develop and install the driver:

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

Establishing a Database Connection


Make sure of the following before connecting to a database, assuming we’ll be working with a MySQL database –

  • TESTDB is the database that you have constructed.
  • EMPLOYEE was created by you in TESTDB.
  • This table has the following fields: AGE, SEX, INCOME, FIRST_NAME, LAST_NAME.
  • The password “test123” and user ID “testuser” are configured to access TESTDB.
  • Your computer has a correctly installed Ruby Module DBI.
  • To learn the fundamentals of MySQL, you have completed the tutorial.

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

#!/usr/bin/ruby -w

require "dbi"

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

This script generates the following outcome on our Linux computer when it runs.

Server version: 5.0.45

After establishing a connection with the data source, a Database Handle is returned and stored in dbh for future use; if not, dbh is set to nil, and e.err and e::errstr return an error string and an error code, respectively.

Data Insertion

Be sure to end the database connection and release the resources before exiting.
To generate your records in a database table, you must use the INSERT operation.

We can use the do method or prepare and execute method to build tables or records into the database tables once a database connection has been made.

Understanding do Statements

Execute non-result-returning SQL statements using the database handle’s do method, which returns the number of affected rows.

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

To add a record to the EMPLOYEE table, use the SQL INSERT statement in a similar manner.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the 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 server
   dbh.disconnect if dbh
end

prepare and execute Methods:

Applying prepare and execute
To run the SQL statement using Ruby code, utilize the prepare and execute methods of the DBI class.

To create a record, complete these steps:

  • INSERT statement for SQL statement preparation. The prepare method will be used to accomplish this.
  • Carrying out a SQL query to choose every database result. We’ll use the execute method to accomplish this.
  • Handle for the releasing statement. We’ll use the finish API for this.
  • If everything goes fine, then commit this operation otherwise you can rollback the complete transaction.

The syntax to utilize these two techniques is as follows –

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

SQL statements can be passed bind values using one of these two techniques. In certain situations, the values that need to be entered might not be specified beforehand. In this situation, binding values are applied. Before actual values are supplied through the execute() API, a question mark (?) is used in their place.

Here’s how to add two records to the EMPLOYEE table:

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the 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 server
   dbh.disconnect if dbh
end

It is more effective to prepare a statement first and then run it again within a loop if there are numerous INSERTs occurring at once rather than calling do each time.

Reading Data from the Database

Any database’s READ operation allows you to retrieve some helpful data from it.

We are prepared to run a query into this database once we have established a database connection. To retrieve values from a database table, we can either use the do method or prepare and execute methods.

The steps involved in retrieving records are as follows:

  • creating a SQL query according to the necessary criteria. We’ll use the prepare method to accomplish this.
  • Running a SQL query to retrieve every database result. The execute method will be used for this.
  • Obtaining each result individually and printed it. The fetch method will be used for this.
  • Handle for the releasing statement. The finish procedure will be used for this.

The steps to query every record in the EMPLOYEE table with a salary above 1,000 are as follows.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the 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 server
   dbh.disconnect if dbh
end

This will result in the following outcome −

First Name: Mac, Last Name : Mohan
Age: 20, Sex : M
Salary :2000

First Name: John, Last Name : Poul
Age: 25, Sex : M
Salary :2300

To retrieve records from the database, there are more shortcut techniques. Go through the “Fetching the Result” part if you’re interested, otherwise move on to the next one.

Update Operation

To update one or more records that are already present in the database, use the UPDATE operation. The steps to update all records with SEX as ‘M’ are as follows. In this case, we shall raise each male’s age by one year. There will be three steps involved.

  • Creating a SQL query based on the necessary parameters. We’ll use the prepare method to accomplish this.
  • Selecting every result from the database by running a SQL query. We’ll use the execute method to accomplish this.
  • Handling of the releasing statement. We’ll use the finish method for this.
  • If everything goes fine then commit this operation otherwise you can rollback the complete transaction
#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the 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 server
   dbh.disconnect if dbh
end

Removing Database Records

You must perform a DELETE operation in order to remove certain records from your database. The steps below will remove all employee records where the employee’s age exceeds 20. The steps below will be taken in this operation.

  • Creating a SQL query based on the necessary parameters. We’ll use the prepare method to accomplish this.
  • Running a SQL query to remove necessary database records. We’ll use the execute method to accomplish this.
  • Handling of the releasing statement. We’ll use the finish method for this.
  • If everything goes according to plan, proceed with this operation; if not, you can reverse the entire transaction.
#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the 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 server
   dbh.disconnect if dbh
end

Managing Transactions

Data consistency is ensured by transactions. The four properties listed below should be present in transactions

  • Atomicity − Either nothing occurs or a transaction is completed.
  • Consistency −Every transaction must begin and end in the same condition within the system.
  • Isolation − The intermediate outcomes of a transaction are not accessible to those who are not involved in it.
  • Durability − The consequences of a transaction remain after it has been committed, even in the event of a system failure.

There are two ways to commit or rollback a transaction using the DBI. To carry out transactions, there is an additional technique known as transaction. There are two easy ways to put transactions into practice.

Approach I

The first technique commits or cancels the transaction explicitly using DBI’s commit and rollback capabilities.\

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

Approach II

The second strategy makes advantage of the transaction technique. This is easier since it uses a code block that contains the statements that comprise the transaction. After the block is executed, the transaction mechanism automatically initiates commit or rollback, according on whether the block is successful or unsuccessful.

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

Committing Database Changes

Commit is the action that authorizes the database to complete the modifications; once this is done, no changes can be undone.

The commit method can be called in this straightforward example.

dbh.commit

Rollback in Databases

To revert any unwanted changes made during a database transaction, use the rollback method. This completely reverses all modifications made since the transaction began.

This is a basic example of how to invoke the rollback method .

dbh.rollback

Database Disconnection

Use the disconnect API to end the database connection.

dbh.disconnect

The DBI rolls back any pending transactions if the user disconnects from a database using the disconnect method. However, your application would be better off explicitly calling the commit or rollback rather than relying on any of DBI’s implementation specifics.

Troubleshooting Errors

Errors can come from many different sources. A connection failure, a syntax mistake in a SQL statement that has already been performed, or using the fetch method for a statement handle that has already been canceled or completed are a few instances.

DBI raises an exception if one of its methods fails. Although DBI methods can raise any of a number of exception types, DBI::InterfaceError and DBI::DatabaseError are the two most significant exception classes.

The error number, a descriptive error message, and a standard error code are represented by the three attributes err, errstr, and state that are present in exception objects of these kinds. The characteristics are described below.

  • err − Returns the numerical portion of an ORA-XXXX error message, for instance, or nil if the DBD does not support it. Returns an integer representation of the error that occurred.
  • errstr − Returns back a string describing the error that happened.
  • state − Returns back the SQLSTATE code for the error that occurred. The SQLSTATE is a string of five characters. The majority of DBDs return nil instead of supporting this.

You’ve seen the code above in the majority of the examples−

rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

You can activate tracing to obtain debugging information about the actions of your script while it runs. This is accomplished by calling the trace method, which regulates the trace mode and output destination, after loading the dbi/trace module.

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

trace(mode, destination)

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

Code Blocks Containing Methods

Certain techniques generate handles. A code block can be used to call these methods. Using code blocks in conjunction with methods has the benefit of automatically cleaning up the handle after the block ends and providing the handle to the code block as a parameter. There aren’t many examples to help you grasp the idea.

  • DBI.connect − This method creates a database handle, and in order to detach the database, it is advised to execute disconnect at the end of the block.
  • dbh.prepare − This technique is best used at the end of the block as it creates a statement handle. To execute the statement, you must call the execute method inside the block.
  • dbh.execute − This method is comparable, but it does not require using execute inside the block. It automatically executes the statement handle.

Example 1

A code block can be passed to DBI.connect, which then automatically disconnects the database handle at the end of the block –

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

Example 2

A code block can be passed to dbh.prepare, which then automatically calls finish at the end of the block in the manner described below –

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

Example 3

A code block can be passed to dbh.execute, which then automatically calls finish at the end of the block as follows. −

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

The code block mentioned above is also used by the DBI transaction mechanism.

Driver-Specific Capabilities

Through the func method of any Handle object, the user can invoke additional database-specific functions that the DBI permits the database drivers to expose.

Using the [] = or [] methods, driver-specific attributes can be set or retrieved.

The DBD::Mysql driver provides these functions:
Sr.No.Functions & Description
1dbh.func(:createdb, db_name)
Creates a new database.
2dbh.func(:dropdb, db_name)
Drops a database.
3dbh.func(:reload)
Performs a reload operation.
4dbh.func(:shutdown)
Shuts down the server.
5dbh.func(:insert_id) => Fixnum
Returns the most recent AUTO_INCREMENT value for a connection.
6dbh.func(:client_info) => String
Returns MySQL client information in terms of version.
7dbh.func(:client_version) => Fixnum
Returns client information in terms of version. It’s similar to :client_info but it return a fixnum instead of sting.
8dbh.func(:host_info) => String
Returns host information.
9dbh.func(:proto_info) => Fixnum
Returns protocol being used for the communication.
10dbh.func(:server_info) => String
Returns MySQL server information in terms of version.
11dbh.func(:stat) => String
Returns current state of the database.
12dbh.func(:thread_id) => Fixnum
Returns current thread ID.

Example

#!/usr/bin/ruby

require "dbi"
begin
   # connect to the 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 result in the following outcome –

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

Post Views: 0

Categorized in:

Ruby