I’m finishing up a small project that required validating dollar amounts on web-based form fields, and luckily I was using the wonderful Validation jQuery plugin.

Although this plugin doesn’t come with an out-of-the-box dollar validator method, it’s very easy to add your own. The trick was to find an appropriate regular expression. Although Google turned up several regex examples for validating dollar inputs, none met my exact criteria (the ultimate goal being to provide users with the greatest amount of data-entry leeway):

• Allow the user to type in the $ (or not)
• Allow amounts less than one dollar (i.e., 0 – .99)
• Allow an optional one or two decimal places
• No negative dollar amounts

So this is what I came up with, and it seems to be working. Two notes: (a) this expression allows inappropriate commas, but I’m stripping those out during the server-side validations, and (b) it is used in conjunction with the max() validator function to ensure the user doesn’t enter anything that could blow up a SQL Server money column:

^\$?[0-9][0-9\,]*(\.\d{1,2})?$|^\$?[\.]([\d][\d]?)$


The final jQUery validator method:

1
2
3
4

//custom validator for money fields
jQuery.validator.addMethod("money", function(value, element) {
	return this.optional(element) || /^\$?[0-9][0-9\,]*(\.\d{1,2})?$|^\$?[\.]([\d][\d]?)$/.test(value);
}, "Please provide a valid dollar amount (up to 2 decimal places)");

Part of my job is moonlighting as a SQL Server database admin, so a co-worker recently asked me to run a script against a production table. The task was simple: add a few new columns and create some indexes. He had generated the script by using SQL Server 2008 Management Studio’s (SSMS) Generate Change Script function.

Although the general sequence of steps in the generated script made sense, I have some complaints about the SSMS output. Here’s a very simple recreation of the scenario.

Consider a table with two columns. One is a primary key, and one has a default constraint:

1
2
3
4
5
6

CREATE TABLE phillies (
   phils_id INT IDENTITY(1,1)
,  phils_year CHAR(4) NOT NULL
   CONSTRAINT df_phillies_year DEFAULT (2008) 
,  CONSTRAINT pk_phils_id 
      PRIMARY KEY CLUSTERED (phils_id))

Using the SSMS table design view to add two columns to the phillies table and saving the change script results in the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

BEGIN TRANSACTION
GO
ALTER TABLE dbo.phillies
   DROP CONSTRAINT df_phillies_year
