Is there a tool which can extract all SQL command strings from Delphi form files?

Question

For documentation and further inspection, I would like to run an 'extract strings' on all DFM files in many projects to find all SQL statements. Are there command line tools which can do this? The DFM files are all in text format.

Answer 1

Depending on the kind of query component you're using, I would imagine you could do this using a command-line grep (or any other text-searching tool) on your project folders. In the DFM, for normal TQuery-alike components, you're going to have something along the lines of

   SQL.Strings=( 'select * from mytable' )

or possibly (I've no Delphi to hand to check, the joys of being at home!)

   SQL.Text=( 'select * from mytable' )

Given how these strings can spread over several 'lines' inside the DFM, and given how there might be several variations you'd need to check for, personally I'd write a small piece of Delphi to do this.

The basic idea would be; iterate through all the files/subfolders in a given directory, looking for all the DFM files, and for each one, read it into a TStringList, check for any of the TQuery (etc) SQL properties you're interested in, and write the results (component name, filename, actual SQL string) out to a results file. Really shouldn't be more than an hour or two's work at the most.

If you have stored procs, that you call using something other than a TQuery-type component, you'll have to have a peek inside a DFM first and see how the SQL appears; it will probably be along the lines of

   CommandText=( 'exec mysproc :id, :value' )

etc.

edit: Following the discussion in the comments, here's a sample from one of my DFMs;

    (other properties)
    SQL.Strings = (
      'SELECT D.*, '
      'C.DESCRIPTION AS CLASS_DESCRIPTION, '
      'C.CHQ_NUM_THRESHOLD AS CLASS_CHQ_NUM_THRESHOLD,'
      'C.CREDIT_LIMIT AS CLASS_CREDIT_LIMIT,'
      'A.REF AS ACCOUNT_REF,'
      'A.SORT_CODE AS ACCOUNT_SORT_CODE,'
      'A.ACCOUNT_NUMBER AS ACCOUNT_ACCOUNT_NUMBER,'
      'A.PREFERRED_ACCOUNT AS ACCOUNT_PREFERRED_ACCOUNT'
      'FROM '
      'DRAWER_ACCOUNTS A LEFT JOIN DRAWERS D '
      'ON D.REF=A.DRAWER_REF'
      'LEFT JOIN REF_DRAWER_CLASSES C'
      'ON D.DRAWER_CLASS = C.CLASS_ID'
      'WHERE A.SORT_CODE=:PSORT AND A.ACCOUNT_NUMBER=:PACC')
    (other properties)

So all I really need to do is to spot the SQL.Strings = ( bit, then read the rest of the line and all subsequent lines, removing the leading and trailing ', until I get to a line that ends in ')' - at which point I'm done. Whatever interesting SQL (and comments) might have been contained within the quotes on each line is irrelevant, really. You want to read each line that you're interested in and cut out the text between the first and last quote on each line. This must work because I can't see how Delphi would stream this any other way itself, it can't possibly 'read' the string contents - it's just working on the basis that the stringlist is (possibly) broken into lines and each line is delimited in the DFM with an opening and closing ', and the whole stringlist contents themselves are contained within a pair of brackets.

Does that make sense, or am I still missing something? :-)

Answer 2

Since the DFM is basically in name=value format, you could just load into a tStringlist, then loop through each line looking for the specific property names your interested in:

var
  slDfm : tStringList;
  Item : String;
  ix : integer;
begin
  slDFM := tStringlist.create;
  try
    slDFM.LoadFromFile( filename );
    for ix := 0 to slDfm.Count-1 do
      begin
        slDfm.Strings[ix] := Trim(slDfm.Strings[ix]);
        if SameText(Trim(slDfm.Names[ix]),'CommandText') then
          memo1.Lines.Add('"'+Trim(slDfm.ValueFromIndex[ix])+'"');
      end;
  finally
    slDFM.free;
  end;
end;

Answer 3

Here is a DFM Parser from Felix Colibri

DFM Parser

Here is an interesting tool for doing stuff like this

YACC

Answer 4

Many thanks for the answers! Another solution which I will try is the 'extract strings' tool included in "GNU Gettext for Delphi and C++ Builder".

The .po files include not only all component text for but also all resourcestrings (another place where SQL commands are stored), complete with references to the origin (which pas or dfm file, which component property name) and it is a very simple "name=value" list already.

With a .po file it will be easy to sort out all SQL.Text from .pas files and all resourcestrings with names like 'SQL_..." in all files.