Why Oracle Sux

DECLARE
v_First VARCHAR2(20) := NULL;
v_Second VARCHAR2(20) := '';
v_Third VARCHAR2(20) := 'Something';
v_Blank VARCHAR2(2) := '';
v_Junk VARCHAR2(2) := '&~';
BEGIN
-- you need to run the following once, on it's own to make DBMS_OUTPUT.PUT_LINE work
-- what a shame there is no PRINT statement like a real database
-- SET SERVEROUTPUT ON;

-- proving NVL does not work with '' as the value
IF NVL(v_First,'')  = '' THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = "" Works'); END IF;
IF NVL(v_Second,'') = '' THEN DBMS_OUTPUT.PUT_LINE ( '"" = "" Works'); END IF;
IF NVL(v_Third,'') <> '' THEN DBMS_OUTPUT.PUT_LINE ( 'Something != "" Works'); END IF;
-- proving NVL does not work with a variable containing ''
IF NVL(v_First,v_Blank)  = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Blank Works'); END IF;
IF NVL(v_Second,v_Blank) = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( '"" = Blank Works'); END IF;
IF NVL(v_Third,v_Blank) <> v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Blank Works'); END IF;
-- proving that Oracle is junk, as other <> '' values do the trick
IF NVL(v_First,v_Junk)  = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Junk Works'); END IF;
IF NVL(v_Second,v_Junk) = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( '"" = Junk Works'); END IF;
IF NVL(v_Third,v_Junk) <> v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Junk Works'); END IF;
END;
/
 
RESULT:
NULL = Junk Works
"" = Junk Works
Something != Junk Works

 

Wot - me biased?

SQL Server - Oracle FAQ: "SQL Server"

Possibly the most xenophobic piece of community written drivel I have ever seen.

ViewState 101

TRULY Understanding ViewState - Infinities Loop

ViewState is a very misunderstood animal. I would like to help put an end to the madness by attempting to explain exactly how the ViewState mechanism works, from beginning to end, and from many different use cases, such as declared controls vs. dynamic controls.

I wish I had written this

Five Simple Database Design Errors You Should Avoid

Clear, concise and on the money.

This article covers:

(1) Common Lookup Tables
(2) Check Constraint conundrum
(3) Entity-Attribute-Value Table
(4) Application Encroachments into DB design
(5) Misusing Data values as Data Elements

Fun with triggers

