Create dynamic SQL according to available params

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Create dynamic SQL according to available params

Luiz Americo Pereira Camara-3
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

Is there any code that given a SQL Template would generate the second filter when paramy is available and keep blank when not available?

Like
Select * From Customers Where FieldX = 1 ${paramtemplate paramy}

The param not necessarily have to be in TParams, in my case the param is both in a JSON object and in TParams

If there's not in pascal, someone knows such templating in another languages? The hard part is getting a flexible and functional syntax

Luiz

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Michael Van Canneyt


On Thu, 7 Apr 2016, Luiz Americo Pereira Camara wrote:

> 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
>
> Is there any code that given a SQL Template would generate the second
> filter when paramy is available and keep blank when not available?
>
> Like
> Select * From Customers Where FieldX = 1 ${paramtemplate paramy}
>
> The param not necessarily have to be in TParams, in my case the param is
> both in a JSON object and in TParams

I have not found such code. I rolled my own.

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Luiz Americo Pereira Camara-3


2016-04-07 9:03 GMT-03:00 Michael Van Canneyt <[hidden email]>:


On Thu, 7 Apr 2016, Luiz Americo Pereira Camara wrote:


Is there any code that given a SQL Template would generate the second
filter when paramy is available and keep blank when not available?



I have not found such code. I rolled my own.


Any chance sharing the code or at least the syntax?

Thanks in advance

Luiz

 

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Michael Van Canneyt


On Thu, 7 Apr 2016, Luiz Americo Pereira Camara wrote:

> 2016-04-07 9:03 GMT-03:00 Michael Van Canneyt <[hidden email]>:
>
>>
>>
>> On Thu, 7 Apr 2016, Luiz Americo Pereira Camara wrote:
>>
>>
>>> Is there any code that given a SQL Template would generate the second
>>> filter when paramy is available and keep blank when not available?
>>>
>>>
>>>
>> I have not found such code. I rolled my own.
>>
>>
> Any chance sharing the code or at least the syntax?

No, that was for a commercial project. sorry.

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Graeme Geldenhuys-6
In reply to this post by Luiz Americo Pereira Camara-3
On 2016-04-07 13:00, Luiz Americo Pereira Camara wrote:
> If there's not in pascal, someone knows such templating in another
> languages? The hard part is getting a flexible and functional syntax

I have written such code for the tiOPF project, but it should be quick
to detach any tiOPF dependencies. The code is well unit tested too (see
the tiCriteria_TST.pas unit). The unit test code is also good for usage
examples.

It can't handle all complex cases, but is fairly good. It also has an
option to inject any SQL as-is, in case you need a complex WHERE clause.

 http://geldenhuys.co.uk/tiopf/ticriteria/index.html

In summary, you use the TtiCriteria class to add your desired criteria,
then call tiCriteriaAsSQL(...) which will generate the SQL WHERE clause
string for you.

tiOPF is available on SourceForge or Github:
   https://sourceforge.net/p/tiopf/code/ci/tiopf2/tree/

tiCriteria.pas lives in the Core/ directory.


Regards,
  - Graeme -

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Michael Thompson
In reply to this post by Luiz Americo Pereira Camara-3

On 7 April 2016 at 20:00, Luiz Americo Pereira Camara <[hidden email]> wrote:
Is there any code that given a SQL Template would generate the second filter when paramy is available and keep blank when not available?

Nope, or at least I don't think so.  I've spent a long time looking as well.  Been meaning to bring up the discussion either here or in the forum

