Beginner’s Ruby / Database Tutorial
Ruby Database Access
• DBI stands for Database independent interface for Ruby
• DBI provides an abstraction layer between the Ruby code and the underlying database.
• You can use databases like Oracle, SQL Server and MySQL.
• DBI defines set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.
• From cmd (or terminal)
– sudo gem install dbi
• This will install the DBI module
MySQL driver installation
• Ruby DBI uses two layers
– The database interface layer
• Database independent layer
– The database driver layer
• Database dependent layer
• Install MySQL database driver
– gem install dbd-mysql (Ignore any Documentation errors)
Initialization:
require “mysql”
Connect to
mysql = Mysql.init()
database
mysql.connect(‘localhost’,'root’,'port’)
mysql.select_db(‘qa_Info’)
Next,
Define the table fields using Ruby Access Database:
mysql.query(“CREATE TABLE
Execute Query to
`qa_info`.`test_results` (`test_id` INTEGER create a table
UNSIGNED NOT NULL AUTO_INCREMENT,
`result` VARCHAR(45) NOT NULL,
`total_Time` INTEGER(4) UNSIGNED NOT
NULL, `total_test_num` INTEGER(4)
Select the
UNSIGNED NOT NULL, `test_pass_num` database Schema
INTEGER(4) UNSIGNED NOT NULL,
`command_pass_num` INTEGER(4)
UNSIGNED NOT NULL, `command_fail_num`
INTEGER(4) UNSIGNED NOT NULL,
`command_error_num` INTEGER(4)
UNSIGNED NOT NULL, PRIMARY KEY
(`test_id`) )”)
Still with me?
Let’s insert a few rows to begin.
mysql.query(‘insert into test_results
values(1,”passed”,73,2,2,0,5,0)’)
mysql.query(‘insert into test_results
values(2,”failed”,73,2,2,0,5,0)’)
mysql.query(‘insert into test_results
values(3,”failed”,73,2,2,0,5,0)’)
res = mysql.query(“select test_id,result
from test_results”)
Now, Let’s make an array.
while row = res.fetch_hash do
printf “%s, %s\n”, row["test_id"],
row["result"]
end
puts “Number of rows returned:
#{res.num_rows}”
res.free
mysql.close()
Finally, Execute Result_table.rb to see the results.
Anthony Long :: Feb.03.2010 :: Blog :: No Comments »