RubyWatir

After searching for hours on how to connect to an SQL database and retrieve information, I decided to put together some useful functions to remind myself in the future. Most tutorials or examples on the web show you how to connect to Mysql databases but not SQL.

The important thing to note is that, you need 2 different ways to get information from an SQL db. One method will be for select statements or statements that return results and the other method will be for statements that dont return results like update, insert… statements

You will need the dbi gem installed

def run_select_query(query)

    connection = nil
    @host = " "
    @username = " "
    @password = " "
    @dbname = " "

  begin
      connection = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=#{@host};Initial Catalog=#{@dbname};User ID=#{@username};Password=#{@password}")
  rescue DBI::DatabaseError => e

       puts "An error occurred"
       puts "Error code:    #{e.err}"
       puts "Error message: #{e.errstr}"
  end

    dataset = connection.execute(query).fetch_all
    connection.disconnect

    return dataset
end

 

def run_update_query(query)

  connection = nil
  @host = Environment.db_host
  @username = Environment.db_username
  @password = Environment.db_password
  @dbname = Environment.db_name

  begin
    connection = DBI.connect("DBI:ADO:Provider=SQLOLEDB;Data Source=#{@host};Initial Catalog=#{@dbname};User ID=#{@username};Password=#{@password}")
  rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
  end

  connection.do(query)
  connection.commit
  connection.disconnect

end
 

 

The 2 methods are shown above. Example of how to use them:

select_query_result = run_select_query("select name from testdatabase where Id = 1")

run_update_query("insert into testdatabase(id,name) values (1,'test_name')")

Its that simple. Most people try and use the same query for selecting and for updating, and they get the following error:

error number=3704 Operation is not allowed when the object is closed
or something similar. This will solve that problem. Enjoy