I ended up implementing my own "Macro" functionality.  Unfortunately I've broken a few rules and tied the functionality to the UI, making it hard to share.
I need this all the time.  My primary usage is in Master-Detail relationships, where I always want the Master to contain an "ALL" option.  To achieve I write SQL's like... 

   Select Incident_Type As "Incident_Type",
           Description As "Description",
           'Where Incident_Type=''' || Incident_Type || '''' As "Filter_ID"
    From PIG
    Union
    Select
         '(ALL)' As "Incident_Type",
         'All Types' As "Description",
         '' As "Filter_ID"
    From DUAL   

The first Select returns the Master Table, the second Select appends my ALL.  Note that "Filter_ID" contains my full SQL Where clause.

Over in my Detail SQL, I can write stuff like

    Select *
    From Incident
    :Filter_ID
    Order By Incident_Type, Incident   

I implement the detail refresh myself (easy), and I implement the Macro substitution myself before executing the Detail SQL.  (Any SQL field ending in _ID is automatically hidden in my code)

I use this so often, and in so many different ways (Macro's don't have to contain Where clauses, they can be any SQL elements, including simple data), that I cannot consider SQL development without it.  And as such, I've often wondered how do others deal with this...

Mike

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Michael Thompson
In reply to this post by Luiz Americo Pereira Camara-3
On 7 April 2016 at 20:06, Luiz Americo Pereira Camara <[hidden email]> wrote:
> Any chance sharing the code or at least the syntax?

(I realise I'm hijacking your conversation with Michael - apologies for that, but this interests me)

My code to initialise the grid (which hides all _ID columns)

Procedure InitialiseDBGrid(oGrid: TDBGrid; oDataset: TDataset; bHideIDs: Boolean = False);
<...>
Begin
  If not oDataset.Active Then
    Exit;

  oDataset.DisableControls;
  Try
   <...>
    oDataset.First;

    For iTemp := oGrid.Columns.Count - 1 Downto 0 Do
    Begin
      oColumn := oGrid.Columns[iTemp];
      oField := oColumn.Field;

      <...>
      // Hide the ID columns if so requested
      If (bHideIDs) And (Copy(oField.FieldName, Length(oField.FieldName) - 1, 2) =
        'ID') Then
      Begin
        oColumn.Visible := False;
        oField.Visible := False;
      End;
      <...>
    End;
    <...>
    oDataset.First;
  Finally
    oDataset.EnableControls;
  End;
End;

Without getting into my architecture too deeply, I have code that hooks into the Master dataset OnAfterScroll, and the Macro's for each Detail data are handled simply by the follow sort of code...

Function TfrmSQLExplorer.ReplaceCommonMacros(sSQL: String): String;
Begin
  Result := sSQL;

  Result := FindReplace(Result, ':UserDate', FormatDateTime('yyyy-mm-dd', dtUserDate.Date));

  Result := FindReplace(Result, ':Time', FormatDateTime('HH:mm:ss', Now));
  Result := FindReplace(Result, ':Date', FormatDateTime('yyyy-mm-dd', Now));

  Result := FindReplace(Result, ':Database', FClient.DatabaseName);
  Result := FindReplace(Result, ':Repository', FClient.DatabaseName + '_Rep');

  Result := FFilters.ReplaceMacros(Result);
End;

(FFilters is essentially a list of frames that each contain a DBlookupCombo)

and

Function TdckMacros.ReplaceCommonMacros(oExclude: TDataset; sSQL: String): String;
Var
  i: Integer;
  iField: Integer;
  oField: TField;
  oDataset: TDataset;
  sReplace: String;
Begin
  Result := sSQL;

  For i := 0 To FDatasets.Count - 1 Do
  Begin
    oDataset := Dataset[i];
    If (oDataset <> oExclude) Then
      For iField := 0 To oDataset.Fields.Count - 1 Do
      Begin
        oField := oDataset.Fields[iField];
        sReplace := oField.AsString;

        If (oField.DataType = ftInteger) And (sReplace = '') Then
          Result := FindReplace(Result, ':' + oField.FieldName, '-99999')
        Else
          Result := FindReplace(Result, ':' + oField.FieldName, sReplace);
      End;
  End;
End;

You don't need to follow my architecture (I hope ;-) ), just the concept of cycling through a dataset, treating all columns as potential Macros for replacement. Sure, there's issues in the above code that are on my TODO, but have been there for a while, but as I construct all the SQLs used by this code, I've got used to working around those issues :-)

Mike

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Michael Thompson
In reply to this post by Graeme Geldenhuys-6
On 7 April 2016 at 20:20, Graeme Geldenhuys <[hidden email]> wrote:
On 2016-04-07 13:00, Luiz Americo Pereira Camara wrote:
> If there's not in pascal, someone knows such templating in another
> languages? The hard part is getting a flexible and functional syntax

I have written such code for the tiOPF project, but it should be quick
to detach any tiOPF dependencies. The code is well unit tested too (see
the tiCriteria_TST.pas unit). The unit test code is also good for usage
examples.

It can't handle all complex cases, but is fairly good. It also has an
option to inject any SQL as-is, in case you need a complex WHERE clause.

 http://geldenhuys.co.uk/tiopf/ticriteria/index.html

In summary, you use the TtiCriteria class to add your desired criteria,
then call tiCriteriaAsSQL(...) which will generate the SQL WHERE clause
string for you.

Interesting.  I've been meaning to look into tiOPF for a while.  Part of that massive TODO list :-)  This moves it up that list...

Mike

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Marcos Douglas B. Santos
In reply to this post by Luiz Americo Pereira Camara-3
On Thu, Apr 7, 2016 at 9:00 AM, Luiz Americo Pereira Camara
<[hidden email]> wrote:

>
> 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
>
> Is there any code that given a SQL Template would generate the second filter when paramy is available and keep blank when not available?

You can do this:

===begin===
select *
from Customers
where 1=1
and FieldX = 1
and (:paramy = -1 or FieldY = :paramy)
===end===

if you do not want to filter, just pass -1 (or whatever). Otherwise,
pass the real value.

For MSSQL I like to declare a variable like this:
===begin===
declare @paramy int = :paramy
...
and (@paramy = -1 or FieldY = @paramy)
===end===

Regards,
Marcos Douglas
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Santiago A.
In reply to this post by Luiz Americo Pereira Camara-3
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.
[hidden email]

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Graeme Geldenhuys-6
In reply to this post by Michael Thompson
On 2016-04-07 13:47, Michael Thompson wrote:
> This moves it up that list...

I can give you many more reason to move it up even further. ;-) tiOPF is
a treasure trove of goodies (for DB and non-DB projects).

Regards,
  - Graeme -

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Luiz Americo Pereira Camara-3
Thanks for all the responses

The Marcos' one is particular interesting since goes in a direction a did not think earlier

Luiz

2016-04-07 12:06 GMT-03:00 Graeme Geldenhuys <[hidden email]>:
On 2016-04-07 13:47, Michael Thompson wrote:
> This moves it up that list...

I can give you many more reason to move it up even further. ;-) tiOPF is
a treasure trove of goodies (for DB and non-DB projects).

Regards,
  - Graeme -

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Marcos Douglas B. Santos
On Thu, Apr 7, 2016 at 12:30 PM, Luiz Americo Pereira Camara
<[hidden email]> wrote:
> Thanks for all the responses
>
> The Marcos' one is particular interesting since goes in a direction a did
> not think earlier

Thanks. I do this for years.

The major advantage, I think, is doesn't need to change the SQL in pieces.
You can put the original SQL in a Query component or file integrally.
The DBMS engine is smarter enough to optimize all that.

Best regards,
Marcos Douglas
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Create dynamic SQL according to available params

Martin Schreiber-2
In reply to this post by Santiago A.
On Thursday 07 April 2016 16:20:16 Santiago A. wrote:

> 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).

In MSEgui SQL properties are of type TSQLStringList. TSQLStringList has a
property "Macros" which is an array of TSQLMacroItem. TSQLMacroItem.Value
inherits from TSQLStringList, so it is possible to build a macro tree which
items can be accessed by a name path (TMacroProperty.ItemByNames()).

"
select ${fields} from ${table} where ${filter}
"
in example
http://mseide-msegui.sourceforge.net/pics/sqlmacros.png

expands to
"
select FIELD1 from TABLE1 where FIELD1 = 'abc'
"

Martin
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal