Modifying an SQL Statement

When DB–Access finds an error in an SQL statement that you are trying to run, the Modify option is highlighted on the SQL menu, and the current statement text and error message are displayed, as Figure 13 shows.

SQL:  New  Run   Modify   Use-editor  Output  Choose  Save  Info  Drop  Exit
Modify the current SQL statements using the SQL editor.

------------------ mystores@dbserver1 -------------- Press CTRL-W for Help ------

CREATE TABLE mystock
   (
   stock_num         SMALLINT,
   manu_code         CHAR(3),
   description       CHAR(15)
   unit_price        MONEY(6),
   unit              CHAR(4),
   unit_descr        CHAR(15),
   PRIMARY KEY (stock_num, manu_code) CONSTRAINT  stock_man_primary,
   FOREIGN KEY (manu_code) REFERENCES manufact
   )




 201: A syntax error has occurred. 

If you press RETURN, DB–Access calls the SQL editor and positions the cursor on the line with the first error. You can correct the error with the SQL editor, or you can press ESC to exit to the SQL menu and select the Use-editor option to edit the statement with your system editor. To exit, perform one of the following steps:

• If you use the SQL editor to make changes, press ESC when you finish editing the statement.
• If you use the system editor to make changes, exit the file according to the convention for that editor.

You then return to the SQL menu, where you can press RETURN to run the statement again.

If the SQL statement is new, the screen is blank. If you want to change or call up this statement with the Choose option, the text of the SQL statement appears on the screen.

If an error occurs while you run an SQL statement, the edit screen contains the error message with a pointer to the likely cause. Figure 14 shows how an editor screen might look after a syntax error. The editor used in this example displays the name of the temporary file assigned to the SQL statement.

CREATE TABLE mystock
   (
   stock_num         SMALLINT,
   manu_code         CHAR(3),
   description       CHAR(15)
   unit_price        MONEY(6),

    ^
#
#201:A syntax error has occurred
#
   unit               CHAR(4),
   unit_descr         CHAR(15),
   PRIMARY KEY (stock_num, manu_code) CONSTRAINT  stock_man_primary,
   FOREIGN KEY (manu_code) REFERENCES manufact
   )





"tmp/rsqa02775.err" 11 lines, 132 characters

Make your corrections to the text. When you finish entering or modifying your SQL statement or statements, exit the editor as you normally do. The SQL menu reappears with the Run option highlighted. The statement text appears in the bottom half of the screen.

Press RETURN to run the statement or select another menu option to save the statement in a command file or direct its output.