Jump to content
limelect

Using memory table or else

Recommended Posts

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!

 

Share this post


Link to post
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 by Serge_G

Share this post


Link to post
Guest

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

Share this post


Link to post
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 by Lajos Juhász
At least on IBM databases (Informix and DB2)

Share this post


Link to post
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 

Share this post


Link to post
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.

Share this post


Link to post
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 by Serge_G

Share this post


Link to post
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 by Guest

Share this post


Link to post

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

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×