GO
CREATE TABLE dbo.Tmp_phillies (
   phils_id int NOT NULL IDENTITY (1, 1),
   phils_year char(4) NOT NULL,
   division_champ_flag bit NOT NULL,
   national_champ_flag bit NOT NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_phillies 
   ADD CONSTRAINT
   df_phillies_year DEFAULT ((2008)) FOR phils_year
GO
SET IDENTITY_INSERT dbo.Tmp_phillies ON
GO
IF EXISTS(SELECT * FROM dbo.phillies)
   EXEC('INSERT INTO dbo.Tmp_phillies 
      (phils_id, phils_year)
   SELECT phils_id, phils_year 
   FROM dbo.phillies 
   WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_phillies OFF
GO
DROP TABLE dbo.phillies
GO
EXECUTE sp_rename 
   N'dbo.Tmp_phillies', N'phillies', 'OBJECT' 
GO
ALTER TABLE dbo.phillies 
   ADD CONSTRAINT
   pk_phils_id PRIMARY KEY CLUSTERED 
   (phils_id) ON [PRIMARY]
GO
COMMIT

This script contains BEGIN TRANSACTION and COMMIT TRANSACTION statements but doesn’t accompany them with any kind of error handling. So if you run it as-is and encounter an error, nothing gets rolled back.

Any error handling that you might add, however, is thwarted by the fact that the script’s statements are contained in individual batches (i.e., separated by GO statements).

Say you individually check each statement for errors and issue a rollback/return if something goes awry.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

BEGIN TRANSACTION
GO
[snip]
CREATE TABLE dbo.Tmp_phillies (
   phils_id int NOT NULL IDENTITY (1, 1),
   phils_year char(4) NOT NULL,
   division_champ_flag bit NOT NULL,
   national_champ_flag bit NOT NULL
)  ON [PRIMARY]
GO
[snip]
-- throw an error
SELECT 23/0
IF @@ERROR <> 0
BEGIN
   PRINT 'error!'
   ROLLBACK TRANSACTION
   RETURN
END
[snip]
GO
DROP TABLE dbo.phillies

In this scenario, changes that occurred prior to the error will be rolled back. However, although the RETURN statement exits the current batch, subsequent batches (for example, the one that deletes your table) will execute.

A TRY/CATCH block is another potential error-handling method, but TRY/CATCH blocks can’t span multiple batches.

So what’s with the batch-happy SSMS? In older versions of SQL it might have been necessary to separate a CREATE TABLE and subsequent ALTER TABLE statements into separate batches (I haven’t tested this). But SQL 2005 and 2008 provide statement-level recompilation, so the multitude of batches is not necessary.

When I tested a “GO-less” version of the script, it worked swimmingly. It’s understandable that SSMS can’t generate the error-handling, but if it would reduce the scripts’ number of batches to the minimum required, it would be easier to add the error handling yourself.

Conclusion: don’t rely on these SQL Server Management Studio-generated change scripts for any serious work, and definitely don’t use them as a model for how to write robust T-SQL.

I’m extremely happy to work in an organization with an uber-smart and supportive group of developers. Once a month we meet for a lunchtime “tech talk” presentation on a topic chosen via Google Moderator.

This month, Tim Allen and I collaborated and spoke about database design. Working with Tim to research and organize the material was a blast, and even though we put silly pictures of ourselves on the slides, I’m posting them.

We focused most closely on normalization and indexing, with a few of our other best practices thrown in the mix. The concepts should be applicable to any RDBMS, but the details are specific to MS SQL Server, the database used in most of the organization’s applications.

wcit-techtalk-database-design

While preparing a database design presentation with my co-worker Tim, I discovered this hypnotic video demonstrating inserts into a b-tree data structure. For some reason, the music compels me to leave the cubicle and pay a visit to Rami’s falafel truck.

On May 13th, 2009, Wharton Computing sponsored the inaugural Evolution of Learning Symposium.  I’m proud to have served on the planning committee for this successful event that featured a keynote by Daniel Pink and a panel discussion by some great educators:

• Chris Lehmann, founding principal of Philadelphia’s Science Leadership Academy
• Doug Lynch, Vice Dean of Penn’s Graduate School of Education
• Karl T. Ulrich, professor of Entrepreneurship and e-Commerce at the Wharton School

It was pretty amazing to sit in a room and listen to these brains hash out some of the issues facing higher ed (and by extension, K-12) right now.  A complete recap, including speaker videos, photos, and a copy of the obligatory live Tweets* are available here.

*For the record, still not a fan of the live Tweeting.

Serving as an alternate juror is one of life’s more frustrating exercises.  It’s all the inconvenience of attending the trial and none of the decision making.  In terms of actual hours worked, however, it’s an easy job:  arrive at 9:30, start working at 10 or 10:30, take a two hour lunch, and leave by 4:30. This schedule leaves plenty of room for deep thoughts. I even broke a longstanding policy and bought a pretentious Moleskin notebook because I wanted my deep thoughts to be important and stylish.

Deep thought number 1:  what do we miss by not paying attention?
Our judge instructed us not to take notes—we had to sit in the jury box without the aid of external mnemonic devices, which seemed like an impediment that could well lead to a travesty of justice.

I found the opposite to be true, though. When was the last time you sat in a room without phones, newspapers, iPods, or conversations and just actively listened? It’s the freedom to find the truth by not only listening but by studying clues like body language, demeanor, and facial expressions, which you can’t do when your head is buried in a notebook. There’s a place for conversations, questions, and notes, but it’s during deliberations, when twelve people bring their individual observations to the jury room and render a verdict.

Outside of the courtroom, we spend a lot of time and money to go places and do things, only to cheat ourselves by not paying attention.  It’s become more important to broadcast and perfectly capture our trips, conferences, and nights out than to live them. What contextual information do we miss when live-tweeting a keynote address?  How can we form mature, thoughtful, and independent judgments in a world of chatter and soundbytes? Information might be free, but knowledge and wisdom require a price, part of which is knowing when to listen.

Note:  This article was originally published to the Wharton Computing Developer Center.

Yesterday a fellow developer hit a strange SQL error and determined that the culprit was a T-SQL CASE statement used in his ORDER BY clause.

1
2
3
4
5
6
7
8
9
10

ORDER BY
CASE
WHEN UPPER(@orderBy)='GROUPMESSAGEID' THEN groupMessageID
WHEN UPPER(@orderBy)='GAMEID' THEN gameID
WHEN UPPER(@orderBy)='GROUPID' THEN groupID
WHEN UPPER(@orderBy)='MESSAGEID' THEN messageID
WHEN UPPER(@orderBy)='ROUNDSENT' THEN roundSent
WHEN UPPER(@orderBy)='SENTON' THEN sentOn
ELSE groupMessageID
END

This code results in the following message:
Operand type clash: uniqueidentifier is incompatible with datetime

After some digging around, we found the underlying cause of the error:  the case statement return values have different data types.  In some cases, returning different data types will behave as expected.  However, mixing numeric and character data causes problems.

In the statement above, gameId, groupID, roundSent, and groupMessageID are integers, sentOn is a datetime, and messageID is a uniqueidentifier.  Because the data type precedence pecking order in this case is datetime, int, and then uniqueidentifier, SQL choose datetime as the return type. Uniqueidentifiers cannot be converted to datetimes, hence the error message.

It all became clear after reading this article by George Mastros.  Thank you to George.

About a year ago, my husband came home from work, started gushing about a school he had visited that day, and continued to talk for hours about how inspired he was by its students and faculty.  That was my introduction to Philadelphia’s Science Leadership Academy, a Philadelphia public high school that opened in 2006.

Here are high school students inventing an efficient flow process for creating biodiesel fuel–how cool is that?  And it’s happening in a Philadelphia public school!

Fast forward to the second Ignite Philly, when Chris Lehmann, principal of SLA, gave his  presentation on the schools we need.   Take five minutes to see a compelling speaker and learn about something wonderful in our city.

Over the summer, a few colleagues and I had the opportunity to present at the 2008 Higher Education Web Symposium, held at the University of Pennsylvania. Our topic was How to Engage Today’s Students: Portals, Instructional Technology & Learning Simulations.

My portion of the presentation was an overview of what we called the New Learner (not the best term, but we liked it better than Digital Native, Millennial, and especially Learner 2.0). Lou Metzger, Jason Lehman, and Erin Wyher followed up with specific examples of how we’re trying to engage this demographic throughout their activities at the school.

Overall, the presentation went well, though we ran long and didn’t have a lot of time to hear ideas from other schools. We also got some feedback that the examples were too Wharton-centric. It’s true that much of the content was Wharton and/or Penn-related, but I hope that didn’t obscure our underlying points:

• Making small changes or adding modules can promote engagement—you don’t have to AJAX-ify or re-write your existing web applications.
• Integrate existing information and present it in the context of a task at hand.
• Animations, avatars, bells, and whistles don’t always translate into a better student experience.
• Buzzwords can distract from what’s important.

Last week, Erin Murphy and I attended Brandon Hall’s Innovations in Learning conference, which also included the Excellence in Learning Awards. One of the award winners, PriceWaterhouseCoopers, had a great spin on corporate training.

PWC created a video series called “The Firm,” which follows several characters—the Sr. Associate who parties too much, the New Hire, the Partner, the Baby Boomer executive assistant, the Generation Y fashionista Associate—through their everyday lives as PWC employees and illustrates the finer points of employee coaching.

They released a new episode of The Firm every two weeks, got thousands of internal views, and made the episodes available on YouTube and pwc.tv to as part of their recruiting strategy.

I love this approach because it’s a story. The people are real employees with real quirks—they shave in the bathroom after partying all night and complain about the way their co-workers dress. The second season of the show was even accompanied by a fictitious blog*.

The cases used in business education already incorporate the element of story, so why not take those stories to the next level (someone out there already is, no doubt)? For starters, what about the Learning Lab’s Raise game, in which students allocate salary increases by reading employee profiles? It be so fun to give those characters a voice.

*Writing a fake blog as part of your job? Sign me up!