Recent

Author Topic: [SOLVED] SQLite check if record exists  (Read 696 times)

Pe3s

  • Sr. Member
  • ****
  • Posts: 476
[SOLVED] SQLite check if record exists
« on: May 31, 2023, 07:07:08 pm »
Hello, how can I check if a record exists, I want the program to display information that the record exists.
e.g. The given e-mail address exists!
« Last Edit: June 01, 2023, 04:50:39 pm by Pe3s »

paweld

  • Hero Member
  • *****
  • Posts: 782
Re: SQLite check if record exists
« Reply #1 on: May 31, 2023, 10:38:45 pm »
Code: Pascal  [Select][+][-]
  1. SQLQuery1.SQL.Text := 'select name from users where email=:mail';  
  2. SQLQuery1.ParamByName('mail').AsString : 'kontakt@example.com';    
  3. SQLQuery1.Open;    
  4. if not SQLQuery1.IsEmpty then  
  5. begin  
  6.   ShowMessage('E-mail is exist for user: ' + SQLQuery1.FieldByName('name').AsString);  
  7.   SQLQuery1.Close;  
  8. end  
  9. else  
  10. begin  
  11.   SQLQuery1.Close;  
  12.   //Add email to db  
  13. end;
  14.  
Best regards / Pozdrawiam
paweld

Чебурашка

  • Sr. Member
  • ****
  • Posts: 450
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite check if record exists
« Reply #2 on: June 01, 2023, 08:42:03 am »
Hello, how can I check if a record exists, I want the program to display information that the record exists.
e.g. The given e-mail address exists!

I normally use sql count function.

Code: Pascal  [Select][+][-]
  1.   q.SQL.Text := 'select count(id) as count_id from table where string_expr=:string_expr';  
  2.   q.ParamByName('string_expr').AsString : 'what?';    
  3.   q.Open();
  4.   Exists := (q.FieldByName('count_id').AsInteger > 0);    
  5.   q.Close();
  6.  
  7. if Exists then
  8.   ShowMessage(Format('Requested string_expr "%s" exist', ['what?']));
  9.  
  10.  
« Last Edit: June 01, 2023, 08:45:09 am by Чебурашка »
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

  • Sr. Member
  • ****
  • Posts: 476
Re: SQLite check if record exists
« Reply #3 on: June 01, 2023, 08:59:14 am »
And what would the procedure of adding data look like with checking if the record exists

Чебурашка

  • Sr. Member
  • ****
  • Posts: 450
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite check if record exists
« Reply #4 on: June 01, 2023, 09:15:40 am »
And what would the procedure of adding data look like with checking if the record exists

Code: Pascal  [Select][+][-]
  1.  
  2. if Exists then (*say that exists*)
  3. else
  4.   // add
  5.  
  6.   q.SQL.Text = 'insert into table (...) values (:...)'
  7.   q.ParamByName('...').As<Type> := v...;
  8.   // .. repeat for each param
  9.   q.ExecSQL();
  10.  
  11. (* surround with try except to make it rite *)
  12. (* maybe say that you did add *)
  13.  
  14.  
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: 1935
Re: SQLite check if record exists
« Reply #5 on: June 01, 2023, 09:23:23 am »
And what would the procedure of adding data look like with checking if the record exists
Whatever for?
Declare the EMail-Field as UNIQUE and then just add the record.
If the Value is already there you'll get an exception.

btw: An EMail-Address is also a candidate for a Primary Key
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

Чебурашка

  • Sr. Member
  • ****
  • Posts: 450
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite check if record exists
« Reply #6 on: June 01, 2023, 10:11:18 am »
Declare the EMail-Field as UNIQUE and then just add the record.
If the Value is already there you'll get an exception.

Maybe the OP prefers the schema check existence + insert, instead of insert + manage duplicate exception. I also do, but is just my personal preference.

I am from the "exception is when your code does the right thing but something goes wrong, unpredictably, maybe sometimes yes and sometimes not" school.
« Last Edit: June 01, 2023, 10:20:06 am by Чебурашка »
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: 1935
Re: SQLite check if record exists
« Reply #7 on: June 01, 2023, 10:29:35 am »
« Last Edit: June 01, 2023, 10:33:00 am by Zvoni »
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

Чебурашка

  • Sr. Member
  • ****
  • Posts: 450
  • СЛАВА УКРАЇНІ! / Slava Ukraïni!
Re: SQLite check if record exists
« Reply #8 on: June 01, 2023, 10:45:38 am »
Huh?
Why is OP asking the same question: https://forum.lazarus.freepascal.org/index.php/topic,61425.0.html

Maybe he wants to increase the post count because his goal to become a Hero Member asap.
We should think about attributing a significance to all the posts we do (for example if I post "Thanks for the kind answer", I'd say that my post should not be counted in my posts count)
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

  • Sr. Member
  • ****
  • Posts: 476
Re: SQLite check if record exists
« Reply #9 on: June 01, 2023, 04:50:25 pm »
Hello, I wanted to dust off the previous question, but the forum did not allow because it exceeded 120 days, so I asked again because I wanted to have a better grade at school at the end of the year about being a hero - pajamas with the Superman logo are enough  ;)

Thank you very much for all the replies, Regards and have a nice day

 

TinyPortal © 2005-2018