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)
- OCI8 (Oracle)
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.
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
The Mac OS system needs to modify the ~/.bash_profile or ~/.profile file and add the following code:
Or use a soft connection:
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:
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:
Get and install Ruby/DBI
You can download and install the Ruby DBI module from the link below:
Or directly zip the package and unzip it.
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.
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:
The final step is to create the drive and install it using the following command:
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":
When running this script, the following results will be generated on the Linux machine.
Server span> version span> : Span> 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.
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.
Similarly, you can execute a SQL INSERT statement to create a record inserted into the EMPLOYEE table.
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: