One of the things I like about MS Access is the AutoNumber field type. And SQL Server has its Identity field type. Very handy for primary keys. But from time to time I convert applications into a more heavy-duty database, such as Oracle, where to do the same thing, you need to select from a sequence first. Here’s how to do an Autonumber-style field without mucking about with the sequence.

You do, of course, need the sequence, to track unique IDs.

CREATE SEQUENCE [Schema].[SequenceName] START WITH [Number] INCREMENT BY [Number] MINVALUE [Number] NOCACHE NOCYCLE;

NoCache will avoid skipping values due to Oracle creating any numbers in advance. NoCycle avoids the number repeating itself. I generally name my sequence after the field it’s being used for, with an _SEQ suffix.

Putting the sequence number into the field is done by a trigger:

CREATE TRIGGER [TriggerName]
before insert on [TableName]
for each row
begin
select [SequenceName].nextval into :new.[ColumnName] from dual;
end;

Easy!

There is a catch. While in Access or SQL Server there are easy ways to find out the value of the AutoNumber/Identity field in the row you just added (through @@IDENTITY in SQL Server or in Jet 4.0 or later), this isn’t so easy in Oracle.

You can query the column’s MAX value or the sequence CURRVAL afterwards — but this isn’t reliable if yours is not the only process inserting data. Would it work if you placed your code in a transaction? I’m not convinced.

So the trigger method should only be used when you don’t need to know the number entered straight afterwards. If you need to know, then forget the trigger — just select your Sequence value first, then use that in your insert(s).

As someone who administers a number of databases, I’d say to anyone that you need a damn good reason to put non-standard values into a field.*

Today’s curse is sorting out the “Counties” field, which is supposed to be used to show British counties. To add insult to injury, people have to select from a drop-down menu, and if they try to type in a value that doesn’t exist, they are specifically asked if they mean to add that value (methinks the database is not locked down quite tightly enough, but I can’t control that bit at the moment).

People, let me tell you, from even basic general and geographical knowledge: Austria, Australia, BD20 0DX, Bahrain, Brentwood, Brighton, Jacksonville Canada, NW10 0HD… none of these are British counties, nor were they ever, nor will they ever likely be.

* That good reason doesn’t exist. The same goes for towns, titles, gender, and a whole host of other fields with clearly defined standards.

Daniel’s favourite file formats for distributing multimedia to friends, on the web, that kind of thing. I know a lot of hardcore geeks will know all this stuff (and disagree with some of it), but I was asked recently about it, so here goes.

For pictures containing large blocks of colour, including most screendumps out of Windows… PNG. GIF used to do the job for this, but PNG supports more than 256 colours, and is now in widespread use. Even Windows XP Paint manages to handle it, though older versions don’t.

For pictures with lots of variance in colour, such as most photos, it’s gotta be… JPEG. Try out the various compression settings to see what works. You can often shrink files down a surprising amount with little in the way of visible detail loss. Of course, keep a pristine copy if it’s a picture you’ll need to work on in the future. Windows XP Paint can do JPEGs… uhh, kind of… no control over compression.

For sound, the cross-platform standard now is MP3. A nice balance of quality and file size. Playback software is standard on any computer of the last 5 years or so (and if not already installed ‘cos it’s some ancient beast, is freely available), and unlike WMA it’s supported on pretty much every portable digital music player ever made. (I’m with Cameron – Microsoft should retire WMA. They won’t, of course.)

Movies? MPEG-1. Don’t get me wrong, I love DivX. DivX good. And in comparison, MPEG-1 is not the most efficient of formats, but even ancient creaking Windows NT Media Player supports it. No mucking about with making people download extra codecs or special players – any setup can handle it. No RealPlayer or Quicktime nagware. No Windows-only WMV that the poor Mac and Linux people can’t handle.

And if you use a decent encoder such as TmpGenC (free!), you can do a fair bit of tweaking so you get a watchable movie that’s not too humungous. Fiddle the bitrates, try a variable bitrate, reduce the frames per second, crop unused parts of frame, use a efficient sound compression. Takes a bit of practice, but worthwhile in my opinion. Hopefully in the near future, more efficient versions such as MPEG-2 and MPEG-4 will become standard on all computers. But in the meantime, I’m sticking with MPEG-1.

Agree? Disagree? Comment?