database query failed

[Derpy]

hi umm i was trying to practice sql and i got an error which makes no sense to me as table exists

local PLAYER_DATABASE = dbConnect("sqlite","database/trexarex.db") 
  
setTimer(function() 
if PLAYER_DATABASE then 
   sendResult("successfully loaded player database! (trexarex.db)",root) 
   onDatabaseStart() 
else 
   errorMessage("failed to load player database! (trexarex.db)",root) 
end 
end,50,1) 
  
  
function onDatabaseStart() 
dbExec(PLAYER_DATABASE, "CREATE TABLE IF NOT EXISTS data_system ( serial TEXT, ip TEXT, account TEXT, country TEXT)") 
outputDebugString("onDatabaseStart; data_system SQL Table created!") 
validatePlayerData() 
end 
  
function validatePlayerData() 
   for i,v in pairs(getElementsByType("player")) do 
      local result = executeSQLQuery("SELECT * FROM data_system WHERE serial=?",getPlayerSerial(v)) 
      if #result == 0 then 
         dbExec( "INSERT INTO data_system ( serial, ip, account, country ) VALUES ( ?, ?, ?, ? )", 
            getPlayerSerial(v),getPlayerIP(v),getAccountName(getPlayerAccount(v)),getElementData(v,"country") or "Unknown") 
            sendResult("successfully executed data into data_system",root) 
      else 
         --nothing? 
         errorMessage("already found the data :O",root) 
      end 
   end 
end 

custom and built in debug methods show that table was created, and it says dbExec: no such table data_system at line 23

please help

[TAPL]

executeSQLQuery is only used for registry.db, while the rest of your code have another db file, you should use this:

dbQuery 

And also you didn't put the connection of the database of dbExec at line 23.

[Derpy]

Okay thank you very much!

Though since im still pretty new to sql, is there a way to prevent having 100 things on e.g same name?

i have added additional data in sql; name

so i would want that if nick isnt in sql database that it writes all data as usual(even if serial is already in sql database) ,otherwise it writes nothing in it

can you please show me an example how to do this?

[Addlibs]

You want to add a record only if there isn't one for that specific nick already?

query: SELECT * FROM data_system WHERE nick = ?

if #result == 0 then

execute: INSERT INTO data_system (nick, ??, ??, ??) VALUES(?, ?, ?, ?)

end

[Gallardo9944]

INSERT INTO yourtable (info,two,three) VALUES ("Exists",2,3) WHERE NOT EXISTS (SELECT info WHERE info="Exists") 

Will add a row and check if it exists further on. Untested tho.