Latest web development tutorials

Ruby database access - DBI Tutorial

This chapter will show you how to use Ruby to access the database.Ruby DBImodule provides a database-independent interface is similar to Perl DBI module for Ruby scripts.

DBI namely Database independent interface, on behalf of the Ruby database-independent interface. DBI between Ruby code with the underlying database provides an abstraction layer that allows you to easily implement a database switchover. It defines a set of methods, variables and norms, provides a database-independent consistent database 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 application architecture

DBI independent of any database available in the background. Whether you're using Oracle, MySQL, Informix, you can use the DBI. The following chart clearly illustrates this point.

Ruby DBI architecture

Ruby DBI general architecture uses two layers:

  • Database Interface (DBI) layer. This layer is database independent and provides a series of public access method, using a database server, regardless of the type of approach.
  • Database driver (DBD) layer. This layer is dependent on the database, a different drive provides access to different database engines. MySQL, PostgreSQL, InterBase, Oracle, respectively, using a different drive. Each driver is responsible for interpreting the requests from the DBI layer, and these requests are mapped to apply to a request for a given type of database server.

installation

If you want to write Ruby scripts to access the MySQL database, you need to install Ruby MySQL module.

Install Mysql Development Kit

# Ubuntu
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev

# Centos
yum install mysql-devel

Mac OS, you need to modify ~ / .bash_profile or ~ / .profile file, add the following code:

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

Or use the soft connection:

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

Use RubyGems install DBI (recommended)

RubyGems about founded in November 2003, became part of the Ruby standard library from Ruby 1.9 version. More details can be viewed: the Ruby RubyGems

Use gem install dbi and dbd-mysql:

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

Use Code installation (Ruby version less than 1.9 to use this method)

The module is a DBD, from http://tmtm.org/downloads/mysql/ruby/ download.

After downloading the latest package, unzip into the directory, execute the following command to install:

% ruby extconf.rb

或者

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

或者

% ruby extconf.rb --with-mysql-config

Then compile:

% make

Obtain and install the 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, make sure you have root privileges. Now, install the following steps to install:

step 1

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

Or directly under another zip and unzip.

Step 2

Enter the directoryruby-dbi-master,usesetup.rbconfiguration script in the directory. The most common configuration commands are not followed by any parameter config parameters. The default configuration command to install all drivers.

$ ruby setup.rb config

More specifically, you can use the --with option to list the specific part you want to use. For example, if you want to configure the main DBI module and the MySQL DBD layer drive, enter the following command:

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

Step 3

The last step is to create the drive, use the following command to install:

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

Database Connectivity

Suppose we are using the 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 with a field FIRST_NAME, LAST_NAME, AGE, SEX and INCOME.
  • Set user ID "testuser" and the password "test123" to access TESTDB
  • Already on your machine correctly installed Ruby module DBI.
  • You've seen MySQL tutorial, understanding the underlying operating MySQL.

The following are examples of MySQL database connection "TESTDB" of:

#!/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, will produce the following results on a Linux machine.

Server version: 5.0.45

If you establish a connection with a data source, the returned database handle (Database Handle), and saved to thedbh for subsequent use, or dbhwill be set to nilvalue,e.err ande :: errstrreturn the error code and error string.

Finally, before exiting this program, be sure to close the database connection, the release of resources.

INSERT operation

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

Once a database connection, we're ready to create a table or insert data in the table to create a recording method using ado or prepareandexecutemethods.

Use do statement

The statement does not return rows by callingdo database processing methods.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 executeaSQLINSERTstatement to create records 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

Usingprepareandexecute

You can use DBI'sprepareandexecutemethod to execute Ruby code SQL statements.

To create a record as follows:

  • Prepared SQL statement with the INSERT statement. This will be accomplished through the useprepare method.
  • Execute SQL query, select all the results from the database. This will be accomplished by usingthe execute method.
  • Release the statement handle. This will be accomplished through the use offinish API.
  • If all goes well, then thecommit operation, or you can complete the transaction rollback.

Here is the syntax of these two methods:

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

Both methods can be used tobind a value to pass SQL statements.Sometimes the value is entered may not be given in advance, in this case, is used to bind values. Use a question mark(?) Instead of the actual value, the actual value to pass through 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 INSERT, then first prepare a statement, and then execute multiple times in a loop through each cycle than it calls do much more efficiently.

READ operation

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

Once a database connection, we're ready to query the database. We can use a method ordo prepareandexecutemethod to get the value from a database table.

Get step recorded as follows:

  • Based on the conditions required for the preparation of SQL queries. This will be accomplished through the useprepare method.
  • Execute SQL query, select all the results from the database. This will be accomplished by usingthe execute method.
  • One by one to get results, and outputs the results. This will be accomplished through the use offetch methods.
  • Release the statement handle. This will be accomplished through the use offinish methods.

The following example query all wage (salary) recorded more than 1,000 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 produces 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 many methods to obtain records from the database, if you are interested, you can view Ruby DBI Read operation .

Update Operation

Any database UPDATE operation refers to one or more database update existing records. The following example updates SEX is 'M' for all records. Here, we will add a year all males AGE. It will be divided into three steps:

  • Based on the conditions required for the preparation of SQL queries. This will be accomplished through the useprepare method.
  • Execute SQL query, select all the results from the database. This will be accomplished by usingthe execute method.
  • Release the statement handle. This will be accomplished through the use offinish methods.
  • If all goes well, then thecommit operation, or you can complete the transaction rollback.
