Code snippets, hints and tips for the software testing professional

Well the young boys keep raving about Ruby. I still think Python has a lot more library support via eggs because it has been around longer. Anyhow I gave Ruby a crack for this trivial task and, yes, the same as Python, it does’nt take too much code to automate a painful manual task.

This little script, runs on windows. It will connect to a remote IBM DB2 database, execute a query, then output the result set to a CSV file. You will need some prerequisites before you can do this. Before you continue for this you will need a DB2 client installed, although not the full DB2 client is needed you can download and install the appropriate IBM Data Server Driver for ODBC/JDBC and CLI from here IBM DataServer drivers. If you already have DB2 client software installed then dont worry about this.

First you will need to install Ruby for Windows do that here Ruby 1.8.6 One Click Installer .
Once you have installed Ruby, then you need to install a few Gems in this order.
Download and install the activesupport gem here ActiveSupport 2.3.5 gem

gem install activesupport

Download and install the activerecord gem here ActiveRecord 2.3.5

gem install activesrecord

Download and install the ibm_db mswin32 gem here IBM DB MSWin32 gem

gem install ibm_db

Right now heres the code

require 'rubygems'
require 'mswin32/ibm_db'
require 'date.rb'


t=Time.now
lines=[]
filename = 'c://Temp//' +t.strftime("%m%d%Y_%H%M%S")+'.txt'
conn = IBM_DB.connect("DRIVER={IBM DB2 JDBC DRIVER};DATABASE=DBNAME;\
                      HOSTNAME=999.999.999.999;PORT=50004;PROTOCOL=TCPIP;\
                      UID=userid;PWD=password;", "", "")
sql = "SELECT address_id, addr_line_one FROM address fetch first 100 rows only"
stmt = IBM_DB.exec(conn, sql)
while row = IBM_DB.fetch_array(stmt)
  lines << "#{row[0]},#{row[1]}"
end

# Output resultset to a CSV file
File.new(filename ,"w")
lines.each do |row|
    File.open(filename ,"w") do |the_file|
      0.upto(lines.size) { |i| the_file.puts "#{lines[i]}" }
    end
end