Using the ED Command Within ISQL to Edit SQL Statements

Before sending SQL statements to the DBMS when you enter the GO command, ISQL acts as a line editor. As you learned in Tip 39, "Using the MS-SQL Server ISQL to Execute SQL Statements from the Command Line or Statements Stored in an ASCII File," the format of the command to start ISQL at the MS-DOS command line is:

ISQL -S -U -P

Note

Substitute the name of your SQL Server for NVBizNet2, and use your own username and password for login if the sa account is not available to you.

Thus, to log in to the NVBizNet2 MS-SQL Server as username sa, perform the following steps:

1. Click your mouse on the Start button, select Programs, and click your mouse on Command Prompt. Windows will start an MS-DOS session.

2. To start ISQL, type ISQL -SNVBizNet2 -Usa -P and press the Enter key. ISQL will display its Ready prompt (1>).

3. Next, enter the SQL SELECT statement:

   SELECT * FROM authors
   

4. Press the Enter key. After ISQL puts your statement in its statement buffer, your screen will appear similar to the following:

   ISQL -SNVBizNet2 -Usa -P
   1> SELECT * FROM authors
   2>
   

Since you did not identify the database you want to use, ISQL will display the following if you enter GO and press the Enter key at the Ready prompt (2>).

 Msg 208, Level 16, State 1, Server NVBIZNET2, Line 1
Invalid object name 'authors'.

Because ISQL is a line editor interface, you cannot move your cursor in front of SELECT and insert a statement. Thus, if you had only ISQL, your only choice would be to enter EXIT or QUIT at the Ready prompt (2>) and start over, this time either adding the -d parameter to the ISQL command line or typing USE in response the first Ready prompt (1>).

Fortunately, ISQL lets you use the MS-DOS full-screen editor.

To start the full screen editor, type ED at a ready prompt (2>) and press the Enter key. ISQL will start the MS-DOS editor and transfer the contents of its statement buffer, similar to that shown in Figure 40.1.

Figure 40.1: The MS-DOS full-screen editor as started by ISQL

To insert the USE statement in front of the SELECT statement, move your cursor in front of the word SELECT. Type USE pubs and press the Enter key. Once you've done that, you will have two statements in the text editor:

 USE pubs
SELECT *  FROM authors

To transfer the contents of the full-screen editor to the ISQL statement buffer, select the File, Exit option. When the editor prompts you to save your changes, press Y. The MS-DOS editor will send its contents to ISQL which will display them as individual lines similar to:

 1> USE pubs
2> SELECT * FROM authors
3>

Now, type GO and press the Enter key to send the USE and SELECT statements to the DBMS. After ISQL displays the query results, type EXIT and press the Enter key to exit ISQL and return to the MS-DOS prompt.

The important things to know are:

• You can work in single-line edit mode by typing your SQL statements in response to each ISQL Ready prompt.

• ISQL stores each statement you enter in its statement buffer.

• You can use a full-screen editor by entering ED in response to an ISQL Ready prompt.

• When you start the full-screen editor (with the ED command), ISQL copies the contents of its statement buffer to the editor screen.

• When you leave the full-screen editor (by selecting the File menu Exit option), ISQL reads the contents of the editor screen into its statement buffer as one statement per editor line.