SOLVED - Values of AutoInc fields in SQLite3 lazarus / 16-Databases ---------------------------------------------------------------------- aslearner · 2011-10-20 18:54 Hi all, Does anyone know of a reliable method of returning the value of an AutoInc field in a recently posted record, please? I need this to maintain referential integrity in the Foreign Key fields of a detail table and its master when both tables are having data values entered and new records posted. The obvious method of: Code: [Select] MyTableDataSet.Locate('mykeyfield',mykeyvalue,locateoptions); avalue := MyTableDataset.FieldByName('myfield').AsInteger; does not work because the value returned is always 0 (zero) even when other tools (such as sqlitestudio) show that there are one or more different values in the table . Thanks in advance for any advice, ---------------------------------------------------------------------- Lacak2 · 2011-10-21 10:45 I am not sure, but SQLite support such (identity, serial) columns ? ---------------------------------------------------------------------- aslearner · 2011-10-21 17:51 Hi All, Thanks for the reply Lacak2 but I have now solved my problem. SQLite3 can return the information which I needed, but the Locate method does not work properly on Date/TDate fields (which were the type I need to handle in my main project). I had not found out about this factor when I posted before but now I have built a small application which shows this clearly, and also demonstrates a work-around. This programme is shown in full below:- Code: [Select]unit Main; {$mode objfpc}{$H+} interface uses   Classes, SysUtils, Sqlite3DS, db, FileUtil, Forms, Controls, Graphics,   Dialogs, DBGrids, StdCtrls, ExtCtrls, Variants; type   { TMainForm }   TMainForm = class(TForm)     LocateDateFieldsButton: TButton;     IDLabel: TLabel;     SearchLabeledEdit: TLabeledEdit;     LocateAlphaButton: TButton;     LocateNumberButton: TButton;     LocateTDateButton: TButton;     DBGrid1: TDBGrid;     ValuesDatasource: TDatasource;     ValuesDataset: TSqlite3Dataset;     procedure LocateDateFieldsButtonClick(Sender: TObject);     procedure FormCreate(Sender: TObject);     procedure LocateAlphaButtonClick(Sender: TObject);     procedure LocateTDateButtonClick(Sender: TObject);     procedure LocateNumberButtonClick(Sender: TObject);   private     { private declarations }     function ParseDate(AValue:string):TDate;     function PopulateRecord:Boolean;     procedure SeparateDateFields(const AValue: string; var d: Word; var m: Word; var y: Word);   public     { public declarations }   end; var   MainForm: TMainForm; implementation {$R *.lfm} { TMainForm } const   captstr = 'Value not found';   //NB - This constant will need to be changed to suit other systems   thisappdir = '/home/agsw/Projects/Lazarus/LocateTest/'; procedure TMainForm.LocateAlphaButtonClick(Sender: TObject); var   searchstr:string;   options:TLocateOptions;   OK : Boolean; begin   //Initialise the Boolean result variable, if this is not   //done the default seems to be True   OK := False;   searchstr := Trim(SearchLabeledEdit.Text);   //Initialise the search options, if this is not done   //the default seems to be [loCaseInsensitive]   options := [loCaseInsensitive,loPartialKey];   OK := ValuesDataset.Locate('Alpha',searchstr,options);   if OK then      begin      SearchLabeledEdit.Color := clWindow;      IDLabel.Caption := ValuesDataSet.FieldByName('Record').AsString;      end   else      SearchLabeledEdit.Color := clRed; end; procedure TMainForm.FormCreate(Sender: TObject); var   indx: Integer; begin   IDLabel.Caption := captstr;   //Set up the database if needed and possible   ValuesDataset.FileName := thisappdir + 'TestVals.db3';   ValuesDataset.TableName := 'Values';   if not ValuesDataset.TableExists then      begin      ValuesDataset.FieldDefs.Clear;      ValuesDataset.FieldDefs.Add('Record',ftAutoInc);      ValuesDataset.FieldDefs.Add('Alpha',ftFixedChar);      ValuesDataset.FieldDefs.Add('Number',ftInteger);      ValuesDataset.FieldDefs.Add('ADate',ftDate);      ValuesDataset.FieldDefs.Add('DateY',ftWord);      ValuesDataset.FieldDefs.Add('DateM',ftWord);      ValuesDataset.FieldDefs.Add('DateD',ftWord);      //At the next line (with a non-existent table and the TableName set either      //in code or via the ObjectInspector) an exception is thrown on my system      //'EDatabaseError with message ValuesDataset: SQLITE_ERROR      //near "Values":syntax error'      ValuesDataset.CreateTable;      end;   //Because of the above Exception the empty table was set up in SQLitestudio   //and this application then runs as intended    ValuesDataset.Open;    if ValuesDataSet.IsEmpty then       begin       //Try and make sure that new records end up on file       ValuesDataset.SaveOnClose:=True;       Valuesdataset.SaveOnRefetch:=True;        for indx := 1 to 10 do            begin            if not PopulateRecord then Exit;            //Post as we go along            ValuesDataset.Post;            end;       //Save the new records to file, has failed once even with PrimaryKey set       ValuesDataset.ApplyUpdates;       end;   ValuesDatasource.DataSet := ValuesDataset;   ValuesDataset.Active:=True;   DBGrid1.DataSource := ValuesDatasource; end; procedure TMainForm.LocateDateFieldsButtonClick(Sender: TObject); var   y,m,d:Word;   searchstr:string;   options:TLocateOptions;   OK:Boolean;   vararray: array[0..2] of Variant;   locvar:Variant; begin   //Initialise the Boolean result variable, if this is not   //done the default seems to be True   OK := False;   searchstr:= Trim(SearchLabeledEdit.Text);   //Keep the compiler happy by initialising values   y := 0;   m := 0;   d := 0;   //Initialise the search options, if this is not done   //the default seems to be [loCaseInsensitive]   options := [loCaseInsensitive,loPartialKey];   SeparateDateFields(searchstr,d,m,y);    if not ValuesDataSet.Active then       ValuesDataSet.Active := True;    try      vararray[0] := y;      vararray[1] := m;      vararray[2] := d;      locvar := VarArrayOf(vararray);      OK := ValuesDataSet.Locate('DateY;DateM;DateD',locvar,options);      if OK  then      begin      SearchLabeledEdit.Color := clWindow;      IDLabel.Caption := ValuesDataSet.FieldByName('Record').AsString;      end   else      begin      SearchLabeledEdit.Color := clRed;      IDLabel.Caption := captstr;      end;    finally      VarClear(vararray[0]);      VarClear(vararray[1]);      VarClear(locvar);    end; end; procedure TMainForm.LocateNumberButtonClick(Sender: TObject); var   searchint:Integer;   options:TLocateOptions;   OK : Boolean; begin   //Initialise the Boolean result variable, if this is not   //done the default seems to be True   OK := False;   searchint := StrToInt(Trim(SearchLabeledEdit.Text));   //Initialise the search options, if this is not done   //the default seems to be [loCaseInsensitive]   options := [loCaseInsensitive,loPartialKey];   OK := ValuesDataset.Locate('Number',searchint,options);   if OK then      begin      SearchLabeledEdit.Color := clWindow;      IDLabel.Caption := ValuesDataSet.FieldByName('Record').AsString;      end   else      begin      SearchLabeledEdit.Color := clRed;      IDLabel.Caption := captstr;      end; end; function TMainForm.ParseDate(AValue: string): TDate; var   y,m,d:Word;   thisval: TDate; begin   //Keep compiler happy by initialising values   y := 0;   m := 0;   d := 0;   SeparateDateFields(AValue, d, m, y);   thisval := EncodeDate(y,m,d);   //During debugging it was made certain that this value was in the table   result := thisval; end; function TMainForm.PopulateRecord: Boolean; var   OK:Boolean;   randint:Integer;   thisdate:TDate;   Y,M,D:Word; begin   //Initialise the Boolean result variable, if this is not   //done the default seems to be True   OK := False;   ValuesDataset.Append;   //I may be misusing the function but the output of Random   //seems rather repeatable from one programme run to the next   randint := Random(10);   ValuesDataset.FieldByName('Alpha').AsString:=Chr(65+randint);   randint := Random(10);   ValuesDataSet.FieldByName('Number').AsInteger:= randint;   randint := Random(10);   //It is known that for my system this will give dates in November 2011   thisdate :=  TDate(40852+randint);   ValuesDataSet.FieldByName('ADate').AsDateTime:=thisdate;   DecodeDate(thisdate,Y,M,D);   ValuesDataSet.FieldByName('DateY').AsInteger:=Y;   ValuesDataset.FieldByName('DateM').AsInteger:=M;   ValuesDataset.FieldByName('DateD').AsInteger:=D;   //If we get this far without dropping out with an Exception we   //should be in position to go further   OK := True;   Result := OK; end; procedure TMainForm.SeparateDateFields(const AValue: string; var d: Word; var m: Word; var y: Word); var   separator: string;   remstr: string;   itemstr: string;   lindx: Integer;   findx: Integer;   errcode: Integer; begin   //NB - This works for my locale and other machine settings,   //but it is definitely not a general method. YMMV   //First identify the separator character   if Pos('-', AValue) > 0 then      separator := '-'      else      separator := '/';   findx := 1;   lindx := Pos(separator, AValue);   //Extract day value   itemstr := Copy(AValue, findx, lindx-findx);   remstr := Copy(AValue, lindx+1, Length(AValue)-lindx);   Val(itemstr, d, errcode);   //Extract month value   findx := 1;   lindx := Pos(separator, remstr);   itemstr := Copy(remstr, findx, lindx-findx);   Val(itemstr, m, errcode);   itemstr := Copy(remstr, lindx+1, Length(remstr)-lindx);   //Extract year value   Val(itemstr, y, errcode);   //Correct value for two digit representation, if needed   if y < 1900 then      y := y + 2000; end; procedure TMainForm.LocateTDateButtonClick(Sender: TObject); var   searchstr: string;   searchdat:TDate;   options:TLocateOptions;   OK : Boolean; begin   //Initialise the Boolean result variable, if this is not   //done the default seems to be True   OK := False;   searchstr:= Trim(SearchLabeledEdit.Text);   searchdat := ParseDate(searchstr);   //Initialise the search options, if this is not done   //the default seems to be [loCaseInsensitive]   options := [loCaseInsensitive,loPartialKey];   OK := ValuesDataset.Locate('ADate',searchdat,options);   if OK then      begin      SearchLabeledEdit.Color := clWindow;      IDLabel.Caption := ValuesDataSet.FieldByName('Record').AsString;      end   else      begin      SearchLabeledEdit.Color := clRed;      IDLabel.Caption := captstr;      end; end; end. The methods of creating the table in code does not actually work, even on my machine, although it was copied directly from the Yolasite dataset-tutorial-1 and neither does the record population procedure. I had to cheat here and use Sqlitestudio again to do the work quickly and reliably. The database I produced is attached as TestVals.db3.zip and if this is expanded and put in the same directory as the code, then permissions allowing, it should run elsewhere. The principle of my work-around is to break the date up into separate Integer fields on storage and then locate these fields as a group on retrieval. Working out the syntax for Locate to use multiple fields in this way kept me occupied for a couple of hours yesterday Thank you, Andrew Smith ---------------------------------------------------------------------- BigChimp · 2011-10-21 19:02 PMFJI, I have no experience with SQlite, but if this is a bug, could you file it at the relevant bugtracker (e.g. the Lazarus/FreePascal bugtracker if you're using SQLDB for Sqlite access)? This will hopefully get fixed which will make it easier for everyone in future  Thanks, BigChimp ---------------------------------------------------------------------- aslearner · 2011-10-26 00:57 Hi all, Thanks BigChimp, I did raise an issue on the Mantis Bugtracker #20545. This received a very quick response saying that it was not using the Locate function correctly to supply a TDate to it instead of a TDateTime. This is difficult to understand as lines 301 to 303 of systemh.inc show the following definitions of TDateTime, TDate and TTime: 301 TDateTime = type Double; 302 TDate = type TDateTime; 303 TTime = type TDateTime; so they are identical and a function which works on one should therefore work on another of the triplet. (Or fail to work). For the bug report I wrote a small demonstration, but since the issue has now been closed I cannot now submit it via the Mantis system. To make it available I am now attaching a revised version to this post. (Locater1.zip) This has code which may be converted between calling Locate with a TDate or a TDateTime by commenting or uncommenting two lines. The failure to Locate will be shown in either form. Thanks for your efforts, Andrew Smith ---------------------------------------------------------------------- LuizAmérico · 2011-10-27 00:08 Your example does not compile (missing lpr file)