Blog is moving

This blog has moved to http://digits.transatlantical.me

Parameterized Query with LIKE using Oracle.DataAccess

If I have to figure this out yet again for the nth time, I'll get ticked off. So here is how to do a LIKE query in C# using an OracleParameter:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using System.Data;

namespace Oracle
{
    class Program
    {
        static void Main(string[] args)
        {
            const string searchTerm = "bd";
            const string cs = @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=thehostname)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=the.service.name)));User Id=user;Password=pass.word;";
            const string sql = @"select rm_id, ref_fac_id || ' - ' || rm_num as name
                        from asbestos.rooms
                        where rownum < 51
                          and ref_fac_id is not null
                          and( lower(ref_fac_id) like :q
                            or lower(rm_num) like :q)
                        order by rm_num asc";
            var conn = new OracleConnection(cs);

            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            var param = cmd.CreateParameter();
            param.DbType = DbType.String;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = ":q";
            param.Value = string.Format("{0}{1}{0}", "%", searchTerm.ToLower());
            cmd.Parameters.Add(param);
            conn.Open();

            using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(1));
                }
            }

            Console.Read();
        }
    }
}

Port blocking in Windows 7 makes for unhappy WCF testing

I typically do something like this when I'm testing a WCF service (using NUnit here):

namespace Tests
{
    [TestFixture]
    class When_running_as_hosted_service
    {
        private ServiceHost _host;
        private IWhoKnowsService _client;

        [SetUp]
        public void Before_any_test()
        {
            // 1.
            // Start a WCF service hosted in a generic ServiceHost
            // This is exactly the same as hosting in IIS or a console app
            const string port = "http://localhost:12345/";
            var uri = new Uri(port);
            _host = new ServiceHost(typeof(WebService), uri);
            _host.AddServiceEndpoint(typeof(IWhoKnowsService), new WSHttpBinding(), uri);
            _host.Open();
            
            // 2.
            // Now dynamically build a client to that service.
            // This is the same as 'Add Service Reference' but without all
            // of the code generation
            _client = new ChannelFactory(new WSHttpBinding())
                .CreateChannel(new EndpointAddress(port));
        }

        [TearDown]
        public void After_all_tests()
        {
            // Tear it down so that the TCP port is release correctly
            _host.Close();
        }

        [Test]
        public void Can_get_a_list_of_descripts_for_a_skill_level() 
        {
            var descriptions = _client.DescriptionsByLevel(SkillLevel.Padwan);
            Assert.IsNotNull(descriptions);
            Assert.Greater(descriptions.Length, 0);

            foreach (var item in descriptions)
            {
                Console.WriteLine(item);
            }
        }
    }
}

I've used this technique many, many times on Windows XP but I just made the switch to Windows 7 for development, having skipped Vista entirely. When trying to run that test I get this error back from the test runner:

AddressAccessDeniedException: HTTP could not register URL http://+:12345/<…>.  Your process does not have access rights to this namespace

That's not good. So I hit the Internet and found an excellent post by Nicholas Allen that set me straight. Basically Windows 7 really locks down your usage of TCP ports over HTTP. Using the trusty old netsh command he shows how to open up the port of choice in one easy step:

netsh http add urlacl url=http://+:12345/ user=dev2010\Dylan

Problem solved! I reran the test in Visual Studio and got a pass.

NOTE: You must be running your Command Prompt as an administrator in order for this to work. In other words, right click on Command Prompt in your Start Menu and choose the appropriate option.

Ruby strings on Windows. Backlashes again!

There are all kinds of discussions about how to correctly use single or double quoted strings in Ruby, especially where character escaping is concerned. I was faced with just such an issue today and thought I'd share my experience.

Basically I'm building a rake script to automate my .NET project setup. Part of that setup involves doing some search and replace is SQL scripts so that a developer can store some settings in a config file and have that populate variables in files. For example:

• YAML file config.yml contains a setting called production -> db -> backup_location. The text in the file is "\\\\some\\unc\\path\\name". You see how the backslashes in the Windows path are doubled up? That's to satisfy Ruby and it is in double quotes.
• The rake script uses this value as a parameter to a method that I wrote that is heavily influenced (copied with simplification!) by the albacore project, which is a set of rake tasks geared towards .NET projects. The important lines of the script are these:

  replacements.each do |search,replace|
      search_term = "%#{search}%"
      template_data.gsub!search_term, replace
  end

