Author Archives: daniel

Queries in OLEDB: keep your parameters in order

If like me you earn your keep writing code to interrogate databases, you’re probably familiar with parameterised queries. If not, take a look here — they offer a way of passing parameters into queries without all that mucking about with formatting for different data types and so on. They also offer (so I’m led to believe) performance gains from the database server. And OLEDB makes it pretty easy to do it, too. So throw away those horrible old queries and switch to parameter queries today!

One thing not often mentioned however is that the parameters have to be in order. Because in the SQL you identify them only as question marks:

SELECT FIELD1 FROM TABLENAME WHERE CRITERIA1 = ? AND CRITERIA2 = ?

they have to be added in the correct order. If you add CRITERIA2 then CRITERIA1 to your parameters, then you’ll get unexpected results. The parameter names don’t actually match up to your SQL by name, only by position. This is kinda logical, because if you wanted:

SELECT FIELD1 FROM TABLENAME WHERE CRITERIA1 > ? AND CRITERIA1 < ?

both your parameters couldn’t be named CRITERIA1.

For some of my code, I build an INSERT or an UPDATE depending on whether I have a new or updated record to write. To avoid two sets of code, I’ll build the parameter list with the key field last, and though it may seem counter-intuitive, I build the INSERT statement to match. Bad semi-VB pseudocode follows:

If (new record) Then
  SQL = "INSERT INTO TABLENAME (FIELD1, FIELD2, KEYFIELD) VALUES (?, ?, ?)"
Else (updating record)
  SQL = "UPDATE TABLENAME SET FIELD1 = ?, FIELD2 = ? WHERE KEYFIELD = ?"
End If

…then add my parameters FIELD1, FIELD2 and KEYFIELD in that order. Easy.

Microsoft 2005 dev tool betas

To those who watch Microsoft’s dev tools, there are betas of the 2005 versions now available. MSDN subscribers can download full products straight away; others can order CDs, or stick to the Express products, which for trying out new languages, are quite nicely featured.

It’s a cunning strategy for Microsoft, helping to counter the proliferation of free programming languages such as Java and PHP by providing free development environments for ASP, VB, C#, C++ and their own (some would say mutated) J# implementation of Java.

MSDE, which has been around for years now, providing a royalty-free cut-down SQL Server, has been renamed SQL Server Express to show its heritage. (Well, its SQL Server heritage… most people know it grew out of Sybase, but that’s ancient history). Keeping it free thus helps fight off the MySql threat and allowing people for whom Access isn’t cutting it to be encouraged up to SQL Server).

Looking back 20 years to when I was growing up, trying out BASIC on my Commodore 64 or BBC Micro, I ponder how the next generation of programmers are getting hooked into this game. I suspect a mix of freebie entry-level products like this (and their counterparts from the world of open-source) is one way they can get involved. Which probably explains MS’s “Coding 4 Fun” web site.

Dave Winer and Google News

Dave Winer applied to get Scripting News onto Google News, and got rejected.

Apparently they did so on the basis that it’s just him that writes it. I suspect the real reason is that his site is not a news site, at least not in the conventional sense. It’s mostly just pointing to stuff elsewhere on the web, rarely with any comment or analysis. It’s certainly valuable, and maybe the media world will change over time, but for now a one-liner with a hyperlink is not a news service.

Dear Mr Gullible

Dear Mr Gullible,

How would you like to renew your domain name at an exhorbitant rate with us?

Sincerely,
Borderline Fraud Department,
Domain Registry of America

Domain Registry of America offer

Access-style Autonumber fields in Oracle

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).

Classic VB go bye bye

Mainstream support for Visual Basic 6 (the last version before .Net) finished at the end of March, and there’s growing murmurs in the VB development community, calling for it to be resurrected. While “classic” VBers didn’t mind being shunted into the .Net framework, they objected to some of the bigger changes to the language itself, which made it difficult to migrate old projects over. And remembering that “classic” VB is the most popular computer language ever, there’s a lot of old systems out there still running with it. A petition has been organised, with nearly 200 MVPs having signed up so far.

Microsoft have put up a new site highlighting VB6 called VBRun (harking back to the pre version 5 days when the main DLL was called VBRun). It’ll have VB6 content on it, as well as nudging developers towards .Net.

I work with VB. I’ve got a lot of VB6 code still running. But I’m slowly moving some stuff over to .Net. I’m not convinced it’s better yet. It’s certainly different, but I’ll get used to it eventually.

Doubts over Podcasting

While Cam and Mick at The Podcast Network are doing great things, I have niggling doubts about the long-term prospects for podcasting. Darren Barefoot has expressed some of them. My niggling doubts primarily come from the differences between blogs and podcasts, and the technical limitations of podcasting.

  • You can’t mass-consume podcasts like you can blogs, via aggregators. (Okay, so you can see summaries via aggregators, but not the content itself)
  • You can’t scan an audio file like you can a long blog entry.
  • Even on broadband, you don’t get instant gratification when you click on a link to podcast content – you have to wait X minutes, then you get the file, which you have to find time to listen to, and to find the bit you wanted… now, what was it I was clicking on again?
  • Most podcasts I’ve seen don’t have the immediatcy of blogs. There’s an element of post-production involved which inevitably introduces delays to getting the content out to the world.
  • You can’t easily quote a bit out of a podcast, or find a permalink to that specific moment.
  • Which means you can’t find podcasts by searching for keywords, unless there’s a transcript.

So will podcasting go mainstream? Will it knock radio off its roost? Or did video already do that? 🙂 Will the technical limitations be overcome? It’ll be interesting to see how it pans out in the long term.

iSkin

My iPod is back, and I decided to get a case for it. ‘Cos what with carrying it about, and the kids playing with it, I figured it could do with some protection, as well as a belt clip.

After looking about at the various products, and with Tony’s recommendation, I looked at the iSkin. They appear to have gone all out to design something that’s both practical and fairly visually appealing (though to be fair, nothing beats the look of the iPod itself — even down to the normally fugly things like the power supply, Apple have created something that is utterly beautiful).

iSkins are not cheap, at least not to those of us trading in the Australian Peso. A little shopping around showed a price of between A$49 and $59. Unfortunately the $49 price was at Streetwise, whom I have had recommended to me, but are not particularly conveniently located, and even if they were, have been closed this past weekend for moving. And I wanted my iSkin straight away. Likewise, Streetwise’s or Apple’s online shop could have sold me the product, but online shopping for physical items doesn’t give you instant gratification.

So I coughed up the $59 at the AppleCentre on Flinders Street. Took the skin back to my iPod, and then wrestled with it for about half an hour, trying to figure out how it opened.

See, in looking at the adverts and catalogues, I’d got it into my head that the iSkin was made of some kind of rigid plastic. It isn’t. It’s flexible, and you’re meant to get the iPod (and the instructions and other items in the packet) in and out by way of the gap for the screen.

When I figured this out, I found the instructions inside telling me so. WhyTF they couldn’t put a hint on the outside of the package, I don’t know. Would have saved me a bit of fiddling about.

Oh well, the iSkin is lovely, though I don’t think much of (and am not using) the “free bonus” click-wheel cover they included. The other downer: it can plug into its USB/firewire connection while in the skin, but it’ll have to come out to fit into the dock.

But hopefully it will keep my iPod safe from the ravages of the world.