(This post was originally published April 23, 2009)
Using UltraEdit’s powerful external tool integration, this article will show you how to submit an SQL query directly from UltraEdit and have the results returned directly to the editor.
This example uses Microsoft SQL Server 2000, but the basic technique can be applied to most any database tool.
Using the Tool Configuration option (in the Advanced menu), you can define external tools to be used within your UltraEdit (UE) session. Mostly this is used to drive language compilers so you can code and compile directly within UE, but we’ll use it to drive a command line query tool. SQL Server comes with a couple of command line query tools (iSQL and oSQL), which both operate in a similar manner. Although, I believe that iSQL is legacy and being phased out, so we’ll use oSQL.
From UE’s Advanced menu, select Tool Configuration. On the Command tab of the dialog, pick a name for the Menu Item Name box (this will be displayed as a menu item under the Advanced menu).
Next, fill in the Command Line box (substitute text in italics with specifics for your database):
osql -S ServerName,Port -d DatabaseName -w 500 -U UserName -P Passwrod -q”%sel%”
where —
ServerName,Port is the name and port number of the server where your database resides (the default port number is 1433 and can be omitted if you’ve never changed it).
DatabaseName is the name of the database within SQL Server (example: NorthWind)
UserName is the name of the user account within the database that will run the query
Password is the password of the above user account
Next, fill in the Working Directory box with the location of the osql.exe file (the default install path for SQL Server 2000 is: C:Program FilesMicrosoft SQL Server80ToolsBinn ).
On the Options tab, make sure that Dos Program is selected (since osql.exe is a DOS application). You can also select options to save your files before running the query (probably a good idea).
On the Output tab select the options: Output to List Box, Capture Output and No Replace. You can experiment with the other options depending on how you wish to utilize this tool for your situation.
Be sure to click Apply, to save your settings. You will now see a menu item under the Advanced menu… Time to test it out!
Create a new document within UE, and type a simple query. For example:
SELECT count(*) FROM TableName
Highlight the query, and select your new menu item from the Advanced menu (or hit the shortcut keys — the first Tool entry is Ctrl+Shift+0). Your query will execute and the results will be displayed in a new document within UE. Easy as that!
There are many ways to use this tool configuration. Let’s say that you are constantly typing up a weekly status report on sales figures, you could use this tool (with the option Replace selected text with: set to Captured Output) and type something like this:
The total sales for the month of July is select sum(sales) from SalesDB where month=’07’
Then simply highlight the embedded select at the end of that sentence, run your query, and let UE replace the selected text with the results from the database.
That’s just a really simple idea, but you can now see the possibilities. You can experiment with any DOS or Windows program that returns some form of output, just define it as a Tool and easily run it from within UE (I’ve used it to capture ipconfig and ping information directly into the editor). Leave a comment if you have any questions or discover any novel uses for the tool feature in UE.
Comments copied from the original blog entry:
Paul
Thursday, September 16, 2010 7:10 PM
Again thanks for a simple to read article. I have been asked to present a how to on SQL. Query language, I comprehend when applying myself, but to explain to a staff workgroup is another matter. I am hoping that I can adopt some of your advice when undertaking our workshop practice for developing our internal web site.
Martin Varesio
Wednesday, March 02, 2011 6:14 AM
Yes, the Options tab, make sure that Dos Program is selected (since osql.exe is a DOS application). You can also select options to save your files before running the query!!
tod
Friday, April 01, 2011 10:10 AM
I’m trying to run this with oracle sqlplus and getting this error:
“The system cannot find the file specified.”
It will launch sqlplus and connect to the database with this as the command
“D:oracleproduct11.2.0client_1BINsqlplus.exe -l login/pwd@instance”
but will not run the highlighted sql.
I’m using something simple: select * from dual;
any thoughts ? Thanks
mkizer
Saturday, April 02, 2011 8:16 PM
I haven’t tried it with sqlplus (I mainly use SQL Developer or Toad for Oracle stuff). SQLplus is a GUI app so I don’t know if it will work or not. I haven’t looked, but does Oracle include a commandline version as well? I’ll have to check into it next week at work.
tod
Sunday, April 03, 2011 4:30 AM
cool. let me know what you find. sqlplus is the cmd line. sqlplusw is the GUI
mkizer
Friday, April 08, 2011 1:25 PM
OK, I looked into SQL*Plus a bit, but it appears that it can only receive a filename in the command string, not a SQL query (like SQL Server’s osql can). So, it looks like this method won’t work for SQL*Plus.
There may be other command line Oracle tools available that may allow this, but I am not aware of any offhand.
dia
Wednesday, March 26, 2014 10:17 AM
I was looking for how to integrate SMSS into the environment.. THANK YOU. this centralizes work efficiently. I added this link on my LinkedIn, so you may get a few more hits.
chuck
Thursday, September 18, 2014 10:48 AM
It’s possible to use SQLplus in the same fashion, it just needs a few adjustments.
On my system, sqlplus.exe is in the PATH, so I don’t need to fill in either the Working Directory or include the full path in the command. Also, we connect to Oracle using tnsnames, and “instance” represents a valid name in my tnsnames.ora file.
Using the following for the Command (with appropriate replacements for user, pw, and instance) should work:
echo exit | sqlplus user/pw@instance @%F
The generic command format to run a SQL script from the command line is
sqlplus user/pw@instance @filename.sql
which will run whatever is in filename.sql. If there is no exit command in the sql file, sqlplus will remain open.
The Command I used above has two differences from the generic version:
1) UltraEdit replaces %F with the name of the currently active file.
2) It pipes an exit to sqlplus, so it will successfully exit whether there is an exit command in the file or not.
UltraEdit can only display the results after sqlplus closes, so the exit ensures that that happens.