• My SQL file has this in it that should have various paths inserted:

  FROM  DISK = N'%backup_file%' WITH  FILE = 1
   , MOVE N'Cityworks_Production' TO N'%db_dir%\Cityworks_WCS.mdf'
   , MOVE N'Cityworks_Production_log' TO N'%db_dir%\Cityworks_WCS_1.ldf'
   , MOVE N'sysft_CityworksFtCat' TO N'%db_dir%\Cityworks_WCS_2.CityworksFtCat'
   , move N'ftrow_CityworksFtCat' to N'%db_dir%\CityworksFtCat.ndf'
  ,  NOUNLOAD,  STATS = 10
  GO

Unfortunately when I run the script using a UNC path rather than a typical C:/something/or/other path I could never get two backslashes to appear at the start of the path. I tried (literally!) every combination I could think of: doubling up, adding an extra backslash, single quotes, double quotes, you name it. Along the way I wrote a little one liner that converts a string from Ruby path to a Windows path (winpath = ruby_path.gsub('/','\\')) but still no joy. Then I searched some more online and finally found an answer: it wasn't my various File.join or File.expand_path calls that were the issue: it was the gsub call itself that was the problem.

In fact the question was answered by Matz himself (inventor of the Ruby language). While my string with four backslashes actually did give me the right answer of two escaped backslashes, gsub also assigns special meaning to backslashes. So get this - you need to quadruple any backslash in a string if you are going to gsub it. Crazy but true. Here's what my fixed up code looks like now:

replacements.each do |search,replace|
    search_term = "%#{search}%"
    template_data.gsub! (search_term) { replace } unless replace.nil?
end

The trick is to use a block rather than tacking on the replace as a parameter. In this way gsub offloads the whole string interpretation to the block rather than doing its funky thing with interpreting the backslashes inline. Boy, am I glad I got that figured out.

A little coding test

Do this without compiling...

Someone recently asked me about some questions to give someone during an interview. The fun part is having the interviewee do the problem on a white board without an IDE to help them. Or having them do it in Visual Studio, Eclipse, or whatever IDE they typically use for their language of choice. Here is one such problem. It’s not very complicated but it does force the interviewee to think about assumptions being made:

Given a certain amount of money that you need, and a set of coin denominations, tell me the minimum number of coins of each denomination I need to have that amount of money.

Having heard the problem statement I tried the whiteboard version. Then I opened up Visual Studio and typed out the following. I waited until I typed the whole thing before trying to build or run tests, just to keep myself honest about assumptions I was making.

---

STOP! Try to do this yourself without reading my approach. Not that I'm right, fast, wrong, or anything else, but see how you do first.

---

public class CoinCounter
{
  /// <summary>
  /// Assumptions made:
  /// 1. There's always a denomination of 1 unit
  /// 2. There are no fractional units like the 1/2 pence in England
  /// 3. The denominations are always in the same units (cents, not cents and dollars)
  /// </summary>
  public static Dictionary GetMinimumCoinsNeeded(int[] denominations, int targetValue)
  {
    var result = new Dictionary();
    
    // Sort in descending order so we get the biggest denomination first
    Array.Sort(denominations);
    Array.Reverse(denominations);
    int remaining = targetValue;
    
    foreach (int denomination in denominations)
    {
      // Turns out there's a Math.DivRem function that gets both the
      // coins and remaining value in one go.
      int coins = remaining / denomination;
      
      // Forgot to add this first time through - don't need to
      // do the rest if there aren't any coins
      if (coins == 0) 
        continue;
      
      // Got this backwards first time - denomination % targetValue
      remaining = targetValue % denomination;
      result.Add(denomination, coins);
      Console.WriteLine("Adding {0} coins of denomination {1} leaving {2} to work with...",
            coins, denomination, remaining);
      
      if (remaining == 0)
        break;
    }
    
    return result;
  }
}

And see if it works when you finally build it

When I saw this problem I ended up with a solution that resembles the one above. I did make some goofy mistakes that involved some wiping out and re-“coding” on the whiteboard. For example, I'd just been using Excel to do some modulus work and actually wrote in the Excel Mod function rather than the C# modulus (%) built in function. Embarassing! The other things I found when I actually tried to compile then run the code are listed in C# comments. For example, I got the order of the two parameters in the % call backwards. Here is the final part where I report the number of different coins to use:

var result = CoinCounter.GetMinimumCoinsNeeded(new[] { 2, 1, 100, 500, 10, 25 }, 2341);

foreach (var item in result)
{
 Console.WriteLine("Denomination: {0}, Coins: {1}", item.Key, item.Value);
}

