How to connect to an Oracle database in IronRuby

After spending a few hours trying to connect to an oracle database in ironruby using various gems, I gave up. None of the gems out there would work, each for a different reason. It was time to write my own class to do the job of managing connections and executing queries.

You’ll need a few things:

  1. A copy of the ‘Oracle.DataAccess.dll’ that you can find somewhere inside this outrageously large download:
    http://www.oracle.com/technology/software/tech/windows/odpnet/index.html
    Copy the ‘Oracle.DataAccess.dll’ into your load path (you’ll find it somewhere in the bowels of the directory structure that the above installs)
  2. An oracle database you can point at
  3. The connection string required to connect to the database. It’ll look something like:
    Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROT...

Once you’ve got those details, you can use the following class:

require 'System'
require 'System.Data'
require File.join(File.expand_path(File.dirname(__FILE__)), "Oracle.DataAccess.dll")

class IronRubyOracleClient
  #pass in oracle connection string, eg, for Test env:
  def initialize(connection_string)
    @connection = Oracle::DataAccess::Client::OracleConnection.new(connection_string)
  end

  #opens connection
  def open
    @connection.open
  end

  #returns 2D array
  def execute(query)
    @query = query
    cmd = Oracle::DataAccess::Client::OracleCommand.new(@query, @connection)
    cmd.CommandType = System::Data::CommandType.Text
    data_reader = cmd.ExecuteReader()
    column_count = data_reader.visible_field_count.to_i

    result_rows = ::System::Collections::ArrayList.new

    while(data_reader.read) do
      row = ::System::Collections::ArrayList.new
      column_count.times do |i|
        row.add(data_reader.get_oracle_value(i).to_string)
      end
      result_rows.add(row)
    end

    result_set = []

    result_rows.each do |result_row|
      ruby_row = []
      result_row.each do |cell|
        ruby_row << cell.to_s
      end
      result_set << ruby_row
    end

    result_set
  end

  #close connection
  def close
    @connection.close
  end
end

And here’s how you use it:


#create your connection string
connection_string  = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=..." #etc...
#create an instance of the client, passing in the connection string
my_client = IronRubyOracleClient.new(connection_string)
#open a connection to the database
my_client.open
#execute a query and save the result
results = my_client.execute("select * from some_table")
#dump the results (a 2D array of values)
puts results.inspect
#close the connection
my_client.close

It’s fairly slow, but it works. Which is an improvement on what’s out there…

Note that everything is returned as a string. For some reason, the unless the data is a basic string or is a number that fits into an integer, the data gets garbled somewhere between the dll and ironruby. I can’t find out where, so everything-returned-as-a-string is the current compromise. If you can get it to work with all data types, send it along!

Testing redirects with ruby

—UPDATE—
Since writing this post, I’ve put together a gem called ‘responsalizr‘ which is a way better solution than what follows in this post. Read about it here. And now back to the original post…

Testing redirects from a web app is simple enough – make a request and check the response code making sure it’s a 301, 302 or whatever you’re expecting. The test you end up writing isn’t nice idiomatic ruby though. So, I wrote a quick monkey patch… here it is:

class Net::HTTPResponse
  #returns true if the response is a 200
  def ok?
    instance_of?(Net::HTTPOK)
  end

  #returns true if the response is a 301
  def found?
    instance_of?(Net::HTTPFound)
  end

  #returns true if the response is a 302
  def moved_permanently?
    instance_of?(Net::HTTPMovedPermanently)
  end

  #returns true for any kind of redirect (301..307)
  def redirect?
    kind_of?(Net::HTTPRedirection)
  end

  #returns the url being redirected to if this response is a redirect
  def redirect_url
    redirect? ? self['location'] : raise("Not a redirect response")
  end
end

Basically, it adds the following predicate methods to the Net::HTTPResponse class: “ok?” (returns true if response code is 200), “found?” (returns true if response code is 301), “moved_permanently?” (returns true if response code is 302) and “redirect?” (returns true if the response is a redirect – from 301..307). Because these are predicate methods, they can be used by rspec – your test code suddenly becomes much cleaner! The moneky patch also adds the “redirect_url” method which returns the url to be redirected to if the response is a redirect of some sort. The advantage of this is that your tests become much more idiomatic:


