Extracting SQL from Delphi code and DFMs

[David Schwartz 430]

We've got a ton of SQL queries embedded in a Delphi app that we've decided to move to the server where they belong. Around 700 or so of them.

 

Does anybody have any ideas about how to most easily extract the SQL from Delphi code that generates them at run-time with a bunch of string catenation before stuffing them into the .SQL.Text property, or even via repetitive SQL.Add(...) methods?

 

Also, what's the best way to extract them from SQL properties in DFMs where they're defined at design-time?

 

The "obvious" approach in my mind is to use some kind of editing, like regular expressions, to remove common syntactic sugar needed to manipulate a bunch of string constants used to build up a big string.

 

Another thought was to use some kind of scripter to actually process the Delphi code in a way that generates the strings dynamically, then grab them from the buffer that way. (ie., use a run-time interpreter to process limited lines of code that produce longer strings, rather than at run-time.)

 

Any ideas?

[KodeZwerg 54]

Idk if possible, how about a simple line by line parser/checker?

[Guest]

It's a breeze using Delphi-AST! Try it, you'll be up n running fast.

 

/D

[David Schwartz 430]

9 hours ago, Dany Marmur said:

It's a breeze using Delphi-AST! Try it, you'll be up n running fast.

 

/D

care to elaborate a bit?

[toms 29]

Quote

Developer Roman Yankovsky has an Abstract Syntax Tree Builder source code project called Delphi AST over on Github.
An abstract syntax tree represents a tree of the abstract syntactic structure of source code in languages like Object Pascal. 
What you can do with it is basically pass in some Object Pascal source code like a unit and it will turn it into an XML representation
of that unit which can be navigated using XPath and other methods

http://www.fmxexpress.com/abstract-syntax-tree-builder-source-code-for-delphi-xe8-firemonkey-on-android-and-ios/

Edited October 31, 2018 by toms

[Erix A. 8]

21 hours ago, David Schwartz said:

We've got a ton of SQL queries embedded in a Delphi app that we've decided to move to the server where they belong.

Could you, please, elaborate what "move to the server" means? Is the result just a simple .sql files, which will be run on a server?

[David Schwartz 430]

some are simple, some are quite complex. The thing is, right now they're embedded in Delphi code on the client side. We want to move them into the server as stored procs, funcs, and views, and then replace the code on the Delphi side with simple calls to those server-side objects, parameterized where needed.

 

(We're preparing to migrate from Oracle to PostgeSQL, and there are tools that can convert server-side assets automatically -- not 100%, but enough to be useful. However, they won't read the Delphi code! So we want to take this opportunity to extract all of this random business logic and other SQL CRUD code from the client side and move it over to the server where it belongs.)

[Guest]

I built my 'SQL-DFM' browser using DX. If you have DX i can send the sources. The AST part i OS. But it hardly matters, look at AST demo, 700 strings takes less than a, sec to scan. Then you can even output or rearrange the props in the DFM. Or as i did, collect all in a neat grid with search, filters, grouping, sorting... you essentially write you own "navigator" for specific properties.

Edited October 31, 2018 by Guest

[David Schwartz 430]

2 minutes ago, Dany Marmur said:

I built my 'SQL-DFM' browser using DX. If you have DX i can send the sources. The AST part i OS. But it hardly matters, look at AST demo, 700 strings takes less than a, sec to scan. Then you can even output or rearrange the props in the DFM. Or as i did, collect all in a neat grid with search, filters, grouping, sorting... you essentially write you own "navigator" for specific properties.

They're scattered across a few hundred source files. It's probably worth taking a look. Do you want to post it here and I can grab it, then you can leave it or delete it later?

[Guest]

55 minutes ago, David Schwartz said:

They're scattered across a few hundred source files. It's probably worth taking a look. Do you want to post it here and I can grab it, then you can leave it or delete it later?

I'll think of sumthing during work hours tomorrow.

[il2 1]

Hi Dany! I'm interested in your tool either. May be a case for "I made this" sub-forum?

1 hour ago, Dany Marmur said:

I'll think of sumthing during work hours tomorrow.

 

[Sherlock 663]

9 hours ago, il2 said:

May be a case for "I made this" sub-forum?

I second that!

[Guest]

OK. Just went in to check on that little tool. 

 

Turns out i have used DevExpress and RTC 😞 DevExpress because i do not have to do anything but fill a grid and there's search, sorting, filtering, pinning and all else. The only function i added was a checkbox to show the full row-height for the statements or just one row.

