How to Apply Updates to TSQLite3DataSet in Lazarus

Question

I am trying to implement TSQLite3DataSet functionality to the Lazarus program. To give an MCVE here is an SQLite table of test.db database:

CREATE TABLE "ttest" (
  "ID"  INTEGER PRIMARY KEY AUTOINCREMENT,
  "Data"  INTEGER)

To implement the TSQLite3Dataset instance I did so:

  object Sqlite3Dataset1: TSqlite3Dataset
    FileName = 'C:\Users\User\Desktop\SQLIte\test.db'
    PrimaryKey = 'ID'
    TableName = 'ttest'
  end 

It works fine saving the updated and appended data. For example using this code:

procedure TForm1.Button3Click(Sender: TObject);
begin
  if Sqlite3Dataset1.State in [dsEdit, dsInsert] then
    Sqlite3Dataset1.Post;
  Sqlite3Dataset1.ApplyUpdates;
  Sqlite3Dataset1.RefetchData;
end; 

But if I assign the real empty table to the class instance:

CREATE TABLE "base" (
"ID"  INTEGER PRIMARY KEY AUTOINCREMENT,
"Code"  varchar NOT NULL,
"GCode"  varchar DEFAULT NULL,
"Fam"  varchar DEFAULT NULL,
"Name"  varchar DEFAULT NULL,
"Patr"  varchar DEFAULT NULL,
"Age"  smallint DEFAULT NULL,
"Gender"  smallint DEFAULT NULL,
"Invalid"  smallint DEFAULT NULL,
"AdmDate"  date DEFAULT NULL,
"Departament"  smallint DEFAULT NULL,
"DsAdm"  varchar DEFAULT NULL,
"DsClin"  varchar DEFAULT NULL,
"Surgery"  varchar DEFAULT NULL,
"ConditAdmission"  smallint DEFAULT NULL,
"DiagnRemarks"  varchar DEFAULT NULL,
"DiagnDuration"  float DEFAULT NULL,
"TrMethod"  smallint DEFAULT NULL,
"TreatDetails"  varchar DEFAULT NULL,
"DiseaseDuration"  float DEFAULT NULL,
"Acute"  smallint DEFAULT NULL,
"Course"  varchar DEFAULT NULL,
"Outcomes"  smallint DEFAULT NULL,
"GBA"  smallint DEFAULT NULL,
"GUA"  smallint DEFAULT NULL,
"Coag"  smallint DEFAULT NULL,
"PTI"  smallint DEFAULT NULL,
"Group"  smallint DEFAULT NULL,
"Stool"  smallint DEFAULT NULL,
"BCA"  smallint DEFAULT NULL,
"GP"  smallint DEFAULT NULL,
"Alb"  smallint DEFAULT NULL,
"Glob"  smallint DEFAULT NULL,
"Bil"  smallint DEFAULT NULL,
"BilCon"  smallint DEFAULT NULL,
"NonConBil"  smallint DEFAULT NULL,
"Chol"  smallint DEFAULT NULL,
"AST"  smallint DEFAULT NULL,
"ALT"  smallint DEFAULT NULL,
"AmilBl"  smallint DEFAULT NULL,
"AmilUr"  smallint DEFAULT NULL,
"DUr"  smallint DEFAULT NULL,
"Carbamide"  smallint DEFAULT NULL,
"Crea"  smallint DEFAULT NULL,
"Tim"  smallint DEFAULT NULL,
"Glu"  smallint DEFAULT NULL,
"RW"  smallint DEFAULT NULL,
"HBS"  smallint DEFAULT NULL,
"X-Ray"  smallint DEFAULT NULL,
"FGDS"  smallint DEFAULT NULL,
"ECG"  smallint DEFAULT NULL,
"Use"  smallint DEFAULT NULL,
"PeptUlcerGastr"  smallint DEFAULT NULL,
"PeptUlcerDuod"  smallint DEFAULT NULL,
"AcUlcer"  smallint DEFAULT NULL,
"Loc1"  varchar DEFAULT NULL,
"Loc2"  varchar DEFAULT NULL,
"Compl1"  varchar DEFAULT NULL,
"Compl2"  varchar DEFAULT NULL,
"Operation1"  varchar DEFAULT NULL,
"Operation2"  varchar DEFAULT NULL,
"ManipName"  varchar DEFAULT NULL,
"Manipulation"  varchar DEFAULT NULL,
"Coexist1"  varchar DEFAULT NULL,
"Coexist2"  varchar DEFAULT NULL,
"Coexist3"  varchar DEFAULT NULL,
"Coname"  varchar DEFAULT NULL,
"Coexisting"  varchar DEFAULT NULL,
"Cholecyst"  smallint DEFAULT NULL,
"Pancreatitis"  smallint DEFAULT NULL,
"GIB"  smallint DEFAULT NULL,
"Bulbitis"  smallint DEFAULT NULL,
"Hepatitis"  smallint DEFAULT NULL,
"Scar"  smallint DEFAULT NULL,
"Gastritis"  smallint DEFAULT NULL,
"Additional"  smallint DEFAULT NULL,
"NameofAddit"  varchar DEFAULT NULL,
"OtherName"  smallint DEFAULT NULL,
"Other"  varchar DEFAULT NULL,
"SocState"  smallint DEFAULT NULL,
"Occupation"  varchar DEFAULT NULL,
"Hazards"  smallint DEFAULT NULL,
"HazardsOther"  varchar DEFAULT NULL,
"SatisfGen"  smallint DEFAULT NULL,
"SatisfMoral"  smallint DEFAULT NULL,
"SatisfMater"  smallint DEFAULT NULL,
"UseNutrit"  float DEFAULT NULL,
"UseClothes"  float DEFAULT NULL,
"UseCulture"  float DEFAULT NULL,
"UseTreat"  float DEFAULT NULL,
"UseSport"  float DEFAULT NULL,
"FamCond"  smallint DEFAULT NULL,
"FamRelat"  smallint DEFAULT NULL,
"FamScand"  smallint DEFAULT NULL,
"FamScandReas"  smallint DEFAULT NULL,
"Stress"  smallint DEFAULT NULL,
"RelativesCount"  smallint DEFAULT NULL,
"RelativesPersons"  varchar DEFAULT NULL,
"Drugs"  smallint DEFAULT NULL,
"DrugsDetails"  varchar DEFAULT NULL,
"DietSubj"  smallint DEFAULT NULL,
"DietPlace"  smallint DEFAULT NULL,
"DietPlaceOther"  varchar DEFAULT NULL,
"DietDry"  smallint DEFAULT NULL,
"DietRegular"  smallint DEFAULT NULL,
"DietFreq"  float DEFAULT NULL,
"DietChew"  smallint DEFAULT NULL,
"DietObj"  smallint DEFAULT NULL,
"AlcoholFam"  smallint DEFAULT NULL,
"AlcoholSelf"  smallint DEFAULT NULL,
"AlcoholObj"  smallint DEFAULT NULL,
"AlcoholBeginAge"  float DEFAULT NULL,
"AlcoholBeforeYears"  float DEFAULT NULL,
"SmokeBeginAge"  smallint DEFAULT NULL,
"SmokeTotalYears"  smallint DEFAULT NULL,
"Smokes"  smallint DEFAULT NULL,
"SmokesCigarettes"  smallint DEFAULT NULL,
"ProphylPurp"  smallint DEFAULT NULL,
"Recommend"  smallint DEFAULT NULL,
"AcuteFreq"  smallint DEFAULT NULL,
"ReasontoCome"  varchar DEFAULT NULL,
"AddonA1"  varchar DEFAULT NULL,
"AddonS1"  smallint DEFAULT NULL,
"ExaminationOtherCount"  smallint DEFAULT NULL,
"ExaminationOtherName"  varchar DEFAULT NULL,
"Complications"  varchar DEFAULT NULL,
"AddonA2"  varchar DEFAULT NULL,
"AddonA3"  varchar DEFAULT NULL,
"Surgery1"  varchar DEFAULT NULL,
"Surgery2"  varchar DEFAULT NULL,
"Fio"  varchar DEFAULT NULL,
"Perf"  smallint DEFAULT NULL,
"Penetr"  smallint DEFAULT NULL,
"Malign"  smallint DEFAULT NULL,
"SmokesSigarettesNumber"  float DEFAULT NULL,
"Stenosis"  tinyint DEFAULT NULL,
"FamScandReas0"  tinyint DEFAULT NULL,
"FamScandReas1"  tinyint DEFAULT NULL,
"FamScandReas2"  tinyint DEFAULT NULL,
"FamScandReas3"  tinyint DEFAULT NULL,
"FamScandReas4"  tinyint DEFAULT NULL,
"FamScandReas5"  tinyint DEFAULT NULL,
"FamScandReas6"  tinyint DEFAULT NULL,
"Drugs0"  tinyint DEFAULT NULL,
"Drugs1"  tinyint DEFAULT NULL,
"Drugs2"  tinyint DEFAULT NULL,
"Drugs3"  tinyint DEFAULT NULL,
"Drugs4"  tinyint DEFAULT NULL,
"PeptUlcerUnknown"  smallint DEFAULT NULL)

Nothing is done after I call ApplyUpdates. The changes are not saved to the database – neither updates nor appends.

I am stuck with the problem. Does anybody know a way to deal with it?

Answer 1

Actually the answer was as follows:

The table was created in the way when the field names were spelled in quotation marks "". This disguised the fact that there were fields Group, Glob (reserved words) and X-Ray (contains minus) and these words were not highlit in the query editor. When TSQLiteDataSet.ApplyUpdate builds the SQL string it skips (does not add) quotation marks from the fields' definitions.

I replaced the names with Grp, Glb and XRay and the issue was over.