About Becky

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

Surrogate keys: keep ‘em meaningless

By on October 19, 2007 in Database

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

By on April 3, 2007 in Database

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

Using Eclipse and Ant for Flex team development

By on February 6, 2007 in Programming

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

Eclipse Ant Screenshot

Recently the Learning Lab has been discussing ways to do team development as we transition to Flex 2 and the Eclipse IDE. We’re starting a project now that will involve four developers, so we’ll need to keep our own working copies of the project. Not an earth-shattering idea, but it’s new for us–we usually share a single code base during development.

Ted, who has the most Eclipse experience in the group, suggested that Ant, a java-based build tool, could be used to make the team development process smoother. Because Ant ships with Eclipse and because Adobe recently released custom Flex Ant tasks, in theory it should be possible to create an Ant script that will mimic the default Flex build process and also perform extra tasks such as moving files around.

In practice, however, it’s not easy to create such a script if you’re new to Flex 2, Eclipse, and Ant because the documentation is scattered between all three products (as well as some blogs). So I munged the available information into a single “how to.” The steps below show how to implement a very basic Ant build script that will copy Flex code from the “master” code base to your working area, compile it, and create the swf html wrappers.

1. If you’re using the stand-alone version of Flex Builder, follow these directions to set up the Ant environment: http://www.peterelst.com/blog/2006/09/03/flex-builder-2-ant-support/. Skip this step if you’re using the plug-in version of Flex Builder.

2. Download the Flex Ant tasks from Adobe Labs and follow the installation instructions: http://labs.adobe.com/wiki/index.php/Flex_Ant_Tasks
Note: The instructions say to copy the flexTasks.jar file to “Ant’s lib directory.” I put my files in the locations below, which seems to work fine:

  • Stand-alone: C:Program FilesAdobeFlex Builder 2plugins
  • Eclipse plug-in: C:Program FilesEclipsepluginsorg.apache.ant_1.6.5lib

3. Open your Eclipse Flex project and add a build file to it. An Ant build file is an XML document that contains a series of tasks called “targets.” A sample build file template is attached.

4. Modify the build file template to work with your current project. The only things you should need to change are the properties (i.e., variables) at the top of the file:

  • FLEX_HOME: points to the location of your Flex SDK. By default this location is C:Program FilesAdobeFlex Builder 2Flex SDK 2 for the standalone version of Flex Builder and C:Program FilesAdobeFlex Builder 2 Plug-inFlex SDK 2 for the plug-in.
  • DEPLOY_DIR: the folder that will contain your Flex executables (i.e., the swf and its corresponding html wrappers)
  • MAIN_APP_ROOT: the location of the project’s “master” code base
  • WORKING_APP_ROOT: the location of your working folder

5. Open the Eclipse Ant window: Window–>Other Views (select Ant from the Show View window).

6. Once the Ant window is open in your Eclipse workspace, add your build file (see screenshot below):
a. In the upper-right hand corner of the Ant window, click the Add Buildfiles button (i.e., the button with the picture of an ant).
b. From the Buildfile Selection window, choose your build file and click OK.
c. You should now see your build file displayed in the Ant window. You can expand the individual tasks (i.e., targets).
d. You should be able to execute the individual tasks in the script by double-clicking them. Output will appear on the console.

This is as far as I’ve gotten. I’ve been using the built-in Flex compiler when working in my sandbox and the Ant script to get the latest code updates from the main project folder. It seems like a lot of work just to copy files around, but once the initial setup is complete it’s a handy way to avoid manually pushing updates from SourceSafe and potentially overwriting changes. No doubt there’s much cooler things you can do with Ant–if anyone has any tricks, please share!

Other resources:

  • Official Ant Manual, which includes a complete list of available Ant tasks: http://ant.apache.org/manual/index.html
  • Defining the Flex Ant Tasks within your Eclipse environments means that you won’t have to put the taskdef resource=flexTasks.tasks line at the beginning of the build scripts–Eclipse will just know how to find the tasks: http://www.flex2ant.org/
  • To set up Ant configurations and run the build tasks in a specified order, search for “Ant support” in the Eclipse documentation: http://help.eclipse.org/help32/index.jsp
Comments are closed

Re-usable Flex 2 Item Renderer

By on January 23, 2007 in Programming

flex item renderer

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

While working on a Flex 2 application, I needed to render the contents of several datagrid columns as currency. I knew how to create an mxml item renderer to do the job, but the code required hard-coding the name of the datagrid column (i.e., each column would require its own renderer).

Ben Forta’s blog has an entry about using ActionScript to create a reusable item renderer for this situation. Using his code sample and tweaking it based on a commenter’s suggestion, I created a reusable currency renderer in a few lines of ActionScript. The renderer can be attached to any datagrid column that requires currency formatting–no hard-coding required.

Also, as someone new to Flex, ActionScript, and object-oriented programming, I found the simple renderer example to be a good way to start understanding how Flex 2 customization works.

Click here for the sample renderer and source code view.

Comments are closed

SQL error-handling snafu

By on January 13, 2006 in Database, Programming

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

Recently I encountered a SQL Server error-handling trap that caused a transaction not to roll back as expected. At the risk of sounding like an amateur, here’s what happened. The code was issuing two insert statements that were grouped into a single transaction:

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

BEGIN TRANSACTION
 
INSERT INTO dbo.Table1 (SOME COLUMNS)
VALUES (SOME VALUES)
 
SELECT @error = @@error, @rowcount = @@rowcount
 
IF @error <> 0
BEGIN ROLLBACK TRANSACTION RAISERROR('error on the first insert!', 16, 1) RETURN END
 
[DO STUFF]
 
INSERT INTO dbo.Table2 (SOME COLUMNS)
VALUES (SOME VALUES)
 
SELECT @error = @@error, @rowcount = @@rowcount
 
IF @error <> 0
BEGIN ROLLBACK TRANSACTION RAISERROR('error on the second insert!', 16, 1) RETURN END
 
COMMIT TRANSACTION


The above error-handling works most of the time. However, if there is a run-time T-SQL error (for example, a missing object or a divide by 0 error), the offending statement fails but the rest of the transaction executes. In my case, the second insert failed because Table2 had been renamed. The subsequent @error check was never invoked, so the first insert wasn’t rolled back.

This behavior can be overridden by setting SET XACT_ABORT to ON, which specifies that the current transaction will automatically roll back in the event of a T-SQL run-time error.

External Link: (SET XACT_ABORT)

Comments are closed