Creating a User-Defined Function
Unlike the output of a stored procedure, the result of a UDF can be accessed in a select statement
Creating a User-Defined Function
Unlike the output of a stored procedure, the result of a UDF can be accessed in a select statement
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)
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.attr() e.g. $('#myelementid').attr('class').hasClass()$(selector).length gives the count of the matched elements… and things to check if code isn’t working as expected
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
});
There’s a very useful article by Stephen Walther here. Some parts do need to be updated, though:
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
var myStubbedObject = MockRepository.GenerateStub<IStubbedObject>();myStubbedObject.Stub(x => x.MyStubbedMethod(myParameter)).Return("testValue");myStubbedObject.Stub(x => x.MyStubbedProperty).Return("testValue");File methods, for example, you can get round this issue by creating a wrapper class and stubbing that instead.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> |
 |
<code> |
`backticks` |
Daring Fireball Markdown: basics
Stack Overflow Markdown Reference
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.
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.
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.)
ISelenium object.
@"*firefox C:\Program Files\Mozilla Firefox\firefox.exe"*safariproxy instead of *safari (see here). *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.)selenium.Open("/") when testing Safari.
java -jar <server filename>.jar (you can put this in a bat)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.
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.
SELECT MAX(StartDate) FROM Staff
(from s in staff select (s.StartDate)).Max();
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) };
(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) };
group clause refers to the range variable (co1), not the data source (companies). 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.)
LINQ Syntax Fundamentals
An Introduction to LINQ – has sections on how the new language features in C# 3.0 are relevant.
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?
Other useful reading:
The Difference Between Ruby Symbols and Strings.
13 Ways of Looking at a Ruby Symbol
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.