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

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

Michael.

2016-04-07 9:03 GMT-03:00 Michael Van Canneyt :

Any chance sharing the code or at least the syntax?

Thanks in advance

Luiz

No, that was for a commercial project. sorry.

Michael.

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 -

On 7 April 2016 at 20:00, 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?

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

On 7 April 2016 at 20:06, Luiz Americo Pereira Camara <

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

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

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

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.

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 -

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
:

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

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