Introduction
Think about why PHP is so popular, a little reason is because there is a mysql of the natural partner. Now, MySQL is almost the most database used in OpenSource. In Ruby, you can pass Tomita Masahiro's MySQL module, which provides a client API for Ruby, in fact, is a packaging of MySQL C API.
This article mainly said to install the mysql module, write Ruby MySQL script.
Translation finishing: http://www.ruby-cn.org/
Install MySQL module
To use Ruby's MySQL module, you must first make sure your MySQL C client API header file and library file have been successfully installed, which is necessary because the MySQL module is based on this.
First, download the package from http://www.tmtm.org/en/mysql/ruby/, then decompress, such as:
% TAR ZXVF MYSQL-RUBY-2.4.4A.Tar.gz
Then enter the top layer directory after decompression, start configure (Ruby unique way).
% Ruby Extconf.rb
ExtConf.rb will automatically check the mysql head and library files. If you find it, you can do it directly, if you don't find it, you need to manually fill in the parameters, such as:
% Ruby Extconf.rb /
--with-mysql-include = / usr / local / mysql / incrude / mysql /
--with-mysql-lib = / usr / local / mysql / lib / mysql
Assume that the head and library files of MySQL are as shown above.
After the above operation is successful, you can run Make.
% make
% make install (running with root)
Mysql module overview
The MySQL module includes 4 classes:
• MySQL
Main class, providing functions such as connecting databases, sending query statements, management operations.
• MySQLRES
Results The result is used to generate the results obtained.
• MySQLField
Metadata class, which contains information on the results centralized field, such as the field name, type, size, etc.
• MySQLError
Abnormal class, when the other three classes have an error, it is available.
Because in general, the MySQL module is packaged for MySQL C API, so its function name is also regular, such as the function name of the C API is mysql_real_connect (), in Ruby, it is real_connect (), it can be seen, it is removed The previous mysql_ prefix.
First simple example
First ensure that we have a database in the local database called rubydb, a user TEST, password is also TEST.
The example is as follows:
# file name: eXample1.rb
Require "mysql"
Begin
#connect to the server
DBH = mysql.real_connect ("Localhost", "Test", "Test", "Rubydb")
# Get Server Version String and Display IT
PUTS "Server Version:" dbh.get_server_info
Rescue mysqlerror => e
Print "Error Code:", E.ERRNO, "/ N"
Print "Error Message:", E. Error, "/ N"
Ensure
# disconnect from Server
dbh.close
end
Take a look at this program now:
The first line contains the MySQL module, telling ruby to introduce the MySQL module.
The REAL_CONNECT function link database, four parameters are easy to understand. In fact, the MySQL module also provides some alias, such as New, Connect and Real_Connect are equivalent.
DBH (Database Handle) is used to interact with MySQL, such as calling the GET_SERVER_INFO function in the example to get server-related information, such as version.
It is the thing that is talked in the exception handling, similar to the Java's TRY Catch Finally mechanism. If DBH is wrong in interacting with the database, it is captured by the rescue and assigns an exception to a mysqlerror. This exception includes two read-only properties: errno, error number, integer, error, error message, string type.
If this program is executed correctly, no error, the result is as follows:
% Ruby Example1.rb
Server Version: 4.0.12-log
If an error is performed, for example, we wrote a wrong host name, write localhost to Loclahost, will be wrong:
% Ruby Example1.rb
An error Occurre
Error Code: 2005
Error Message: Unknown MySQL Server Host 'Loclahost' (1)
Processing query
We temporarily divide SQL into two categories: DML, DDL (this don't know more). DDL includes Create, INSERT, UPDATE, DELETE; DML includes SELECT, SHOW, and the like. DDL does not need to return the result set, so it is easy to handle; DML needs to return the result set and require a number of processing procedures.
1. For the Query method that does not need to return the result set, you can use affected_rows if you want to know the number of rows affected. The integrated example is as follows:
DBH.Query ("Drop Table Test")
DBH.Query ("Create Table Test
ID INT,
Name char (20)
")
DBH.Query ("INSERT INTO TEST VALUSE (1, 'Name1')")
Printf "% D ROWS WERE INSERTED / N", DBH.Affected_Rows
2. For queries that need to return results, such as SELECT, you need to follow the following procedure:
a. Call the Query method to send a query request to the database and get a result set (an instance of MySqlres), which provides a method such as metadata, moving, acquisition, and a Metadata.
b. Hand each row of data with functions such as fetch_row and so on.
C. If you want to know how many rows returned a total, you can use Num_ROWS methods. d. Don't forget to finalize the results set object with free.
Res = dbh.query ("SELECT ID, NAME from Test1)
While row = res.fetch_row do
Printf "% s,% s / n", row [0], row [1]
end
Printf "% d rows were returned / n", res.num_rows
Res.free
Or use iteration:
Res = dbh.query ("SELECT ID, NAME from Test1)
Res. Each Do | ROW |
Printf "% s,% s / n", row [0], row [1]
end
Printf "% d rows were returned / n", res.num_rows
Res.free
Fetch_row and Each return an array each time, the element of the subscript is 0 indicates the data of the first column, and the like. In addition, you can also use the fetch_hash method to return a Hash structure, which is similar to the Key-Value structure of Map in Java:
Res = dbh.query ("SELECT ID, NAME from Test1)
While Row = Res.Fetch_hash DO
Printf "% s,% s / n", ROW ["id"], row ["name"]
end
Printf "% d rows were returned / n", res.num_rows
Res.free
Each_hash is the Hash version of Each:
Res = dbh.query ("SELECT ID, NAME from Test1)
Res. Each_hash Do | ROW |
Printf "% s,% s / n", ROW ["id"], row ["name"]
end
Printf "% d rows were returned / n", res.num_rows
Res.free
At the default, the value of KEY in the hash is the column name, but if the selected column is renowned, this may bring a lot of problems, such as the two fields in the following statements called i:
SELECT T1.I, T2.I from T1, T2;
If you use the hash structure, you can only get a list of values. In order to solve this column column conflict, you can specify with_table = true when Fetch_hash or Each_hase, so Harder KEY is indicating that the class name is connected to a piece, the format is TBL_NAME.COL_NAME.
Res = dbh.query ("SELECT ID, NAME ANIMAL")
Res. Each_hash (with_table = true) Do | ROW |
Printf "% s,% s / n", row ["test.id"], ROW ["test. name"]
end
Printf "% d rows were returned / n", res.num_rows
Res.free
If the table or the column name in the query statement uses an alias, then these alias will be the KEY value of Hashi, not the original table name and column name.
With fetch_row, EACH, we must remember the location of each field to find him accurately, which is more difficult to SELECT * queries. Use fetch_hash and each_hash responsibility to avoid this, you can take the value according to the column name without having to go to the position of the list.
If the parameter with_table = true, the query is also express in the field, then the TBL_NAME section in the KEY value of these expressions will be empty (but "." "Orange), such as you have the following query :
SELECT I, I 0, VERSION (), 4 2 from T;
Only i is the column name of the table, others are expressions, then these columns correspond to the KEY values in the hash table: "Ti", ".i 0", ".version ()", and ". 4 2 ".
