... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2007-03-28

Generating Random Numbers in T-SQL

The SQL Server T-SQL function RAND() generates random numbers, but it will only generate exactly one such number in a given query (i.e. multiple 'calls' to the function all return the same number).  Another way is to exploit the NEWID() function which returns a UUID (GUID).  At time of writing, the function returns Version 4 UUIDs, which means that most of the bits are random (note the variant or version bits, see the UUID quick summary, below).

So to get a four byte random number, you can use:

select substring(cast(newid() as binary(16)),1,4)+0

Be careful if you try to extract more bits since it is tricky figuring out which bits are which, viz:

  2BDB9E2A-A20D-43F9-9117-2ABCEBD6434E cast to binary(16) is
0x2A9EDB2B 0DA2 F943 9117 2ABCEBD6434E

Note that the leading eight hex octets are switched to low-endian component-wise -- but the other eight octets are not.  The less-than-random hex digits are bolded.

UUID Quick Summary

The layout of a UUID is as follows:

TTTL-TM-TH-RC-NNNNNN

Where:

TTTL = 4 hex octets, "time low"

TM = 2 hex octets, "time mid"

TH = 2 hex octets, "time high and version", "version" in 4 most significant bits (0001 = time-based, 0010 = DCE, 0011 = name-based/MD5, 0100 = random, 0101 = name-based/SHA-1

R = 1 hex octet, "clock seq high and reserved", "variant" in 1-3 most significant bits (0 = NCS [1-7], 10 = RFC 4122 [8-B], 110 = Microsoft [C-D], 111 = reserved [E-F])

C = 1 hex octet, "close seq low"

NNNNNN = 6 hex octets, "node"

On the wire, the bytes of a UUID are transmitted in network byte order (most significant byte first)

2007-03-21

SQL Constraint Violations

Here is a snippet that will identify whether a given Java exception is caused by an SQL constraint violation:

private static boolean isConstraintViolationException(Exception exception) {
    for (Throwable cause = exception; null != cause; cause = cause.getCause()) {
        if (cause instanceof SQLException) {
            String sqlState = ((SQLException)cause).getSQLState();
            if (null != sqlState && sqlState.startsWith("23")) {
                return true;
            }
        }
    }
    return false;
}

The black magic is bolded.  All of the SQLSTATE values in the 23xxx block represent some form of constraint violation.  Many databases simply return 23000 which indicates a non-specific type of violation.

2007-03-20

Java Class Initialization

I came across a interesting gotcha involving Java class initialization.  Consider the following:

abstract class Base {
    Base() {
        inner();
    }
    protected abstract void inner();
}

public class Derived extends Base {
    int x = 0;  // GOTCHA!
    int y;

    Derived() {
        System.out.format("x = %s, y = %s\n", x, y);
    }

    @Override
    protected void inner() {
        x = 666;
        y = 777;
    }

    public static void main(String[] args) {
        new Derived();
    }
}

The program prints out "x = 0, y = 777".  You might have expected x to be 666.  The culprit is the field initializer labelled GOTCHA! which is executed after the superclass' constructor.  Java takes great pains to prevent you from writing code like this (e.g. you cannot reference this when calling the super() constructor).  And this example shows why.  This is all made clear in the Java Language Specification, but here is a practical instance that actually occurred in the field.

Blog Archive