Idle Musings of a Delphi Junkie

I needed this twelve months ago :-(,
but will use it in future.
 

Your boss heard of unique indexes? And that's not very portable is it?
 

It would be a very foolish man to question the intelligence and skill of my boss.

I didn't think it needed to be said that this was a trivial example used to show this particular technique, which I can assure you does have merit in certain circumstances.
 

Very very usefull thanks...
 

I hate this "trick", I'm sure a programmer debugging a problem in 2 years will not understand why this insert into select is used, and replace it with a normal insert ....
Here are "real" methods that do the same thing.

Option 1: Primary Key
Option 2: Unique Index
Option 3: do it in 2 steps, select count(*) first then if 0 insert ....
 

For those saying he could use a unique index or other mechanisms, it could be possible he wanted to insert a record and ensure its uniqueness WITHOUT raising an error. Why raise an exception if you can avoid it?

In regards to portability, maybe their product works only with Firebird/Interbase, and that's all they need. Maybe the SQL composed is a string returned from a function that takes a database vendor into account.

In regards to using count(*), that isn't optimal because you don't want the database to count each and every occurrence. You are only interested if there is AT LEAST 1. Better option would be using "not exists". A good server will bail out on the first occurrence of the exists being satisfied. Another option might be to join the table with itself. Would have to test that one. Third option would be to create a static table to take the place of RDB$Database. This static table would always contain just one row and be used for nothing other than for occasions like this.
 

LOL! Good trick! You could make a better example though!

For the previous comment, that static table could be a sub-select ;)

This code is totally portable and a very common trick. Not readable to rookies, ofcourse! :)
 

Preventing an error from raising? Are you kidding me? You heard of exception blocks? To try and do the work that a RDMS does and not use the built-in functionality in regard to constraints is the worse thing someone can do, not to add what one of the other users said about it not being readable in 2 years down the road. This is a very good example of bad programming and hacking.
 

>It would be a very foolish man to >question the intelligence and skill >of my boss.

I don't know your boss or his skills so I can't make a judgement. However, what you've just shown is an awful way of handling the situation.
 

Well, this has turned out to be quite an interesting blog post. There have been agreements, disagreements and inaccurate assertions (and a little touch of irony, which I'll not bother pointing out for now ;-)).

Let me clarify a few things. We are fully aware of what database constraints are and how they can be applied to ensure data integrity. It goes without saying that armed with this knowledge we do use them when applicable.

As Kyle pointed out, performing an insert which would violate a unique constraint would result in an exception being raised. If we are running a script which could possibly be inserting a record which would violate a unique constraint, this is a condition known to us, so why would we not handle it, rather than allow the exception to be raised?

And SQL statements are not always run in contexts which allow conditional logic to be applied. This is one of the instances where the technique I outlined can be used.

But the best thing about this technique is that you don't have to use it. Period. We see it as another useful tool in our development toolbox, and as such apply it in situations where we deem it to be appropriate. You are 100% free to adopt it or ignore it as you see fit.
 

Firebird can handle exceptions, in or outside a SQL script. As for the method itself, you won't know if it's been inserted or not either since you don't know if the record exists from beforehand
 

> Firebird can handle exceptions,
> in or outside a SQL script.

But as I said in my last reply, having an existing record is not always an exception, and may actually be an expected condition. This technique assumes this, and handles that particular condition.

> As for the method itself, you
> won't know if it's been
> inserted or not. either since you
> don't know if the record exists
> from beforehand

You don't always need to know. It is often sufficient to simply ensure the record is there at the end of the operation.
 

Anonymous said:

"I hate this "trick", I'm sure a programmer debugging a problem in 2 years will not understand why this insert into select is used, and replace it with a normal insert ...."

Isn't that what comments are for?

To be fair to Dave, he did say it was a trivial example. In scripted dml code it could be even more powerful and remove the reliance on rdb$database.

You want to make sure that each company in your database has a 'Test Branch 99', and you dont want to check each insert for success or failure?

insert into branches(branch_id,company_id,branch_name)
select 99, c.company_id, 'Test_Branch' from company c
left join branches b on b.company_id = c.company_id and b.branch_id = 99
where b.branch_id is null

In dml script code, there's no other way to do it. If a single company has a branch 99, then the entire statement fails (following the atomicity rules).

If you're programming with more control over the statement (as in you did it all in bite sized chunks with a check for "exists" at each record) thenh you're asking for a lot of round-trips to the server and back into your own code.

As Dave says, if you don't want to use it, then don't.

The rest of us can benefit from the idea.
 

I meant to say, "if a single company ALREADY has a branch 99, then the enitre statement fails unless you use this trick"
 

Your boss is an idiot.

Surely this type of code makes it easy to get a job done in a very short amount of time, and when the customers are paying for time, either from your boss, or you in his team, you are shooting yourself in the foot.

I applaud the elegance of the technique when applied to problem-solving, but you (and your boss) need to learn some business sense.

This sort of nonsense can only give the rest of us a bad name.
 

I've heard a rumour that Dave and his team have recently sold the source code to their system for over a million bucks.

Can anyone confirm?
 

I know at least one of the people involved, and they say that the million dollar deal is true.

I'm not sure that Dave even kows about it.
 

Nah, it is bollocks.

I've followed b.p.n.t and as far as I can make out, Cleggy has been working on an ECO system, but then he went all quiet.

If he was selling Win32 stuff, he wouldn't have any where near enough built within this time to make a sale like that, and if he was doing ECO and .NET and had a sale like that we would have heard about it already about it.
 

I reckon there is more to this than meets the eye.

I know that Clegg lives somewhere in Te Atatu, Auckland NZ, and (I think) I've met his boss.

AFAIK, his boss is some kind of weird character who lives in the bush and only comes out to say things like: "This is the way you will write code for a thread-safe linked list"

The man is truly weird. Think Hobbit, and you will get some kind of idea. (except he is a bit taller than a Hobbit).
 

You don't know Dave.

He wouldn't work for a weirdo like that.
 

I like this SQL technique and will use it in my code.

I can't believe that is so simple, but when I look at it I can see that it works beutifully.

Mary
 

So Dave,
have you sold out?

Inquiring minds need to know.
 

What does this have to do with Delphi?