[SOLVED] SQLite record exists

[Чебурашка]

The number is entered manually

ID - primarykey
Equipment number, e.g.: 13E46
Equipment name, e.g. plow
Operator: Text

And there it is (exactly as i thought)
You said, each equipment has its own number.
First mistake: choosing a surrogate primary key, instead of the „real“ primary key „equipment number“, though i accept that surrogate primary keys are something of „state of the art“
Second mistake: Give that column the constraint UNIQUE, and SQLite itself will tell you, if a record with that number already exists. No need to query beforehand

About the first mistake please do this consideration:

1. imagine that you relied in "real" key
2. you used it in other tables to refer to this table (foreign key)
3. For some reason (the customer promises me a Lambo with a blonde girl on it if I implement, so I won't ask him "Why do you want that?" be assured) he wants that the real key is extended adding an extra field, that did not exist in the original design

Result: I have to modify also all the tables referring to this table, and in a database full of data this is a little boring, ans also you have to populate the other tables with the new column added....

The Lambo and the blonde girl will come next year...

Of course not relying on the real key introduces the need of much more joins when you have to reconstruct the data, but as in many things of the programmer life one should do a advantange/disadvantage balance

[Pe3s]

That was the plan.
But as @Zvoni wrote, UNIQUE did the trick.
I need to focus more on learning databases.

Thanks for all replies. Regards

[Zvoni]

The number is entered manually

ID - primarykey
Equipment number, e.g.: 13E46
Equipment name, e.g. plow
Operator: Text

And there it is (exactly as i thought)
You said, each equipment has its own number.
First mistake: choosing a surrogate primary key, instead of the „real“ primary key „equipment number“, though i accept that surrogate primary keys are something of „state of the art“
Second mistake: Give that column the constraint UNIQUE, and SQLite itself will tell you, if a record with that number already exists. No need to query beforehand

So you want to DB to throw an exception once you try inserting a dup key record?

Is possible to operate like this, but is also true that many best practices of programming say that it is not a good approach to use the exceptions as systematic part of the code flow, because in this way you are really not using them as exceptions. Example of exception: try accessing network resource, but find that it is not available, this you cannot predict. In the case posted here you can programmatically understand that record exist before attempting to insert it.

That would be true if you look at it as an exception.
For me it’s more of a „message“ of the Database, and i treat it as such.
Yes, in FPC you use the exception mechanism, but that is a valid way of capturing situations like this.

And in the end, the result is the same: you get the information that a record already exists.
The difference: you save on the calls to the database.
You want to insert 1000 records
In OP‘s way there would be 1000 Selects followed by 1000 inserts, but 10 records already exist.
My way has the 1000 inserts, and 10 exceptions

Do the math

[Чебурашка]

For me it’s more of a „message“ of the Database, and i treat it as such.

The math seems to be on your side (except the small error 1000 select + 1000 insert is in reality 1000 + 990).

I don't know, maybe you are right, but I am trying to search over the internet if this kind of Coding by Exception is recommended, and, sorry, if find only recommendations against it.

[Zvoni]

For me it’s more of a „message“ of the Database, and i treat it as such.

The math seems to be on your side (except the small error 1000 select + 1000 insert is in reality 1000 + 990).

I don't know, maybe you are right, but I am trying to search over the internet if this kind of Coding by Exception is recommended, and, sorry, if find only recommendations against it.

That wouldn't make any sense, because that's what exceptions are for.
Yes, it's good practice to code "defensively", but for critical steps (like an INSERT or UPDATE) it's never wrong to put the code in a "try... Except"-Block,
because frankly: If there is something, that can go wrong, it will go wrong. The question is not if, but when (Murphy's Law).
Of a 1000 INSERTS it will be no issue 990 times, but those 10 times something goes wrong.... and those 10 times are "the Exception to the rule" (now look at that word again...)

[Чебурашка]

That wouldn't make any sense, because that's what exceptions are for.

The problem is what people consider as "exceptional situation" (and I personally respect anyone's idea).

I consider an exception something that should work (i.e. code is right to manage any case) but doesn't (once in a while) just becasue there are external conditions that break it. The most classical example is the one I did before, accessing an external resources where you cannot control if is available so you cannot make any modification to your code, because is already correct, i.e. the problem is elsewhere and the only thing you can do is to trap such a situation in order to your program to avoid to crash.

What I find around the internet as suggestions how to think about exceptions resembles this.

The case you present does not follow at all this principle, because you can forsee with 100% of precision when insert will succed or fail becasue of a dup key before doing it.

Of course, again, I respect your approach, as seems also more efficient (I was thinking that exception management was far more slow than normal code flow, but seems not to be so).

[Zvoni]

That wouldn't make any sense, because that's what exceptions are for.

The problem is what people consider as "exceptional situation" (and I personally respect anyone's idea).

I consider an exception something that should work (i.e. code is right to manage any case) but doesn't (once in a while) just becasue there are external conditions that break it. The most classical example is the one I did before, accessing an external resources where you cannot control if is available so you cannot make any modification to your code, because is already correct, i.e. the problem is elsewhere and the only thing you can do is to trap such a situation in order to your program to avoid to crash.

What I find around the internet as suggestions how to think about exceptions resembles this.

The case you present does not follow at all this principle, because you can forsee with 100% of precision when insert will succed or fail becasue of a dup key before doing it.

Of course, again, I respect your approach, as seems also more efficient (I was thinking that exception management was far more slow than normal code flow, but seems not to be so).

OK, let's agree to disagree 

But as "Food for thought": What is the "classic" exception regarding Databases?
It's the Login of a User.....
I see it too many times, that people have a typo in their username or password (happened enough times to me, too).
There is no other way to catch that "message" from the Database except using the "Exception"-mechanism, because you cannot lookup beforehand, if the login is correct/valid

I consider an exception something that should work (i.e. code is right to manage any case) but doesn't (once in a while) just becasue there are external conditions that break it.

And the "external" condition is a constraint on the column.
I've seen it enough times, that e.g. the Database itself is designed by someone else, and you have to deal with it from the frontend

[Чебурашка]

OK, let's agree to disagree 

We live in a free world babe!

What is the "classic" exception regarding Databases?

- Try to connect with correct params, should work but it does not because cable is brocken/cut/disconnected/server down/wirefall/postgres down/postgres busy/bug
- Do an array of queries read/write with operations in the middle, and several went ok up to a point, and then, after some time, there is a failure due to internet timeout for example. 1000000 times you run same procedure and it works because no external issue happens in the middle. But 1000001 time it doesn't

It's the Login of a User.....
I see it too many times, that people have a typo in their username or password (happened enough times to me, too).
There is no other way to catch that "message" from the Database except using the "Exception"-mechanism, because you cannot lookup beforehand, if the login is correct/valid

Are you referring to the DB connection?
In this case I agree that the only mechanism can be to trap an exception (unless the db connector does not forsee a login method that returns false in case login credentials are invalid, without raising Exceptions), but this is not the case initially introduced.

I've seen it enough times, that e.g. the Database itself is designed by someone else, and you have to deal with it from the frontend

In a situation like this where there is a unknown design or a design error, of course one could reasonably use the exception handling, but IMHO this is not a valid reason to extend its usage to situations like the one we started from (prevent a dup key exception by doing a select before the insert).