Using EditPlus as a TSQL Editor

1. Motivation

As we all know, the TSQL editors supplied with Microsoft SQL Server 2000 (Query Analyzer and Enterprise Manager) are just barely passable. To be honest, I don't know how people can work in the environment doing anything nontrivial (it's slightly better in 2005, but still not as good as EditPlus). Some of the more annoying facets of working with the text editor supplied with Microsoft SQL Server 2000 include:

• No auto line tabbing. Every time you hit Enter, the editor brings the tab back to the beginning of the line. Very annoying for those of us that insist on writing neat, organized code.
• Low undo buffer. I don't know what the maximum number of undos is and I don't know if it can actually be increased (I know I've tried), but the levels of undos is limited, which makes it a pain in the ass to work with large pieces of code.
• Can't reload your workspace. Basically, you have to save all of your working windows to files and then manually reopen them...very annoying, especially for those of us that turn our computers off at night.
• No regular expression support. This is a big WTF. Why can't Microsoft just standardize the text editor across all of the their products?
• No macro/keystroke recording support.
• And on and on...

There's probably a whole lot more, but I'm not harping on this.

At some point, I think it was when I was working at ITT, I just got fed up with working with the default toolset. Being the cheap bastard that I am ;), I refused to use any IDEs that I have to pay for (which excludes some well known ones like Toad). I somehow came across Microsoft's little known osql.exe tool. After toying around with it a bit, I finally made the connection between the executable and EditPlus's ability to utilize external tools! Yes, I was in heaven! With a little bit of exploration, I was able to hook up EditPlus to work almost exactly like the default IDEs while eliminating a lot of the BS!

So for the sake of all SQL Server developers (note that osql.exe also allows ODBC DSNs, so, theoretically, it should work for any DBs that support ODBC) out there, this mini-workshop goes over how to configure EditPlus to work with OSQL to enable working with TSQL in EditPlus instead or the default IDEs.

Side note: You've heard the slogan "You've tried the rest, now try the best"? Well, this is perfectly fitting for EditPlus. In my opinion, no text editor can beat EditPlus considering all of the factors such as portability, configurability, customizability, price, speed, and features. I'll have to do an EditPlus appreciation article some other time :) It's been my primary text editor all throughout college and my professional career for doing everything from Prolog to C++ to Java and of course DHTML.

2. Objective

The objective of this workshop is to learn how to hook up EditPlus with osql.exe via EditPlus's ability to support external tools. After completing this workshop, you should have an ass kicking TSQL development environment for free! While EditPlus does ask you to register, rest assured that you can keep "evaluating" it after the 30 day trial (one of these days, I swear I'm mailing them a check).

3. Auxiliary Files/Prerequisites

To try this out for yourself, you will need the following:

• I'll assume that you have the SQL Server client tools installed as we need osql.exe.
• The "pubs" database. If it wasn't installed, you can grab it from Microsoft's website and follow the directions for creating the database. I will be using this in my demos, but you can feel free to use any existing database that you have.
• EditPlus. Bestest. Editor. EVAR!!!

4. Install EditPlus

This should be pretty self explanatory no? Note that EditPlus can be installed to and run from a floppy disk. Yes, it's that portable (although I think you have to remove some of the default autocomplete files). Once installed, open up EditPlus by either clicking on the shortcut in your programs menu or the executable itself if you didn't install to your programs menu. I'm not going to cover customizing the UI in this article (maybe some other time); but you can easily find other resources around the web if you'd like to do that now.

The one thing that we need to do is to add SQL syntax support. The EditPlus website has a large list of user files which contain syntax, auto-complete, and clip-text files for different languages. For this exercise, we'll need a TSQL set. Once you've downloaded the .zip file, unzip the file and copy the contents into the same directory where EditPlus was installed (you can put them anywhere you want, but for simplicity's sake, it's easiest to keep them organized in the same directory as EditPlus).

To set up the syntax and auto-complete files:

1. Select "Tools"->"Preferences" from the main menu.
2. Select the "Settings & syntax" node in the tree.
3. Click the "Add" button to add a new entry for SQL.
4. Enter "SQL" into the "Description" and "sql" into the "File extensions" textboxes respectively.
5. In the "Syntax file" textbox, enter or browse to the path of the .stx file we copied earlier.
6. (optional) In the "Auto completion" textbox, enter or browse to the path of the .acp file we copied earlier.

Here are my settings for reference:

That should do it. Click "Save" and save the settings. Feel free to take this time now to adjust the syntax colors, tabbing, and other options specific to the language. You can also customize the syntax and auto-complete files easily by opening the .stx and .acp files in EditPlus. The format is very simple (I'm not going to cover it here), so it shouldn't be hard to modify the existing entries or copy and modify them yourself. Note that after editing the files, you may need to refresh the STX/ACP settings by going to "Document"->"Refresh STX/ACP" in the main menu.

5. Find the osql.exe Executable

Now that we've got the syntax files set up, we can move on to setting up the custom tool. We'll need the path to the osql.exe executable to hook up EditPlus. On my machine, the path is C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe.

6. Add a New User Tool

Note: Don't worry about the differences in the interface in the screen capture, look for the menus and icons.

I'll assume that you have your server and login information for your instance of SQL Server. In EditPlus, navigate to "Tools"->"Configure User Tools".

This should open the preferences window and highlight the "User Tools" tree node. From the dropdown, select any of the tool groups and feel free to rename it while we're here (I'm going to name mine "pubs Database Tools"). What I like to do is to create a tool group for each of the projects I'm working on for the various external tools I may use.

After setting up the tool group, we need to add a new tool. Click the "Add Tool" button and select "Program" from the submenu. Now there's four pieces of information that you will have to enter:

Menu text: Enter any name you would like. Using the pubs database as my sample, I will name this "pubs Connection".

Command: This is the path to the osql.exe executable, excluding commandline parameters. Enter the path to the executable, and, if necessary, use double quotes to enclose the entire path (necessary if there is whitespace in the path). In my case, I enter "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" (keep the double quotes since there is whitespace in the string).

Argument: Here's where it gets a bit tricky. If you go to "Start"->"Run" and type in "cmd", you can open the command window. In the command window, type the path to the executable followed by "?" to see all of the available options.

There are numerous different variations that you can use to configure your tool, I'll be covering two of them that I use most often. The argument string should be in the following format:

In my case, the agrument string looks like so:

Looking at the options in the command window (see screenshot above), we see that:

• -S is for the name or address of the SQL server instance we want to connect to.
• -U is for the name of the user to connect as.
• -P is for the password to use when authenticating.
• -d specifies the database to connect to.
• -i specifies the input file name which contains the TSQL we want to execute against the database. The value should be in double quotes since there might be a space in the path.
• -n specifies to remove numbering.

Note the token $(FileName) in the argument string. EditPlus understands a set of macros which allows you to create very granular argument strings. You can play around with the different macros to customize how you prefer working with your SQL files.

Initial Directory: This is the initial directory for the context of the command. In our case, the macro $(FileDir) should be used to expand it to the location of the currenly active file.

Last thing to do is to check the "Capture Output" checkbox. Okay, we're all done with the first entry. Your configuration should look similar:

7. Add Another User Tool for TSQL Fragments

The user tool that we just configured executes all of the TSQL statements in the file specified by $(FileName). So what if you only want to execute a snippet of TSQL? I'm a step ahead of ya ;-)

Go ahead and create another user tool just like previously. I'm going to name this one "pubs Connection (Snippet)". The only thing different this time around is the argument string. This time, we want it to look like:

In my case, the agrument string looks like so:

The four new switches are:

• -Q is for the query snippet that we want to execute. $(CurSel) expands to the currently selected text in the editor. Note that the carriage return character breaks the query, so you can only execute single line query snippets. Fortunately, EditPlus has a nice little tool to join lines of text by simply pressing Ctrl+Alt+J.
• -w is for the width of the display column (if your query returns results). I picked 3000, you can pick any value you like.
• -s is for the column seperator. I'm using the "pipe" symbole.
• -p turns on printing of statistics.

At this point, your configuration should look like so:

8. Using the User Tools

Okay, now that we have them set up, we can go ahead and add some shortcut buttons to our menu bar to make it easier to call the tools. In the main menu, go to "Tools"->"Preferences" and select the "Toolbar" node. In the "Buttons" dropdown, select "User" and make sure that "Standard" is selected in the "Toolbars" dropdown. Scroll down in the "Buttons" window and find a symbol that looks like a hammer with a little number in the upper left corner. Scroll down in the "Toolbars" window and find where you want to place the button. Click the "-->" button to add the button to the tool bar.

Alternatively, the toolbars can also be accessed by using Alt+1 or Alt+2.

Now it's time to test :) For our first test, we'll just create a simple stored procedure to retrieve the book title and publisher name from the pubs database.

Open a new file in EditPlus and enter the following text:

/*-----------------------------------------------------------
[Name]
    SelecttitleAndPublisher
    (Proc.Pubs.SelectTitleAndPublisher.sql)
[Purpose]
    Selects a list of book titles and publishers.
-----------------------------------------------------------*/
CREATE PROCEDURE SelectTitleAndPublisher
AS
BEGIN
    SELECT
        titles.title,
        publishers.pub_name
    FROM
        titles
        JOIN
        publishers
            ON titles.pub_id = publishers.pub_i
END

Save this file (mine is named Proc.Pubs.SelectTitleAndPublisher.sql) and hit Alt+1 to run the SQL statement.

Oops! Looks like we made a small boo boo in the SQL. You should see the following in your output window:

Look at that, we left out the "d" in "pub_id". Type in the "d" and hit Alt+1 again. This time we should see it complete successfully :-)

Let's try out the second user tool as well. This time, we want to execute the following SQL:

Make sure that you highlight (select) the text in the text editor and then execute the user tool (tool #2). You should see the following output:

9. Wrap Up

That wraps up this workshop. EditPlus is a great editing environment for working with TSQL and with the addition of the custom user tools, there's no reason to look back :) The only thing that's lacking is the output window. Unfortunately, the results are only returned as text, so it can get messy when testing in EditPlus.

Aside from that EditPlus addresses each of the issues that we listed in the "Motivation" section and adds a lot of features to boot! Note that in the preferences, under the main node, there is an option to "Reload working files on startup", which means that if you save your SQL files and close the editor, you'll automatically regain your working environment when you open the editor again.

Typically, I'll do all of my authoring in EditPlus, using SQL Management Studio or Query Analyzer to test and check results. What's good about working this way is that I can use Subversion as source control on the .sql files.

By the way, it's easy to see how EditPlus can serve as an execellent tool for all sorts of development that can use an executable to perform tasks such as compilation. It would work very well with Ant or NAnt.

If you have any comments, problems with the workshop, or questions, please feel free to leave a comment in the thread or email me.

10. Additional Resources

A collection of resources from around the web so that you can get the most out of EditPlus:

• Printer Friendly Format
• EditPlus Wiki (http://www.editplus.info/wiki/Main_Page)
• EditPlus Quick Tutorial (http://www.terminallearning.com/.../editplus.html)
• EditPlus User Files (http://editplus.com/files.html)
• OSQL MSDN Reference (http://msdn.microsoft.com/.../cp_osql_1wxl.asp)