[ANSWERED] SQLQuery:Field "ID" has an invalid field type (Memo) to
base index on
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-12-30 15:44

Hi!

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils,
sqlite3conn, sqldb, FileUtil, Forms, Controls, Graphics, Dialogs,
StdCtrls, clipbrd; type   { TForm1 }   TForm1 = class(TForm)   
SearchButton: TButton;    SQLite3Connection1: TSQLite3Connection;   
SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;   
procedure SearchButtonClick(Sender: TObject);  private    { private
declarations }  public    { public declarations }  end; var  Form1:
TForm1; implementation {$R *.lfm} { TForm1 } procedure
TForm1.SearchButtonClick(Sender: TObject);begin  try    try     
 SQLite3Connection1.Open;       SQLTransaction1.Active:= true;      
 with SQLQuery1 do       begin         Readonly:=true;        
 SQL.Text:='SELECT * FROM '+'test_table';         Active:=true;     
 end;        SQLQuery1.Locate('ID', 'HH2', []);     except       on
E:Exception do Clipboard.AsText:=E.Message;     end;  finally   
SQLQuery1.Close;    SQLite3Connection1.Close;  end;end; end. 
Hi!

Okay, I know, not is the best choice the TEXT type field for primary
key, but the original database isn't my creation.
The problem: locate doesn't work.
Error message: Field "ID" has an invalid field type (Memo) to base
index on

Thanks!

ps.: Project/Publish project is buggy, because I modify include filter
to:
*.(db|pas|pp|inc|lpr|lfm|lrs|lpi|lpk|xml|sh)
but *.db file is not copied.

----------------------------------------------------------------------
Leledumbo · 2015-12-30 17:04

Unfortunately, no compare function is defined for ftMemo, hence you
can't use it for Locate. The list of supported field types is in the
body of method TCustomBufDataset.ProcessFieldsToCompareStruct .

----------------------------------------------------------------------
totya · 2015-12-30 17:30

Quote from: Leledumbo on December 30, 2015, 06:04:21 pmUnfortunately,
no compare function is defined for ftMemo, hence you can't use it for
Locate. The list of supported field types is in the body of method
TCustomBufDataset.ProcessFieldsToCompareStruct.

LOL. Thanks for this information! Well, then I use this "ugly" method:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.SearchButtonClick(Sender: TObject);begin  try    try     
 SQLite3Connection1.Open;       SQLTransaction1.Active:= true;      
 with SQLQuery1 do       begin         Readonly:=true;        
 SQL.Text:='SELECT * FROM test_table WHERE ID LIKE "HH2" AND Data LIKE
"HH2 DATA";';         Active:=true;          ExecSQL;          Last; 
      if RecNo>0 then ShowMessage('FOUND: '+IntToStr(RecNo));     
 end;     except       on E:Exception do Clipboard.AsText:=E.Message; 
   end;  finally    SQLQuery1.Close;    SQLite3Connection1.Close; 
end;end;

----------------------------------------------------------------------
mangakissa · 2015-12-31 09:03

This is similar to Tdataset.locate. It's basic, but you can extend it
with parameters and array's
Code: [Select]function MyLocate(var ID : string) : boolean;
begin
  try
    try
       SQLite3Connection1.Open;
       SQLTransaction1.Active:= true;

       with SQLQuery1 do
       begin
         Readonly:=true;
         SQL.Text:='SELECT count(ID)  AS countrows, ID FROM test_table
WHERE ID LIKE "HH2" AND Data LIKE "HH2 DATA";';
         Active:=true;
         result :=  countrows > 0;
         ID := SQLQuery1.fields[1].AsString;
       end;
     except
       on E:Exception do result := false
     end;
  finally
    SQLTransaction1.committed;
    SQLite3Connection1.Close;
  end;
end;