Ruby Query Analyzer

• Tweet
• 

        // This is a Ruby database query analyzer using the DBI library. It shows how to use the DBI in a simple way and how powerful Ruby is (a query analyzer // in ~90 lines with comments and command line parsing). 

#!ruby
# == Synopsis
# Copies files to stdout
#
# == Usage
# ruby QueryAnalyzer.rb -d database (ODBC database name) -u username -p password
# 
# == Author
# Scott LaBounty (slabounty@gmail.com)
#
# == Copyright
# Copyright(c) 2006 Scott LaBounty
#
#

require 'getoptlong'
require 'rdoc/usage'
require 'dbi'

##
# Main Program
#
#

if __FILE__ == $0

    # Set up the command line options
    opts = GetoptLong.new(
        ["--database", "-d", GetoptLong::REQUIRED_ARGUMENT],
        ["--username", "-u", GetoptLong::REQUIRED_ARGUMENT],
        ["--password", "-p", GetoptLong::REQUIRED_ARGUMENT],
        ["--verbose", "-v", GetoptLong::NO_ARGUMENT],
        ["--help", "-h", GetoptLong::NO_ARGUMENT]
        )

    # Set the default values for the options
    verbose = false
    date=""
    database = 'database'
    username = 'username'
    password = 'password'

    # Parse the command line options. If we find one we don't recognize
    # an exception will be thrown and we'll rescue with a RDoc::usage
    begin
        opts.each do | opt, arg|
            case opt
            when "--database"
                database = arg
            when "--username"
                username = arg
            when "--password"
                password = arg
            when "--help"
                RDoc::usage
            when "--verbose"
                verbose = true
            end
        end
    rescue
        RDoc::usage
    end

    puts "database = #{database} username = #{username} password = #{password}"

    begin
        dbh = DBI.connect("DBI:ODBC:#{database}", username, password)
        print ">> "
        while ((line = gets.chomp!) !~ /(exit|quit)/i) do
            begin
                rows = dbh.select_all(line)
                p rows[0].column_names if rows.length > 0
                rows.each do | row |
                    p row
                end
                print ">> "
            rescue DBI::DatabaseError => e
                puts "SQL error: Error #{e.errstr}"
                print ">> "
            end
        end
    rescue DBI::DatabaseError => e
        puts "Database connect error: Error #{e.errstr}"
    ensure
        dbh.disconnect if dbh
    end

end

Tags:
• Database
• Ruby