Recent

Author Topic: [SOLVED] SQLite record exists  (Read 1684 times)

Pe3s

  • Hero Member
  • *****
  • Posts: 533
[SOLVED] SQLite record exists
« on: November 30, 2022, 11:21:00 am »
Hello, how to check if a record exists?
« Last Edit: November 30, 2022, 05:59:48 pm by Pe3s »

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: SQLite record exists
« Reply #1 on: November 30, 2022, 11:54:21 am »
Hello, how to check if a record exists?

If I didn't misunderstood you:
Code: Pascal  [Select][+][-]
  1. SQL.Text := 'select ... from ... where ...';
  2.   Open;
  3.   If not Eof then // this is check if you get anything

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite record exists
« Reply #2 on: November 30, 2022, 11:55:38 am »
This works also with other DBMS, not only with SQLite.

Code: Pascal  [Select][+][-]
  1. var
  2.   query:TSQLQuery;
  3. begin
  4.   [...]
  5.   query.SQL.Text := 'select count(*) as cnt from Table where (recordKey = :recordKey)'; // table name and condition will depend on your structure
  6.   query.ParamByName('recordKey').AsInteger := 45; // params and related types will depend on your structure
  7.   [...]  
  8.   query.Open();
  9.   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
  10.   query.Close();
  11.  
« Last Edit: November 30, 2022, 12:03:47 pm by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite record exists
« Reply #3 on: November 30, 2022, 12:15:18 pm »
Hello, how to check if a record exists?
Whatever for would you need that?
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: SQLite record exists
« Reply #4 on: November 30, 2022, 12:26:56 pm »
tt's suggestion should work. I did it on Firebird, that was very similar to the code on the reply #2.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite record exists
« Reply #5 on: November 30, 2022, 03:43:17 pm »
tt'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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Handoko

  • Hero Member
  • *****
  • Posts: 5131
  • My goal: build my own game engine using Lazarus
Re: SQLite record exists
« Reply #6 on: November 30, 2022, 04:13:56 pm »
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.
« Last Edit: November 30, 2022, 04:17:24 pm by Handoko »

Pe3s

  • Hero Member
  • *****
  • Posts: 533
Re: SQLite record exists
« Reply #7 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.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite record exists
« Reply #8 on: November 30, 2022, 04:30:03 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
Quote
each equipment will have its own number
Something 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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite record exists
« Reply #9 on: November 30, 2022, 04:45:54 pm »
Whatever 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?

« Last Edit: November 30, 2022, 04:54:18 pm by tt »
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Pe3s

  • Hero Member
  • *****
  • Posts: 533
Re: SQLite record exists
« Reply #10 on: November 30, 2022, 04:48:06 pm »
The number is entered manually

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

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite record exists
« Reply #11 on: November 30, 2022, 04:49:13 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)
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite record exists
« Reply #12 on: November 30, 2022, 04:56:11 pm »
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
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: SQLite record exists
« Reply #13 on: November 30, 2022, 04:59:36 pm »
Whatever 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.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Чебурашка

  • Hero Member
  • *****
  • Posts: 568
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite record exists
« Reply #14 on: November 30, 2022, 05:03:42 pm »
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.
FPC 3.2.0/Lazarus 2.0.10+dfsg-4+b2 on Debian 11.5
FPC 3.2.2/Lazarus 2.2.0 on Windows 10 Pro 21H2

 

TinyPortal © 2005-2018