Using ABASQL with TextPad

Contents:
    Downloading and Installing TextPad
    Setting up SourceSafe
    Setting up TextPad
        Setting up a Tool for ABASQL
        Setting up a Document Class for SQL Files
        Other TextPad Settings

The most effective way to use ABASQL is from within a text editor that permits you start a command-line program to operate on a window. There are plenty of text editors that are available as freeware or shareware at a reasonable cost. Here is a list of some editors I know of, and links to their sites:

If you search around at sites like download.com, you can find even more.

The editor we have used over the years in AbaSec has been TextPad, and the rest of this text will show you how to set up TextPad and SourceSafe so that you easily can work with SQL files from TextPad. If you prefer to use another editor, it is very likely that this editor too has the necessary capabilities. However, you will have to find out from the description for TextPad on how to do it for your editor.

Downloading and Installing TextPad

You can download TextPad from http://www.textpad.com. Just click on the downloaded executable to install. You find licensing information on the TextPad site. Currently there are two versions of TextPad available for download: 4.7.3 and 5.3.1. I recommend 4.7.3. TextPad 5 has been quite much of a disappointment in my opinion.

Setting up SourceSafe

I recommend that you set up TextPad as the default editor for SourceSafe. You do this under Tools->Options->Editor for viewing files.

Once you have done this, when you right-click on a file, and select Edit, the file will open in TextPad for editing and you will check it out in the same mouse-click.

If you for some reason do not want to use TextPad as your general editor for SourceSafe files, you can add lines like this one to your SS.INI:

.sp = C:\Program Files\TextPad 4\TextPad.exe

This will cause SourceSafe to open files with the extension .SP in TextPad. (A hint in passing: this is very handy to do for .DOC and .XLS so that they open in Word and Excel respectively.)

Setting up TextPad

Setting up a Tool for ABASQL

Under Configure->Preferences in TextPad you get a dialogue with a menu tree to the left. At the bottom at the menu tree is Tools. Select this one, and then select Add and under this menu Program File. This opens a browse dialogue, and you should browse for ABASQL.BAT. Provided that you read this page from within your AbaPerls installation, ABASQL.BAT is in this directory. When you have found ABASQL.BAT and completed the browse dialogue, you can click on the name in the list box, and the name will be open for editing, akin to how you rename files in Explorer. (Alas, F2 does not work.) As it is likely that you will set up at least one tool for each server you work with, possibly even one per database, you should pick a name related to the server/database. Eventually click Apply. The name will now be visible as an item in the Tools menu in the main menubar of TextPad.

Now select the newly added tool to configure it. When you are done, it should look something like this:

The Command field specifies where ABASQL is located. This is on my development machine, so it is not likely that this path applies to you!

In the Parameters field you enter the command-line switches and parameters to ABASQL. Although ABASQL has an abundant of switches, the only ones you are likely to need in TextPad are -Server, -database and -Password. $file is a TextPad macro which represents the file in the current current window.

The Initial folder field is less important, at least if you have set the TextPad option Working folder follows active document. (See below under Other TextPad Settings.) But the default of $FileDir, the directory of the current file, will do no harm.

Below these input field are six checkboxes. You should check Save all documents first and Capture output, and you should uncheck Run minimized and Suppress output.... Prompt for parameters is more of a matter of preference. It will give you a small window where you get the parameters from the Parameters field as a default, so that you can for instance change database for the file you are about to load. This reduces the need to have one tool for each database. Then again, you may find the prompting a hassle. Finally, Sound alert... is purely a matter of convenience. (But since you get different sound events depending on success or failure of ABASQL, it gives valuable feedback.)

The significance of the next input field, Regular expression to match output, may not be immediately clear to you, but filling in this correctly adds tremendous power to the editing experience. To wit, when you get an error message, you can click on that error message in the Command Results window and TextPad will take you the line where the error is. (Well, at least where SQL Server says the error is. It's not always entirely precise.) To achieve this, you have to fill in this field, exactly as above in the picture. I repeat it here so that you easily can cut and paste. (Note that there is no leading space.)

.*Line \([0-9]+\), \(.+\)$

The input in Registers maps to the parentheses. Beware that the default is 1 for File and 2 for Line, so you will have to change these. (Note to those who are using other editors: this is likely to be the point where you may have most problems to get your editor to work well with ABASQL. The error messages from AbaPerls are designed to work with TextPad, sorry.)

Once you have all this in place, press Apply or OK.

To invoke the tool you can select it from the Tools menu. You can also see keyboard shortcuts here, starting with Ctrl+1 for the first tool. In the Configure-> Preferences->Keyboard menu you can bind the tool to another key if you prefer.

If you want to set up more tools, you have to go through the same process. Unfortunately there is no Clone command for tools in TextPad.

Setting up a Document Class for SQL Files

A powerful feature in TextPad is the ability to define document classes. A document class permits you to define different preferences for different types of files.

To create a document class you first have to close all windows. The you you use Configure->Preferences->Document Class and enter a new document class there. Or you can use Configure->New Document Class to use a wizard for the task. Assuming that you use the wizard, you are first asked to specify the members of the class. These are all file the extensions used by AbaPerls for SQL:

*.sql, *.xmlsc, *.typ, *.tbltyp, *.tbl, *.fkey, *.ins, *.ix, *.tri, 
*.syno, *.sqlfun, *.view, *.vtri, *.vix, *.sqlinc, *.sp, *.postsql, *.assem

Next you are asked for syntax highlightning. If you are used to the coloured SQL from Query Analyzer you probably want this one. The AbaPerls distribution comes with a syntax-definition file for TextPad that includes Preppis directives and macros. (You find it the MISC directory of the installation.) At TextPad's web site you can find other syntax files for (T-)SQL. Put the file of your choice in the USER directory of your TextPad installation prior to running the wizard. Then you can select it from the drop-down box.

As for the remaining properties of a document class, these are mainly a matter of personally preference, and I will not delve into these. But check that you have Maintain indentation set for your SQL document class. This alone makes TextPad a better alternative for editing SQL files than Query Analyzer. Also, I cannot escape from pointing you to the Tabulation menu. My strong recommendation is that you check Convert new tabs to spaces and Convert existing tabs.... If you thinks tabs are handy, recall the next guy may not have the same tab stops as use. Tabs is a sure recipe for badly formatted code and misery and mayhem in general.

Other TextPad Settings

Again, other TextPad settings are more a matter of personal preference, but I like to tip you about a few that I have found very useful:

General->Working folder follows active document – This is a very handly option which makes TextPad's Open/Save dialogues to start in the directory of the active document. Once you got used to this one, you will swear over the fact that tools like Word or Excel does not provide it.

I include here a copy of my settings under the File menu:

The intricate thing is the frame for When files are modified by another process. Earlier versions of TextPad had some problems to get this work properly over network drives. The settings I have here works well for me at least.

In the Associated files menu you can associate all extentions you want to use with TextPad at a little more than ease than you do in Explorer. It's a good idea to asscoiate all the SQL extensions that AbaPerls uses.

Among the many editing features of TextPad, I will briefely mention a few that I find useful:

Rectangular cut-and-paste.
Select text and use tab of shift+tab to increase/decrease indent.
Search/replace with regular expressions. (Alas, the syntax is not exactly the same as in Perl.)
Clip library, so you can click to paste in commonly use code snippets, for instance for error checking.
Search in files, and double-click on the matches to get to them.
Rename a file from within TextPad with F2.