Welcome to the Blog of David Clegg. I am a hard-core Delphi fan, so most of my posts will probably reflect this.
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 :-
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.
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 ....
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.
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.
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
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.
You don't always need to know. It is often sufficient to simply ensure the record is there at the end of the operation.
"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 ...."
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?
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.
I meant to say, "if a single company ALREADY has a branch 99, then the enitre statement fails unless you use this trick"
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.
I've heard a rumour that Dave and his team have recently sold the source code to their system for over a million bucks.
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.
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).
Hey, I recently added a news widget from www.widgetmate.com to my blog. It shows the latest news, and just took a copy and paste to implement. Might interest you too.
There is the hoary old saying "the difficult we do at once, the impossible takes a little longer." We don't actually make this claim for ourselves but to a certain extent, the saying is true when it comes to research. Research can create new ways of doing things that were previously unknown. This can put you ahead of your competitors. It can let you achieve results that it is not possible for your competitors (unless they do research too).
Scholarly or scientific investigation aimed at discovering new facts or laws and the revision of accepted theories or laws in the light of new facts.
We are not motivated by "publish or perish" as academic researchers are. This means we can make our results exclusive to you. It also means more value for your money because we do not need to spend time polishing the results to publishable standard. All we care about is whether our research makes money for you. However, we are willing to publish and present our work on your behalf for marketing purposes if you wish.
(Note that we do not do market research although we are able to analyse and interpret market research data. We do not do stock research which is the research that stock analysts do though we do work with stock analysts. We do not do economics or social science research although we can research quantitative methods that can be applied to these areas.)
This is because we "understand" data. We have decades of experience working with data and have an intuitive feeling for what is real and what is not in the numbers. We understand randomness and noise. This means that we won't "torture" data until it reveals something that is not there. We will not overfit models. For example: we use cross-validation to test back-fitted models; we know when to use more than one random-effect term when fitting a regression line, we use a hypothesis-testing approach when data mining.
We are careful, skeptical, and even cynical when it comes to using models, especially when it comes to fitting financial models that beat the stock market. For example, we assume random walks until proven otherwise. We calculate confidence intervals for our estimated parameters. This gives you measurable safety in applying our results.
Our work can qualify for the New Zealand IRD 15% tax rebate for research. To qualify our work must include "R&D activities which are systematic, investigative and experimental. They either seek to resolve scientific or technological uncertainty or involve an appreciable element of novelty." For more information see the IRD page Research and development (R&D) tax credit.
We are an IRD Listed Research Provider for this purpose. This means that the minimum $20,000 R&D spend is waived if you spend it with us. And it means that we fulfil certain IRD criteria. For more information see the IRD page What are listed research providers?
Note that research in economics to the extent that it is a social science is excluded from eligibility for the tax rebate. Research to develop quantitative products that may be used in economics as a social science is eligible for the rebate.
Our work may qualify for grants from government bodies. For example, our work has qualified for grants from the Foundation for Research, Science and Technology (FRST).