Recent

Author Topic: SQLite REPLACE INTO error  (Read 532 times)

Middlecope

  • Jr. Member
  • **
  • Posts: 70
SQLite REPLACE INTO error
« on: August 27, 2020, 04:24:36 pm »
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][+][-]
  1.  WITH SQLQuery1 DO BEGIN
  2.     Active:= FALSE;
  3.     SQL.Clear;
  4.     SQL.Add('REPLACE INTO test (naam)');
  5.     SQL.Add('VALUES (''Nog een erbij'')');
  6.     SQL.Add('WHERE auto_inc = 3');
  7.     ExecSQL;
  8. END;    
I get an error message: 
Quote
SQLite3Connection1 : 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

  • Hero Member
  • *****
  • Posts: 4329
Re: SQLite REPLACE INTO error
« Reply #1 on: August 27, 2020, 04:48:21 pm »
I get an error message: 
Quote
SQLite3Connection1 : near "WHERE": syntax error.
I tried the text field between " " (double quotes) and between single quotes
The same error
Use UPDATE ??

Code: SQL  [Select][+][-]
  1. UPDATE test
  2. SET NAAM = 'Nog een erbij'
  3. 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][+][-]
  1. REPLACE INTO test (auto_inc, naam)
  2. VALUES (3, 'Nog een erbij')
« Last Edit: August 27, 2020, 04:54:30 pm by rvk »

Thaddy

  • Hero Member
  • *****
  • Posts: 10516
Re: SQLite REPLACE INTO error
« Reply #2 on: August 27, 2020, 05:02:02 pm »
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.
« Last Edit: August 27, 2020, 05:11:45 pm by Thaddy »

Middlecope

  • Jr. Member
  • **
  • Posts: 70
Re: SQLite REPLACE INTO error
« Reply #3 on: August 27, 2020, 08:29:56 pm »
OK It works when "WHERE is OMITTED
Code: Pascal  [Select][+][-]
  1. SQL.Add('INSERT OR REPLACE INTO test (auto_inc,naam)');
  2.     SQL.Add('VALUES (3,,''Nog een erbij JA'')');
  3.     ExecSQL;                                  
This works. The first field "auto_inc" is NN AI U
Now I try:
Code: Pascal  [Select][+][-]
  1. SQL.Add('INSERT OR REPLACE INTO test (auto_inc,naam)');
  2.     SQL.Add('VALUES (0,''En nog een poging 4'')');
  3.     ExecSQL;                                  
I expected that a record is INSERTED . No a record with auto_inc value 0 is inserted.

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SQLite REPLACE INTO error
« Reply #4 on: August 27, 2020, 08:43:44 pm »
I 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

  • Hero Member
  • *****
  • Posts: 10516
Re: SQLite REPLACE INTO error
« Reply #5 on: August 27, 2020, 09:00:29 pm »
Correct. And a better description of what a mean. Thx.

Middlecope

  • Jr. Member
  • **
  • Posts: 70
Re: SQLite REPLACE INTO error
« Reply #6 on: August 27, 2020, 09:11:24 pm »
Quote
Auto 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

  • Hero Member
  • *****
  • Posts: 10516
Re: SQLite REPLACE INTO error
« Reply #7 on: August 27, 2020, 09:18:25 pm »
auto_inc can not be NULL except if there are no records at all, but it can be 0.

rvk

  • Hero Member
  • *****
  • Posts: 4329
Re: SQLite REPLACE INTO error
« Reply #8 on: August 27, 2020, 09:20:26 pm »
Quote
Auto 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

  • Jr. Member
  • **
  • Posts: 70
Re: SQLite REPLACE INTO error
« Reply #9 on: August 28, 2020, 03:28:08 pm »
When I tried on a bigger record I get error:
Quote
SQLite3Connection1 : near "?": syntax error
My code is:
Code: Pascal  [Select][+][-]
  1. Query1.SQL.Active:= FALSE;
  2. Query1.SQL.Clear;
  3. Query1.SQL.Add('INSERT OR REPLACE INTO afspraken(auto_inc,datum,tijd,wie,onderwerp,wie_email,waarschuwing)');
  4. Query1.SQL.Add('VALUES (1,2020-08-15,11:10,"mijzelf","programma","teunis@gmail",0)');
  5. Query1.ExecSQL;
Of course there is no question mark in the SQL
The table CREATE statement, copied via DB  is:
Quote
CREATE 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

  • Hero Member
  • *****
  • Posts: 4329
Re: SQLite REPLACE INTO error
« Reply #10 on: August 28, 2020, 03:32:04 pm »
My code is:
Code: Pascal  [Select][+][-]
  1. 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

  • Jr. Member
  • **
  • Posts: 70
Re: SQLite REPLACE INTO error
« Reply #11 on: August 28, 2020, 03:46:01 pm »
Thanks RVK
After quoting it works fine.
Many thanks for all helping people

 

TinyPortal © 2005-2018