Sqlite insert only Where Not Exists

Solar2D General Questions/Discussion
#1

I’m using the page as shown elsewhere, but as it is, everytime you reload the game it inserts the same lines over and over.

I’m unsure if it does this on the phone everytime the game is started.

I would like to insert my data only if the row doesn’t exist, I’ve tried below, but nothing gets inserted.

Thanks
Dan

[code]
–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS gamestats (id INTEGER PRIMARY KEY, content TEXT, content2 SMALLINT);]]
–print(tablesetup)
db:exec( tablesetup )

db:exec[[
INSERT INTO gamestats VALUES(NULL,‘audiosetting’,1) SELECT ‘audiosetting’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘audiosetting’);

INSERT INTO gamestats VALUES(NULL,‘dogtreat’,1) SELECT ‘dogtreat’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘dogtreat’);

INSERT INTO gamestats VALUES(NULL,‘level1score’,9800) SELECT ‘level1score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level1score’);

INSERT INTO gamestats VALUES(NULL,‘level2open’,1) SELECT ‘level2open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level2open’);

INSERT INTO gamestats VALUES(NULL,‘level2score’,12400) SELECT ‘level2score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level2score’ );

INSERT INTO gamestats VALUES(NULL,‘dogl2treat’,1) SELECT ‘dogl2treat’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘dogl2treat’);

INSERT INTO gamestats VALUES(NULL,‘level3open’,1) SELECT ‘level3open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level3open’);

INSERT INTO gamestats VALUES(NULL,‘level3score’,8000) SELECT ‘level3score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level3score’);

INSERT INTO gamestats VALUES(NULL,‘level4open’,1) SELECT ‘level4open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level4open’);

INSERT INTO gamestats VALUES(NULL,‘level4score’,17270) SELECT ‘level4score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level4score’);

INSERT INTO gamestats VALUES(NULL,‘dogl4treat’,0) SELECT ‘dogl4treat’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘dogl4treat’);

INSERT INTO gamestats VALUES(NULL,‘level5open’,1) SELECT ‘level5open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level5open’);

INSERT INTO gamestats VALUES(NULL,‘level5score’,0)SELECT ‘level5score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level5score’);

]]
[import]uid: 78446 topic_id: 15428 reply_id: 315428[/import]

#2

try this
[lua]INSERT OR REPLACE INTO gamestats VALUES(NULL,‘audiosetting’,1) [/lua]

[import]uid: 71210 topic_id: 15428 reply_id: 57048[/import]

#3

if that doesn’t work…try this
[lua]–Setup the table if it doesn’t exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS gamestats (id INTEGER PRIMARY KEY, content TEXT, content2 SMALLINT);]]
–print(tablesetup)
db:exec( tablesetup )

–checking if the table is empty
local sql = [[SELECT * FROM gamestats]]
local rowcount = 0
for row in db:nrows( sql ) do
rowcount = rowcount + 1
end
if rowcount == 0 then
db:exec[[
INSERT INTO gamestats VALUES(NULL,‘audiosetting’,1) SELECT ‘audiosetting’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘audiosetting’);

INSERT INTO gamestats VALUES(NULL,‘dogtreat’,1) SELECT ‘dogtreat’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘dogtreat’);

INSERT INTO gamestats VALUES(NULL,‘level1score’,9800) SELECT ‘level1score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level1score’);

INSERT INTO gamestats VALUES(NULL,‘level2open’,1) SELECT ‘level2open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level2open’);

INSERT INTO gamestats VALUES(NULL,‘level2score’,12400) SELECT ‘level2score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level2score’ );

INSERT INTO gamestats VALUES(NULL,‘dogl2treat’,1) SELECT ‘dogl2treat’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘dogl2treat’);

INSERT INTO gamestats VALUES(NULL,‘level3open’,1) SELECT ‘level3open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level3open’);

INSERT INTO gamestats VALUES(NULL,‘level3score’,8000) SELECT ‘level3score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level3score’);

INSERT INTO gamestats VALUES(NULL,‘level4open’,1) SELECT ‘level4open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level4open’);

INSERT INTO gamestats VALUES(NULL,‘level4score’,17270) SELECT ‘level4score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level4score’);

INSERT INTO gamestats VALUES(NULL,‘dogl4treat’,0) SELECT ‘dogl4treat’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘dogl4treat’);

INSERT INTO gamestats VALUES(NULL,‘level5open’,1) SELECT ‘level5open’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level5open’);

INSERT INTO gamestats VALUES(NULL,‘level5score’,0)SELECT ‘level5score’ WHERE NOT EXISTS
(SELECT * FROM gamestats WHERE content=‘level5score’);
]]

end [/lua] [import]uid: 71210 topic_id: 15428 reply_id: 57049[/import]

#4

Thanks, my original code has something wrong and wouldn’t work, I used this part and it worked.

[code]
if rowcount == 0 then
db:exec[[
INSERT INTO gamestats VALUES(NULL,‘audiosetting’,1) ;

INSERT INTO gamestats VALUES(NULL,‘dogtreat’,1) ;

INSERT INTO gamestats VALUES(NULL,‘level1score’,9800);

INSERT INTO gamestats VALUES(NULL,‘level2open’,1);

INSERT INTO gamestats VALUES(NULL,‘level2score’,12400);

INSERT INTO gamestats VALUES(NULL,‘dogl2treat’,1);

INSERT INTO gamestats VALUES(NULL,‘level3open’,1);

INSERT INTO gamestats VALUES(NULL,‘level3score’,8000);

INSERT INTO gamestats VALUES(NULL,‘level4open’,1);

INSERT INTO gamestats VALUES(NULL,‘level4score’,17270);

INSERT INTO gamestats VALUES(NULL,‘dogl4treat’,0);

INSERT INTO gamestats VALUES(NULL,‘level5open’,1);

INSERT INTO gamestats VALUES(NULL,‘level5score’,0);
]]

end [/code]

The bad thing with this though, is if I make updates in the future and add to the DB it will cause me problems in the future. That’s why I was hoping to get something like what I had with a If not exists instead of an if… statement, but for now it works.

Thanks
Dan [import]uid: 78446 topic_id: 15428 reply_id: 57192[/import]

#5

just put a set of update statements also and if there are values in the table just execute those… [import]uid: 71210 topic_id: 15428 reply_id: 57193[/import]