1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Step By Step Guide to Using MySQL in Ruby

Discussion in 'Ruby on Rails' started by asha, Dec 4, 2012.

  1. asha

    asha New Member

    Joined:
    Nov 9, 2006
    Messages:
    44
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Homemaker
    Location:
    Kolkata
    Home Page:
    MySQL is a hot favourite RDBMS, primarily when developing web applications, and Ruby is an emerging language in the web development business. I have earlier discussed about working with MySQL in many other languages like Python, Perl, C, etc. In this article we will be looking at installation and usage of the MySQL library for Ruby.

    Installing MySQL Library for Ruby



    Installation is pretty straightforward using Ruby Gems.

    Code:
    $ gem install mysql
    Fetching: mysql-2.9.0.gem (100%)
    Building native extensions.  This could take a while...
    Successfully installed mysql-2.9.0
    1 gem installed
    Installing ri documentation for mysql-2.9.0...
    Installing RDoc documentation for mysql-2.9.0...
    
    You may also install the library from source, but I doubt you would need to do that.

    Basic Usage



    Basic usage would mean connecting to the database server, issuing queries, read resultset & loop through them. Follow the sample code below to understand the basic usage, I have added comments for ease.

    Code:
    #!/usr/bin/ruby
    
    ## load gems
    require 'rubygems'
    ## load mysql library
    require 'mysql'
    
    ## connect to the mysql server
    con = Mysql.new('localhost', 'myuser', 'myPas*', 'my_db') 
    
    ## execute a query
    rs = con.query('select * from people')  
    
    ## loop through the result set
    ## this returns each row as hash, the field name being the key
    rs.each_hash(with_table=false) { |h| 
        puts h['name']
    }
    
    ## alternate way to iterate through the result set
    n_rows = rs.num_rows
    
    n.rows.times do
        puts rs.fetch_row.join(" ")
    end
    
    con.close
    

    Advanced Usage



    In advanced usage we will look at prepared statements which improve the efficiency and the security, provide features like place holders, and error handling. Unless you are a completely new to programming the sample code below will be more than enough to get you started in enabling database connections in your Ruby program.

    Code:
    #!/usr/bin/ruby
    
    require 'rubygems'
    require 'mysql'
    
    ## block where the code runs, so that the error may be trapped
    begin
        ## connect to the mysql server
        con = Mysql.new('localhost', 'myuser', 'myPas*', 'my_db') 
    
        ## execute a query
        prepared_statement = con.prepare('select * from people where age > ?')
        prepared_statement.excute 3
    
        ## loop through the result set
        ## this returns each row as hash, the field name being the key
        prepared_statement.each { |h| 
            puts h.join(" ")
        }
    
        ## clean up
        prepared_statement.close if prepared_statement
    rescue Mysql::Error => e
        ## handle/report error
        puts e.errno
        puts e.error
    ensure
        ## this ensures that the database connection is closed
        con.close if con
    end
    
    Enjoy coding in Ruby.
     
    shabbir likes this.

Share This Page