[SOLVED] SQLite record exists lazarus / 16-Databases ---------------------------------------------------------------------- Pe3s · 2022-11-30 10:21 Hello, how to check if a record exists? ---------------------------------------------------------------------- dseligo · 2022-11-30 10:54 Quote from: Pe3s on November 30, 2022, 11:21:00 amHello, how to check if a record exists? If I didn't misunderstood you: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQL.Text := 'select ... from ... where ...';  Open;  If not Eof then // this is check if you get anything ---------------------------------------------------------------------- Чебурашка · 2022-11-30 10:55 This works also with other DBMS, not only with SQLite. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  query:TSQLQuery;begin  [...]  query.SQL.Text := 'select count(*) as cnt from Table where (recordKey = :recordKey)'; // table name and condition will depend on your structure  query.ParamByName('recordKey').AsInteger := 45; // params and related types will depend on your structure  [...]    query.Open();  exists := (query.FieldByName('cnt').AsInteger > 0); // if recordKey is primary key, and record exists this will be 1, otherwise 0, if recordKey is just a matching condition, count can be more than 1 if it maches  query.Close(); ---------------------------------------------------------------------- Zvoni · 2022-11-30 11:15 Quote from: Pe3s on November 30, 2022, 11:21:00 amHello, how to check if a record exists? Whatever for would you need that? ---------------------------------------------------------------------- Handoko · 2022-11-30 11:26 tt's suggestion should work. I did it on Firebird, that was very similar to the code on the reply #2. ---------------------------------------------------------------------- Zvoni · 2022-11-30 14:43 Quote from: Handoko on November 30, 2022, 12:26:56 pmtt's suggestion should work. I did it on Firebird, that was very similar to the code on the reply #2. It might work, it might not. The important question is, WHY does he wants to check if a record exists? Because i have a suspicion, that it's not even necessary to query the Database if a record exists, if the table/columns are set up correctly. Because this is a "classic" question from Database-Beginners: Check if record exists. If no, insert the new record If yes, warn user "Ehh...No...Already Exists" (optionally offer to update existing record instead of inserting a new one). With correctly set up tables/columns, it's not necessary to query beforehand, since SQLite supports all necessary mechanisms out-of-the- box ---------------------------------------------------------------------- Handoko · 2022-11-30 15:13 I cannot answer why he need it. In my case, if user omit the ID for the new record, the system will generate it automatically but it needs to make sure that ID haven't been used. I knew auto increment field, but that was for learning purpose, so I chose not use use auto increment field. ---------------------------------------------------------------------- Pe3s · 2022-11-30 15:23 @zvoni, I need agricultural equipment for records, each equipment will have its own number, I want to check if equipment with this number is already in the database. ---------------------------------------------------------------------- Zvoni · 2022-11-30 15:30 Quote from: Pe3s on November 30, 2022, 04:23:21 pm@zvoni, I need agricultural equipment for records, each equipment will have its own number, I want to check if equipment with this number is already in the database. Exactly what i was talking about Quoteeach equipment will have its own numberSomething like that is called a primary key or at least has the Attribute UNIQUE Now the pertinent question: How do you choose that "number"? Do you get that number from somewhere? Are you calculating it yourself? I'd first answer those questions before implementing unnecessary code. Could you give me an overview, what you need in that table? and how the information is interrelated incl. the attributes of each information? Sounds simple enough ---------------------------------------------------------------------- Чебурашка · 2022-11-30 15:45 Quote from: Zvoni on November 30, 2022, 12:15:18 pmWhatever for would you need that? A question like this we could make it to a million of persons asking anything in any forum. For example: Q- Hello, how can I implement a tcp/ip connection to exchange 3760 bytes encrypted messages to a remote client? And can somebody help me to implement aslo the client please? Imma newbie, so I dunno from where to start. A- Why do you need that? Q- Hello, can someboby tell me the recipe of russian borsch? A- Why do you need that? ---------------------------------------------------------------------- Pe3s · 2022-11-30 15:48 The number is entered manually ID - primarykey Equipment number, e.g.: 13E46 Equipment name, e.g. plow Operator: Text ---------------------------------------------------------------------- Чебурашка · 2022-11-30 15:49 Quote from: Pe3s on November 30, 2022, 04:23:21 pm@zvoni, I need agricultural equipment for records, each equipment will have its own number, I want to check if equipment with this number is already in the database. In this case you are checking against the equipment identifier (I guess), but a similar question arises if you want to make a search over a table: Tell is there are equipments whose tyre mount date is older than 4 years. select count(*) from Equip where (tyre mount date < now - 4 y) ---------------------------------------------------------------------- Zvoni · 2022-11-30 15:56 Quote from: Pe3s on November 30, 2022, 04:48:06 pmThe 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 ---------------------------------------------------------------------- Zvoni · 2022-11-30 15:59 Quote from: tt on November 30, 2022, 04:45:54 pmQuote from: Zvoni on November 30, 2022, 12:15:18 pmWhatever for would you need that? A question like this we could make it to a million of persons asking anything in any forum. It has more to do with that I’m following OP‘s threads for some time now, and he himself has said, he‘s a beginner. I „smelled“ the reason why he wants to check existence of a record. ---------------------------------------------------------------------- Чебурашка · 2022-11-30 16:03 Quote from: Zvoni on November 30, 2022, 04:56:11 pmQuote from: Pe3s on November 30, 2022, 04:48:06 pmThe 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. ---------------------------------------------------------------------- Чебурашка · 2022-11-30 16:10 Quote from: Zvoni on November 30, 2022, 04:56:11 pmQuote from: Pe3s on November 30, 2022, 04:48:06 pmThe 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 · 2022-11-30 16:59 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 · 2022-11-30 17:12 Quote from: tt on November 30, 2022, 05:03:42 pmQuote from: Zvoni on November 30, 2022, 04:56:11 pmQuote from: Pe3s on November 30, 2022, 04:48:06 pmThe 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 ---------------------------------------------------------------------- Чебурашка · 2022-11-30 19:07 Quote from: Zvoni on November 30, 2022, 06:12:39 pmFor 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 · 2022-12-01 09:00 Quote from: tt on November 30, 2022, 08:07:03 pmQuote from: Zvoni on November 30, 2022, 06:12:39 pmFor 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...) ---------------------------------------------------------------------- Чебурашка · 2022-12-01 09:20 Quote from: Zvoni on December 01, 2022, 10:00:35 amThat 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 · 2022-12-01 09:39 Quote from: tt on December 01, 2022, 10:20:43 amQuote from: Zvoni on December 01, 2022, 10:00:35 amThat 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 QuoteI 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 ---------------------------------------------------------------------- Чебурашка · 2022-12-01 09:59 Quote from: Zvoni on December 01, 2022, 10:39:07 amOK, let's agree to disagree  We live in a free world babe! Quote from: Zvoni on December 01, 2022, 10:39:07 amWhat 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 Quote from: Zvoni on December 01, 2022, 10:39:07 amIt'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. Quote from: Zvoni on December 01, 2022, 10:39:07 amI'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).