#the urls we're testing with...
@url_initially_navigated_to = "http://mail.google.com"
@expected_redirect_url = "https://www.google.com/.../etc/..."

#make the request
@response= Net::HTTP.get_response(URI.parse(@url_initially_navigated_to))

#nice idiomatic tests! - use the one you're expecting...
@response.should be_ok                #expecting a 200
@response.should be_found             #expecting a 301
@response.should be_moved_permanently #expecting a 302
@response.should be_a_redirect        #expecting anything between 301..307

#testing the url being redirected to
@response.redirect_url.should match(@expected_redirect_url)

There you go! Nice idiomatic redirect tests! Enjoy.

How to test a WPF app using IronRuby and White

UPDATE
Since I wrote this post, I have put together a ruby gem designed for testing WPF UIs called ‘bewildr’. I wrote up an introductory post about bewildr here. Bewildr removes the need for using White – it’s written in ruby, for ruby! You’ll find its API clean and idiomatic. Anyway, back to the original post…
—————–

…and you thought ruby was only good for web testing…

So… having previously failed at getting ruby-based automated testing of WPF working, I attacked it from a different angle and succeeded! The difference is that this time I used IronRuby instead of ruby. In doing so, the White Automation library could be used out-of-the-box! It’s time to throw away your expensive, proprietary test tools and replace them with open source tools instead! Here’s how it’s done:

Prerequisites

  • Install .Net Framework 2.0, 3.0 (and possibly 3.5 – I’ve got it installed and haven’t tested this stuff without it)
  • Install IronRuby (but put it in c:\ironruby, not the default location that contains spaces in the path)
  • Download the latest version of White (0.19 at time of writing)
  • Install UISpy.exe to investigate objects on screen
  • Bookmark the White API – you’ll use this a lot

I’m presuming that you know to run ruby files in IronRuby using the ir command, not the standard ruby command, iirb instead of irb and irake instead of rake.

Requires, Dlls and some helpful methods

First thing, you need to gain access to White in your project. To do that, put the following at the top of your app (eg: your env.rb file in cucumber):


$LOAD_PATH << File.expand_path("lib/White_Bin_0.19")
require "White.Core.dll"
require 'UIAutomationTypes, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

module IronRubyHelpers
 def r_array_to_cs_array(args)
   System::Array[System::String].new(args.map {|arg| arg.to_s.to_clr_string})
 end

 def r_string_to_c_string(r_string)
   System::String.new(r_string)
 end
end

This requires some explanation… The first line puts the directory containing the White.Core.dll file on the load path. In this case, I’ve taken the White_Bin_0.19 directory that was created when I expanded the zip I downloaded from the White site, and put it inside the lib directory in my NetBeans project. The next line requires the White library. The line after that loads the UIAutomationTypes.dll – you’ll need this if you want to find WPF objects by their type.

Next, the IronRubyHelpers module… it contains 2 methods: one converts a ruby array to a dot net array (required whenever a White object method takes an array as an argument, eg: menu interaction), and the other converts a ruby string to a dot net string (sometimes required when a White object arg takes a string).

That’s all the setup you need.

Starting and Killing WPF Apps

Now we start dealing with White… Here’s how to start a WPF app :

@app = White::Core::Application.Launch("c:\my_app\example.exe")

And here’s how to kill it:

@app.kill

The Launch(string) method is one of many ways to start an app. Check out the Application.cs documentation to see how else it can be done (I can’t get AttachOrLaunch to work – ymmv).

Grabbing Windows

You get access to objects by using the window that they’re in. That means you need to get the window first. Here’s how it’s done:

@main_window = @app.get_windows.select{|window| window.title == "My Window Title"}.first

That will get you a window whose title matches “My Window Title” exactly. If you want a bit more flexibility (eg: if your app displays its version number in the title and you don’t want to have to change your recognition string everytime you get a new build), you can use the following regex-powered window finder:

