https://github.com/erikh/ruby-dbi |
Before starting the installation, make sure you have root permissions. Now, please follow the steps below to install:
Step 1
git clone https://github.com/erikh/ruby-dbi.git
Or directly download the zip package and unzip it.
Step 2
Enter the directory ruby-dbi-master, and use the setup.rb script in the directory to configure. The most commonly used configuration command is the config parameter followed by no parameters. This command is configured to install all drivers by default.
$ ruby setup.rb config
More specifically, you can use the --with option to list the specific sections you want to use. For example, if you only want to configure the main DBI module and MySQL DBD layer driver, please enter the following command:
$ ruby setup.rb config --with=dbi,dbd_mysql
Step 3
The last step is to create the driver and install it using the following command:
$ ruby setup.rb setup
$ ruby setup.rb install
Assuming we are using a MySQL database, before connecting to the database, please make sure:
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 user ID "testuser" and password "test123" to access TESTDB
Have the Ruby module installed correctly on your machine DBI.
You have watched the MySQL tutorial and understood the basic operations of MySQL.
The following is an example of connecting to the MySQL database "TESTDB":
#!/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
endWhen running this script, it will be in Linux The following results are produced on the machine.
Server version: 5.0.45
If the connection is established with a data source, the database handle (Database Handle) will be returned and saved to dbh for subsequent use, otherwise dbh will be Set to a nil value, e.err and e::errstr return the error code and error string respectively.
Finally, before exiting this program, please make sure to 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 the database connection is established, we are ready to create a table or create an insert into the data table using the do method or the prepare and execute methods record of.
Use do statement
Statements that do not return rows can be done by calling the do database processing method. 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 execute the SQL INSERT statement to create records for insertion 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
endUsing 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 the SQL statement with the INSERT statement. This will be done using the prepare method.
Execute a SQL query and select all results from the database. This will be done 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 to complete the transaction.
The following is the syntax for using these two methods:
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 value being entered may not be given in advance, in which case a bound value is used. Use question marks (?) in place of the actual value, which is passed through the 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
endIf you use multiple INSERTs at the same time, prepare a statement first, and then execute it multiple times in a loop than It's much more efficient to call do each time in a loop.
The READ operation on any database refers to obtaining useful information from the database.
Once the database connection is established, we are ready to query the database. We can get the values from the database table using do method or prepare and execute methods.
The steps to obtain records are as follows:
Prepare a SQL query based on the required conditions. This will be done using the prepare method.
Execute a SQL query and select all results from the database. This will be done using the execute method.
Get the results one by one and output them. This will be done using the fetch method.
Release the statement handle. This will be done using the finish method.
The following example queries all records with salary (salary) exceeding 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 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 also many ways to get records from the database, if you are interested, you can check out the Ruby DBI Read operation.
The UPDATE operation on any database refers to updating one or more existing records in the database. The following example updates all records whose SEX is 'M'. Here we will add one year to the AGE for all males. This will be done in three steps:
Prepare the SQL query based on the required conditions. This will be done using the prepare method.
Execute a SQL query and select all results from the database. This will be done using the execute method.
Release the statement handle. This will be done using the finish method.
If everything goes well, commit the action, otherwise you can rollback to complete the transaction.
#!/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
endWhen you want to delete records from the database, you need to use the DELETE operation. The following example deletes all records from EMPLOYEE with an AGE greater than 20. The steps for this operation are as follows:
Prepare the SQL query based on the required conditions. This will be done using the prepare method.
Execute the SQL query to delete the required records from the database. This will be done using the execute method.
Release the statement handle. This will be done using the finish method.
If everything goes well, commit the action, otherwise you can rollback to complete the transaction.
#!/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
endTransactions are a mechanism to ensure transaction consistency. A transaction should have the following four attributes:
Atomicity: The atomicity of a transaction means that the program included in the transaction works as the logic of the database Unit, all data modification operations it performs are either performed or not performed at all.
Consistency: The consistency of a transaction means that the database must be in a consistent state before and after a transaction is executed. A database is said to be consistent if its state satisfies all integrity constraints.
Isolation: Isolation of transactions means that concurrent transactions are isolated from each other, that is, the operations within a transaction and the data being operated must be blocked up so that it cannot be seen by other transactions attempting to modify it.
Durability (Durability): The durability of a transaction means that when the system or media fails, it ensures that updates to committed transactions cannot 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 backup and recovery.
DBI provides two methods of executing transactions. One is the commit or rollback method, which is used to commit or rollback the transaction. There is also the transaction method, which can be used to implement transactions. Next, we will introduce these two simple methods of implementing transactions:
Method I
The first method uses DBI’s commit and rollback Method to explicitly commit or cancel a 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'] = trueMethod II
The second method uses the transaction method. This method is relatively simple because it requires a block of code that contains 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 is executed successfully:
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'] = trueCommit is an operation that identifies that changes to the database have been completed. After this operation, all changes are irreversible.
The following is a simple example of calling the commit method.
dbh.commit
If you are not satisfied with one or several changes and you want to completely revert these changes, use the rollback method.
The following is a simple example of calling the rollback method.
dbh.rollback
If you need to disconnect from the database, please use the disconnect API.
dbh.disconnect
If the user closes the database connection through the disconnect method, DBI will roll back all outstanding transactions. However, without relying on any DBI implementation details, your application is just fine with explicitly calling commit or rollback.
There are many different sources of errors. For example, there is a syntax error when executing a SQL statement, or the connection fails, or the fetch method is called on a canceled or completed statement handle.
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. The sub-table represents the error number, a descriptive The error string and a standard error code. The attributes are specifically described as follows:
err: Returns the integer representation of the error that occurred, or nil if the 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 nil is returned.
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
ensure
# 断开与服务器的连接
dbh.disconnect if dbh
endIn order to get debugging information about what the script is executing when it 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, destination)
mode values can be 0 (off), 1, 2 or 3, the value of destination should be an IO object. The default values are 2 and STDERR respectively.
There are some methods for creating handles. These methods are called through code blocks. The advantage of using code blocks with methods is that they provide a handle to the code block as a parameter, which is automatically cleared 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, 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 within the block. Statement handles are automatically executed.
Example 1
DBI.connect can have a code block, pass the database handle to it, and will automatically disconnect at the end of the block Open handle.
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
"testuser", "test123") do |dbh|Example 2
dbh.prepare You can have a code block, pass it a statement handle, and finish will be automatically called at the end of the block.
dbh.prepare("SHOW DATABASES") do |sth|
sth.execute
puts "Databases: " + sth.fetch_all.join(", ")
endExample 3
dbh.execute can have a code block, pass it a statement handle, and finish will be automatically called at the end of the block.
dbh.execute("SHOW DATABASES") do |sth|
puts "Databases: " + sth.fetch_all.join(", ")
endDBI transaction The method can also have a code block, which has been explained in the above chapter.
DBI allows the database driver to provide additional database-specific functions, which can be used by the user through any Handle object's func method is called.
Use the []= or [] methods to set or get driver-specific properties.
DBD::Mysql implements the following driver-specific functions:
| Serial Number | Function & Description |
|---|
| 1 | dbh.func(:createdb, db_name)
Create a new database. |
| 2 | dbh.func(:dropdb, db_name)
Delete a database. |
| 3 | dbh.func(:reload)
Perform the reload operation. |
| 4 | dbh.func(:shutdown)
Shut 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 according to version. |
| 7 | dbh.func(:client_version) => Fixnum
Return client information according to 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 the MySQL server-side information according to the version. |
| 11 | dbh.func(:stat) => Stringb>
Returns the current status of the database. |
| 12 | dbh.func(:thread_id) => Fixnum
Returns the ID of the current thread. |
Example
#!/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
endThis will produce 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
