[SOLVED] SQLite check if record exists

[Pe3s]

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!

[paweld]

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.  

[Чебурашка]

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.  

[Pe3s]

And what would the procedure of adding data look like with checking if the record exists

[Чебурашка]

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.  

[Zvoni]

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

[Чебурашка]

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.

[Zvoni]

Huh?
Why is OP asking the same question: https://forum.lazarus.freepascal.org/index.php/topic,61425.0.html

[Чебурашка]

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)

[Pe3s]

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