@main_window = @app.get_windows.select{|window| window.title =~ /My App, version (.*)/}.first

Your window takes a while to appear? Here’s how to wait for it:

Timeout.timeout(10) do
  sleep 0.2 until @app.get_windows.select{|window| window.title == "My App"}.size > 0
end

Now you’ll wait for up to 10 seconds checking ~5 times a second to see if a window entitled “My App” appears (it only takes a tiny tweak to make it use regex). If it takes longer than 10 seconds you’ll get a Timeout exception.

Grabbing and Interacting with Objects

Now that we can get hold of windows, we can look inside them and get access to the object that they contain. White uses static methods on SearchCriteria to access object. Generally, you’ll need 2 bits of info:

  1. How to search (eg: by id, by control type, by text, etc)
  2. The value to search for (eg: “btnLogin”, ControlType.Edit, “Login…”)

Here are some examples:

@chk_visibility = @main_window.get(White::Core::UIItems::Finders::SearchCriteria.ByAutomationId(r_string_to_c_string("visibleCheckBox")))

Hey, no one said it was pretty. Here we’re getting a checkbox that lives in the main window. We’re identifying it by its automation id, which happens to be “visibleCheckBox”. Another example:

@cmb_zoom = @main_window.get(White::Core::UIItems::Finders::SearchCriteria.ByControlType(System::Windows::Automation::ControlType.ComboBox))

Here we’re getting the only combo box in the main window. This time we’re searching by ControlType. You can find a full list of the available control types here under the “Fields” section.

There are a few more ways to use SearchCriteria, you can read about them in the SearchCriteria.cs documentation.

And now to object interaction… Once you’ve got your object reference, you can call methods on it and hope it responds. Here are some examples:

@chk_visibility.checked

…will return true or false based on whether the checkbox is checked – read about the available methods on checkbox here. Another example:

@cmb_zoom.select(r_string_to_c_string("200%"))

This will select the “200%” option from the combo box.

Depending on the object type you could call .click, .value = "hello", .double_click, etc.

Navigating the Window Menu

Since using the window menu doesn’t fit the standard object-locator pattern, we’ll cover that here.

def select_menu_item(*args)
 my_menu_bar = @main_window.menu_bar
 my_menu_item = my_menu_bar.menu_item(r_array_to_cs_array(args))
 my_menu_item.click
end

select_menu_item("File", "Open...")

Using window menus is a bit weird in White. The easiest way to go about it is to use the select_menu_item above. It gets the menu bar out of the window, does some ruby-to-dot-net magic with the arguments and then clicks the menu item. I’ve included an example call to the method that does the standard File->Open action.

A few more things

Because we’re using IronRuby, there’s a quirk you need to be aware of – you may have spotted it already… IronRuby translates White’s CamelCase methods to the standard ruby_underscore_case. Eg:, if the White documentation says that a particular object supports the “DoubleClick” method, you can call that method in your ruby scripts with “double_click”. You get used to it.

Speed and Reliability

…or lack of same. White is slow. And buggy. IronRuby startup time is slow too. I’ve found loads of places where White claims to do something (eg: table interaction), but what’s there isn’t unusable. Some of the stuff that does work only works often, not all the time. I’ve rewritten some of the buggy functionality using the low level interaction libraries provided by microsoft and the replacement code runs several orders of magnitude faster than white. Please don’e expect fast execution – you just won’t get it. Be prepared for molasses-speed testing.

Example project using Paint.NET

I’ve put together a badly designed set of tests around Paint.NET (3.5.3 at time of writing) using Test::Unit. It’s a NetBeans project, but even so you’ll need to run the tests off the command line (there’s no IronRuby-NetBeans support that I can find). Make sure to have Paint.NET installed in its default location and then try running “irake test” in the project directory… and have patience – it crawls. It takes around 2 minutes to run 4 tests.

>>> Download my example project here <<<

Enjoy!

Updated script to list all cucumber step definitions

