Recent

Author Topic: [ANSWERED] SQLQuery:Field "ID" has an invalid field type (Memo) to base index on  (Read 2152 times)

totya

  • Hero Member
  • *****
  • Posts: 640
Hi!

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, sqlite3conn, sqldb, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, clipbrd;
  9.  
  10. type
  11.  
  12.   { TForm1 }
  13.  
  14.   TForm1 = class(TForm)
  15.     SearchButton: TButton;
  16.     SQLite3Connection1: TSQLite3Connection;
  17.     SQLQuery1: TSQLQuery;
  18.     SQLTransaction1: TSQLTransaction;
  19.     procedure SearchButtonClick(Sender: TObject);
  20.   private
  21.     { private declarations }
  22.   public
  23.     { public declarations }
  24.   end;
  25.  
  26. var
  27.   Form1: TForm1;
  28.  
  29. implementation
  30.  
  31. {$R *.lfm}
  32.  
  33. { TForm1 }
  34.  
  35. procedure TForm1.SearchButtonClick(Sender: TObject);
  36. begin
  37.   try
  38.     try
  39.        SQLite3Connection1.Open;
  40.        SQLTransaction1.Active:= true;
  41.  
  42.        with SQLQuery1 do
  43.        begin
  44.          Readonly:=true;
  45.  
  46.          SQL.Text:='SELECT * FROM '+'test_table';
  47.          Active:=true;
  48.        end;
  49.  
  50.        SQLQuery1.Locate('ID', 'HH2', []);
  51.      except
  52.        on E:Exception do Clipboard.AsText:=E.Message;
  53.      end;
  54.   finally
  55.     SQLQuery1.Close;
  56.     SQLite3Connection1.Close;
  57.   end;
  58. end;
  59.  
  60. end.
  61.  

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.
« Last Edit: December 30, 2015, 06:31:25 pm by totya »

Leledumbo

  • Hero Member
  • *****
  • Posts: 8274
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: SQLQuery: Field "ID" has an invalid field type (Memo) to base index on
« Reply #1 on: December 30, 2015, 06:04:21 pm »
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

  • Hero Member
  • *****
  • Posts: 640
Re: SQLQuery: Field "ID" has an invalid field type (Memo) to base index on
« Reply #2 on: December 30, 2015, 06:30:58 pm »
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.

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

Code: Pascal  [Select][+][-]
  1. procedure TForm1.SearchButtonClick(Sender: TObject);
  2. begin
  3.   try
  4.     try
  5.        SQLite3Connection1.Open;
  6.        SQLTransaction1.Active:= true;
  7.  
  8.        with SQLQuery1 do
  9.        begin
  10.          Readonly:=true;
  11.  
  12.          SQL.Text:='SELECT * FROM test_table WHERE ID LIKE "HH2" AND Data LIKE "HH2 DATA";';
  13.          Active:=true;
  14.  
  15.          ExecSQL;
  16.  
  17.          Last;
  18.          if RecNo>0 then ShowMessage('FOUND: '+IntToStr(RecNo));
  19.        end;
  20.      except
  21.        on E:Exception do Clipboard.AsText:=E.Message;
  22.      end;
  23.   finally
  24.     SQLQuery1.Close;
  25.     SQLite3Connection1.Close;
  26.   end;
  27. end;                            
  28.  
« Last Edit: December 30, 2015, 06:38:55 pm by totya »

mangakissa

  • Hero Member
  • *****
  • Posts: 1099
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;                             
« Last Edit: December 31, 2015, 10:05:36 am by mangakissa »
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

 

TinyPortal © 2005-2018