Recent

Author Topic: [SOLVED] SQLite Can't UPDATE  (Read 851 times)

pcurtis

  • Sr. Member
  • ****
  • Posts: 351
[SOLVED] SQLite Can't UPDATE
« on: July 29, 2020, 01:12:16 pm »
Why doesn't this give any errors? (Can't find Param1 ...)

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.   sSQL : string;
  4. begin
  5.   sSQL := ' UPDATE tnTEST' +
  6.           ' SET' +
  7.           ' fnTEST = :Param1' +
  8.           ' WHERE fnIDX = :Param2';
  9.  
  10.   SQLQuery1.DataBase := SQLConnector1;
  11.   SQLQuery1.SQL.Clear;
  12.   SQLQuery1.SQL.Add(sSQL);
  13.  
  14.   SQLQuery1.ExecSQL; <* Should throw an error here
  15. end;  
  16.  
« Last Edit: July 29, 2020, 06:44:10 pm by pcurtis »
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

six1

  • Jr. Member
  • **
  • Posts: 91
Re: SQLite UPDATE
« Reply #1 on: July 29, 2020, 01:28:56 pm »
You've to define the Parameters i.e.:
SQLQuery1.SQL.Parambyname('Param1').asstring:= .....
SQLQuery1.SQL.Parambyname('Param2').asstring:= .....

PascalDragon

  • Hero Member
  • *****
  • Posts: 2424
  • Compiler Developer
Re: SQLite UPDATE
« Reply #2 on: July 29, 2020, 01:31:53 pm »
You've to define the Parameters i.e.:
SQLQuery1.SQL.Parambyname('Param1').asstring:= .....
SQLQuery1.SQL.Parambyname('Param2').asstring:= .....

pcurtis seems to know what the problem is. Their question was why there is no error if no parameters are set.

pcurtis

  • Sr. Member
  • ****
  • Posts: 351
Re: SQLite UPDATE
« Reply #3 on: July 29, 2020, 01:39:51 pm »
Yes "Why there is NO error"

Or the main problem is when I specify the correct parameters

SQLQuery1.SQL.Parambyname('Param1').asinteger:= 100
SQLQuery1.SQL.Parambyname('Param2').asinteger:= 1

 the table isn't updated.

(And yes there is a row where fnIDX = 1)
« Last Edit: July 29, 2020, 01:45:41 pm by pcurtis »
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

shadow

  • New member
  • *
  • Posts: 9
Re: SQLite UPDATE
« Reply #4 on: July 29, 2020, 01:45:54 pm »
I guess Param1 and Param2 are being interpreted as NULL if you don't assign an explicit value.

pcurtis

  • Sr. Member
  • ****
  • Posts: 351
Re: SQLite UPDATE
« Reply #5 on: July 29, 2020, 01:48:39 pm »
See above comment

(And ParamCheck := true)
« Last Edit: July 29, 2020, 01:50:24 pm by pcurtis »
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

korba812

  • Full Member
  • ***
  • Posts: 142
Re: SQLite Can't UPDATE
« Reply #6 on: July 29, 2020, 03:06:12 pm »
ParamCheck property does not mean that the parameters will be checked for null value.
https://www.freepascal.org/docs-html/current/fcl/sqldb/tsqlquery.paramcheck.html
Besides, null value is also a valid value for the parameter.
You probably aren't committing the transaction after execution and therefore the data isn't updated in the table.

pcurtis

  • Sr. Member
  • ****
  • Posts: 351
Re: SQLite Can't UPDATE
« Reply #7 on: July 29, 2020, 06:43:26 pm »
Problem solved - the database was corrupt, and needed repairing.
But I still think it should throw an error when a parameter is missing (Take ZEOS for example)
« Last Edit: July 29, 2020, 06:46:54 pm by pcurtis »
Windows 10 / Linux Mint 20
Laz 2.10.0
FPC 3.2.0

 

TinyPortal © 2005-2018