Category: code

Color Palette Generator

by Jason on

mouseover me…

I’ve been wanting to create something to get color averages or perhaps generate a palette from an image for awhile now and decided to whip something up using something I rarely use: HTML5’s canvas tag.

The images in this post are drawn onto a canvas and processed using this Palette class. Note that image size is defined via CSS. A babelized version is available for evergreen browsers here.


			    <div class="palette-example2"></div>
			    <link rel="stylesheet" href="/color/scripts/index.css" />
			    <script src="/color/scripts/dist/index.js"></script>
			    new Palette({
			        image: '/color/dragon.jpg',
			        selector: '.palette-example2',
			        horizontalSegments: 20,
			        verticalSegments: 10,
			        displayPalette: false, // default true
			        displayTools: false // default true

			    new Palette({
			        image: '/color/oscar.jpg',
			        selector: '.palette-example3',
			        horizontalSegments: 1,
			        verticalSegments: 100,
			        threshold: 60


  • image
    Type: string

    URL of the image source

  • selector
    Type: string

    CSS selector for the target element / container

  • segments optional
    Type: number

    Number of horizontal and vertical image segments to generate

  • verticalSegments optional
    Type: number

    Number of vertical image segments to generate

  • horizontalSegments optional
    Type: number

    Number of horizontal image segments to generate

  • threshold optional
    Type: number

    Number between 1 and 255 used to calculate whether a color is sufficiently different enough to be included in the palette

  • displayPalette optional
    Type: boolean

    Display the palette div

  • displayTools optional
    Type: boolean

    Display the tools / slider div

Mise en place - Duex

by Jason on

Part Duex
(Better, Faster, Stronger… Silverier?)

An update to Mise en place development environment

Mise en place (or, literally translated, everything in its place) is a culinary concept that we would do well to practice in software development. Like starting on a woodworking project with a cluttered shop or painting without first prepping the canvas, you’re setting yourself up for failure without first starting from a place where all your pre-requisites are available.

So what’s changed in 4 years…

When I last addressed this my daily routine consisted of creating C# applications and web applications with an occasional foray into PHP or JavaScript applications in Adobe AIR. 5 years later and I still do a heavy bit of C# however the majority of my time is spent enjoying JavaScript. There were times in the past where I made the erroneous (and misinformed) choice of $ over functionality and used PHP where I could have used C#. For other things there were times when I would use Adobe AIR to craft some simple cross-platform desktop application that, given the same constraints, today I’d probably choose nwjs or if I was convinced I couldn’t get by with pure JavaScript, C#.


  • A decent JavaScript editor. There are so many great ones to choose from now. Visual Studio excels at JavaScript but sometimes you just want something that’s a bit lighter. I’ve found Brackets to be a good choice on Windows or, when on Mac, SublimeText or WebStorm.
  • Microsoft Visual Studio. Visual Studio remains a joy to work with. If you’re not fortunate enough to be provided a Pro or other commercial license, get Community, it’s awesome.
  • Node.js (of course)
  • Git
  • Virtual Machines

Nice to Haves

  • Database administration. From MongoDB to the growth in usage of PostgreSQL the number of database technologies a developer might need to work with on a typical project has grown. These days I still keep Microsoft SQL Management Studio around (and it gets heavy usage) but since it’s just as likely I’ll need something else this is no longer a must have.
  • A decent image editor: my usage of this has dropped dramatically and I rarely find myself needing to produce anything with these tools. But when you need one it can be a huge time suck to find or install.
  • Patterns was recommended to me by a colleague a year or two ago and I’ve been surprised how much I’ve come to use it.
  • Fiddler (Windows) or Postman (Mac).
  • I’ve alluded to it throughout the post: OS X. In the past I enjoyed working in Windows primarily and was a staunch advocate of the ecosystem. I begrudgingly switched to Mac when offered by my employer (all-the-while making snide uninformed comments along the way) and over a surprisingly short amount of time grew to love it. Windows is now usually in a VM unless I just don’t have access to a Mac. “Finder” and window management is a joke in OS X compared to Windows (or Linux) but it’s enough of a *nix system to keep me happy and productive while not interfering with my productivity with arbitrary (or whimsical) user interface flaws.

Well that’s it for this!

See you in 4 or 5 years for Mise en Place Trois.


Using Cloudinary with Ghost

by Jason on

Update: Looks like Ghost will support this sort of thing through the App API. Keeping this around in case anyone needs it before that feature goes live.

Decided to take Ghost for a spin. My testbed for that kind of thing is typically AppFog.

Installation was pretty simple. (Here’s a snippet of my Ghost config that I got running on AppFog for anyone who might be looking for this sort of thing. Ignore the storage section unless you read on.)

So it’s alive, running and woohoo! But then I uploaded a cat picture (are there any other kinds?), restarted the application and my picture was gone. It was then I remembered:

No persistent file storage on AppFog

This is a big problem. I can’t upload pictures of cats! Wha? What’s the use of a blog anyway without that functionality! However let me be clear this is an AppFog problem, not a Ghost problem. AppFog has been working on “persistent file storage” for well over a year now.

Ghost is easily extendible and this is a solvable problem. We could use Cloudinary, Amazon, Rackspace or any other cloud to solve the problem. I settled on Cloudinary as it was mentioned in the Ghost to Wordpress migration plugin.

My additions to support Cloudinary in Ghost are in this pull request. Check it out if you’re facing the same issue and want get around AppFogs problems or if you want to extend Ghost support to more cloud providers.

My config for Ghost on AppFog: (my changes from the default highlighted in blue.)

			// # Ghost Configuration
			// Setup your Ghost install for various environments
			// Documentation can be found at
			var path = require('path'),
			var mysqlCred = {};
			if (process.env.NODE_ENV === 'production') {
			    mysqlCred = (JSON.parse(process.env.VCAP_SERVICES || '{}')["mysql-5.1"] || [{}])[0].credentials;
			config = {
			    // ### Production
			    // When running Ghost in the wild, use the production environment
			    // Configure your URL and mail settings here
			    production: {
			        url: '',
			        mail: {},
			        database: {
			            client: 'mysql',
			            connection: {
			                host     :,
			                user     : mysqlCred.user,
			                password : mysqlCred.password,
			                database :,
			                charset  : 'utf8'
			            debug: false
			        server: {
			            // Host to be passed to node's `net.Server#listen()`
			            host: process.env.VMC_APP_HOST || '',
			            // Port to be passed to node's `net.Server#listen()`, for iisnode set this to `process.env.PORT`
			            port: process.env.VMC_APP_PORT || 1337
			        storage: {
			            type: 'cloudinary-file-store',
			            credentials: {
			                cloud_name: '',
			                api_key: '',
			                api_secret: ''

Webcam stop motion animator project

by Jason on

Here is a thing I’ve thrown together to do basic stop motion animation using webcams. Tested using my built-in hp webcam as well as a nicer standalone Logitech 9000 series. 52kb To run, unzip this and run StopImage.exe

Requirements: .NET framework 2.0+, a webcam, and probably more skill than I have at stop motion animation :D

To do:

  • provide a method to change the target framerate (currently this is fixed at 13 fps)
  • make it easier to upload videos after creation (right now videos, after creation, are in My Stop Motion Projects/Your Project Name/Your Project Name.avi)
  • provide the ability to select cameras if a user has more than one (right now you may have to disable an onboard camera in order to access a higher quality USB camera)
  • create a basic installer etc
Want more features or have an idea? Just ask in the comments!

A quick sample output:

About this app: Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License. This app was created using C# in Visual Studio 2010 and uses the AForge.NET Framework for video rendering and VFW device access.

CSS Gradient Tool

by Jason on

Here’s a CSS Gradient Tool I created to help shortcut browser compatibility with CSS gradient backgrounds.

Please note, while you can save your gradients into a “pallet” of sorts (the actual reason I made this) this version only saves to a local cookie. Additionally you can output the bare minimal settings required (colors & color stops) to json if you find that handy for some reason. (I needed the JSON for another project)

Mise en place development environment

by Jason on

Photo by chotda Creative Commons photo by chotda

As I’ve grown older I’ve become obsessive about my development environment and it’s state of readiness. A few months ago Cynth laughed at me because I said I couldn’t get to work yet (it was 8 am) because I had left my headphones in the car and my coffee was still brewing. I told her I hadn’t setup my mise en place yet.

It’s not that I’m a prima donna (or at least I hope not). I’ve just learned over the years that starting a project (or starting the day) without having everything “put in place” can lead to huge gulfs of waisted time. The coffee and headphones were a joke (just barely though!) However some stuff absolutely has to just be there and working properly. Usually this is in the form of “tools” or the “environment”.

The following is my development mis en place on a Windows development box

The obvious stuff:

  • A recent stable Netbeans IDE with at least Java and PHP
  • Visual Studio 2008 (with at least C# and VB of course)
  • SQL Server Management Studio (hopefully not express)
  • MySQL Tools: (I prefer to have both MySQL Workbench and sqlYog available)

The nice to have stuff:

  • A decent image editor. (I prefer having both Photoshop and Fireworks available.)
  • Fiddler for troubleshooting anything that communicates over http
  • WinMerge for those times you have to work with another developer that is too “busy” for source control.
  • Firefox+Firebug and Google Chrome

The stuff that saves epic amounts of time:

  • VirtualBox running the closest clone of a production environment I can simulate. (Usually for me this is latest Fedora, latest CentOS, Windows Server 2005 or 2008) I do sacrifice some things for comfort here. I always use a shared folder for development and I forward all Apache, IIS, SQL and MySQL ports to the host machine through guest extensions.)
  • VirtualBox with a version of Windows XP SP2 for testing compatibility with IE7 (not 6, let 6 die) or for testing C# desktop installations in earlier versions of .net when necessary).
  • A decent Subversion client (I really like Netbean’s SVN plugin although it is pretty basic)

What since you don’t work in a team, you don’t need source control?The next time it is 2am and you have written yourself into a corner and you know that your work at 9pm was much closer to what you need now: I hope you can comfort yourself thinking about all the time you saved not using source control.

The rest:

  • 7zip
  • Various SDKs (I usually have at least the android, air and flex sdks available)
  • winMTR (is the downtime caused by them or us?)
  • FileZilla Client
  • putty
Well that’s it for my development environment.

Want to share yours, have suggestions, criticism or questions? Leave a comment.

Oooh, an update! Shiny!

jQuery nextN and prevN continued...

by Jason on

Previously I provided a jQuery plugin for next N siblings.

I’ve extended this a bit to include or not include the parent selector as well as providing a prevN plugin.

jsFiddle examples:

nextN plugin javascript:

			 *  nextN jQuery Plugin
			 *  example: $('li').nextN(5)
			 *  returns the next 5 siblings
			 *  example: $('li').nextN(5, true)
			 *  returns the next 5 elements including the parent selector
			 *  example: $('li').nextN(5, false)
			 *  returns the next 5 siblings
			(function($) {
			        nextN: function(nextN_limit, includeSelf){
			            var nextN_els = (includeSelf ? this : $());
			            var lastElement = this;
			            if(includeSelf){ nextN_limit--;}
			            //loop until limit or break;
			            for(var i=0;i<nextN_limit;i++){
			               //add next sibling after last if it exists
			                if($(lastElement).next().length > 0){
			                    nextN_els = $(nextN_els).add($(lastElement).next());
			                    lastElement = $(nextN_els).filter(':last');
			               } else {
			            return nextN_els;
prevN plugin javascript:
			 *  prevN jQuery Plugin
			 *  example: $('li').prevN(5)
			 *  returns the previous 5 siblings
			 *  example: $('li').prevN(5, true)
			 *  returns the previous 5 elements including the parent selector
			 *  example: $('li').prevN(5, false)
			 *  returns the previous 5 siblings
			(function($) {
			        prevN: function(prevN_limit, includeSelf){
			            var prevN_els = (includeSelf ? this : $());
			            var lastElement = this;
			            if(includeSelf){ prevN_limit--;}
			            //loop until limit or break;
			            for(var i=0;i<prevN_limit;i++){
			               //add next sibling after last if it exists
			                if($(lastElement).prev().length > 0){
			                    prevN_els = $(prevN_els).add($(lastElement).prev());
			                    lastElement = $(prevN_els).filter(':first');
			               } else {
			            return prevN_els;

jQuery selecting the next N siblings

by Jason on

Problem: you have a large set of tags and need to get the next 50 out of the set.

To do this I would in the past use: $(‘li’).nextAll().slice(0,50) Or $(‘li’).nextAll(‘*:lt(50)’)

However on a very large set of tags (> 20,000) nextAll can cause script timeout warnings / errors. (This makes sense as you are getting the entire set, storing it in a jQuery object and then filtering it.)

Here’s a tiny jQuery plugin that provides “nextN”:


  • nextN jQuery Plugin *
  • usage: $(‘li’).nextN(50)
  • returns the top 50 elements that match the parent selector / (function($) { $.fn.extend({

    nextN: function(nextN_limit){
    			    var nextN_els = this;
    			    //loop until limit
    			    for(var i=1;i&lt;nextN_limit;i++){
    			       //add next sibling after last if it exists
    			       if($(nextN_els).filter(':last').next().length &gt; 0){
    			            nextN_els = $(nextN_els).add($(nextN_els).filter(':last').next());
    			    //return modified jquery object
    			    return nextN_els;

    }) })(jQuery);

Note: unlike “nextAll” and “next” this plugin returns the parent as well.

How common a problem is this? Probably pretty rare. In most instances $(‘li’).nextAll(‘*:lt(50)’) would be appropriate.

Requiring elevated UAC permissions in C#

by Jason on

More of a note to myself than anything else but perhaps it would be handy for others…

2 Solutions:

Throw the UAC prompt when a user tries to execute your application:

To enforce UAC rules on your C# application add the appropriate param in your application manifest file:

Add the Application Manifest:

Add New Item to your project, under C# items, Application Manifest File.

Open your manifest, under “requestedExecutionLevel” change the level param to “requireAdministrator” and voila, your application will now throw a UAC prompt before executing.

Note: using Visual Studio 2008

Run the app regardless and bail when incorrect permissions are found

If you’d like you users to just right click / run as administrator and not worry about UAC add the following to your Main sub or application entry point:

add this method to you application:

public static bool checkAdminPrivileges(){
			    WindowsIdentity i = WindowsIdentity.GetCurrent();
			    WindowsPrincipal p = new WindowsPrincipal(i);
			    return p.IsInRole(WindowsBuiltInRole.Administrator);

Call that method and take appropriate action:

if (!checkAdminPrivileges())
			        Console.Error.WriteLine("Access Denied. Administrator permissions needed");
			        //do something here, exit, die

Adobe AIR 1.5 and SQLite example

by Jason on

I’ve been asked a couple of times about how to track an adobe AIR application you’ve developed without disclosing personal information or requiring user interaction.

My answer to this is always the same: I usually store a randomly generated key in the SQLite database when the application is first run.

I’ve decided to upload some code to demonstrate my approach to synchronous database connections in Adobe AIR. The version of the code provided is a bit stripped down from what I usually use (my main code provides for both synchronous and asynchronous connectivity but I stripped a lot out for this example and will provide a seperate example for asynch at some point int he future.)

To reiterate: This code may be overkill for some people and uses my custom namespaces.

Now that I’ve got that out of the way:

Download: SQLiteExample.air OR License: Creative Commons Attribution 3.0 United States License (Please keep or attribute name in code comments etc.)

Quick and dirty explanation: Since this is a lightweight application I use my trusty airDB javascript namespace. airDB is just a wrapper I wrote to simplify synchronous SQLite connections to the air database. Three main methods are really all you need to understand to get started (see code as well method list below). Those are airDB.init(), airDB.fetchRS and airDB.execute. If you’re just looking for an explanation of how to connect / work with a SQLite database in AIR read the documentation here or check out my source file ns.airDB.js (I’ve heavily commented it hopefully to help explain things but frankly I’m bad at that so the official Adobe help is probably the way to go).

Within the startup method in the index.html file you’ll find that I’m calling airDB.init with an object passed in.

// initiate db connection / test schema etc // see init in ns.airDB.js for more information airDB.init({ databaseName: ‘example.db’, // name of our database fetchJSArray: 1, // force fetchRS to return a javascript array openImmediately: 1, // open the database on init testSchema: 1, // test the database on init traceLog: 1 // verbose log to air.trace });

A little information about ns.airDB.js:

  • airDB.init (options)

    • performs initial setup of connection variables needed to access the SQLite database in synchronous modeSee the init method for default options and their use
    • Returns: null

    • opens the database connection. Requires that airDB.init has specified a filename to connect to
    • [Optional]; can be called directly from init if openImmediately is passed in as 1.
    • Returns: null
  • airDB.close()

    • closes the database connection
    • Returns: null
  • airDB.test(forceFail)

    • performs a basic schema test to see if the database is setup. (set forceFail = true to recreate the database regardless of test results).
    • Note: This method should be rewritten to each specific application as schema will vary per application. The provided one is super simple for examples sake.
    • [Optional]: can be called directly from init if testSchema is passed in as 1.
    • Returns: null
  • airDB.connected

    • Returns: boolean (whether connection is present or not)
  • airDB.fetchRS(sqlStatement)

    • performs a SQL Select statement or other statement that is expected to return a recordset.If fetchJSArray (in init) is = 1, we return a custom javascript object that’s formatted like the following: { rows: number of rows returned, data: array( row 1 => {columnName1: columnValue1, ColumnName2, ColumnValue2} ) } (using this type in the example code)

      if fetchJSArray (in init) is = 0, we return a object (More information on handling that return type can be found here)

    • Returns: Javascript Object or Flash.Data object dependent on init option “fetchJSArray”
  • airDB.execute(sqlStatement)

    • executes a sql statement (insert / update / delete / drop etc.)
    • Returns: null
  • airDB.sqlText(string)

    • replaces any single quotes in a string with double quotes
    • Returns: string
  • airDB.sqlNumber(number)

    • ensures that a number passed in is numeric / ok to execute in a sql statement
    • Returns: number (returns 0 if number is not numeric)
After init in the “startup” function I call airKey.init airKey is a javascript namespace solely created for generating a random string of however many characters you wish and storing that in the local database. I won’t go into too much detail here because it’s super easy to understand (generate a key if one isn’t present and store it in the database, if one is already present, retrieve it.) (see ns.airKey.js)

I then call a custom function called “getNames()”. getNames is just an example of how to call a select statement and use the custom JS array to output data. In the example provided I fetch the column names as well as the values to generate the table of “names” within the main air window. When looking at the function you might ask why I’m using a custom javascript recorset that fetchRS returns rather than the standard object. 1 : my custom object returns the number of rows as a static int, so that I never have to calculate that. 2 : I also don’t want to pass flash objects around because it’s caused memory leaks for me in the past that were a pain. (However if that’s your thing, remember you can just set fetchJSArray = 0 (or not pass in that option at all) in init to just use standard access methods. (I do this for large databases because transforming the data on large recordsets is a bad idea)

Other than that, this example is pretty basic. Adding a name using airDB.execute as well as rebuilding the database from scratch is included in the example.

Finally: a bit more about airKey. by storing a random string I can test against that when the application performs URLRequests against an owned web service. This lets me generate reports (or turn off a particular app) without having to be too intrusive to the user. To make this more robust I highly recommend using the local Encrypted Store available to you in AIR applications. (If you’re worried about piracy of your application the encrypted store will help a little but frankly right now AIR is not the platform (for HTML/JS applications) to use if that’s your concern as source code is freely available to the user.)

If you have any questions feel free to ask in the comments and I’ll answer.

Adobe AIR print css and html printing hack

by Jason on

Posting to give an example of how to print using the local browser using Adobe AIR.  This is in response to this AIR forum thread.

I need to have an alternate stylesheet for my application so I embed those styles in an inline style tag from a css file.  I also do not want to include all the scripts and external references that my application may have on the particular page so I strip out only the body and build a new page from it.

This probably isn’t for everyone but might give you a good idea of where to start (or provide an example of how not to do this).

Note this uses jQuery’s .html function, but you could just as easily replace that with document.getElementById(‘MyPrintableStuff’).innerHTML();


			    Quick and dirty print function for AIR
			    @htmlTitle string    :    text to be placed in <title> tag of the document
			    @printCSSFile    :    css file to parse, should be relative to the app ie ‘stylesheets/print.css’
			function printThis(htmlTitle, printCSSFile)
			    var locFileStream = false,
			        styleFile = air.File.applicationDirectory;
			        locStyles = ‘’;

<span class="rem">/* fetch css definitions for print stylesheet and store in locStyles */</span>
			styleFile = styleFile.resolvePath(printCSSFile);
			locFileStream = <span class="kwrd">new</span> air.FileStream();, air.FileMode.READ);
			locStyles = locFileStream.readUTFBytes(locFileStream.bytesAvailable);
			<span class="rem">/* HTML to string */</span>
			locHTML = $(<span class="str">'body'</span>).html();
			<span class="rem">/* wrap with doc tags, add &quot;window.print&quot; to the onload */</span>
			locHTML = <span class="str">'&lt;!DOCTYPE html PUBLIC &quot;-//W3C//DTD XHTML 1.0 Strict//EN&quot; &quot;;&gt;'</span> +
			        <span class="str">'&lt;html xmlns=&quot;; xml:lang=&quot;en&quot; lang=&quot;en&quot;&gt;'</span> +
			        <span class="str">'&lt;head&gt;'</span> +
			        <span class="str">'&lt;title&gt;'</span> + htmlTitle + <span class="str">'&lt;/title&gt;'</span> +
			        <span class="str">'&lt;style type=&quot;text/css&quot;&gt;'</span> +
			        locStyles +
			        <span class="str">'&lt;/style&gt;'</span> +
			        <span class="str">'&lt;/head&gt;'</span> +
			        <span class="str">'&lt;body onload=&quot;window.print()&quot;&gt;'</span> +
			            locHTML +
			        <span class="str">'&lt;/body&gt;'</span> +
			        <span class="str">'&lt;/html&gt;'</span>;
			<span class="rem">/* output the file to a temporary file in user's docs, launch url in native browser</span>

TODO: delete tmp file after launch */ var tmpFile = air.File.documentsDirectory; tmpFile = tmpFile.resolvePath("temp.html"); tmpURLToOpen = tmpFile.url;

var tmpFileStream = <span class="kwrd">new</span> air.FileStream();, air.FileMode.WRITE);
			var locURLReq = <span class="kwrd">new</span> air.URLRequest(tmpURLToOpen);
			<span class="rem">/* null objects */</span>
			locURLReq = <span class="kwrd">null</span>;
			tmpFileStream = <span class="kwrd">null</span>;
			locFileStream = <span class="kwrd">null</span>;
			styleFile = <span class="kwrd">null</span>;


Adobe AIR 1.5 Print Styles (UGH)

by Jason on

While writing a new Adobe Air app it’s come to my attention that along with the pitiful print functions available in Adobe Air 1.5, print media stylesheets also do not appear to work.

Here’s the contents of my test stylesheet:

@media screen
			    .noprint {display:none;}

@media print { .noprint { display:block !important; padding: 5px; border: solid 3px #0000FF; font-weight:bold; text-align:center; } }

As you can see the display style “noprint” is defined as “none” in screen and block in print. I’ve attached a pdf of the output of what it should look like and what it does in fact look like.

All in all printing in Air has been an extreme disappointment. If you are building a desktop application then printing is, by necessity, a highly probable feature.

Test file Printed w/ Air Adobe Air “printing” (PDF)

Same file printed w/ Firefox: Firefox Printing (PDF)

Frankly I just don’t see an excuse for the shoddy print functionality in Air.

If you’re considering Air for your next desktop widget please consider the following:

  1. Does your application need to print anything?
  2. Do you mind your print looking like monkey-ass?
If you answered Yes to either of those questions then Air may not be the tool for you just yet.

Position:fixed and floating divs in IE6+

by Jason on

I had a need to keep a “header” div at the top of the page at all times. This was surprisingly difficult considering that IE6 and apparently IE7 do not support position:fixed.

After a lot of tweaking and various hacks I came across this solution which works nicely.

Simply put after creating my div I assigned it an id of “top” and created the following style rule in my stylesheet:

height: 100px;
z-index: 999;

This is based on Anne’s example here.

Very handy, tested in IE6, IE7, Firefox, Safari for Windows beta.

Safari gives some funny results on a menu that is contained in my div, but Safari gives funny results for all sorts of things so I’m not going to lose sleep over it.

Tree / Hierarchical Recordset in Sql Server 2005

by Jason on

Scenario: Your uncle Tim has been selling AmWay products since 1987.  Over the years he’s brought on quite a few “distributors” and needs to know the structure of his mighty branch of the multi-level marketing machine.  Lucky for you he’s been keeping records digitally on the same computer he purchased in 1984 with Lotus 123.  Unfortunately it’s a mess and he wants you to turn that data into something useful.

After losing a few hours of your life reading up on some documentation you have successfully imported that data into your handy copy of MS SQL Server.  With much conversation you two decide that what he needs is the ability to drill down to whatever branch of the tree he feels like.

Example Data:

You could recursively loop through the data in a Stored Procedure to spit out your new recordset but what a lot of code!

Enter Sql Server 2005’s CTEs (MS: Using Common Table Expressions)

Warning: Notice I said SQL Server.  CTEs do not appear to be available in Express / MSDE. Please correct me if I’m wrong, would be handy to know.
I first came across CTEs in a book I bought for a coworker who is learning a bit of SQL for one of our in-house tools.  After about 5 minutes of reading I had to slap my forehead against the desk a few times for wasting so much time on so much unnecessary code.

A CTE could be considered an in-line recordset but because of it’s recursive abilities it’s a lot more powerful than a traditional table variable.    (See the MS article above for a real in-depth explanation that does a better job than me).

In short to produce the hierarchical recordset for this requirement we need essentially to build a CTE using a Union within.

DECLARE @Branch AS Integer;
SET @Branch = 1;
With PeopleCTE
    As (
        Select ID, FirstName + ‘ ‘ + LastName As FullName, ParentID, 0 as Lvl
            From tmpPeople where ID = @Branch
        UNION ALL
        Select Child.ID, Child.FirstName + ‘ ‘ + Child.LastName, Child.ParentID, Parent.Lvl + 1
            FROM PeopleCTE As Parent
                JOIN dbo.tmpPeople As Child
                    on Child.ParentID = Parent.ID
Select * from PeopleCTE
    Order By Lvl

Example Output from the statement:

In the example we start off by declaring the root or branch as @Branch.   We then declare the CTE with the “With” statement.

Note: the statement preceding a CTE declaration must be properly closed with a semicolon (;).  I’m terrible about this so I kept my code as close to my normal code (sans-semicolons) as po ssible as an example of what not to do.

 Within the CTE notice that the column definition (much like a normal union) is completely determined by the first statement.  That statement is where we set our root node or @Branch.

The second Unioned statement is a join between our tmpPeople table and the CTE itself.  This is a recursive statement and lets us avoid a loop.  Note:  a recursive CTE can only drill down to 100 levels of recursion.   So watch out for infinite loops in your relationships.

By enclosing our Query in a Table-valued Function we could make our Hierarchical recordset searchable. 


CREATE FUNCTION [dbo].[fn_PeopleTree] 
    @Branch Int
    ID Int,
    FullName VarChar(255),
    ParentID Int,
    Lvl Int)
    With PeopleCTE
    As (
        Select ID, FirstName + ‘ ‘ + LastName As FullName, ParentID, 0 as Lvl
            From People where ID = @Branch
        UNION ALL
        Select Child.ID, Child.FirstName + ‘ ‘ + ChildLastName, Child.ParentID, Parent.Lvl + 1
            FROM PeopleCTE As Parent
                JOIN dbo.People As Child
                    on Child.ParentID = Parent.ID
    Insert INTO @People (ID, FullName, ParentID, Lvl)
        Select ID, FullName, ParentID, Lvl from PeopleCTE


And now Uncle Tim can search by the individual branch of the tree and easily spot weaker branches in his burgeoning empire.

Worthless Nested Recordsets

by Jason on

Consider your alternatives. The vast majority of the time you do not need a nested recordset.

The scenario: A colleague of mine at a company in SA that will go unnamed messaged me about how to speed up a nested stored procedure inside standard query recordset.

His code at it’s core was something like this (Classic ASP and fictional db class used for illustration):

   1: SET rs = OpenQuery(“Select OrderID, OrderTotal from Orders where OrderID = “ & IDVar & “)
   3: Dim CustomerName
   5: Do Until rs.EOF
   6:     SET rs2 = OpenProc(“exec spCustomerFromOrderID “ & 
   7:     rs.Fields.Item(“OrderID“).Value)
   9:     CustomerName = rs2.Fields.Item(“CustomerName“).Value
  11:     Response.Write rs.Fields.Item(“OrderID“).Value & 
  12:     rs.Fields.Item(“OrderTotal”) & 
  13:     CustomerName
  15:     rs.MoveNext()
  16: Loop

After doing this he was surprised that he had a bit of a speed problem…

I know it’s tempting when the boss or client comes back with a last minute addition to perform that in a nested loop. Just don’t do it…

Nested loops have their place but that place is almost never to retrieve a one column answer. Remember that if you are paging through lots of data, each internal RS is another query to your server. That overhead isn’t huge but on a fairly large recordset it will add up quickly.

Solution A: (Quick Implementation)

In the original recordset add a function (“Select OrderID, OrderTotal, dbo.fn_CustomerName(OrderID) from Orders . . .”
Solution B: (Maybe you need data transformation)

Replace original recordset with your proc (return all columns needed from one proc)
Solution C: (Far more likely what you need)

Just do a join in your select statement… If you’re bothering with a function or SP to retrieve a single column name from an indexed table, what’s the point?
All three solutions (A,B,C) required no nested recordset. In actuality my friend was just hacking together a solution and all he needed was a join.

Instant performance gain.