About Becky

I like to write things on the internet.
Author Archive | Becky

SQL Server operand type clash!

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.

Comments are closed

The Schools We Need: Chris Lehman @ Ignite Philly

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.

Comments are closed

Presentation: How to Engage Today’s Students

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.
Comments are closed

Stories in Learning: The Firm

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!

Comments are closed

Backtest the musical

In September of 2007, the Learning Lab rolled out Backtester,a Flex-based backtesting tool for use in investment classes.  I learned a lot about Flex and ActionScript while working on Backtester and am particularly proud of application’s wizard component, which allows users to design an investment strategy in 3 easy steps and test it against historical data.

We made a short screencast* for last spring’s faculty meeting and have been using it to test video tagging toys such as Viddler and Veotag. This post is just a test of Viddler’s new customizable player.

Comments are closed

ColdFusion: keep it girlie

Attention, female ColdFusion developers. I found the following information in some recent internet travels:

Silly CF Banner

I, for one, refuse to “upgrade” to a less girlie version of ColdFusion.  In fact, my pores are clogging at the mere thought.  Does anyone have a recipe for scones?

Girlie CF Banner

Comments are closed

HTTPService: passing XML between ColdFusion and Flex

We recently had some trouble with a Flex RemoteObject call. It kept throwing timeout errors on a method that invokes a CFHTTP call, even though we could invoke the same method from a ColdFusion page with no problems.

After increasing the timeout parameters of both the CFHTTP statement and the RemoteObject definition to ridiculous amounts, we decided to use the mx:HTTPService function as an alternative.  Kind of annoying to have to add the extra layer, but it works.

No doubt there is a perfectly logical explanation for the RO timeout, but we needed to get this proof of concept code working as quickly as possible.

The ColdFusion file.
This invokes the method that contains the problematic CFHTTP call and returns the status info in an XML blurb.

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
    <cfsetting showdebugoutput=”no” />
 
    <cfset structResult = StructNew() />
    <cfset structResult.backtestId = url.backtestId />
 
    <cffunction name=”RunBacktest” access=”private” output=false” returntype=”struct” hint=”run a specified backtest”>
 
       <cfinvoke component=”TheProblemComponent” etc />do some stuff….
       <cfreturn structResult />
 
    </cffunction>
 
    <cfset result = RunBacktest() />
    <cfoutput>
    <cfxml variable="resultXML">
       <runResult>
          <backtestResult>
             <backtestId>#result.backtestId#</backtestId>
             <returnCode>#result.returnCode#</returnCode>
             <msg>#result.msg#</msg>
          </backtestResult>
       </runResult>
    </cfxml>
    </cfoutput>
 
    <cfcontent reset =”yes” type=”text/xml; charset=UTF-8>
 
    <cfoutput>#resultXML#</cfoutput>


The Flex HTTPService definition from Services.mxml:

1
2
3
4
5
6
7
8
<mx:HTTPService id=”runBacktest”
   method="GET"
   resultFormat="e4x"
   result="resultTesty(event);" >
   <mx:request xmlns="">
      <backtestId />
   </mx:request>
</mx:HTTPService>


The result handler
Example of how to reference the XML coming back from CF, even though we ended up not needing to do this.

1
2
3
4
5
6
private function resultTesty (event:ResultEvent):void
{
   var backtestRunResult:XML = new XML(event.currentTarget.lastResult);
   Alert.show(event.currentTarget.request.backtestId);
   do some stuff;
}


Invoking the service call:

1
2
3
4
5
6
private function runBacktest(backtestId:Number):void
{
   Application.application.srv.runBacktest.url = "SuperSecret/RunBacktestFolder/RunBacktest.cfm";
   Application.application.srv.runBacktest.request.backtestId = backtestId;
   Application.application.srv.runBacktest.send();
}

Comments are closed

Enhance the default Flex form validation

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

Do you write or support Flex applications? Do you rely on the built-in functionality to validate form fields? Do your users get confused because the validation error messages don’t show up unless they use a mouse to hover over said form fields?

This post by Aral Balkan shows how you can get those “hovering” tool-tip error messages to display inline, as the user fills out the form: http://aralbalkan.com/1125

Comments are closed

Surrogate keys: keep ‘em meaningless

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

At two recent code reviews, we’ve discussed the use of SQL Server auto-incrementing fields as primary keys. Using system-generated numbers to uniquely identify rows (i.e., implementing surrogate keys) is a good practice* because it reduces the likelihood of errors introduced by bad data. However, these surrogate keys are meaningless and exist only for the purpose of identification.

The problem is that many applications do not treat surrogate keys as meaningless numbers, a practice that reduces portability. Consider a “master table” that lists an application’s user roles. The most basic structure for such a table would be two columns, role_id and role_name:

role_id role_name
1 student
2 faculty
3 TA
4 admin

Many applications would do the following to get a list of faculty users:

SELECT user_name FROM tblRole WHERE rold_id = 2.

However, there’s no guarantee that the faculty role will be assigned to the id of 2 in perpetuity. 2 doesn’t mean faculty, it just means 2; if you reload your data and port the application to another environment, the faculty role id might be 4 or 10 or 33,288.

It’s safer to look up the role_id where role_name = ‘faculty’ and use that value instead of assuming 2. This method, of course, would create problems if the application users decide to change the ‘faculty’ role to ‘instructor.’ A solution would be to create more descriptive master tables that supports front-end changes while maintaining meaningless surrogate keys:

role_id role_code role_name role_sort_order role_create_date role_update_date
1 s student 3 10/31/2007
2 f faculty 1 10/31/2007
3 t TA 2 10/31/2007 11/03/2008
4 a admin 4 10/31/2007

This table structure allows you to get the faculty surrogate key by querying for role_code = ‘f.’ The role_name field is what users see and can be modified without breaking the application. Finally, role_sort_order controls the front-end display order of these items and allows you to change the sort order without touching any code.

*The surrogate key versus natural key debate still rages among database geeks, but it seems that surrogate keys are a standard practice at this shop.

Comments are closed

Dynamic SQL: exec vs. sp_executesql

Note: this article was originally published to the Wharton Computing Development Center

Dynamic SQL came up again at a recent code review.  If only for your own maintenance sanity, it’s worth eliminating dynamic SQL where possible, but there are times you can’t avoid it (for example, some implementations of the new SQL 2005 PIVOT function). If you must do the dynamic SQL thing, you should know that there are two ways to execute it

  • EXEC(@stringofsql)
  • sp_executesql @stringofsql, @someparameters

The first option is old school, but it still works. Sp_executesql, however, is newer and allows you to use parameters in conjunction with the dymanically-built SQL string. Having the option to use parameters is a great improvement–if the parameters are the only part of the SQL command that changes, the optimizer can reuse the execution plan instead of generating a new one each time the code is run. See SQL Server Books Online for the exact sp_executesql syntax.

If you want to know more about dynamic SQL, take a look at this aricle.

Comments are closed