Which returns this output:

Denomination: 500, Coins: 4
Denomination: 100, Coins: 3
Denomination: 25, Coins: 1
Denomination: 10, Coins: 1
Denomination: 1, Coins: 1

Try it yourself. It’s a good test of your logic ability and knowledge of your core language and platform.

It only took me 3 hours to get Rails running on Ubuntu

I wish! I've used Ruby (the .NET version IronRuby at least) for building a project, and tinkered with Rails in a non-serious way, but I find myself wanting to test something real for work these days. Well, it's a long shot, but I'm wondering what the cost in man hours would be to move our public Subversion subversion from a Windows 2003 VM to Ubuntu. The reason? I like VisualSVN Server for its ease of use and no brainer installation, but I need to get a decent web GUI for svn up and running. There's the PHP-based WebSVN project that presumably I can plug in to svn, but I honestly don't want to mess with co-hosting the private Apache server that VisualSVN uses on port 80 with a full blown Apache install...also on port 80...just for the PHP stuff.

Maybe that really isn't too big of a deal, but I also find myself wanting to try out the truly beautiful and useful svn GUI that is Warehouse. It is a Ruby on Rails application, and that is the real impetus for trying Ubuntu and Apache: Rails is a bugger to get working correctly on Windows in anything other than play mode. I've tried mongrel packs (!), WebBrick (can't believe I actually did that), and other approaches and it just seems like I'm trying to force the Rails round peg into the Windows square hole. So on to Ubuntu. I've been using Sun's (Oracle's?) VirtualBox for a while now, ever since VMware completely hosed my laptop and external USB drive with all my backups. I really like it because it just works and has great bridged networking support. What with all the crazy eth0 and other intuitive network adapter names on Linux, that out-of-the-box networking experiences is truly a blessing. So without further ado, here's a screenshot of my desktop about 10 minutes ago. I'm pretty pleased with progress thus far, even if most of it was trolling through online "HOW-TOs" and the phenomenal TekPub Linux for Softies screencast series (totally worth $14).

I have to say, the apt-get package manager for Ubuntu is truly amazing. I was floored when I just typed sudo apt-get install apache2 and it just worked. Anyway, nothing useful to mention like how I actually got this far, but again, I'm pretty pleased with the end result: a scaffolded Rails app that actually serves up HTML. Woohoo!

Oracle Spatial SIG group

I just finished setting up a new place on the web for the Independent Oracle User Group (IOUG) Oracle Spatial Special Interest Group (SIG). That’s a mouthful so luckily we abbreviate it to Spatial SIG and still know what we are referring to. Anyway, if you are interesting in Oracle Spatial technology then head on over to the new group site and take a look at membership options, our charter, and the newly relaunched discussion list. Okay, so the discussion list has one post in it so far (from yours truly) but it will get more content as the days and weeks pass.

Lessons learned on a project...time to 'fess up

Here's an internal presentation I gave at one of our ‘DevCasts’ a few months back. I shared my successes and failures with the team in return for a great discussion on effective use of tooling, when is too much design, and some virtual back slapping. Nothing earth shattering but I learned a lot...meaning it hurt sometimes but felt good at the end of the project!

Dev Cast - Lessons from a Complex Application

View more presentations from Dylan Thomas.

Markdown, Pandoc, Love

I'm in the middle of writing a lengthly software requirements spec this week. It's actually been fun to go through the process again on a fresh project with a clean slate. The requirements gathering meetings were great: business leader, sales people, and developers all in one room and surprisingly united in their goals. I used the usual tools of the trade—like FreeMind and Unable (a clone of a great little note taking application called Notable)—and was able to get a lot of the requirements and user story details knocked out on site.

Then I was sitting back at the office wondering how to keep all of my SRS edits in version control and hit upon the idea of using HTML rather than Word. No good reason other than wanting to keep my changesets small. I'm very comfortable with HTML and can whip out a mean CSS/HTML combo in no time flat. The beauty of it all, of course, is that I can just write what I want without fussing over headers, page numbers, indenting bullets the right amount, and all that guff. I like a good looking document as much as the next guy but frankly I just needed to focus on getting the requirements done, not worrying about the font color of my headings.

Enter Markdown

Being no stranger to sites like Stack Overflow I know that people can write pretty complex stuff using HTML. But also that it's a total drag when again, all I really need is the write text with some minimal markup. So if I want to highlight something I might type this is *important* stuff and I might want to create a bulleted list like so:

* item one
* item two
* and something else

This is pretty much how I write notes anyway, and it turns out it's very, very close to how a simple 'HTML lite' syntax called Markdown does things. Essentially is a dead simple syntax that can be transformed into HTML by any number of tools. So I did a little test. I started to write my SRS in Markdown format using a text editor rather than Word, all the while committing my changes to Subversion. It was actually pretty nice and I did indeed focus on writing instead of formatting. But then what? I've got a text file in some goofy format that only a programmer could love (ahem! guilty as charged!!!). I needed to deliver something usable to my client like a web page. Here's what a typical section looks like:

#### Trademarks
All brand names and product names are trademarks or registered trademarks of their respective companies

Software Requirements Purpose and Process
-----------------------------------------

A Software Requirement Specification--hereafter called an SRS--is an initial attempt to describe the functionality of a custom software product or custom system. It can be likened to the blueprint for building a new house (or perhaps remodeling an existing house). Just as an architect guides a homeowner through their needs for the new house, so Woolpert technical staff guide clients though the requirements process. This implies two very important points that the client project management team and stakeholders should remember through the software requirements process:

* *Continuous client involvement* is crucial to success. A homeowner generally does not sign off on architectural plans early on and then just hope that the architect understood perfectly your meaning. Woolpert technical staff are highly skilled, but they cannot possibly know all the needs or business processes of the clients as well as they do.
* *Requirements are a moving target*, not a set of mandates that are set in stone. To be sure, some requirements are so central to the project that they are unlikely to change. However, changing requirements...

Ignore the actual content for a second, and focus on the simple rules that Markdown uses. If I want a heading I underline the text. If I want bullets I put an asterisk as the first character and leave some white space around the 'list'. If I want a highlight (<em> in HTML) I put a couple of asterisks around the text. Simple. To be sure there are complicated parts but at that point I just drop in the HTML, e.g., a div is just a div.

Enter Pandoc

In the 'Holy Cow! How did he do that?' category I found a wonderful little program called Pandoc written in Haskell (?!) by an associate professor of philosophy from the University of California at Berkeley (!!!!???!?!). Not exactly a normal, run of the mill combination there, but Pandoc is a beautiful little program. It essentially does one thing, and does it exceptionally well: it takes one or more Markdown text files and spits out HTML or PDF (or DocBook and a few other formats). So in order to turn my srs.markdown text file and various screenshots in to an HTML page complete this those hyperlink-thingies, I just double-click the batch file I wrote and in less than a second I've got an excellent SRS document in HTML format that my client can open on any computer, share with anyone on the team, and click around in. From my script you can see that I'm including some CSS stylesheets, I'm asking for HTML out, and I've overridden a default HTML template because I needed to add some autogenerated stuff to the HTML HEAD section. Oh, and I also wanted it to generate a hyperlink table of contents for me based upon my H tags. Short and sweet.

pandoc -o srs.html
  -c "assets/css/blueprint/print.css"
  -c "assets/css/blueprint/screen.css"
  -c "assets/css/woolpert.css"
  -w html srs.markdown
  --template html.template
  --toc

I wish I could show you some of the output but I'm under a non-disclosure agreement. But I promise, it's really...neat. Not to mention, when I've made changes, my client simply gets the latest from my employer's public svn repository, double-clicks the same batch file, and gets the very latest docs. [Smile]

Whew!

Holy tamales, what a day! I just wrapped up a marathon session getting a new "product" ready for a demo on the east coast (yes, the one that's not the West Coast). I spent all of yesterday struggling with 3rd party vendor software and finally deciding that it sucked and I can do it better myself. So I ripped that stuff out, wrote some handy dandy SQL, and had a WAAAAAAY faster solution by 7:00am this morning.

I fed Lyra and reunited her with her pacifier to grant us some peace and quiet for an additional 30 minutes, and managed to knock out some other bugs that had be...well, bugging me. All before 8:00am :-) And a few hours later it's looking really spiffy. OK, so actually it's not that exciting to look at, just a couple of ASP.NET MVC sites with dead simple controllers. But man, oh man, is the code behind it all sweet. We've got messages, we've got reliability, we've got factories, we've got...whatever: it's freaking cool because it took me ages and the prospective client is going to be very excited when I show them a web page with their logo on it.

The real bottom line is that I can actually look forward to my long flight tomorrow as a time for tweaking and tinkering and polishing the demo rather than sweating my 'nads off while trying to just get the code working. “I love it when a plan comes together”