RTC is because i'm familiar with its JSON classes. The RTC JSON classes can be replaced with something else.

 

I just want to make it clear that for a "I made this" sharing i'd have to at *least* 1) get rid of 3rd party 2) implement settings for paths, properties selection et.al. 3) consider using project files in stead of paths, 4) consider an exe or plugin for the IDE... the to-do list could grow exponentially.

 

Another thing that i dearly have to apologise for is the mention of Delphi-AST, i have no idea what i was on about. I used this brilliant project:

 

https://github.com/masonwheeler/DFMJSON

 

not Delphi-AST at all. Apologies. Really. I should not post so fast.  

 

This is the complete non-visual unit, what properties i'm interested in are hard-coded (!):

 

unit Data.ObjectCollector;

interface

uses
  System.SysUtils,
  System.Classes;

type
  TStatementInfo = record
    FormName: string;
    StatementName: string;
    StatementClass: string;
    StoredProcName: string;
    StatementSQL: string;
  end;

  TObjectCollectoDataModule = class(TDataModule)
  private
    procedure DoCollect(
      const filename: string;
      const onFound: TProc<TStatementInfo>;
      const onSuccess, onErr: TProc<string>);
  public
    procedure RunCollect(
      const path, mask: string; recurse: boolean;
      const onFound: TProc<TStatementInfo>;
      const onSuccess, onErr: TProc<string>);
  end;

var
  ObjectCollectoDataModule: TObjectCollectoDataModule;

implementation

uses
  System.Types,
  System.IOUtils,
  System.StrUtils,
  dwsJSON,
  DFMJSON,
  rtcInfo;

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

{ TObjectCollectoDataModule }

procedure TObjectCollectoDataModule.DoCollect(
  const filename: string;
  const onFound: TProc<TStatementInfo>;
  const onSuccess, onErr: TProc<string>);
var
  I, J: Integer;
  dfm: TdwsJSONObject;
  rtcValue: TRtcValue;
  rtcArray: TRtcArray;
  lCurrentStatement: TStatementInfo;
