[SOLVED] SQLite record exists

[Pe3s]

Hello, how to check if a record exists?

[dseligo]

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

[Чебурашка]

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.  

[Zvoni]

Hello, how to check if a record exists?

Whatever for would you need that?

[Handoko]

tt's suggestion should work. I did it on Firebird, that was very similar to the code on the reply #2.

[Zvoni]

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

[Handoko]

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]

@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]

@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

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

[Чебурашка]

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?

[Pe3s]

The number is entered manually

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

[Чебурашка]

@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]

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

[Zvoni]

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.

[Чебурашка]

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.