What Happened:
1) Insert occurs - fires insert trigger (value 10)
2) Insert trigger updates record (value = 20) - fires update trigger
3) Update trigger updates record (value = 30) this does NOT fire a recursive call to the trigger
4) Update trigger writes hist record
5) Original insert trigger writes hist record
Hmmm interesting....



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MainTable]
( [MainTableID] [int] IDENTITY(1,1) NOT NULL
, [TheValue] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[MainTable_Hist]
( [MainTable_HistID] [int] IDENTITY(1,1) NOT NULL
, [Created] [datetime] NOT NULL
CONSTRAINT [DF_MainTable_Hist_Created] DEFAULT (GETDATE())
, [MainTableID] [int] NOT NULL
, [TheValue] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[trgMainTable_UPD]
ON [dbo].[MainTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @ThisValue AS INT
      , @ThisID AS INT

SELECT @ThisID = MainTableID
     , @ThisValue = TheValue
FROM inserted

IF ( @ThisValue < 100 )
  UPDATE dbo.MainTable
  SET TheValue = @ThisValue + 10
  WHERE MainTableID = @ThisID

  INSERT INTO [dbo].[MainTable_Hist] ( [MainTableID], [TheValue] )
  SELECT MainTableID, TheValue
  FROM Inserted
END
GO

CREATE TRIGGER [dbo].[trgMainTable_INS] ON [dbo].[MainTable]
AFTER INSERT AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @ThisValue AS INT
    DECLARE @ThisID AS INT

    SELECT @ThisID = MainTableID , @ThisValue = TheValue
    FROM inserted IF ( @ThisValue < 100 )

    UPDATE dbo.MainTable
    SET TheValue = @ThisValue + 10
    WHERE MainTableID = @ThisID

    INSERT INTO [MainTable_Hist] ( [MainTableID], [TheValue] )
    SELECT MainTableID, TheValue
    FROM Inserted
END
GO

INSERT INTO [MainTable] ( [TheValue] ) VALUES ( 10 )
GO

--==--==--==--==--==--
SELECT * FROM [MainTable] ORDER BY MainTableID

MainTableID TheValue
1           30 

SELECT * FROM [MainTable_Hist] ORDER BY MainTable_HistID

MainTable_HistID Created                  MainTableID  TheValue
1                2009-10-05 14:33:02.553  1            20
2                2009-10-05 14:33:02.553  1            10

SQL Stupidity

So you are logged on to SQL Management studio.
The user you are logged in as has database X as the default database
Now you detach that database... and you are stuck - the connection fails, you can't do anything.

DUH!

A warning would have been nice.

SOLUTION: Connect as another user and set the default database to something else then re-connect.

Excellent resource on Cancer

BBC NEWS | Health | Cancer: The facts

One in three of us will be diagnosed with cancer during our life.

Open Government and Local body data

Open Data Catalogue

Opengovt.org.nz is an open, independent catalogue of Government and Local Body datasets

Looking to download the latest list of Primary Schools? Want to find out the average market rents for Dunedin suburbs and use these in a report? Are you interested in the boundaries of your suburb? If the answer is yes then you may find some of this information hard to come by. Even though this information should be easy to obtain it is sometimes hard to find who to talk to, where to look and worse hidden behind pay-walls and restrictive license agreements. The Open Data Catalogue is an attempt to classify where this information resides, who ‘owns’ it, what license it is distributed under and if it is free or not.

Including SPATIAL datasets

marriage advice for IT peeps

CIO > Why (some) IT people's marriages are from hell

With the right type of communication, and by taking small, proactive measures to demonstrate that they're thinking about their spouses, IT leaders can have happier, more satisfying marriages, says a psychoanalyst turned life coach.

Elegant solution for juggling windows

WinSplit Revolution: "WinSplit Revolution is a small utility which allows you to easily organize your open windows by tiling, resizing and positioning them to make the best use of your desktop real estate."

WinSplit Revolution is freeware. Some Rights Reserved.

What’s your favorite “programmer” cartoon?

What’s your favorite “programmer” cartoon?

Usability Testing

Usability Testing With 5 Users

The ultimate user experience is improved much more by three tests with 5 users than by a single test with 15 users

New to computing? Have a friend/parent wanting to become digital? - try this!

Digital Literacy Curriculum Original Version XP/Office 2003

Digital Literacy Curriculum II Vista/Office 2007

Cut 8 wires and see what happens

Bruce Perens - A Cyber-Attack on an American City

The city of Morgan Hill and parts of three counties lost 911 service, cellular mobile telephone communications, land-line telephone, DSL internet and private networks, central station fire and burglar alarms, ATMs, credit card terminals, and monitoring of critical utilities. In addition, resources that should not have failed, like the local hospital's internal computer network, proved to be dependent on external resources, leaving the hospital with a "paper system" for the day.

The rest of the article is very interesting.

Oracle debug - output a CRLF delimited block of text

v_crlf CHAR(2):=CHR(13) || CHR(10);

v_RemainingText:=v_Orig_Text || v_crlf ;
WHILE length(v_RemainingText) > 1 LOOP
    v_crpos:=instr(v_RemainingText,v_crlf);
    v_ThisText:=substr(v_RemainingText,1,v_crpos-1);
    dbms_output.put_line(v_ThisText);
    v_RemainingText:=substr( v_RemainingText
                           , v_crpos+2
                           , length(v_RemainingText)-(v_crpos+1)
                           );
END LOOP;

VPC Images for testing IE6, 7, 8

Download details: IE App Compat VHD:

Nice Free Icons

Free Icons: Function Icon Set, 128 Completely Free Icons

I Speak Geek

Scott Hanselman's Computer Zen - FizzBin - The Technical Support Secret Handshake:

Fun with Foreign Keys

SELECT object_name(SR.rkeyid) AS Master
     , RC.name                AS Master_Column
     , object_name(SR.fkeyid) AS Child
     , FC.name                AS Child_Column
     , object_name(SR.constid)AS FK_Name

FROM       sysreferences SR
INNER JOIN sys.columns   RC ON RC.Object_ID = SR.rkeyid AND RC.Column_ID = SR.rkey1
INNER JOIN sys.columns   FC ON FC.Object_ID = SR.fkeyid AND FC.Column_ID = SR.fkey1
ORDER BY object_name(SR.rkeyid)
       , object_name(SR.fkeyid)

I Am Employed

Web developer at iSoft

You know you want to

I don't come in different colours or sizes, but I am great value for money.
This year you could have your very own MrDee working at your place. (as long as your place is in Auckland)
As of tomorrow I am officially on the Job market.
So drop me a line I would love to chat.

What was XTRA thinking

Yesterday I posted an article about the Google streetview technology.
I saw it last year and took a virtual tour of Brooklyn NY. Brilliant.
I never dreamed that NZ would be in the first 10 countries to get this. Well done Google.

So when XTRA, the number one ISP in NZ (by volume) looked for a partner to offload their e-mail capability they selected Yahoo.
I assume this 'partnership' was because of the care and concern Yahoo has for our country.
just look at the stunning level of detail in the auckland Yahoo map

Pitiful.

Yahoo mail is hostile, the data is stored off shore and XTRA is still crashing more often then a drunk university student in Palmerston north.

Can anyone recommend an ISP that is affordable and still works for their money?

Walk on by...

Map here

I will wave at you from my desk :D

A support headache cured.

Computerworld > Microsoft discontinues Windows... 3.x

As of November 1, 2008 Microsoft has stopped issuing licences for Windows 3.x, which debuted in May 1990.

Published! (sort of)

Ask Phoebe : Grab a street map - you'll need it - 06 Nov 2008 - NZ Herald:

I have tried, to no avail, to discover the scale and extent of the roadworks on the Te Atatu interchange. A sign warned that the work would take from October 2008 to February 2009, but what exactly is being done? Is there a website that will explain this to me? Andrew Dixon, Te Atatu.