[fpc-pascal] Create dynamic SQL according to available params

[Santiago A.]

El 07/04/2016 a las 14:00, Luiz Americo Pereira Camara escribió:
> I enconter the following pattern frequently (simplified):
>
> SQL:
> Select * From Customers Where FieldX = 1
>
> Later i need a similar query that uses a different filter like
>
> Select * From Customers Where FieldX = 1 and FieldY = :paramy

I remember a component of RxLib named TRxQuery. It had a published
property "macros" of type TParams, so you could write this sql

Select * From Customers Where FieldX = 1 and (%extrafilter).
After setting de sql property, in design time was added an item to
macros with name 'extrafilter' of type string and initial value '(0=0)',

In run time you could do:

RxQuery1.Macros.ParamByName('extrafilter'):='(0=0)';
or
RxQuery1.Macros.ParamByName('extrafilter'):='FieldY=:paramy';
(I think there was also a RxQuery1.MacroByName to shorten
RxQuery1.Macros.ParamByName)

When you prepared the sql, macros where expanded in the sql.

The was also a property named RealSQL of TString, it was the sql
statement after macro substitution. (I'm not sure if RealSQL was public
or I modified the library to make it public)

When you changed a macro it was like changing the sql. You needed to
prepare again the query.

It is not that different from saving the original sql string and
replacing certain parts, (in fact, I have done things like that). But it
was handy.

-- 
Saludos

Santiago A.
svaa at ciberpiula.net