Rusty Divine

Live, Love, Learn, Teach

What Happened Now – April 17, 2008

Have you heard of “Jumping the Shark”?  Well, this week two prominent bloggers have teamed up to launch a new website stackoverflow.com.

Welcome to the premier edition of What Happened Now, a technology based webcast.

I’m your host, Osmyn, and today is April 17th, 2008.

Today Jeff Atwood of CodingHorror and Joel Spolsky of Joel on Software announced their partnership on stackoverflow.com – a wiki/ranking hybrid aiming to provide the best and most current answers to software-related quieres.

Jeff, a west-coast blogger, has been critcal of Joel in the past – accusing him of becoming highly-illogical and having jumped the metaphorical shark after Joel posted a blog about how his company created a proprietary coding language for their flagship product FogBugz.

Critics are skeptical of site’s accompanying weekly podcast covering ongoing development of the application and answering listeners questions sent in as audio clips.

One commenter said, “What a crummy idea.  I hope that you fall flat on your face…you idiot.”  Others are alarmed to hear Joel’s popular discussion boards will be shut down in favor of using the new website.

Next up, Tricia Walsh-Smith has posted a rant the likes of which YoutTube may have never seen before, if that’s possible.

The New York woman is upset that her Broadway Producer-husband is filing for divorce and evicting her from their apartment.

According to the video, there’s a botched pre-nup, an evil step daughter, and a handful of Viagra, condoms, and video porn involved.
Finally, some good news from the popular animated webcomic Homestarrunner – a 3d video game will be released this summer for the Wii and PC.

Strong Bad’s Cool Game for Attractive People, or SBCG4AP for those in the know, will be available in 5 monthly, downloadable installments starting in June from Telltale games.

One of the plotlines for the game will be to go on a quest in response to a Strongbad email, for example, building a pizza parlor in order to meet girls.

If I didn’t have reason enough to get a wii before, I do now.  I’m Osmyn, and thanks for dropping in on What Happened Now.

Tips on Using a Stored Procedure for Searching

Every business application I’ve worked on has the All-Encompasing Search Tool.  Its the one where you have about 30 text, dropdown, and list boxes that the user can enter their search parameters into (First Name, End Date, Product Number, Product Status, etc.).

What do you do when you’re on a project that uses stored procedures to search the database?  Furthermore, many DBA’s don’t allow direct access to the tables or views in the database for security reasons, which means that you can’t even pass in a dynamically-generated SQL string and do an EXEC @SQL on it.  How do you do this complex search using parameters and nont use dynamic SQL?

I’ve broken the issue down into four main problems and their solutions:

Problem:

There are like 120 search parameters that are possible, but most are probably going to be left blank by the user and should therefore should not not narrow the results.  How do I include these null parameters in my SQL without affecting the results?

Solution:

By including an OR clause for every parameter that cancels its affect out if it is null:

 WHERE ([UserId] = @UserId OR @UserId is null) 

Alternatively, force a null value to another meaningful value:

COALESCE(@WithinXDaysofRenewal,0)

Problem:

On the Search Tool, there is an option to search by “OR” or by “AND”, e.g. FirstName = “Johnny” AND LastName = “SMITH”.  How can a stored procedure switch between narrowing results (using AND) and expaning the results (using OR)?

Solution:

The stored procedure will need to have 2 distinct select clauses, one with OR’s in the where clase and one with AND’s.  Pass a parameter (@SearchUsingOr) into the stored procedure, and then set up the branch like:

 IF ISNULL(@SearchUsingOR, 0) <> 1   BEGIN     SELECT …     FROM …     WHERE (FirstName = @FirstName OR @FirstName = Null) AND --use AND   END 
ELSE   BEGIN       SELECT …     FROM …     WHERE (FirstName = @FirstName OR @FirstName = Null) OR --use OR   END 

Problem:

The user can select multiplve values from a multi-select list box for one or more of the parameters; e.g. there is a list box with all 50 states in it, and the user can select any number of them.

Solution:

Pass the selected values as one comma-delimited list paramter : @States = “NE, WA, CA”, then use the table-valued function “Split” below to split these values out into a result set.  Join on the result set in the FROM clause of the search query like this:

 
SELECT usrs.* 
FROM dbo.Users usrs INNER JOIN [dbo].[fn_Split] ('NE,WA,CA', ',' ) splt ON usrs.State = splt.value 

Here’s the script to create the split function (I think this orignally appeared in an MSDN article):

 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
	@value varchar(8000),
	@bcontinue bit,
	@iStrike smallint,
	@iDelimlength tinyint

IF @sDelim = 'Space'
	BEGIN
	SET @sDelim = ' '
	END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
	BEGIN
	WHILE @bcontinue = 1
		BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
		IF CHARINDEX(@sDelim, @sText)>0
			BEGIN
			SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			
--Trim the element and its delimiter from the front of the string.
			--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
			SET @idx = @idx + 1
			SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
		
			END
		ELSE
			BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			--Exit the WHILE loop.
SET @bcontinue = 0
			END
		END
	END
ELSE
	BEGIN
	WHILE @bcontinue=1
		BEGIN
		--If the delimiter is an empty string, check for remaining text
		--instead of a delimiter. Insert the first character into the
		--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
		IF DATALENGTH(@sText)>1
			BEGIN
			SET @value = SUBSTRING(@sText,1,1)
				BEGIN
				INSERT @retArray (idx, value)
				VALUES (@idx, @value)
				END
			SET @idx = @idx+1
			SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
			
			END
		ELSE
			BEGIN
			--One character remains.
			--Insert the character, and exit the WHILE loop.
			INSERT @retArray (idx, value)
			VALUES (@idx, @sText)
			SET @bcontinue = 0	
			END
	END

