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

[Michael Thompson]

On 7 April 2016 at 20:06, Luiz Americo Pereira Camara <
luizamericop at gmail.com> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20160407/e21b9c38/attachment.html>