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. You can use Visual Studio or a free-standing text editor. 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.
In this topic, I will show how to set up Visual Studio and TextPad for ABASQL. The other editors listed above have similar features to TextPad why the section on TextPad should help you to configure these editors, even if you have to figure out some specifics on your own. The reason I give coverage to TextPad is simply because that this is the editor we have used for many years in the development with AbaSec.
You may note that I don't list SQL Server Management Studio (SSMS) here. The solution for Visual Studio works for SSMS as well, but nevertheless I recommend against using SSMS for editing SQL files. What is important, is that when you use AbaPerls, you should never send the SQL directly from the editor window to SQL Server, but you should always go through ABASQL. This is a natural process from an editor like TextPad and it can be somewhat natural from Visual Studio as well, but it's kind of a backward thing to do in SSMS.
There are several advantages with using Visual Studio to edit your SQL code:
AbaPerls uses many file extensions for SQL files, and Visual Studio is not aware of any other extension than .sql. However, Visual Studio permits you to associate file extensions with a certain editor. From Tools->Options select the Text Editor node, and then select the File Extension subnode. Enter the extension and select the
Microsoft SQL Server Data Tools, T-SQL Editor and press Add.
To set up ABASQL with Visual Studio, go the Tools menu and find External Tools, the fourth-to-last item. This opens a configuration dialogue. Here is an example how it is set up for ABASQL:
Title – You can use any name you want. As the picture indicates, it is not unlikely that you want to set up multiple entries for ABASQL.
Command – Here you specify the location of ABASQL.
Arguments – Here you specify the arguments you want to pass to ABASQL. This should always include ‑S for server and ‑d for the database. Depending on how you connect, you may also have to specify ‑P and ‑U for password and user. (If you specify neither, this implies Windows authentication.) You should also specify $(ItemFileName)$(ItemExt) as argument. This tells Visual Studio that you want to operate on the current
file. (Note that you should not use $(ItemPath) which gives you the
full path, as this will override the rule that files are taken from source
control if the file is not checked out.)
Initial directory – This should always be $(ItemDir), so that ABASQL can find other files in the AbaPerls SQL Directory Structure.
Use Output window – This option should always be checked, so that you can see any error messages in the output window in Visual Studio. (If you uncheck this option, the output will appear in a command-line window.)
Prompt for arguments – Here you can do as you like. Checking this option, means that you get the chance to add extra arguments to ABASQL, for instance ‑save if you want debug the resulting SQL from ABASQL. But you may prefer not to get nagged every time. Recall that you can set up multiple tools entries for ABASQL with different settings.
Treat output as Unicode – You should not check this option, else the output from ABASQL will be pure garbage.
Close on Exit – This checkbox is only available if you uncheck Use Output window. If you uncheck both, the command-line window will remain open.
To run the tool, you can select it from the Tools menu. ABASQL will appear on the menu among the other tools. As defined in the screen shot above the two ABASQL entries will appear after Spy++. You can use the Move Up and Move Down entries to change the menu order. If you prefer, you can define keyboard shortcuts for your tools. The dialogue for setting up keyboard shortcuts in Visual Studio is some unwieldy. To define a shortcut for the first ABASQL entry above, you should define a shortcut for Tools.ExternalCommand7 as in this screen shot:
In this example I have tied Ctrl-Alt- to the tool ABASQL TestServer.
Beware that if you later change the order of the external tools, you will need to reassign the shortcuts.
And now to the bad news:
This may or may not be an issue to you. If it is, you should consider using TextPad or some other shareware editor. They typically have this capability.
Why would you use TextPad (or any other editor) rather than Visual Studio? There are a couple of possible reasons, but mainly this is a matter of personal preference, why I see no reason to list them. Use whatever works best for you. The only "hard fact" is that are two pieces of bad news I listed in the previous section.
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.4.2. I recommend 4.7.3. TextPad 5 and later version has been quite much of a disappointment in my opinion. The screen shots in this article are from TextPad 4.7.3. (Actually personally, I have switched to EditPlus in recent years.)
If you use SourceSafe as the version-control system, you can set up SourceSafe so that files open by default in TextPad. 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.)
If you want to use TextPad when your version-control system is TFS, you can of course first check out the file in the Source Control Explorer and then open the file in TextPad. But in the long run this is not particularly convenient.
If you download and install the TFS Power Tools, two other options become available to you.
The Windows Shell Extensions gives you the possibility to check in/out files directly from Windows Explorer. The Shell Extensions recognizes that you are in one of your workspaces, and you can see a small green spot added to the icons for the files that are downloaded from TFS. If you click a file and open the context menu, you will see a submenu Team Foundation Explorer from which you can perform actions against TFS. Rather than doing this from a separate Explorer window, you can also do this from the Open dialogue. in TextPad.
In the Source Control Explorer, you will find that a new item has been added to the context menu, View With. If you select this option, you will see a list of editors. When I tried it, TextPad was already included in that list. If you don't see it, you can use the Add button to add it. You can select Set as Default, and from now on TextPad will be the default editor for that particular extension and you only have to double-click such files and they will open in TextPad. You will still have check them out separately, though.
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. 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.) 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
menu bar 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.
In the Parameters field you enter the command-line switches and
parameters to ABASQL. You will always need to specify ‑S
and ‑d for the server and database. Depending how you connect you may also need to specify ‑U and ‑P
for username and password. The last parameter should always be $FileName which is a TextPad macro
which represents the file in the current
window. (Note that you should not use $file which is filled in by
default, as this gives you the full path and this does not play well with the
idea of you always retrieve a file from TFS when it is not checked out.)
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 the input fields there are six checkboxes. You should always 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 the 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 your 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; the dot is the first character.)
.*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 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.
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 can
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, *.seq, *.tbltyp, *.tbl, *.fkey, *.ins,
*.ix, *.tri, *.syno, *.sqlfun, *.view, *.vtri, *.vix,
*.sqlinc, *.sp, *.postsql, *.assem
Next you are asked for syntax highlighting. 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. (If you would like to have intellisense, this feature is not available in TextPad, but I believe you can use Red Gate's SQL Prompt on top of any editor to achieve this.)
As for the remaining properties of a document class, these are mainly a matter of personal preference, and I will not delve into these.
But check that you have Maintain indentation set for your SQL document class. 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 think tabs are handy, recall the next guy may not have the same tab stops as use. Tabs are a sure recipe for badly formatted code and misery and mayhem in general.
There is a per document-class setting Write Unicode and UTF-8 BOM. Make sure that you have this set for SQL and Perl. TextPad is not really Unicode-able, since it cannot work with the full Unicode character set, but it can still write files in UTF-8 and UTF-16 encodings. When AbaPerls determines the encoding of a file, it looks for a BOM (byte-order mark) in the beginning of the file, and if there is no ANSI, AbaPerls assumes it's looking at an ANSI file, which can lead to errors if the file is actually a Unicode file.
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 handy 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 extensions you want to use with TextPad at a little more than ease than you do in Explorer. It's a good idea to associate all the SQL extensions that AbaPerls uses with TextPad.
Among the many editing features of TextPad, I will briefly mention a few that I find useful:
Copyright © 1996-2014,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under
Perl Artistic License
This page last updated 20-05-19 14:45