SQLIte Replace or Insert

SQLite has this command called Replace or Insert. And I think you can just use Replace.
REPLACE or INSTER Tbl_Name (fields, fields, …) VALUES (value, value, …)

It inserts a record if it does not exist, or updates a record if it does. *actually the update is a Deletion then an Insertion.

BUT … REPLACE seems to just keep adding records because I am not supplying the Primary Key.

What I am looking for … is a way to ADD to the database if a record does not exist, or UPDATE it if it does.
What I am doing now is to DELETE all the records that match then INSERT them like this:

My little drawing program has a listbox that contains all the shapes a person has added to a canvas.
When they add a shape, the Canvas Invalidate method is called and the listbox of shapes is read and drawn.
When they want to save this drawing, every shape has a unique name.
I just hate wiping out data.
I’d like to have an SQLite command that just ADDs to the database if the unique name does NOT exist,
and UPdata the shape data if the shape already exits.

Since my unique name is not an integer , I don’t see how to use it as a Primary Key. Or unique so the REPLACE command will work.

Any suggestions?

What I do now if find the saved drawing name, and Delete all the records for that drawing in the table of shapes. Then loop over the listbox and save them all anew.

Clumsy I know, but it works.

Thanks Eric

You’re going to get more responses if you login with your real name as (almost) everyone else has.

Looking forward to welcoming you to the forum.

Well, for the sake of helping others which committed the error of not having a primary key, for this part you can do something like:

[code]-- Convert database and promote a field as a primary key
– This is just an example, read it and adapt it.

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE mydrawingtable RENAME TO _myold_drawingtable;

CREATE TABLE mydrawingtable (
“idtxt” TEXT NOT NULL, – my field needing to become a primary key
“another_field_txt" TEXT, – add the rest of the old structure
PRIMARY KEY(“idtxt”) – define the missing primary key
);

– convert the old table
INSERT INTO mydrawingtable (“idtxt”, “another_field_txt”, …) SELECT “idtxt”, “another_field_txt”, … FROM _myold_drawingtable;

COMMIT;

PRAGMA foreign_keys=on;[/code]

– remove the backup _myold_drawingtable if you wish after some inspection and tests

You definitely need to add a primary key. It doesn’t have to be an integer, but don’t make it dependent on actual data - it should not be based on a value that might change. But an integer makes it easier to do something like the delete statement below.

In the listbox, use rowtag and/or celltag to keep track of the primary key value and a status for whether the listbox row is new or has been updated. Add the pk of removed rows to a string array. At save time delete all the removed rows from the db using something like

"delete from [table] where [pk] in (" + Join(removed-array,",") + ")"

Then loop through the listbox updating existing records and adding new ones.

The table does have a primary key, it’s lot loaded into the drawing listbox when the user adds shapes.

BUT I guess I could look up how to add a constraint to a column. By making the ShapeName Unique, that may let REPLACE work as I want it to as it needs a unique column to match the record.

@Kem Tekinay, When I signed up for the account it was to get on the email list. Now that I am using the forum I don’t see how to change the name. And BTW, I don’t like being like everyone else.

Thanks Eric

Certainly your right, but don’t be surprised if responses to your requests are limited.

If you do decide to make the change, I’m sure customer service can help.

help@xojo.com

[quote=386617:@none none]@Kem Tekinay, When I signed up for the account it was to get on the email list. Now that I am using the forum I don’t see how to change the name. And BTW, I don’t like being like everyone else.

Thanks Eric[/quote]
go to xojo.com, ( not the forum) enter your login/pass
there you can then edit your name that appears on the forum.