Using memory table or else

[Guest]

@Serge_G, aha! He got the error client side because the column changed name when he did not enforce it with an alias.

I usually let my fieldobjects be created dynamically. Well spotted!

 

[limelect 48]

@Serge_G read my solution it is so simple no need for software

[Serge_G 87]

22 hours ago, limelect said:

@Serge_G read my solution it is so simple no need for software

Which one ? I was only noticing that your query don't work because you forgot to put an alias name  in this SQL part
 

 FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,CAST(Category AS CHAR)  ,Description,FilesIndex'); 

by the way, your whole query

FDQuery4.SQL.add('Select SectionsId,MainDirectory,MyDateTime,ProjectrealName,Category,Description,FilesIndex');
  FDQuery4.SQL.add('from Files,Projects');
   FDQuery4.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%'''
    + 'and FilesIndex=SectionsId');

is an horid thing, did you ever ear about JOINtures, down a normalize SQL

SELECT SectionsId,MainDirectory,MyDateTime,ProjectrealName,CAST(Category AS VARCHAR(150)) Category ,Description,FilesIndex
FROM FILES JOIN PROJECT on FilesIndex=SectionsId

and Firedac parameters or macros ?

 FDQuery4.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%'''

this should be wrote  like this

 

FDQuery4.SQL.add('WHERE MainDirectory LIKE &amacro');
//usage, before openning query
FDQuery4.macrobyname('amacro').asRaw:=QuotedStr('%'+jvdirectoryEdit1.text+'%');
FDQuery4.open;

Or changing all the where clause like this

FDQuery4.SQL.add('&amacro');
//usage, before openning query
FDQuery4.macrobyname('amacro').asRaw:=Format('WHERE maindirectory LIKE %s',[QuotedStr('%'+jvdirectoryEdit1.text+'%')]);
FDQuery4.open;

 

Edited February 26, 2022 by Serge_G

[Guest]

What is preventing FireDAC users from using a parameter for the like clause?

[Lajos Juhász 293]

14 minutes ago, Dany Marmur said:

What is preventing FireDAC users from using a parameter for the like clause?

SQL servers. Parametar cannot be used with like or matches.

Edited February 25, 2022 by Lajos Juhász
At least on IBM databases (Informix and DB2)

[Serge_G 87]

7 minutes ago, Dany Marmur said:

What is preventing FireDAC users from using a parameter for the like clause?

Ah, I don't know why but every time I test the parameter for the LIKE clause it does not work (Firebird speaking here)

But, if Firebird speaking LIKE can be replaced by CONTAINING and this one, I think, never test myself, can be used with parameter 

[Guest]

Hmm... i was never a like kind of guy. More of a containing one 🙂

Could be the wildcards, %, but even though... like is a keyword.

I really get the problems with IN, but like's operand is a string.

Anyhoo - if i get time and the need i'll go figure.

Do not fret on this for my sake.

[Serge_G 87]

2 hours ago, Dany Marmur said:

Do not fret on this for my sake. 

Should just for my peace of mind, but tomorrow

 

No program writing, just an FdConnection and FDQuery and at design time I can confirm that a CONTAINING clause can work with a parameter 

Edited February 25, 2022 by Serge_G

[Guest]

2 hours ago, Serge_G said:

Ah, I don't know why but every time I test the parameter for the LIKE clause it does not work (Firebird speaking here)

But, if Firebird speaking LIKE can be replaced by CONTAINING and this one, I think, never test myself, can be used with parameter 

hi Serge, you can do it this way as any SQL Database target. Here using Firebird v4 or any other:

procedure TViewFormMain.Btn_Open_TablesClick(Sender: TObject);
begin
  dmMain.fdqTB_MASTER.SQL.Text := 'select * from TB_Master where PROJECT_NAME like :PROJECT_NAME'; // like :YOUR_PARAM_ON_SQL_DEFINITION
  //
  dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_NAME').ParamType := ptInput;
  dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_NAME').DataType  := ftString;
  dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_NAME').Value     := '%roj%';  //  %initBy  ... endedBy%   %containedBy% the "%" should be used on "Value params"
  //
  //dmMain.fdqTB_MASTER.SQL.Text := 'select * from TB_Master where PROJECT_DATE between :PROJECT_DATE_BEFORE and :PROJECT_DATE_LATER'; // valid to other types 
  //
  //dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_DATE_BEFORE').ParamType := ptInput;
  //dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_DATE_BEFORE').DataType  := ftDate;
  //dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_DATE_BEFORE').Value     := StrToDate('01/02/2022');
  //
  //dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_DATE_LATER').ParamType := ptInput;
  //dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_DATE_LATER').DataType  := ftDate;
  //dmMain.fdqTB_MASTER.Params.ParamByName('PROJECT_DATE_LATER').Value     := StrToDate('20/02/2022');
  //
  dmMain.fdqTB_MASTER.Open;

 

Edited February 25, 2022 by Guest

[Serge_G 87]

Ok, I don't know this way was possible. So I explore more over, and you can also write something like
 

select * from myable where mycol like '%'||:a||'%'

Anyway, I still prefer the macro way to adjust the where clause if necessary

[Guest]

Thought so 🙂