#!/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. The following examples delete AGE 20 over all records from the EMPLOYEE. The operation steps are as follows:

  • Based on the conditions required for the preparation of SQL queries. This will be accomplished through the useprepare method.
  • Execute SQL queries, delete the desired records from the database. This will be accomplished by usingthe execute method.
  • Release the statement handle. This will be accomplished through the use offinish methods.
  • If all goes well, then thecommit operation, or you can complete the transaction rollback.
#!/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

Executive Affairs

A transaction is a mechanism to ensure transaction consistency. Services should have the following four attributes:

  • Atomicity (Atomicity): atomicity of the transaction means that the program is included in the transaction as a logical unit of work database, it does the data modification operations either all executed or not executed at all.
  • Consistency (Consistency): Consistency transaction refers to a transaction executed before and after the implementation of the database must be in a consistent state.If the state of the database satisfies all integrity constraints, say the database is consistent.
  • Isolation (Isolation): transaction isolation refers to the concurrent transactions are isolated from each other, that is, within a transaction of operation and operating data are to be sealed off, it is not seen other attempts to modify the transaction.
  • Persistent (Durability): transaction durability means that when a system or media failure, committed transactions to ensure that updates can not be lost.That once a transaction commits, change its data in the database should be permanent, endure any database system failure. Persistent guaranteed by database backup and recovery.

DBI provides two ways to perform the transaction. One is thecommitorrollbackmethods to commit or roll back the transaction. There is also atransactionmethod can be used to implement the transaction. Next we introduce two simple method of implementing transactions:

Method I

The first method uses DBI'scommitandrollbackmethods to explicitly commit or cancel the transaction:

   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 thetransactionmethod. This method is relatively simple, because it requires a transaction constitutes a code block containing statement.transactionmethod executes the block, and then the block is executed successfully, automatically invokecommitorrollback:

   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 operation has been completed to identify the database changes, and after this operation, all the changes are not recoverable.

Here is a simple examplecommit method invocation.

     dbh.commit

ROLLBACK operation

If you are not satisfied with one or a few changes, you want to completely restore these changes, use therollback method.

Here is a simple example ofrollback method call.

     dbh.rollback

Disconnect the database

To disconnect from the database, use the disconnect API.

    dbh.disconnect

If the user closes the database connection disconnect method, DBI rolls back all incomplete transactions. However, it does not rely on the implementation details of any DBI, and your application can be a good explicit call to commit or rollback.

Processing error

There are many different sources of error. For example, syntax errors in the implementation of the SQL statement or the connection fails, or is a complete statement or a canceled handle calls fetch method.

If a DBI method fails, DBI will throw an exception. DBI method can throw any type of exception, but the two most important exception class isDBI :: InterfaceErrorandDBI :: DatabaseError.

Exception objects of these classes areerr,errstr three attributes andstate,sub-table represents the error number, a descriptive error string and a standard error code. Attribute specified as follows:

  • err: returns an integer error that occurred notation, if DBD does not support the return ofnil.For example, Oracle DBD Returns section ORA-XXXXerror message.
  • errstr: Returns a string representation of the error that occurred.
  • state: Returns the SQLSTATE error code occurred.SQLSTATE is a five-character string length. Most of the DBD does not support it, it will return nil.

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

To obtain debug information about the contents of the script is executed when the script is executed, you can enable tracing. To do this, you must first download the dbi / trace module, and then call tracking control mode and output destinationtracemethod:

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

trace(mode, destination)

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

Block method

There are ways to create a handle. These methods are invoked by code block. Advantages of using code blocks with the method is that they provide a handle for the block as an argument when the block is terminated automatically cleared handle. Here are some examples to help understand this concept.

  • DBI.connect: This method generates a database handle, it is recommended at the end of the block calldisconnectto disconnect the database.
  • dbh.prepare: This method generates a statement handle, recommends callingfinishat the end of the block.Within a block, you must call theexecutemethod to execute the statement.
  • dbh.execute: This method is similar to dbh.prepare, but dbh.execute not need to call execute method within the block.A statement handle is automatically executed.

Example 1

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

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

Example 2

dbh.prepare may have a code block, passing the statement handle, 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

Example 3

dbh.execute may have a code block, passing the statement handle, and automatically calls finish at the end of the block.

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

DBItransaction method may also be provided with a block of code, which in the above sections have explained before.

Driver-specific functions and properties

Let DBI database driver provides additional functions specific database, these functions can be called by any userfuncmethod Handle object.

Use[] = or []method to set or get attributes specific drivers.

DBD :: Mysql implements the function following a specific driver:

No. Function & Description
1 dbh.func (: createdb, db_name)
Create a new database.
2 dbh.func (: dropdb, db_name)
To delete a database.
3 dbh.func (: reload)
Reload operation.
4 dbh.func (: shutdown)
Shut down the server.
5 dbh.func (: insert_id) => Fixnum
Returns recent AUTO_INCREMENT value of the connection.
6 dbh.func (: client_info) => String
Returns the MySQL client version information as required.
7 dbh.func (: client_version) => Fixnum
Depending on the version information back to the client. This is: Similar to client_info, but it will return a fixnum, rather than returning a string.
8 dbh.func (: host_info) => String
Returns the host information.
9 dbh.func (: proto_info) => Fixnum
Returns the protocols used for communication.
10 dbh.func (: server_info) => String
Returns the MySQL server version information based.
11 dbh.func (: stat) => Stringb >
Returns the current state of the database.
12 dbh.func (: thread_id) => Fixnum
Returns the current thread ID.

Examples

#!/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 produces 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