Introducing the MySQL gem

Install

apt-get install mysql-server mysql-client libmysql-ruby1.9.1 libmysqlclient-dev -y
sudo gem1.9.1 install mysql

Database creation

I use MySQL's shell to create the database, create the user and set the user's database permissions,

mysql --user=root --password="password"

Once I'm logged in:

create user james;
=> Query OK, 1 row affected (0.00 sec)

create database users;
=> Query OK, 1 row affected (0.00 sec)

GRANT ALL on users.*
TO james@localhost
IDENTIFIED BY 'secretpassword';
=> Query OK, 1 row affected (0.00 sec)

code

require 'mysql'

db = Mysql.new 'localhost', 'james', 'password', 'users'

(db.public_methods - Object.public_methods).sort.each_slice(4){|x| puts "%-19s %-22s %-25s" % x }

affected_rows       autocommit             change_user              
client_info         client_version         close                    
connect             dump_debug_info        errno                    
escape_string       field_count            get_client_info          
get_host_info       get_proto_info         get_server_info          
host_info           info                   insert_id                
list_dbs            list_fields            list_processes           
more_results        more_results?          next_result              
ping                prepare                proto_info               
query_with_result   query_with_result=     quote                    
real_query          reconnect              reconnect=               
reload              rollback               select_db                
server_version      set_server_option      shutdown                 
ssl_set             stat                   stmt_init                
thread_id           use_result             warning_count
db.list_dbs
#=> ["information_schema", "users"] 

C

reating tables

db.query("DROP TABLE IF EXISTS animal")
db.query("CREATE TABLE animal
          (
            name     CHAR(40),
            category CHAR(40)
          )
        ")
db.query("INSERT INTO animal (name, category)
            VALUES
              ('snake', 'reptile'),
              ('frog', 'amphibian'),
              ('tuna', 'fish'),
              ('racoon', 'mammal')
          ")
puts "Number of rows inserted: #{db.affected_rows}"

Querying table

s

res = db.query("SELECT name, category FROM animal")

while row = res.fetch_row do
 printf "%s, %s\n", row[0], row[1]
end
puts "Number of rows returned: #{res.num_rows}"

res.free


# or to retrieve rows into an array
a = []
sql = "SELECT name, category FROM animal"
db.query(sql).each{|x| a << x}

Resources

Tags:
Source:
1904hrs.txt
Published:
26-12-2011 19:04