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

Here is a DFM Parser from Felix Colibri

DFM Parser

Here is an interesting tool for doing stuff like this

YACC

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.