MSSQL MERGE/UPSERT with Ruby and the Sequel gem

My last post contained some code snippets useful for working with an Oracle database from Ruby. I’ve also had to interface with Microsoft’s SQL Server database (to add data to a data warehouse) using Ruby and Sequel. Here’s a snippet for using the MERGE statement with the Sequel gem, useful unless/until Sequel gets official MERGE support. This snippet only supports basic UPSERT functionality, where data is updated when the key is matched, inserted when not matched. It does support multi-column keys for matching.

The code builds the SQL dynamically since it has to insert a different number of parameters based on how many columns are being updated or used as keys.

Make sure you read the code comments from the snippet. Also make sure you’ve read up on how the Sequel gem qualifies schema, table, and column names using double- and triple-underscore.

You’ll need the following gems:

Interfacing Legacy Oracle PL/SQL and Modern Ruby Code

Ruby and PL/SQL GitHub Gist

My last couple of blog posts have been about the backend and middleware work I did at my previous employer. I wrote about two Ruby gems, ClassyHash and HashFormer, that help with data validation and transformation in backend APIs and in middleware. Today we’ll look briefly at some code snippets to help Ruby developers interface with older systems that were written in Oracle PL/SQL, possibly using Oracle applications like EBS (E-Business Suite).

To help launch my previous employer’s updated site, I kind of dove head-first into the deep end of PL/SQL, teaching myself the PL/SQL language, refactoring thousands of lines of legacy code that was not designed with maintenance in mind, and learning chunks of Oracle EBS (both the arcane UI and the even more arcane internal PL/SQL interfaces) over a period of several months. Let my pain be your gain. Continue reading for essential gems, some tips and tricks, and a wrapper script to make the sqlplus command-line interface a little nicer for Ruby developers.

Hashformer: Transform data in Ruby

Hashformer on GitHub

Let me tell you about Hashformer, another open source Ruby Gem I wrote for my previous employer. Like ClassyHash, Hashformer was written as part of the “middleware” interface between a Solidus ecommerce site and an Oracle E-business Suite backend. Hashformer defines a Ruby DSL for basic data transformations, like renaming keys, merging values, etc.

Hashformer is a bit more niche than ClassyHash — most projects probably wouldn’t have a use for it, but when you need it, you need it. It’s similar in spirit to that old four-letter word, XSLT, but simpler and Ruby-centric. Read on for an example.

Data validation in Ruby with ClassyHash

In this post I will briefly demonstrate the ClassyHash gem, which is a fast data validation gem written in pure Ruby. I recently released version 0.2.0 of ClassyHash, with some cool PRs integrated and quite a few new features. Here’s a quick example:

schema = {
  query:'repo', 'user'), # Match either 'repo' or 'user'
  values: [[String]], # Match an array of zero or more Strings
data = {
  query: 'repo',
  values: ['classy_hash', 'hashformer'],
CH.validate(data, schema) # Returns true if the data is valid, raises an error if not

Continue reading to learn about ClassyHash’s history and see a basic ClassyHash validation tutorial.

Resurrecting a dinosaur: the iBIZ KeySync keyboard for Pocket PC

Several years ago I came across a number of obsolete Pocket PC handhelds and accessories at a surplus sale. While the PocketPCs themselves weren’t worth keeping due to the dead batteries and slow processors, I held onto a compact portable keyboard with a 9-pin serial connector made by a company called iBIZ. I’m finally getting around to hooking the keyboard up and seeing if I can get it to work. In this post I’ll document the process of connecting to the iBIZ KeySync keyboard and decoding its protocol.

New Blog Theme

If you’ve come to my blog before, you may have noticed there’s now a new site design. While Octopress is still awesome, my previous Octopress-based blog theme wasn’t a good match for the new design of my main site. This updated design, using Bootstrap with some customizations, pays homage to the original Octopress layout, but provides a more modern look and feel.

I’m a desktop first kind of guy, and even I love the new theme on my mobile devices. I think you will too. Continue reading to see a side-by-side comparison of the old and new styles, and to see some of the other new tricks of the upgraded theme.

RailsConf 2014 Lightning Talk Slides - Failures of Distributed Systems

I’ve been doing a lot of Ruby on Rails development lately, and this year I had the privilege of attending RailsConf 2014. DHH created a bit of a stir when he declared TDD obsolete and dubbed us all software writers, and several of the sessions were related to distributed systems and service-oriented architectures.

As such, I was motivated to give a presentation during the “open mic” lightning talks session about the failures of distributed systems, and how software writers can use some engineering techniques to survive these failures. After much delay, I’m finally uploading my hastily prepared slides (with one minor grammatical correction).

Important bug fix in Depth Camera Controller firmware build 62

I’ve released a new version of the Depth Camera Controller firmware that should fix a nasty bug affecting certain newer models of Kinect for Xbox 360 and Kinect for Windows sensors. This bug caused those particular sensor models to display a “Camera Offline” error. Though controllers attached to these newer sensors still do not have 100% of the compatibility of the original Kinect for Xbox 360, they should provide fully functional depth and brightness sensing.