home PYTHONJAVA
 

Ruby Database Access - DBI Tutorial

This chapter will show you how to access the database using Ruby. The Ruby DBI module provides a database-independent interface to Ruby scripts similar to the Perl DBI module.

DBI is the Database independent interface, which represents Ruby's database-independent interface. DBI provides an abstraction layer between Ruby code and the underlying database, allowing you to easily implement database switching. It defines a set of methods, variables, and specifications that provide a consistent database interface independent of the database.

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 is independent of any database available in the background. Whether you are using Oracle, MySQL, Informix, you can use DBI. The architecture diagram below clearly illustrates this.

Ruby DBI The general architecture uses two layers:

  • Database Interface (DBI) layer. This layer is independent of the database and provides a set of public access methods, regardless of the database server type.
  • Database Drive (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 those requests to requests for a given type of database server.

Install

If you want to write a Ruby script to access a MySQL database, you need to install the Ruby MySQL module first.

Install Mysql Development Kit

# Ubuntu sudo apt-get install mysql-client sudo apt-get install libmysqlclient15-dev # Centos yum install mysql-devel

The Mac OS system needs to modify the ~/.bash_profile or ~/.profile file and 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 a soft connection:

sudo ln -s /< Span class="hl-string">usr/local/mysql/lib /libmysqlclient.18 .dylib /usr/lib /libmysqlclient.18.dylib

Install DBI with RubyGems (recommended)

RubyGems was created in November 2003 and became part of the Ruby standard library from Ruby 1.9. More details can be found at: Ruby RubyGems

Install dbi and dbd-mysql with gem:

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

Use source installation (Ruby version less than 1.9 using this method)

Download the latest package, extract it to the directory, and execute 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 link below:

Step 1

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

Or directly zip the package and unzip it.

Step 2

Go to the directory ruby-dbi-master and configure it in the directory using the setup.rb script. The most common configuration command is the config parameter without any arguments. This command is configured by default to install all drivers.

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:

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

Step 3

The final step is to create the drive and install it using the following command:

ruby setup .rb setup ruby setup.rb install

Database Connection

Assuming we are using a MySQL database, make sure to:

before connecting to the database:

  • You have created a database TESTDB.
  • You have created the table EMPLOYEE in TESTDB.
  • This table has fields FIRST_NAME, LAST_NAME, AGE, SEX, and INCOME.
  • Set the user ID "testuser" and the password "test123" to access TESTDB
  • The Ruby module DBI has been properly installed on your machine.
  • You have seen the MySQL tutorial and understood the basics of MySQL.

The following is an example of connecting the MySQL database "TESTDB":

Instance

#!/usr/bin/ruby -w require "dbi" begin # Connect to the MySQL server dbh= DBI< Span class="hl-code">.connect("DBI:Mysql:TESTDB:localhost", "testuser"< Span class="hl-code">, "test123") # Get the server version string and display row = dbh< Span class="hl-code">.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< /span> dbh.disconnect< Span class="hl-code"> if dbh end

When running this script, the following results will be generated on the Linux machine.

  Server   version  :  5.0.45< /span>

If the connection is made with a data source, return the database handle (Database Handle) and save it to dbh for later use, otherwise dbh will be set to The nil value, e.err and e::errstr return the error code and error string, respectively.

Finally, before exiting this program, be sure to close the database connection and free up resources.

INSERT action

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

Once the database connection is established, we can prepare to create a table or create an insert data table using the do method or the prepare and execute methods. record of.

Use the do statement

Statements that do not return rows can be processed by calling the do database. This method takes a statement string argument and returns the number of rows affected by the statement.

dbh. Do("DROP TABLE IF EXISTS EMPLOYEE ") dbh.do< Span class="hl-brackets">("CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )" );

Similarly, you can execute a SQL INSERT statement to create a record inserted into the EMPLOYEE table.

Instance

#!/usr/bin/ruby -w require "dbi" begin # Connect to the MySQL server dbh= DBI< Span class="hl-code">.connect("DBI:Mysql:TESTDB:localhost", "testuser"< Span class="hl-code">, "test123") dbh.do< Span class="hl-brackets">( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000)" ) puts "Record has been created" dbh.commit< Span class="hl-code"> rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback< Span class="hl-code"> ensure # Disconnect from the server< /span> dbh.disconnect< Span class="hl-code"> if dbh end

Use prepare and execute

You can use DBI's prepare and execute methods 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 the prepare method.
  • Execute the SQL query and select all the results from the database. This will be done by using the execute method.
  • Release the statement handle. This will be done using the finish API.
  • If everything goes well, commit the action, otherwise you can rollback complete the transaction.

The following is the syntax for using these two methods:

dbh[ 'AutoCommit'] = false # Set auto-submit to false. begin dbh.do< Span class="hl-brackets">("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'") dbh.do< Span class="hl-brackets">("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'") dbh.commit< Span class="hl-code"> rescue puts "transaction failed" dbh.rollback< Span class="hl-code"> end dbh['< Span class="hl-string">AutoCommit'] = true

Method II

The second method uses the transaction method. This method is relatively simple because it requires a block of code that contains the transaction statements. The transaction method executes the block and then automatically calls commit or rollback depending on whether the block was executed successfully:

Instance

dbh[ 'AutoCommit'] = false # Set auto-submit to false dbh.transaction< Span class="hl-code"> do |dbh| dbh.do< Span class="hl-brackets">("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'") dbh.do< Span class="hl-brackets">("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'") end dbh['< Span class="hl-string">AutoCommit'] = true

COMMIT operation

Commit is an operation that identifies a database that has completed a change, after which all changes are unrecoverable.

The following is a simple example of calling the commit method.

dbh. Commit

ROLLBACK operation

If you are not satisfied with one or several changes and you want to fully recover those changes, use the rollback method.

The following is a simple example of calling the rollback method.

dbh. Rollback

Disconnect database

To disconnect the database, use the disconnect API.

dbh. Disconnect

If the user closes the database connection via the disconnect method, DBI will roll back all outstanding transactions. However, without relying on the implementation details of any DBI, your application can explicitly call commit or rollback explicitly.

Processing errors

There are many different sources of error. For example, a syntax error when executing a SQL statement, or a connection failure, or a fetch method is called on a canceled or completed statement handle.

If a DBI method fails, DBI will throw 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 have three attributes: err, errstr, and state. The subtables represent error numbers and a descriptive The error string and a standard error code. The properties are specified as follows:

  • err: returns the integer representation of the error that occurred, or nil if DBD does not support it. For example, Oracle DBD returns the numeric portion of the ORA-XXXX error message.
  • errstr: Returns a 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 don't 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< Span class="hl-code"> ensure # Disconnect from the server< /span> dbh.disconnect< Span class="hl-code"> if dbh end

To get debug information about the script execution when the script is executed, you can enable tracing. To do this, you must first download the dbi/trace module and then call the trace method that controls the trace mode and output destination:

require " dbi/trace" .............. trace(mode< Span class="hl-code">, 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.

method's code block

There are some ways to create a handle. These methods are called by code blocks. The advantage of using code blocks with methods is that they provide a handle to the code block as a parameter, which automatically clears the handle when the block terminates. Below 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 the database.
  • dbh.prepare : This method generates a statement handle, suggesting to call finish at the end of the block. Within 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 need to call the execute method inside the block. The statement handle is executed automatically.

Instance 1

DBI.connect can take a block of code, pass it a database handle, and automatically break the handle at the end of the block.

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

Instance 2

dbh.prepare can take a block of code, pass a statement handle to it, and automatically call finish at the end of the block.

dbh. Prepare("SHOW DATABASES") do |sth | sth.execute< Span class="hl-code"> puts "Databases: " + sth.fetch_all. join("< Span class="hl-string">, ") end

Instance 3

dbh.execute can take a block of code, pass a statement handle to it, and automatically call finish at the end of the block.

dbh. Execute("SHOW DATABASES") do |sth | puts "Databases: " + sth.fetch_all. join("< Span class="hl-string">, ") end
The

DBI transaction method can also have a block of code, as explained in the previous section.

Specific driver functions and properties

DBI allows database drivers to provide additional functions for specific databases that can be called by the user through the func method of any Handle object.

Use the []= or [] method to set or get the properties of a specific driver.

DBD::Mysql implements the following specific driver functions:






welookups is optimized for learning.© welookups. 2018 - 2019 All Right Reserved and you agree to have read and accepted our term and condition.