Executing SQL Statements

In the SQL Commander, you can execute one or multiple statements. You can also control if the execution should stop or continue when the execution of a statement results in a warning or error.

Execute a Script with Multiple Statements

Use the SQL Commander->Execute main menu operation to execute the SQL in the SQL Commander editor. The SQL Commander executes the statements one by one and indicates the progress in the log area. The currently selected Database Connection is used for all statements. The SQL Commander does not support executing SQLs for multiple database connections in one batch.

DbVisualizer uses the delimiters specified in the Tool Properties dialog, in the General / SQL Commander / Statement Delimiters category under the General tab, to separate one statement from the next. Usually semicolon ";" following the actual statement or "go" which should be the only command on a new line directly after the statement (then with no semicolon after) that should be executed. The "go" command supports setting the number of times the statement should be executed. For example "go 5" will then execute the statement 5 times.

The result of the execution is displayed in the results area based on the type of results result(s) that are returned. If there are several results and an error occurred in one of them, the Log tab is automatically displayed to indicate the error.

If you select a statement in the SQL editor and choose SQL Commander->Execute main menu option, only the selected statement is executed. This is a useful feature when you have several SQL statements in the SQL editor and you just want to execute one or a few of the statements.

Note the option Change Default Values in the SQL Commander->SQL Commander Options. This offers a shortcut to navigate to the Connection properties section for the options. Setting the options in the Connection properties will result in that these values will be the default for SQL Commanders opened for this connection.

Execute Only the Current Statement

The SQL Commander->Execute Current operation is useful when you have a script with several SQL statements. It lets you execute the statement at the cursor position without first having to select the SQL statement. The default key binding for execute current is Ctrl-PERIOD (Ctrl-.).

Execute Buffer

The SQL Commander->Execute Buffer sends all of the content in the SQL editor to the database in a single run. This is useful when executing complex SQL statements such as CREATE STORE PROCEDURE (or similar) where statement splitting on semicolon must not be done as with SQL Commander->Execute and SQL Commander->Execute Current.

Control Execution after a Warning or an Error

You can control whether subsequent statements should be executed when a statement results in an error, a warning or returns or affects no rows.

Open Tools->Tool Properties and select the SQL Commander category under the General tab. There you find Stop on Error,Stop on SQL Warning and Stop on No Rows check boxes for enabling these features in all SQL Commander tabs.

Alternatively, you can use DbVisualizer client side commands to enable or disable these features in a script.

@stop on error;
@stop on sqlwarning;
@stop on norows;

@continue on error;
@continue on sqlwarning;
@continue on norows;

Stopping Execution

Apart from clicking the Stop the current script execution button the client side command "@stop now" may be used to stop the script execution.

Handling Connection loss

If DbVisualizer detects that the database connection has been lost, during the execution of a script, DbVisualizer can automatically restore the lost connection. To enable this change the settings in the Handling loss of Connection section in the Connection Properties tab and the SQL Commander category

• No Reconnect: Do not automatically reconnect
• Reconnect: Try to automatically restore the connection. If the connection was successfully restored the script execution is continued. Note that automatically restoring a connection is only possible if the connection is in auto-commit mode.
• Reconnect and re-execute: As Reconnect and also try to re-execute the command that encountered the loss of connection.