In a previous post I put up a small script that would dump out all step definitions available in a cucumber project. Here’s an updated version… it hasn’t changed much apart from the output is now a html table that for each step definition contains the regular expression, any regex modifiers, any parameters to the step definition and a link to the file that contains the definition.

Anyway, here’s the script:

step_definition_dir = "./features/steps"

f = File.new("output.htm", "w")

f << "<table><th>Regex</th><th>Modifiers</th><th>Step Definition Args</th><th>Source file</th>"

Dir.glob(File.join(step_definition_dir,'**/*.rb')).each do |step_file|
  File.new(step_file).read.each_line do |line|
    next unless line =~ /^\s*(?:Given|When|Then)\s+\//
    matches = /(?:Given|When|Then)\s*\/(.*)\/([imxo]*)\s*do\s*(?:$|\|(.*)\|)/.match(line).captures
    matches << step_file
    f << "<tr>"
    f << "<td>#{matches[0]}</td>"
    f << "<td>#{matches[1]}</td>"
    f << "<td>#{matches[2]}</td>"
    f << "<td><a href=\"#{matches[3]}\">#{matches[3]}</a></td>"
    f << "</tr>"
  end
end

f << "</table>"

—— UPDATE ——

Turns out that in the latest version of cucumber (0.6.1) there’s a new formatter called stepdefs which prints out the step regexs and the step definition file that they live in. It doesn’t report regex modifiers or the step arguments – apart from that it’s great. Here’s how to use it:

cucumber -d -f stepdefs

…though it only seems to work for steps called in an ordinary scenario, not a scenario outline.

Automating a WPF UI using ruby, win32ole and White

UPDATE (17/2/2010): It *can* be done, see here: How to test a WPF app using IronRuby and White. The solution doesn’t use win32ole though… just IronRuby

—–

Short story: it doesn’t work (…well, not out-of-the-box).

Long story: I’ve finally had the opportunity to try something that I’ve been meaning to do for a while now… automated testing of a .Net app with a WPF UI by using ruby, its win32ole library and White – the C# library for testing Win32/WPF UIs on windows.

Well, it turns out that it can’t be done because COM does not support static methods. Which is a pain, because White uses static methods all over the place – just to launch the app you want to test requires calling the static Application.Launch(string path_to_exe) method, so you can’t even start it, never mind interact with it!

It can be made to work though… but a thin layer of custom C# would be required (a gap in the open source test tools world – anyone?). A colleague and I proved that if we modified the White.Core.Application class by adding a instance method that calls the pre-existing static Launch method and returns an application instance, our method *can* be called by ruby’s win32ole library and we got an instance (back in rubyland) of the WPF app we wanted to test.

But a custom Launch method is only the beginning. Each of the static methods in White that would need to be called in ruby would need to be wrapped somehow in an instance method. There are quite a lot of them – the methods in classes like SearchCriteria, etc. So it’d be possible, but painful.

If you’re lazy, give up and stop reading here. If you want to have a play with this, here are the steps that I took just to get to the stage where I could create a White.Core.Application object in ruby. None of this is documented anywhere (well, not that I could find in the 2 days that I was playing with this), so I hope it’s of help to someone…

Compile White in such a way that it’s accessible to win32ole

1) Open the White solution in Visual Studio
2) Open the properties for the Core project, select the Application tab, click the Assembly Information button and check the ‘Make Assembly Com-Visible’
3) In the properties window again, select the Compile tab and check the Register for COM Interop checkbox
4) Compile (Release, not Debug)

Register the White.Core.dll Assembly

Buried inside \bin\Release will be the White.Core.dll file. We need to register it…

1) regasm /tlb:mytlb.tlb White.Core.dll (creates the type library)
2) regasm /codebase White.Core.dll (registers the assembly)

Playing with stuff in ruby

require 'win32ole'

WIN32OLE_TYPE.new('White_Core', 'Application') #returns an application instance (note the underscore)

WIN32OLE_TYPE.ole_classes( 'White_Core').collect {|c| c.to_s}.sort.each {|s| puts s} (lists all available white classes)

WIN32OLE.new('White.Core.Application') (note… no underscores!)

