My T-SQL Bookmarks

T-SQL Built-in Functions

Creating a User-Defined Function 

Unlike the output of a stored procedure, the result of a UDF can be accessed in a select statement

While Loops

Posted in Databases | Leave a comment

Custom Functions in VBA

I had a nostalgic kind of week this week, working with VBA to create a custom function to apply some business logic to a calculation in a spreadsheet. I got into programming through working with VBA macros, and still find myself defaulting to VBA syntax when I’m very tired even though I haven’t worked regularly with VBA for over four years. It felt a bit like driving a horse and cart after driving a car (and, yes, I have driven both), but was useful for the task in hand.

Anyway, a useful guide to creating a user-defined function in a module in a workbook can be found in Microsoft’s online Office support. You can access most of the Excel worksheet functions in VBA using the WorksheetFunction object and you can pass in ranges e.g.

Function Score(r As Range)
    If WorkSheetFunction.Sum(r) >= 100 Then
        Score= 1
    Else
        Score = 0
    End If
End Function

(see here for more examples of using the WorksheetFunction object)

You can access custom functions which are saved in another workbook (open in the same instance of Excel) by prefixing the function with the workbook name e.g.
Functions.xls!Score(A1:A10)

Posted in Uncategorized | Leave a comment

jQuery and JavaScript for Debugging

Here are a few of the JavaScript and jQuery methods which I find useful when debugging my projects.

  • JSON.stringify() can be used to inspect objects
  • To inspect the value of element attributes like class or id, use .attr() e.g. $('#myelementid').attr('class')
  • For an element with multiple classes, you can check if it has a particular class using .hasClass()
  • $(selector).length gives the count of the matched elements

… and things to check if code isn’t working as expected

  • If jQuery code is unexpectedly failing to have any effect on an element in the document, go back to basics and make sure you have put the call to the code in the document‘s ready event so that it is not called until all the elements are present:
    $(document).ready(function(){
    // code
    });
Posted in JavaScript | Leave a comment

Using Rhino Mocks with Windows 7

There’s a very useful article by Stephen Walther here. Some parts do need to be updated, though:

  1. Rhino Mocks downloads have moved to hibernatingrhinos.com
  2. The warning about using assemblies downloaded from the internet in Windows Vista also applies to Windows 7:

    One warning about using Rhino Mocks on Windows Vista. Vista will prevent you from using assemblies that you download from the Internet. Before you reference the Rhino.Mocks.dll assembly in Visual Studio, you need to Unblock the assembly: right-click the assembly file and click the Unblock button under the General tab (see Figure 1).

    If you reference the assembly before doing this, you’ll need to remove the reference, delete the rhinomocks.dll from the bin folder, and then re-add the reference to the enabled assembly file before you’ll be able to use it.

