Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: Filter properties syntax for SQL Server (with FireDAC)


This question is answered. Helpful answers available: 1. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Sep 8, 2017 7:02 AM Last Post By: Dmitry Arefiev Threads: [ Previous | Next ]
Maxime Bonin

Posts: 61
Registered: 1/23/17
Filter properties syntax for SQL Server (with FireDAC)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 20, 2017 1:30 PM
This is a shot in the dark, but one of you guys could know what I'm looking for.

I'm currently working with softwares that were written to work with Advantage database. I'm adding Microsoft SQL Server compatibility using FireDAC (on Delphi XE8). So, most of my job is translating stuff that works with Advantage to FireDAC.

I'm with Datasets' filter now and I'm looking for an operator that would do the same thing as :


with my_Query do    // if it compiles in FireDAC it's a TFDDataset, if it compiles in Advantage it's an AdsDataset
   begin
      {$IFDEF FireDAC}
     Filter := 
      {$ELSE}
      Filter := 'my_first_Column $ (a,b,c)';  //  This is the Advantage's code
      {$ENDIF}
   end;


This filter makes the dataset show only the rows in which the field 'my_first_column' contains a 'a', a 'b' or a 'c'.

What would be the equivalent of the dollar '$' sign in FireDAC ?? I've tried 'Contains' but doesn't work

Edited by: Maxime Bonin on Apr 20, 2017 1:31 PM

Edited by: Maxime Bonin on Apr 20, 2017 1:31 PM

Edited by: Maxime Bonin on Apr 20, 2017 1:57 PM
Lajos Juhasz

Posts: 705
Registered: 3/14/14
Re: Filter properties syntax for SQL Server (with FireDAC) [Edit]
Helpful
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 20, 2017 1:58 PM   in response to: Maxime Bonin in response to: Maxime Bonin
Maxime Bonin wrote:

This is a shot in the dark, but one of you guys could know what I'm
looking for.

I'm currently working with softwares that were written to work with
Advantage database. I'm adding Microsoft SQL Server compatibility
using FireDAC (on Delphi XE8). So, most of my job is translating
stuff that works with Advantage to FireDAC.

I'm with Datasets' filter now and I'm looking for an operator that
would do the same thing as :

 
with myTFDQuery do
   begin
      {$IFDEF FireDAC}
     Filter := 
      {$ELSE}
      Filter := 'my_first_Column $ (a,b,c)';  //  This is the
Advantage's code       {$ENDIF}
   end;


This filter makes the dataset show only the rows in which the field
'my_first_column' contains a 'a', a 'b' or a 'c'.

What would be the equivalent of the dollar '$' sign in FireDAC ??
I've tried 'Contains' but doesn't work

Edited by: Maxime Bonin on Apr 20, 2017 1:31 PM

Edited by: Maxime Bonin on Apr 20, 2017 1:31 PM

This should work:

Filter := 'my_first_Column like ''%a%'' or  my_first_Column like
''%b%'' or my_first_Column like ''%c%''';


http://docwiki.embarcadero.com/Libraries/Berlin/en/Data.DB.TDataSet.Filter
Maxime Bonin

Posts: 61
Registered: 1/23/17
Re: Filter properties syntax for SQL Server (with FireDAC) [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 20, 2017 6:04 PM   in response to: Lajos Juhasz in response to: Lajos Juhasz
yes I had found this ... but was hoping for a single operator to replace the $


This should work:

Filter := 'my_first_Column like ''%a%'' or  my_first_Column like
''%b%'' or my_first_Column like ''%c%''';


http://docwiki.embarcadero.com/Libraries/Berlin/en/Data.DB.TDataSet.Filter

Edited by: Maxime Bonin on Apr 20, 2017 6:04 PM

Edited by: Maxime Bonin on Apr 20, 2017 6:49 PM
Maxime Bonin

Posts: 61
Registered: 1/23/17
Re: Filter properties syntax for SQL Server (with FireDAC)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 17, 2017 5:17 AM   in response to: Maxime Bonin in response to: Maxime Bonin
basic translations from Advantage db's syntax to TSQL's syntax is not a big deal afterall
Michael Sawyer ...

Posts: 4
Registered: 9/10/17
Re: Filter properties syntax for SQL Server (with FireDAC)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 25, 2017 5:43 AM   in response to: Maxime Bonin in response to: Maxime Bonin
Maxime Bonin wrote:
basic translations from Advantage db's syntax to TSQL's syntax is not a big deal afterall

The equivalent to MySQL would be "IN" instead of "$".

So it would be:
Filter := 'my_first_Column IN (a,b,c)';

Not sure if FireDAC is supporting "IN"-Operator.

Edited by: Michael Sawyer Yin on Aug 25, 2017 5:43 AM
Dmitry Arefiev

Posts: 1,375
Registered: 12/7/03
Re: Filter properties syntax for SQL Server (with FireDAC)  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 8, 2017 7:02 AM   in response to: Michael Sawyer ... in response to: Michael Sawyer ...
Filter := 'my_first_Column IN (a,b,c)';
Not sure if FireDAC is supporting "IN"-Operator.

Yes, it supports, but the correct syntax will be:
Filter := 'my_first_Column IN (''a'',''b'',''c'')';  


--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02