Not great, I know. But it’ll get you over the first few hurdles at least.

Useful links:
http://stackoverflow.com/questions/265879/can-ruby-import-a-net-dll
http://rubyonwindows.blogspot.com/

How to list all cucumber step definitions

If you’re using cucumber you probably have step definitions split across several files, and you’re probably using a tool that doesn’t do a good job of listing the available step definitions (one of the biggest downsides of using cucumber).

What follows is a script that will list them all. It’s fairly rudimentary in that for each step definition it just lists the regex, any regex modifiers and the step definition arguments. It does everything I need at the moment so I haven’t developed it any further. Do with it what you will.


root_test_folder = "../my_project/features/support"

Dir.glob(File.join(root_test_folder,'**/*.rb')).each do |support_file|
  File.new(support_file).read.each_line do |line|
    next unless line =~ /^\s*(?:Given|When|Then)\s+\//
    matches = /(?:Given|When|Then)\s*\/(.*)\/([imxo]*)\s*do\s*(?:$|\|(.*)\|)/.match(line).captures
    matches[0] = Regexp.new(matches[0])
    puts matches.inspect
  end
end

Set the root_test_folder variable to the relevant location, run the file, and your console will be filled with what amounts to a step definition dictionary!

Cucumber step definition for debugging

Building on a previous post about debugging cucumber tests with ruby-debug, here’s a simple cucumber step definition you can use in your cucumber scenario in order to debug it:

Then /^I debug$/ do
  breakpoint
  0
end

Use it as follows:

Feature: Debug a cucumber scenario
  As a test writer
  In order to make my life easier
  I want to be able to debug

  Scenario: Calling the debugger in the middle of a test
    Given some condition
    Then I debug #this will call the debugger
    And I do something subsequent to debugging

Debugging cucumber tests with ruby-debug

Testing with cucumber blows test/unit out of the water, apart from one small aspect… I can’t use the Netbeans’ GUI debugger. The Netbeans debugger has been a joy to use, but sadly I have to say goodbye. So I’m left with using the traditional ruby debugger. It uses the same approach as gdb – console based debugging. Tedious and painful. Here’s how to use it…

Preparing your cucumber project

1) Install the gem with:
gem install ruby-debug

2) In your env.rb file, add the following at the top of the file:

require 'rubygems'
require 'ruby-debug'

3) If you want to be able to see stack traces, follow the above 2 lines with:
Debugger.start
(It might be worth wrapping that in some sort of debug-mode check as it does slow things down a little.)

Now you’re ready to debug like it’s 1979!

Using ruby-debug

To get started with ruby-debug, you need to set a breakpoint. How? Like this:


def some_method
  my_obj.thing = 4
  breakpoint #look! a breakpoint!
end

Breakpoints are set by writing breakpoint. Weird, huh? But there is a gotcha – breakpoint can’t be the last line of a method, or the debugger will stop at the calling method. So, you need to do something like this:


def some_method
  my_obj.thing = 4
  breakpoint
  0
end

…which is rubbish.

Once your breakpoint is set and you run the test, you’ll eventually see a command prompt – in Netbeans it’ll be in the output window. It’s time to debug.

Here are some of the commands that are available – they cover 95% of what I usually need from a debugger.

To end the debugging session and return control to the app:
quit

To find out where you are:
where
Note, this will print out a stack trace only if you’ve added the Debugger.start as specified above. If you haven’t, where will only print out the line number and file name of where execution has reached.

To see where you are in context (current line with a few lines before and after):
list

To print out the value of an individual variable:
p my_var

To print out all local and instance variables in the current stack frame:
info variables

To go to the next step (equivalent of ‘step into’):
step

To step over the next line:
next

To let the app run to the next breakpoint:
continue

To restart the app:
restart

You can see that ruby-debug gives you pretty much everything you need. The commands listed above hardly scratch the surface of what it can do. If you want to know more, here’s a good reference: http://bashdb.sourceforge.net/ruby-debug.html

BTW, here’s a post on how to wrap the breakpoint call in a cucumber step definition.