Quick Reminders

  • To stub an object: var myStubbedObject = MockRepository.GenerateStub<IStubbedObject>();
  • To stub a method: myStubbedObject.Stub(x => x.MyStubbedMethod(myParameter)).Return("testValue");
  • To stub a property: myStubbedObject.Stub(x => x.MyStubbedProperty).Return("testValue");
  • If you get an exception message along the lines of ‘Invalid call, the last call has been used or no call has been made (make sure that you are calling a virtual (C#) / Overridable (VB) method)’ then make sure you have stubbed the interface and not the implementing class (assuming that you are working with classes which implement an interface, of course).
  • You can’t stub static methods with Rhino Mocks. If you want to stub File methods, for example, you can get round this issue by creating a wrapper class and stubbing that instead.
  • Remember that you don’t have to stub lists if what you need is a list of stubbed objects – just use a real list and add the stubbed objects to it!
Posted in Uncategorized | Leave a comment

Displaying HTML-formatted documents without HTML

Most of the documents displayed on my genealogy website conform to one of a limited number of formats and I’ve created an HTML template for each format required. However, I also have some free-form text documents which I wanted to display with as much of the original formatting intact as possible. I couldn’t use HTML tags for the formatting, though, because I can’t store HTML formatting in the DB behind my site: the interface flags the input as potentially dangerous code. The solution has been to store the data in a plain-text format that my application can then convert to HTML.

In the past I’ve worked with Redcloth and Bluecloth, which are Ruby implementations of Textile and Markdown respectively, in RoR sites. Markdown in its original incarnation is a Perl software tool that converts plain text marked up using its custom syntax into HTML. Textile does a similar sort of thing, but was originally implemented in PHP. Both have implementations in C#, so I based my decision on which one to use on the ease of use. Although Textile has some tags that would be useful that Markdown lacks (strikethrough, for example), Markdown uses a syntax which I find more attractive in its unconverted state ([link_text](link_address "title") in Markdown, as opposed to "(classname)link text(title tooltip)":link_address in Textile)

Markdown is used on the Stack Overflow site, and they have open-sourced their C# implementation as MarkdownSharp. Marked-up text can be converted to HTML using

var markdown = new Markdown();
var convertedText = m.Transform(inputText);

Also useful for my purposes is the JavaScript port of Markdown by John Fraser, Showdown. The download link on this site is currently broken, but there’s a version on GitHub here. A couple of lines of script, and I can get a rough idea of what my converted content will look like before saving the marked-up text:

        function preview() {
            var converter = new Showdown.converter();
            $("#preview").html(converter.makeHtml($("#text").text()));
        }

So now I can display my great-great-grandfather’s letter with close to original formatting as well as the original rather quirky spelling…

Reference

Tag Markdown
<br /> Two spaces at the end of the line
<em> *asterisks* or _underscores_
<strong> **double asterisks** or __double underscores__
<a> [Text](href “title”)
<h1> heading 1
====
or # heading 1
<h2> heading 2
−−−−
or ## heading 2
<hr/> ***
<ul> * asterisk
<ol> 1. number followed by a period then a space
<blockquote> > angled bracket
<img> ![alt text](/path/to/img.jpg “Title”)
<code> `backticks`

Daring Fireball Markdown: basics
Stack Overflow Markdown Reference

Posted in JavaScript | Leave a comment

Testing Web Apps with Selenium

Inspiration can be found in the most unexpected places: about this time last year I was introduced to the Selenium suite of browser automation tools while chatting to an acquaintance at a wedding. Since then I’ve used two of the products, Selenium IDE and Selenium Remote Control (I’m looking forward to experimenting with WebDriver soon). The IDE allows you to record your behaviour on a website and I find it particularly useful for reproducing bugs where a number of steps are required to reach the problem area. The IDE is convenient, but only works in Firefox. For testing in other browsers you can use RC.

Option 1: Firefox-only testing using recording functionality

Selenium IDE is a Firefox plugin and can be downloaded from here. You’ll probably have to click ‘Allow’ on a message about ‘Firefox prevented this site from asking you to install software on your computer’, and may get other warning messages as you install it.

Using the recorder

  • When you open the tool from the Web Developer menu, the record button is automatically ‘On’.
  • Navigating through your site as if you were using it will record your tests for you.
  • Right-clicking on elements brings up a context menu for you to choose commands from.
  • Tests are saved in HTML table format and can be opened up and edited by hand if desired.

Running Tests

  1. Record and save your tests
  2. If you’re testing a site on localhost, make sure your site is running
  3. Run your tests from the Selenium IDE window. (NB it will overwrite the active window in Firefox with your app window.)

Option 2: testing in other browsers using Selenium RC

Selenium RC consists of a server and client libraries. There’s no actual installation necessary – you just need to download the jar file and appropriate client drivers from the Selenium download page and save them where you want. (Note that Selenium server version 1 onwards contains hudsuckr.exe, which caused a warning to pop up when I downloaded it on a machine running Kaspersky. There are some reports of this here and here.)

Setting up your tests

  • There are code examples in the Selenium documentation here, or if you’ve recorded tests in the IDE you can export them to C# by choosing File | Export Test Case As… | C# in the IDE menu.
  • You need to add the reference to the ThoughtWorks.Selenium.Core.dll from the relevant client driver folder.
  • You pass the name of the browser you want to test as a parameter when you instantiate the ISelenium object.
    • If the browser you want to test isn’t in the PATH on your machine you can specify the path in the same parameter e.g. @"*firefox C:\Program Files\Mozilla Firefox\firefox.exe"
    • To run tests in Safari 5 on Windows (XP and 7), I have to use *safariproxy instead of *safari (see here).
    • Likewise, I have to use *iexploreproxy to test IE8 on Windows 7, otherwise it keeps asking if the pop-up blocker is enabled. (There are some other options suggested on Stack Overflow, but I haven’t tried them.)
  • When working through the code example I did find I had to use google.co.uk instead of .com or I would get an error message when I called selenium.Open("/") when testing Safari.

Running Tests

  1. Start the Selenium server: from the command line, navigate to the folder and execute java -jar <server filename>.jar (you can put this in a bat)
  2. If you’re testing Safari, make sure the pop-up blocker is off
  3. Run your console app containing your tests

Reference

Posted in Testing | Leave a comment

Selecting Child Elements in jQuery

I was recently asked whether it would be ‘better/quicker’ to replace
$('ul').children('li').children('a')
with
$('ul li a')

My initial response was to warn that these two lines of code don’t actually do the same thing. As explained in the jQuery API reference for the descendant selector, the suggested replacement would (for example) pick up li elements that were descendants of ul elements – and ‘descendants’ can include grandchildren and so on, not just children.

In this case, the structure of the page meant that this wasn’t an issue. I was surprised, though, to find an article posted earlier this year by Rob Tarr showing that in fact the existing code could be quicker than the suggested replacement. The article, jQuery Selector Performance Testing, shows the results of performance tests of chaining find method calls, chaining children method calls, and chaining selectors. In the tests, chaining the method calls was significantly faster than chaining the selectors.

So in this case the existing code was probably the ‘better/quicker’ option after all.

Posted in JavaScript | 1 Comment

From SQL to LINQ

Since I starting to work in C# 3.0 I’ve often had occasion to make use of LINQ (Language Integrated Query) queries to work with in-memory collections. My experience with SQL makes it natural for to me to think of this sort of data manipulation in terms of SQL queries, so here are some examples of a query in SQL followed by the LINQ equivalent.

Selecting maximum value

SELECT MAX(StartDate)
FROM Staff

(from s in staff select (s.StartDate)).Max();

Grouping

SELECT Department, MIN(StartDate)
FROM Staff
GROUP BY Department

var groups = from s in staff
             group s by s.Department into g
             select new { Department = g.Key, StartDate = g.Min(x => x.StartDate) };

Grouping with an inner join

(This example returns the id of the most recent record for each name recorded for each person.)

SELECT p2.PersonName, MAX(p1.Id) AS Id 
FROM PersonDetails p1
INNER JOIN PersonDetails p2 ON p1.PersonID = p2.PersonID
GROUP BY p1.PersonID, p2.PersonName

var names = from p1 in persons 
            join p2 in persons on p1.PersonID equals p2.PersonID
            group p1 by new { p1.PersonID, p2.PersonName } into g
            select new { PersonName = g.Key.PersonName, Id = g.Max(x => x.Id) };

Points to bear in mind

  • The group clause refers to the range variable (co1), not the data source (companies).
  • Grouping on multiple attributes is done by creating a new object
  • You can assign an identifier (here, g) to the group using the into keyword. The properties that have been grouped can then be referred to with the prefix g.Key

(See Basic LINQ Query Operations for an explanation of the terminology.)

Other Reading

LINQ Syntax Fundamentals
An Introduction to LINQ – has sections on how the new language features in C# 3.0 are relevant.

Posted in Uncategorized | 1 Comment

Symbols in Ruby

When I started to work with Ruby I was puzzled by the symbol – text preceded by a colon character, as in
session[:cart]
What exactly is a symbol, I wondered? And why is it used? I set out to find an explanation.

Enlightenment came with Kevin Clark’s article Understanding Ruby Symbols. The key for me was Thomas Aylott’s comment:

Symbols are like constants whose name is their value. Just like the number 5 has both the name 5 and the value 5 so the symbol :howdy is the word howdy

That explained what a symbol represents. So why are symbols useful? In Ruby strings are mutable, so every time you create another string with the value “document_title” it is a new object which takes up space in memory (unlike Java’s handling of string literals, for example). In his article Kevin Clark gives an example of how this could become an issue when using strings as hash keys. A symbol provides a solution to the problem because, as explained in the free on-line version of the first edition of Programming Ruby,

The same Symbol object will be created for a given name string for the duration of a program’s execution, regardless of the context or meaning of that name.

Another benefit is that because symbols can be compared by object id, comparison of symbols is faster than comparison of the strings they represent.

So when is it useful to use a symbol?

  • Use symbols instead of strings as hash keys.
  • If you’re going to be using the same string value in many places, consider using a symbol.
  • Symbols are immutable and you can’t invoke string methods on them, so consider using a string if you want to be able to manipulate it.

Other useful reading:
The Difference Between Ruby Symbols and Strings.
13 Ways of Looking at a Ruby Symbol

Posted in Ruby on Rails | Leave a comment

Full-Text Search with SQL Server 2008

The full-text search capabilities of SQL Server 2008 make it possible to search text across multiple columns, weight search terms, and search by proximity. A full-text query can be much faster than the equivalent LIKE query.

The first step is to create a full-text catalog and index and to populate the index. The Full-Text Indexing Wizard takes you through this step-by-step, and allows you to create a catalog if necessary and define population schedules. (There are step-by-step instructions on using the wizard here). If the ‘Full-Text index’ option is greyed-out on the table’s context menu, the full-text component is probably not installed. In order to check, you can execute
SELECT FullTextServiceProperty('IsFullTextInstalled')
This will return 1 if it is installed.

Once you have this set up you can start to query the index. Examples of the functions available can be found at

To query the index from within an application using SubSonic 3 you can use the CodingHorror object to execute inline SQL – see here. Note that the example is parameterized to avoid SQL injection attacks.

Posted in SQL, SQL Server | Leave a comment