END

RETURN
END

Problem:

I need to page my result set and don’t want to store the entire result set in memory (in a dataset or something).

Solution:

SQL 2005 has introduced a Row_Number() function that works like this:

select ROW_NUMBER () OVER ( order by AccountName asc) as rownum, accountname from dbo.account

To see more, check out this page.

For SQL 2000, one way I like to do this is to set the rowcount in the SQL stored proc.  I think this page has an example of how i’ve done it in the past.  SQL 2005 may have some better features for this; I haven’t looked into it.

Problem:

I need to page my result set alphabetically, like # A-D E-H etc..

Solution:

You can use a LIKE statement with [Brackets] to get the results you want:

SELECT AccountName FROM dbo.Account WHERE AccountName LIKE '[ABC]%' 

SELECT AccountName FROM dbo.Account WHERE AccountName LIKE '[0123456789!@#$%^&*()]%'  with results as ( 
select  ROW_NUMBER () OVER ( order by AccountName asc) as rownum,  accountname from  dbo.account where  accountname like '[ABC]%' ) 
select * from results where rownum between 10 and 15 

Kodak EasyShare Z1275

My Kodak EasyShare Z1275came in the mail last week (I bought a refurbished one for $99 from Woot).  It’s a great digital camera, much better than the one I bought 3 years ago.

I’ve never had a digital camera that came with an optical zoom, so I’ve been having lots of fun taking close-ups of my dogs’ noses and flowers coming up in the yard.  The Z1275 has a 5x optical zoom, and another 5x digital zoom on top of that.  When you press the zoom-in button, the telephoto lens extends like Pinnochio’s nose until it reaches its maximum length.  After that, the digital zoom kicks in and you continue to see the image enlarge on your LCD screen.  I like this much better than my old digital camera because on it I could only use the digital zoom after I took the picture and was reviewing it.

The default-take-a-picture mode does a great job of focusing and getting the colors balanced.  There are way too many other modes (like fireworks, image stabilization, fast motion) that generally result in a worse picture than the default mode.  Some of the modes are handy though, like museum mode that turns off the flash and any beeps.

I haven’t installed the software that comes with it; I use Picassa to manage my photo collection.  The camera comes with 64MB of on-board memory, but be sure to pick up an extra memory card or two so that you can take more than ~28 pictures.

I’d recommend this camera to anyone who wants to take it on hikes and sight-seeing and just wants a simple digital camera that takes good pictures and has an optical zoom.

(the hawk picture was taken from inside my house looking at my backyard fence with full optical and digital zoom in use)

Square Foot Gardening

Last July my wife and I moved from Seattle back home to Lincoln, NE.  We lived in Seattle for 5 years (and Washington state for 8), but Seattle’s mobs of people and infamous winters wore us down.

Seattle’s northern clime and short growing season made vegetable gardens a bit too much hassle for us spoiled mid-westerners.  Now we’re back and I had a little spot in my backyard between a fence and the house that was perfect spot for a vegetable garden.

I stumbled upon an interesting gardening technique – gardening by the square foot – that sounded both promising and appropriate for my space.

Pros: Low maintenance, good yields

Cons: Some reviewers reported the loose soil mixture wasn't hefty enough to stabilize tall-growing plants.  Also, with two greyhounds (and one who really loves to dig), I would have to fence off the garden area.

Box

Supplies:

I constructed a raised bed container that measured 12’x2’x6”

  • 4 8’x6” untreated cedar boards from local box home improvement store
    • I cut 2’ off each of these and used them for the ends
  • 2 cubic feet each: Vermiculite, Compost, Peat Moss
    • More would have been better, even though the volume was supposed to be correct.
    • 1 bag of compost at gardening centers is about 1/2 cubic foot
  • Some 2” wood screws that I had on hand
  • Optional – landscaping paper for the bottom
    • I didn’t use this in hopes my plants get into the soil below to stabilize the taller ones
  • Some twine and a handful of nails to divide the boxes into square-foot sections
  • Garden Fence (for keeping out the dogs)

The total pre-planting cost was around $100 and it took me an afternoon to put together.

Plants:SqFtGarden

I used Excel to plan what plants to put where.  I’ve already put in the potatoes, radish seeds, onions, beet and lettuce seeds.  The rest I’ll buy as plants from the nursery when they are ready.

Tips:

  1. Use the soil mix described in Supplies above
  2. Don’t ever walk on or otherwise compact the soil 
  3. No need for fertilizer, and weeds are rare and easily removed
  4. To plant seeds at 1/2”, e.g., scrape 1/2” of the soil off, sprinkle in the seeds, then cover them back up
  5. Read this beginner's guide
  6. Lookup your county’s Extension Office

Links:

  1. Official Site
  2. Square Foot Gardening (the book)
  3. With irrigation
  4. The Ultimate How-To Guide

Time lapse Work Day

Here’s a fun time lapse video of me working in my home office (I’m a full-time telecommuter).

How to make a time lapse video:

This time lapse footage was shot with a Logitech usb webcam and saved to an .avi file with a trial version of HandyAvi.  An image was captured every 4 seconds, then played back at 30 frames per second (which makes about a 4 minute movie from a 9 hour day, but I started around 10 am) and saved to a raw AVI file.  The avi file was then imported into the free Microsoft Windows Movie Maker.  I got the music from aclassical music midi site (but found an .MP3 of the music there that was better than the midi), and dragged that into Movie Maker.  Then I added the opening title screen (really easy in Movie Maker) and published the file by choosing “Save to Web” and selected the option for DSL, which resulted in a handy 8MB .WMV file.  After that, I uploaded the .wmv movie to YouTube and posted it here.