Executing SQL

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Working with SQL >

Executing SQL

SQL statements that have been created in the SQL editor or SQL files that have been opened in the SQL Editor can be executed directly from the SQL Editor. The Execute command can be called via the SQL Editor menu, via a keyboard shortcut or by clicking a toolbar icon.

 

 

To execute SQL in an SQL Editor window:

1.Make the SQL Editor window in which you want to execute SQL the active window.
2.Optionally, select the statement or statements you want to execute.
3.Click the Execute ic_execute-sql button, or select the menu option SQL Editor | Execute, or press F5.
If the data source is not connected, a popup message is displayed asking whether you would like to connect to the data source (provided that the SQL file is not set to Offline).
4.If applicable, click Yes in the message box to connect to the data source.
All SQL statements that are in the SQL Editor, or the selected statements, respectively, are executed. The results appear in separate result windows, one for each statement.
5.Click the respective tabs to access the individual results.

 

Alternatively, you can also execute SQL scripts in the Project window if the SQL files are included in the project.

 

 

To execute an SQL file:

1.In the Project window, select an SQL file that you want to execute.
2.Right-click the file and choose Execute SQL from the context menu. If the data source is not connected, a popup message is displayed asking whether you would like to connect to the data source.
3.If applicable, click Connect in the message box to connect to the data source. The SQL script opens in a new SQL Editor window and is executed immediately.

 

Execute for Data Editing

When supported in the active data source connection, you can also execute a SELECT statement for data editing in the Result window. In this case, the Execute for Data Editing ic_edit-data button will be enabled in the toolbar of the SQL Editor and the Execute for Data Editing command will be available in the SQL Editor menu.

 

 

To execute SELECT statements for data editing:

1.Generate a SELECT statement in an SQL Editor window that is connected to a data source that supports data editing from within the SQL Editor.
2.Click the Execute for Data Editing ic_edit-data button or select the menu option SQL Editor | Execute for Data Editing. The Result window is switched into the Editing mode and you can edit the database data directly in the result grid.

 

Asynchronous execution and retrieval

DatabaseSpy uses asynchronous execution and retrieval when a query is started in the SQL Editor or from within a Design Editor or Data Comparison window. While the execution is in progress, DatabaseSpy displays a message in the Message window.

msg_exec-progress

In large databases, you can cancel the execution by clicking the Stop Execution/Retrieval ic_stop button in the SQL Editor toolbar while the execution is in progress and retrieval has not yet been started. This way, you can abort an execution when you notice that the retrieval would take too much time, and refine the query.

 

After you have canceled an execution or retrieval, an "Execution was cancelled" message is displayed in the status bar of the Result window, and you can edit your query as required and restart the execution.

 

Defining the execution mode

You can choose whether you want to send the SQL that is contained in the active SQL Editor window to the database engine as a whole, or as separate batches. The Properties window provides the options SQL Editor should require semicolons and Group statements for execution with for this purpose.

 

The Group statements for execution with list provides the following options:

 

Semicolons: The parser uses semicolons as statement separator. Any SQL code that is terminated by a semicolon is considered a statement and sent to the server separately. If semicolons are missing from your SQL script, a dialog box may prompt you to add them automatically.
No Grouping: The script is sent to the server as a whole, without any modification. If the script contains multiple statements, and if the driver is known not to support executing multiple statements in bulk, this option is not meaningful. In this case, a dialog box may prompt you about this limitation.
SQL Grammar: The respective flavor of SQL grammar is used to separate the statements when sending them to the server.
GO keyword: A GO keyword must be present in the SQL script to separate execution blocks.

 

Showing execution groups

The toolbar in the SQL Editor provides the Show groupings for execution ic_show-groupings button which allows for a graphical illustration of the statement blocks that will be sent to the database engine for execution.

sql_show-groupings

In the screenshot above, the groupings for execution are displayed when the "Semicolons" option is selected in the Group statements for execution with drop-down list.

 

Execution timeout

You can define a timeout for the execution of queries in the Retrieval settings group box of the SQL Editor options. DatabaseSpy will attempt to stop retrieving data after the specified amount of time has elapsed. Note that the database kind and database driver must support setting a command timeout; otherwise, the query will continue running beyond the timeout period. In this case, you can stop execution using the Stop Execution/Retrieval ic_stop button; however, be aware that some drivers might not support cancelling the execution.

 

Abort execution on error

When the SQL Editor window is open, the Properties window includes a property called Abort execution on error. This option may be useful when you execute SQL scripts that contain multiple statements, and, if any statement fails, you would like the execution to stop at the corresponding statement. To enable this behavior, select the Abort execution on error check box.

ds_abort_execution

Properties window

Consider the following example: you would like to run against the database a script which contains multiple INSERT statements. If the Abort execution on error check box is enabled, and an INSERT statement fails, the script will stop running at the INSERT statement which failed. In this case, any subsequent INSERT statements will not be executed. If the Abort execution on error check box is disabled, and an INSERT statement fails, the script will proceed to executing the rest of the INSERT statements, until the end of the script.

 

The execution status of the script (including the number of affected rows) can be viewed in the Message window, along with additional information about the error which occurred (if any). Click the underlined text in the Messages window to jump to the corresponding line in the SQL Editor.


© 2019 Altova GmbH