Recent

Author Topic: Lazarus 2.0.0.RC3 - Sequence does not work properly for SQLite  (Read 1519 times)

wanderus

  • New Member
  • *
  • Posts: 10
Lazarus 2.0.0.RC3 - Sequence does not work properly for SQLite
« on: February 01, 2019, 11:10:32 am »
Method TSQLConnection.GetNextValueSQL implemented is follows:
(sqldb.pp)
Code: Pascal  [Select][+][-]
  1. function TSQLConnection.GetNextValueSQL(const SequenceName: string; IncrementBy: Integer): string;
  2. begin
  3.   Result := 'SELECT NEXT VALUE FOR ' + SequenceName;
  4. end;
  5.  
This query is incorrect for SQLite. But method not overriden for TSQLite3Connection.
This causes an error "near "FOR": syntax error" when trying to use sequences in TSQLQuery.
The following query can be used for SQLite:
Code: Pascal  [Select][+][-]
  1. function TSQLite3Connection.GetNextValueSQL(const SequenceName: string; IncrementBy: Integer): string;
  2. begin
  3.   Result := 'SELECT seq+1 FROM sqlite_sequence WHERE name=''' + SequenceName+'''';
  4. end;
  5.  
« Last Edit: February 01, 2019, 11:13:09 am by wanderus »

madref

  • Hero Member
  • *****
  • Posts: 814
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Lazarus 2.0.0.RC3 - Sequence does not work properly for SQLite
« Reply #1 on: February 01, 2019, 11:45:49 pm »
The error starts with the 3 quotes after each other.
Code: Pascal  [Select][+][-]
  1. function TSQLite3Connection.GetNextValueSQL(const SequenceName: string; IncrementBy: Integer): string;
  2. begin
  3.   Result := 'SELECT seq+1 FROM sqlite_sequence WHERE name=''' + SequenceName+'''';
  4. end;
  5.  
Instead use chr (34) double quote or chr(39) single quote
Code: Pascal  [Select][+][-]
  1. function SingleQuotedStr (St: String) : String;
  2. begin
  3.   Result := Chr(39)+ St + Chr(39);
  4. end;    // SingleQuotedStr
Code: Pascal  [Select][+][-]
  1. function DoubleQuotedStr (St: String) : String;
  2. begin
  3.   Result := Chr(34)+ St + Chr(34);
  4. end;    // DoubleQuotedStr


So your result is then
Code: Pascal  [Select][+][-]
  1. Result := 'SELECT seq+1 FROM sqlite_sequence WHERE name=' + DoubleQuoteStr(SequenceName);
« Last Edit: February 01, 2019, 11:49:39 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.6 / FPC 3.0.4
Lazarus Trunc / FPC Trunc
Mac OS X Mojave

wanderus

  • New Member
  • *
  • Posts: 10
Re: Lazarus 2.0.0.RC3 - Sequence does not work properly for SQLite
« Reply #2 on: February 04, 2019, 07:57:59 am »
The error starts with the 3 quotes after each other.
You're wrong. I showed a workable code. String values must be framed in single quotes in SQL. A single quote can be entered into pascal  string by double single quote.
Thus, the following expressions are equivalent:

Code: Pascal  [Select][+][-]
  1. Result := 'SELECT seq+1 FROM sqlite_sequence WHERE name=' + SingleQuoteStr(SequenceName);
  2.  
and

Code: Pascal  [Select][+][-]
  1.  Result := 'SELECT seq+1 FROM sqlite_sequence WHERE name=''' + SequenceName+'''';
  2.  

 

TinyPortal © 2005-2018