SQLite REPLACE INTO error lazarus / 16-Databases ---------------------------------------------------------------------- Middlecope · 2020-08-27 14:24 New to SQLite  (I have some fluency in MySQL) On  a form I have SQLite3Connection, SQLTransaction and SQLQuery The table named test contains 2 fields auto_inc INTEGER AI and naam TEXT In the Query the SELECT statement works fine. Now the REPLACE statement: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  WITH SQLQuery1 DO BEGIN    Active:= FALSE;    SQL.Clear;    SQL.Add('REPLACE INTO test (naam)');    SQL.Add('VALUES (''Nog een erbij'')');    SQL.Add('WHERE auto_inc = 3');    ExecSQL;END;     I get an error message:  QuoteSQLite3Connection1 : near "WHERE": syntax error.I tried the text field between " " (double quotes) and between single quotes The same error Can someone help me with this little problem? Thanks, Teunis OpenSuse, Lazarus 2.08 ---------------------------------------------------------------------- rvk · 2020-08-27 14:48 Quote from: Middlecope on August 27, 2020, 04:24:36 pmI get an error message:  QuoteSQLite3Connection1 : near "WHERE": syntax error.I tried the text field between " " (double quotes) and between single quotes The same error Use UPDATE ?? Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i UPDATE testSET NAAM = 'Nog een erbij'WHERE auto_inc = 3 REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. So you could also do Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i REPLACE INTO test (auto_inc, naam)VALUES (3, 'Nog een erbij') ---------------------------------------------------------------------- Thaddy · 2020-08-27 15:02 Note that if you use auto_inc, the value is never guaranteed. E.g. if Item 3 is already removed, accessing auto_inc 3  will fail. (auto fields will not be renumbered). So be careful with that. Auto_inc works like a sparse map, not every value is present with exact sequence.(except if there are never any deletes) This goes for any dialect of SQL that I am aware of: it is only guaranteed that the value is higher than the previous one, but it can differ significantly unless the database is rebuilt or vacuumed. In that case there is again a perfect sequence. ---------------------------------------------------------------------- Middlecope · 2020-08-27 18:29 OK It works when "WHERE is OMITTED Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQL.Add('INSERT OR REPLACE INTO test (auto_inc,naam)');    SQL.Add('VALUES (3,,''Nog een erbij JA'')');    ExecSQL;                                This works. The first field "auto_inc" is NN AI U Now I try: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQL.Add('INSERT OR REPLACE INTO test (auto_inc,naam)');    SQL.Add('VALUES (0,''En nog een poging 4'')');    ExecSQL;                                I expected that a record is INSERTED . No a record with auto_inc value 0 is inserted. ---------------------------------------------------------------------- rvk · 2020-08-27 18:43 Quote from: Middlecope on August 27, 2020, 08:29:56 pmI expected that a record is INSERTED . No a record with auto_inc value 0 is inserted. Auto increment only works if you don't supply the auto_inc field or the auto_inc field supplied is null. ---------------------------------------------------------------------- Thaddy · 2020-08-27 19:00 Correct. And a better description of what a mean. Thx. ---------------------------------------------------------------------- Middlecope · 2020-08-27 19:11 QuoteAuto increment only works if you don't supply the auto_inc field or the auto_inc field supplied is null.In this case the AI field "auto_inc" is 0 or should it be "null"? ---------------------------------------------------------------------- Thaddy · 2020-08-27 19:18 auto_inc can not be NULL except if there are no records at all, but it can be 0. ---------------------------------------------------------------------- rvk · 2020-08-27 19:20 Quote from: Middlecope on August 27, 2020, 09:11:24 pmQuoteAuto increment only works if you don't supply the auto_inc field or the auto_inc field supplied is null.In this case the AI field "auto_inc" is 0 or should it be "null"? If you want the value to be automatically increased then you should omit the field completely or provide null as value (note: not a string). ---------------------------------------------------------------------- Middlecope · 2020-08-28 13:28 When I tried on a bigger record I get error: QuoteSQLite3Connection1 : near "?": syntax errorMy code is: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Query1.SQL.Active:= FALSE;Query1.SQL.Clear;Query1.SQL.Add('INSERT OR REPLACE INTO afspraken(auto_inc,datum,tijd,wie,onderwerp,wie_email,waa rschuwing)');Query1.SQL.Add('VALUES (1,2020-08- 15,11:10,"mijzelf","programma","teunis@gmail",0)');Query1.ExecSQL;Of course there is no question mark in the SQL The table CREATE statement, copied via DB  is: QuoteCREATE TABLE "afspraken" (    "auto_inc"   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,    "datum"   date NOT NULL,    "tijd"   time NOT NULL,    "wie"   TEXT NOT NULL,    "onderwerp"   TEXT NOT NULL,    "wie_email"   TEXT,    "waarschuwing"   INTEGER DEFAULT 0) Thanks for your attention. ---------------------------------------------------------------------- rvk · 2020-08-28 13:32 Quote from: Middlecope on August 28, 2020, 03:28:08 pmMy code is: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Query1.SQL.Add('VALUES (1,2020-08-15,11:10,"mijzelf","programma","teunis@gmail",0)');You should NEVER EVER use hardcoded values like that with SQL. Look up SQL injection on internet. Everyone can hack your complete database when they can provide such fields and you just paste those strings in there. ALWAYS use parameters for that. Anyway... the problem you probably are having is that you didn't enclose the date and time fields in quotes. ---------------------------------------------------------------------- Middlecope · 2020-08-28 13:46 Thanks RVK After quoting it works fine. Many thanks for all helping people