begin
  dfm := nil;
  try
    try
      try
        dfm := DFMJSON.Dfm2JSON(filename);
      except
        on EParserError do
        try
          dfm := DFMJSON.DfmBin2JSON(filename)
        except
          on E: Exception do
          begin
            if assigned(onErr) then
              onErr(format('Error while parsing %s.  Exception %s: %s', [filename, e.ClassName, e.Message]));
            exit;
          end;
        end;
      end;

      rtcValue := TRtcValue.FromJSON(dfm.ToString);
      try
        if rtcValue.isType = rtc_Record then
        begin
          if Assigned( OnSuccess ) then
            OnSuccess(Format( 'Processed %s (%s)', [
                              rtcValue.asRecord.asString['$Class'],
                              rtcValue.asRecord.asString['$Name']]));

          lCurrentStatement.FormName := rtcValue.asRecord.asString['$Name'];
          if rtcValue.asRecord.isType['$Children'] = rtc_Array then
          begin
            rtcArray := rtcValue.asRecord.asArray['$Children'];
            for I := 0 to rtcArray.Count - 1 do
              if rtcArray.isType[I] = rtc_Record then
                if (rtcArray.asRecord[i].asString['$Class'] = 'TIB_Cursor') or
                   (rtcArray.asRecord[i].asString['$Class'] = 'TIB_DSQL') or
                   (rtcArray.asRecord[i].asString['$Class'] = 'TIB_Query') or
                   (rtcArray.asRecord[i].asString['$Class'] = 'TIB_StoredProc') then
                begin
                  if Assigned( OnSuccess ) then
                    OnSuccess(Format('   Found %s (%s)', [
                                     rtcArray.asRecord[i].asString['$Name'],
                                     rtcArray.asRecord[i].asString['$Class']]));

                  if Assigned( OnFound ) then
                  begin
                    lCurrentStatement.StatementName   := rtcArray.asRecord[i].asString['$Name'];
                    lCurrentStatement.StatementClass  := rtcArray.asRecord[i].asString['$Class'];
                    lCurrentStatement.StatementSQL    := '';
                    if rtcArray.asRecord[i].asString['$Class'] = 'TIB_StoredProc' then
                      lCurrentStatement.StoredProcName := AnsiDequotedStr(rtcArray.asRecord[i].asText['StoredProcName'], '''') else
                      lCurrentStatement.StoredProcName :=  '';
                    if rtcArray.asRecord[i].isType['SQL.Strings'] = rtc_Array then
                      for J := 0 to rtcArray.asRecord[i].asArray['SQL.Strings'].Count - 1 do
                      begin
                        lCurrentStatement.StatementSQL :=
                          lCurrentStatement.StatementSQL +
                          AnsiDequotedStr(rtcArray.asRecord[i].asArray['SQL.Strings'].asString[J], '''') + #13#10;
                      end;
                    OnFound(lCurrentStatement);
                  end;
                end;
          end;
        end;
      finally
        rtcValue.Free;
      end;

    except
      on E: Exception do
        if assigned(onErr) then
          onErr(format('Error while converting %s.  Exception %s: %s', [filename, e.ClassName, e.Message]));
    end;
  finally
    dfm.Free;
  end;
end;

procedure TObjectCollectoDataModule.RunCollect(
  const path, mask: string; recurse: boolean;
  const onFound: TProc<TStatementInfo>;
  const onSuccess, onErr: TProc<string>);
var
  files: TStringDynArray;
  filename: string;
  option: TSearchOption;
begin
  if recurse then
    option := TSearchOption.soAllDirectories else
    option := TSearchOption.soTopDirectoryOnly;
  files := TDirectory.GetFiles(path, mask, option);
  for filename in files do
    DoCollect(filename, onFound, onSuccess, onErr);
end;

end.

Call RunCollect and get each component and property/ies back in the onFound procedure. The onSuccess and onErr is for the log.

The rtcXXX functions is used to parse and walk the JSON hierarchy. It's very similar to reading and processing XML. Just ask me what happens if it's unclear.

As you can see the real work is done by the DFMJSON project in the Dfm2JSON and dfm.toString functions.

 

Here are two screenshots:

 

...and...

 

 

but that is very need-specific. I.e. what do you want to do with it all.

 

HTH and Regards,

 

/Dany

[David Schwartz 430]

@Dany Marmur Thanks! We have DevEx ... what's RTC?

[Guest]

@David Schwartz, RTC is here https://rtc.teppi.net/ but as i said - i only used its JSON parsing classes (value handling). You should be able to snappily change it to what comes with Delphi.

Since you have DX, here's the complete visual code. Now i have pasted the whole project 🙂

BTW - you noticed my DACs are IBO, you have to change names to whatever you use.

 

unit Forms.MainForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, cxGraphics, cxControls, cxLookAndFeels, cxLookAndFeelPainters, cxContainer,
  cxEdit, dxLayoutcxEditAdapters, dxLayoutContainer, cxStyles, cxCustomData, cxFilter, cxData, cxDataStorage,
  cxNavigator, cxDataControllerConditionalFormattingRulesManagerDialog, cxMemo, cxGridCustomTableView, cxGridTableView,
  cxGridCustomView, cxClasses, cxGridLevel, cxGrid, cxTextEdit, cxMaskEdit, cxButtonEdit, dxLayoutControl,
  cxCustomListBox, cxListBox, cxCheckBox, cxGridWinExplorerView, dxBarBuiltInMenu, cxGridCustomPopupMenu,
  cxGridPopupMenu, dxLayoutControlAdapters, Vcl.Menus, Vcl.StdCtrls, cxButtons, dxSkinsCore, dxSkinOffice2013White,
  dxLayoutLookAndFeels;

type
  TCollectorMainForm = class(TForm)
    LayoutControlMainGroup_Root: TdxLayoutGroup;
    LayoutControlMain: TdxLayoutControl;
    SQLGridLevel: TcxGridLevel;
    SQLGrid: TcxGrid;
    dxLayoutItem2: TdxLayoutItem;
    SQLTableView: TcxGridTableView;
    SQLTableViewColumnFormName: TcxGridColumn;
    SQLTableViewColumnStmtClass: TcxGridColumn;
    SQLTableViewColumnStmtName: TcxGridColumn;
    SQLTableViewColumnSQLText: TcxGridColumn;
    LogListBox: TcxListBox;
    dxLayoutItem3: TdxLayoutItem;
    FullRowHeightCheckBox: TcxCheckBox;
    dxLayoutItem4: TdxLayoutItem;
    SQLTableViewColumnStoredProcName: TcxGridColumn;
    SQLGridPopupMenu: TcxGridPopupMenu;
    PathTextEdit: TcxTextEdit;
    dxLayoutItem1: TdxLayoutItem;
    ScanButton: TcxButton;
    dxLayoutItem5: TdxLayoutItem;
    dxLayoutAutoCreatedGroup1: TdxLayoutAutoCreatedGroup;
    SQLStyleRepository: TcxStyleRepository;
    cxStyle1: TcxStyle;
    dxLayoutGroup1: TdxLayoutGroup;
    dxLayoutLookAndFeelList1: TdxLayoutLookAndFeelList;
    dxLayoutCxLookAndFeel1: TdxLayoutCxLookAndFeel;
    procedure cxCheckBox1PropertiesChange(Sender: TObject);
    procedure ScanButtonClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    procedure RefreshGrid;
  public
    { Public declarations }
  end;

var
  CollectorMainForm: TCollectorMainForm;

implementation

uses
  Data.ObjectCollector;

{$R *.dfm}

procedure TCollectorMainForm.ScanButtonClick(Sender: TObject);
begin
  RefreshGrid;
end;

procedure TCollectorMainForm.cxCheckBox1PropertiesChange(Sender: TObject);
begin
  SQLTableView.OptionsView.CellAutoHeight := FullRowHeightCheckBox.Checked;
  if FullRowHeightCheckBox.Checked then
    SQLTableView.Controller.TopRowIndex := SQLTableView.Controller.FocusedRowIndex;
end;

procedure TCollectorMainForm.FormCreate(Sender: TObject);
begin
  RefreshGrid;
  SQLTableView.Controller.ShowFindPanel;
end;

procedure TCollectorMainForm.RefreshGrid;
var
  lCurrent: Integer;
begin
  lCurrent := 1;
  SQLTableView.DataController.BeginUpdate;
  LogListBox.Items.BeginUpdate;
  try
    LogListBox.Items.Clear;
    SQLTableView.DataController.RecordCount := 0;

    Data.ObjectCollector.ObjectCollectoDataModule.RunCollect(
      PathTextEdit.Text, '*.dfm', false,
      procedure (aStatementInfo: TStatementInfo)
      begin
        SQLTableView.DataController.RecordCount := lCurrent;
        SQLTableView.DataController.Values[lCurrent - 1, SQLTableViewColumnFormName.Index]        := aStatementInfo.FormName;
        SQLTableView.DataController.Values[lCurrent - 1, SQLTableViewColumnStmtClass.Index]       := aStatementInfo.StatementClass;
        SQLTableView.DataController.Values[lCurrent - 1, SQLTableViewColumnStmtName.Index]        := aStatementInfo.StatementName;
        SQLTableView.DataController.Values[lCurrent - 1, SQLTableViewColumnStoredProcName.Index]  := aStatementInfo.StoredProcName;
        SQLTableView.DataController.Values[lCurrent - 1, SQLTableViewColumnSQLText.Index]         := aStatementInfo.StatementSQL;
        Inc(lCurrent);
      end,
      procedure (aText: string)
      begin
        LogListBox.Items.Append(aText);
      end,
      procedure (aText: string)
      begin
        LogListBox.Items.Append(aText);
      end);

      LogListBox.ItemIndex := LogListBox.Items.Count - 1;
  finally
    LogListBox.Items.EndUpdate;
    SQLTableView.DataController.EndUpdate;
  end;
end;

end.

... and the dfm ...

 

object CollectorMainForm: TCollectorMainForm
  Left = 0
  Top = 0
  ActiveControl = SQLGrid
  Caption = 'DFM SQL Collector'
  ClientHeight = 892
  ClientWidth = 1507
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object LayoutControlMain: TdxLayoutControl
    Left = 0
    Top = 0
    Width = 1507
    Height = 892
    Align = alClient
    TabOrder = 0
    LayoutLookAndFeel = dxLayoutCxLookAndFeel1
    HighlightRoot = False
    object SQLGrid: TcxGrid
      Left = 10
      Top = 55
      Width = 1487
      Height = 793
      TabOrder = 3
      object SQLTableView: TcxGridTableView
        Navigator.Buttons.CustomButtons = <>
        Navigator.Buttons.First.Visible = True
        Navigator.Buttons.PriorPage.Visible = True
        Navigator.Buttons.Prior.Visible = True
        Navigator.Buttons.Next.Visible = True
        Navigator.Buttons.NextPage.Visible = True
        Navigator.Buttons.Last.Visible = True
        Navigator.Buttons.Insert.Visible = False
        Navigator.Buttons.Append.Visible = False
        Navigator.Buttons.Delete.Visible = False
        Navigator.Buttons.Edit.Visible = False
        Navigator.Buttons.Post.Visible = False
        Navigator.Buttons.Cancel.Visible = False
        Navigator.Buttons.Refresh.Visible = False
        Navigator.Buttons.SaveBookmark.Visible = True
        Navigator.Buttons.GotoBookmark.Visible = True
        Navigator.Buttons.Filter.Visible = True
        Navigator.InfoPanel.Visible = True
        Navigator.Visible = True
        FindPanel.DisplayMode = fpdmManual
        DataController.Options = [dcoCaseInsensitive, dcoAssignGroupingValues, dcoAssignMasterDetailKeys, dcoSaveExpanding]
        DataController.Summary.DefaultGroupSummaryItems = <>
        DataController.Summary.FooterSummaryItems = <>
        DataController.Summary.SummaryGroups = <>
        FixedDataRows.PinVisibility = rpvRowHotTrack
        OptionsBehavior.CopyCaptionsToClipboard = False
        OptionsBehavior.FocusCellOnTab = True
        OptionsBehavior.IncSearch = True
        OptionsBehavior.NavigatorHints = True
        OptionsBehavior.RecordScrollMode = rsmByPixel
        OptionsCustomize.ColumnHiding = True
        OptionsCustomize.ColumnHidingOnGrouping = False
        OptionsCustomize.ColumnsQuickCustomization = True
        OptionsCustomize.DataRowFixing = True
        OptionsData.Deleting = False
        OptionsData.Inserting = False
        OptionsView.CellAutoHeight = True
        OptionsView.ColumnAutoWidth = True
        OptionsView.HeaderEndEllipsis = True
        OptionsView.HeaderFilterButtonShowMode = fbmSmartTag
        OptionsView.Indicator = True
        OptionsView.ShowColumnFilterButtons = sfbWhenSelected
        Styles.Background = cxStyle1
        Styles.Content = cxStyle1
        Styles.ContentEven = cxStyle1
        Styles.ContentOdd = cxStyle1
        Styles.FilterBox = cxStyle1
        Styles.FindPanel = cxStyle1
        Styles.IncSearch = cxStyle1
        Styles.Navigator = cxStyle1
        Styles.NavigatorInfoPanel = cxStyle1
        Styles.SearchResultHighlight = cxStyle1
        Styles.FilterRowInfoText = cxStyle1
        Styles.Footer = cxStyle1
        Styles.Group = cxStyle1
        Styles.GroupByBox = cxStyle1
        Styles.GroupFooterSortedSummary = cxStyle1
        Styles.GroupSortedSummary = cxStyle1
        Styles.GroupSummary = cxStyle1
        Styles.Header = cxStyle1
        Styles.Inactive = cxStyle1
        Styles.Indicator = cxStyle1
        Styles.InplaceEditFormGroup = cxStyle1
        Styles.InplaceEditFormItem = cxStyle1
        Styles.InplaceEditFormItemHotTrack = cxStyle1
        Styles.NewItemRowInfoText = cxStyle1
        Styles.Preview = cxStyle1
        Styles.Selection = cxStyle1
        object SQLTableViewColumnFormName: TcxGridColumn
          Caption = 'Form Name'
          PropertiesClassName = 'TcxTextEditProperties'
          Properties.ReadOnly = True
          Options.FilteringPopupIncrementalFiltering = True
          Width = 165
        end
        object SQLTableViewColumnStmtClass: TcxGridColumn
          Caption = 'Statement Class'
          PropertiesClassName = 'TcxTextEditProperties'
          Properties.ReadOnly = True
          Options.FilteringPopupIncrementalFiltering = True
          Width = 110
        end
        object SQLTableViewColumnStmtName: TcxGridColumn
          Caption = 'Statement Name'
          PropertiesClassName = 'TcxTextEditProperties'
          Properties.ReadOnly = True
          Options.FilteringPopupIncrementalFiltering = True
          SortIndex = 0
          SortOrder = soAscending
          Width = 152
        end
        object SQLTableViewColumnStoredProcName: TcxGridColumn
          Caption = 'Stored Proc Name'
          PropertiesClassName = 'TcxTextEditProperties'
          Options.FilteringPopupIncrementalFiltering = True
          Width = 110
        end
        object SQLTableViewColumnSQLText: TcxGridColumn
          Caption = 'SQL Text'
          PropertiesClassName = 'TcxMemoProperties'
          Properties.ReadOnly = True
          Properties.ScrollBars = ssVertical
          Options.FilteringPopupIncrementalFiltering = True
          Width = 739
        end
      end
      object SQLGridLevel: TcxGridLevel
        GridView = SQLTableView
      end
    end
    object LogListBox: TcxListBox
      Left = 10000
      Top = 10000
      Width = 1467
      Height = 63
      ItemHeight = 13
      TabOrder = 4
      Visible = False
    end
    object FullRowHeightCheckBox: TcxCheckBox
      Left = 1405
      Top = 11
      Caption = 'Full row height'
      Properties.OnChange = cxCheckBox1PropertiesChange
      State = cbsChecked
      Style.HotTrack = False
      Style.Shadow = False
      Style.TransparentBorder = True
      TabOrder = 2
      Transparent = True
    end
    object PathTextEdit: TcxTextEdit
      Left = 37
      Top = 10
      Style.HotTrack = False
      TabOrder = 0
      Text = 'C:\Users\Superkey\Documents\Projects'
      TextHint = 'All *.dfm files in the path will be searched'
      Width = 1281
    end
    object ScanButton: TcxButton
      Left = 1324
      Top = 10
      Width = 75
      Height = 21
      Caption = 'SCAN'
      Default = True
      SpeedButtonOptions.CanBeFocused = False
      TabOrder = 1
      OnClick = ScanButtonClick
    end
    object LayoutControlMainGroup_Root: TdxLayoutGroup
      AlignHorz = ahClient
      AlignVert = avClient
      ButtonOptions.Buttons = <>
      Hidden = True
      ItemIndex = 2
      ShowBorder = False
      Index = -1
    end
    object dxLayoutItem2: TdxLayoutItem
      Parent = LayoutControlMainGroup_Root
      AlignVert = avClient
      CaptionOptions.Text = 'SQL Queries'
      CaptionOptions.Layout = clTop
      Control = SQLGrid
      ControlOptions.OriginalHeight = 200
      ControlOptions.OriginalWidth = 250
      ControlOptions.ShowBorder = False
      Index = 1
    end
    object dxLayoutItem3: TdxLayoutItem
      Parent = dxLayoutGroup1
      AlignVert = avClient
      CaptionOptions.Text = 'Log'
      CaptionOptions.Visible = False
      CaptionOptions.Layout = clTop
      Control = LogListBox
      ControlOptions.OriginalHeight = 97
      ControlOptions.OriginalWidth = 121
      ControlOptions.ShowBorder = False
      Index = 0
    end
    object dxLayoutItem4: TdxLayoutItem
      Parent = dxLayoutAutoCreatedGroup1
      AlignVert = avCenter
      CaptionOptions.Text = 'FullRowHeight'
      CaptionOptions.Visible = False
      Control = FullRowHeightCheckBox
      ControlOptions.OriginalHeight = 19
      ControlOptions.OriginalWidth = 92
      ControlOptions.ShowBorder = False
      Index = 2
    end
    object dxLayoutItem1: TdxLayoutItem
      Parent = dxLayoutAutoCreatedGroup1
      AlignHorz = ahClient
      AlignVert = avCenter
      CaptionOptions.Text = 'Path'
      Control = PathTextEdit
      ControlOptions.OriginalHeight = 21
      ControlOptions.OriginalWidth = 121
      ControlOptions.ShowBorder = False
      Index = 0
    end
    object dxLayoutItem5: TdxLayoutItem
      Parent = dxLayoutAutoCreatedGroup1
      AlignVert = avCenter
      CaptionOptions.Text = 'Scan'
      CaptionOptions.Visible = False
      Control = ScanButton
      ControlOptions.OriginalHeight = 21
      ControlOptions.OriginalWidth = 75
      ControlOptions.ShowBorder = False
      Index = 1
    end
    object dxLayoutAutoCreatedGroup1: TdxLayoutAutoCreatedGroup
      Parent = LayoutControlMainGroup_Root
      LayoutDirection = ldHorizontal
      Index = 0
      AutoCreated = True
    end
    object dxLayoutGroup1: TdxLayoutGroup
      Parent = LayoutControlMainGroup_Root
      CaptionOptions.Text = 'Log'
      ButtonOptions.Alignment = gbaLeft
      ButtonOptions.Buttons = <>
      ButtonOptions.ShowExpandButton = True
      Expanded = False
      UseIndent = False
      Index = 2
    end
  end
  object SQLGridPopupMenu: TcxGridPopupMenu
    Grid = SQLGrid
    PopupMenus = <>
    Left = 1048
    Top = 360
  end
  object SQLStyleRepository: TcxStyleRepository
    Left = 952
    Top = 320
    PixelsPerInch = 96
    object cxStyle1: TcxStyle
      AssignedValues = [svFont]
      Font.Charset = DEFAULT_CHARSET
      Font.Color = clWindowText
      Font.Height = -11
      Font.Name = 'Courier New'
      Font.Style = []
    end
  end
  object dxLayoutLookAndFeelList1: TdxLayoutLookAndFeelList
    Left = 856
    Top = 368
    object dxLayoutCxLookAndFeel1: TdxLayoutCxLookAndFeel
      LookAndFeel.NativeStyle = False
      LookAndFeel.ScrollbarMode = sbmClassic
      LookAndFeel.SkinName = 'Office2013White'
      PixelsPerInch = 96
    end
  end
end

HTH

 

/D

[David Schwartz 430]

Thanks, I do appreciate this, although a zip file attachment would be a lot easier to work with 😉  That's a LOT of text to scroll through here.

 

I noticed that the DWS project has migrated to BitBucket. Is there anything to be aware of when using it?

Edited November 1, 2018 by David Schwartz

[Guest]

Yes, but a zip with an executable, then i have to code settings for the different names, i simply do not have the time now 😞.

The three code-windows above is all the code except for the dpr! You can just copy paste it really.

 

This is what i have for DWScript: https://xxx@bitbucket.org/egrange/dwscript.git

This is the path to DWSJSON: https://github.com/masonwheeler/DFMJSON.git

 

I suppose if one had the time you could VCL-ify it, add persistence and settings and some more and you'll have a neat contribution to the community.

This was a very fast hack when i moved from my own dynamic SQL-repository to using DFMs. I missed the global searches i could do in my own repo.

 

HTH,

 

/D

 

 

[David Schwartz 430]

I don't need an executable. It's just that posting the sources here directly take up most of the page and distract from an otherwise useful discussion.

 

[Guest]

@David Schwartz, i see - til next time!

[Bill Meyer 337]

On 10/31/2018 at 4:03 AM, Dany Marmur said:

It's a breeze using Delphi-AST! Try it, you'll be up n running fast.

 

/D

DelphiAST seems a bit unhappy with DFM files. Anything in particular we need to know?

[Guest]

@Bill Meyer, when i wrote the original post from my phone i confused DWSJSON and Delphi-AST. Scroll up, i have a lot of apologies for that 🙂

I used DWSJSON for this project. "Only" to parse the DFM into JSON. Again, my apologies.

[Bill Meyer 337]

@Dany Marmur No problem. I also found: https://github.com/ADUG/DFMJSON

[ByteJuggler 45]

I realise this is slightly late to the party but another possible way to possibly tackle this problem is to actually instantiate the modules containing queries and then iterate over the component collection of each form/module looking for the relevant query component types and then doing whatever you want with them. (E.g. serialise to file, write out parameters, etc.)  Obviously this approach has some caveats that might make it less desirable in some instances (not least dependency issues).  Still it should not be that hard to come up with a project that just includes everything referenced etc.)  Anyway just a thought.

[haentschman 92]

Hi...

 

If you extracting the SQL from Sourcecode, then you can save it as Resource...a better handling for more then one DBMS. 

But i think, that the extraction of of the SQL is manually work.

 

! Advertising from me...

How about this? DIMOWA®SQL Creator ...at the Moment in German.

 

Advantages:

* SQL not in query or sourcecode

* SQL as file on the disk

* SQL can tested in your favored Editor

* SQL, if you want, encypted in the EXE

* Shows more DBMS  side by side  in the editor

...and so on.

Edited November 4, 2018 by haentschman

[Lars Fosdal 1793]

If you use FireDAC, you can also use the FireDAC Monitor to capture everything that goes on, databasewise.

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Tracing_and_Monitoring_(FireDAC)