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.

Unhelm

Am I correct to assume that a "DFM" file is Delphi's version of a ressource file? – Helminthic

@Helminthic yes they contain layout and binding information for 'forms', 'frames' and 'datamodules'. – Unhelm

@lexe but I do not know if they will be technically end up as normal application resources in the binaries – Unhelm

Yes, they do, but as resource type RC_DATA, IIRC. Delphi internally converts to and from actual forms and such. – Consecution

Answer 1

Here is a DFM Parser from Felix Colibri

DFM Parser

Here is an interesting tool for doing stuff like this

YACC

Badger

scope : Delphi 1 to 2005 for Windows, Kylix. i thinkl its not Unicode compatible – Strained

Answer 2

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? :-)

Krakau

SQL commands can include ( and ) (and comments (including ( and ))) so parsing will be a little difficult, because ) also terminates the SQL String... And how about Unicode, if the SQL Text contains special characters in comments or field values. But I guess that will be the fun and easy part :P – Unhelm

Very true... but if you start parsing at the first ' and keep a count of how many levels of quote you're "in" as you go, you ought to be fine. At the end of the day, the Delphi DFM parser must be able to make sense of the string such that it does start and end with a quote. The SQL Text can't contain unicode unless the DFM file is unicode itself (unless I'm missing something about strings in DFMs!) so you could test for a unicode file first - I have some code here in SO somewhere that checks a file to see if it's unicode. Like you say, it's all part of the fun! :-) – Krakau

Delphi's not ideal for this unless you use the RegExp library. I would recommend writing a PERL script to parse the DFM and extract the SQL. I did something similar to convert TTable/TQuery components to the DOA "equivalents" and that involved keeping the pas & dfm in synch as you were modifying the files. Use the right tool for the job. In this case a scripting language with a good implementation of regular expressions. PERL might not be your cup of tea but you should be able to find something you like Python, Ruby? – Unrivalled

@robsoft: a simple /* :-) */ would break the count, comments need to be handled correctly too. And SQL comment syntax is not standardized. Maybe it is easier to copy Delphi's streaming code to read the DFM? – Unhelm

@mjustin - good point, I was thinking about this last night. But I think that each line will always start and end with a ', so instead of parsing the SQL you're really just taking the text from the point that the SQL.Text = (' starts, and removing the leading and trailing ' from all subsequent strings until you get a string that doesn't start with a ' - which would be the start of the next property or component. You just then have to remember to remove the closing bracket on the final string too. The more I think about this now, the more I reckon if you start parsing the SQL you're lost! :-) – Krakau

@mjustin - I've edited my answer to include some code from a DFM. I'm 99.9% sure that there is no need to parse anything inside the strings themselves, just snip the opening and closing quotes of each relevant line. :-) – Krakau

Answer 3

I have created my own DFM parser, tested with 600 source files from both RAD Studio and the company I work at. The parser is written in Go.

https://github.com/gonutz/dfm

You can parse DFM files with it and inspect the in-memory object recursively, looking for properties of type dfm.String.

Inanity

Answer 4

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;

Wright

Answer 5

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.

Unhelm