Idle Musings of a Delphi Junkie

I've decided to take the challenge issued by Steve Trefethan, and have extended his excellent list with my take:

• Learn to populate your user interface from the database without using TDataSet/TDataSource


• Learn to leverage the power of the Refactoring menu


• Learn how to write and leverage effective unit tests


• Write a Delphi for .NET application. You won't get cooties, I promise


• Write an ECO application


• Learn how to leverage interfaces to add runtime discoverable behaviours to classes


• Write an article and submit it to Codegear Developer Network


• Learn another language. You will become a stronger Delphi developer for your efforts


• Point your local school or college to the Turbo Explorer suite of products. For bonus points, offer to teach a Delphi programming course


• Unsubscribe from borland.public.delphi.non-technical

A few weeks back, my boss showed me a technique he uses when inserting a record into a FireBird table, which caters for the scenario that the record may already be there and therefore shouldn't be inserted. It was one of those simple yet elegant solutions, and I've been meaning to share it here. Well, I'm waiting for a data import process to finish, so I thought I'd take the time to finally do this.

The technique revolves around turning the concept of a left join on its head and using it to check for failure (i.e a null field value). Here is an example of what I'm talking about :-

insert into tax_category(category_description, tax_rate)
select 'GST', '12.5' from rdb$database
left join tax_category t
on t.category_description = 'GST'
where t.category_id is null;

The above snippet will attempt to insert a record into the tax_category table using a set of hard-coded values, but because of the left join and where clause, the recordset being used for the insert will be empty if the join finds an existing record in the table.

Labels: Database, Delphi, FireBird, Interbase, programming, SQL