Craig Stuntz’s Weblog

OK, I’m not trying to steal Joe White’s thunder here on Delphi macros, but when I plugged Joe’s series, I mentioned that I use Delphi macros for converting SQL created in a query analysis tool into Delphi constants, and commenter Jack asked for more details on this. I thought it would make a better post than comment.

The general idea is that we want to start with an SQL statement, like this:

SELECT
  *
FROM
  MY_TABLE;

…and turn it into a Delphi constant, like this:

const
  MySQL = ‘SELECT ‘
        + ‘  * ‘
        + ‘FROM ‘
        + ‘  MY_TABLE;’;

The first step is to paste the SQL into the code at the appropriate place. We need to type the non-repeating portion of the constant, and then paste in the SQL:

const
  MySQL = ‘SELECT
  *
FROM
  MY_TABLE;

Now, record the macro:

1. Press the End key to go to the end of the line
2. Press Ctrl+Shift+R to start recording
3. Type a space, then the closing single quote
4. Press the down arrow, then the Home key to go to the beginning of the next line
5. Type the appropriate whitespace, then a ‘+’ sign (for string concatenation), then the open single quote character
6. Press the End key to go to the end of line, to be ready for the next time the macro is played back
7. Press Ctrl+Shift+R to stop recording

To use the macro, press and hold Ctrl+Shift+P. The keyboard auto-repeat who repeatedly play back the macro, formatting the rest of the query. Don’t worry about going too far, since you can always press Ctrl+Z to undo if you do. Since the final closing single quote and semicolon are different from how the rest of the lines are formatted, just type them.

By the way, the Delphi editor works really well for editing plain SQL. If you are editing a file with the ‘. SQL’ extension, it does syntax highlighting (and printing) for SQL. You can also use macros like the one described above when creating your SQL or DDL. For example, I often use macros to turn an INSERT statement into a UPDATE statement with parameters or trigger variable references, and similarly to turn a SELECT into a CREATE VIEW statement.

Share This | Email this page to a friend