Executing SQL

www.altova.com このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  SQL との作業 >

Executing SQL

SQL statements that have been created in the SQL エディター or SQL files that have been opened in the SQL エディター can be executed directly from the SQL エディター. The 実行 command can be called via the SQL エディター menu, via a キーボードショートカット or by clicking a ツールバー アイコン.

 

 

SQL エディターウィンドウ内で SQL を実行する方法:

1.Make the SQL エディターウィンドウ in which you want to execute SQL the active window.
2.任意で、 select the statement or statements you want to execute.
3.実行 ic_execute-sql ボタンをクリック、または、 select メニューオプション SQL エディター | 実行, or press F5.
データソースが接続されていない場合、 a popup message is displayed asking whether you would like to connect to データソース (provided that the SQL file is not set to Offline).
4.適応できる場合、 click Yes in the message box to connect to データソース.
All SQL statements that are SQL エディター内で、 or the selected statements, 個々のly, are executed. The results appear in separate 結果ウィンドウs, one for each statement.
5.Click the 個々の tabs to access the individual results.

 

または、you can also execute SQL scripts in プロジェクトウィンドウ if the SQL files are included in the project.

 

 

SQL ファイルを実行する方法:

1.プロジェクトウィンドウ内で、 実行する SQL ファイルを選択します。
2.ファイルを右クリックし、 「実行 SQL」 をコンテキストメニューから選択します。 データソースが接続されていない場合、 a popup message is displayed asking whether you would like to connect to データソース.
3.適応できる場合、 click Connect in the message box to connect to データソース. The SQL script opens in a new SQL エディターウィンドウ and is executed immediately.

 

データの編集のための実行

When supported in the active data source connection, you can also execute a SELECT statement for data editing in the 結果ウィンドウ. In this case, the データの編集のための実行 ic_edit-data ボタン will be enabled in the ツールバー of the SQL エディター and the データの編集のための実行 command will be available in the SQL エディター メニュー。

 

 

To execute SELECT statements for data editing:

1.Generate a SELECT statement in an SQL エディターウィンドウ that is connected to a data source that supports data editing from within the SQL エディター.
2.「データの編集のための実行」 ic_edit-data ボタンをクリック、または、「SQL エディター | データの編集のための実行」 メニューオプションを選択します。 . The 結果ウィンドウ 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 エディター or from within a デザインエディター or データ比較ウィンドウ. While the execution is in progress, DatabaseSpy displays a message in the メッセージ ウィンドウ.

msg_exec-progress

In large databases, you can cancel the execution by clicking the Stop Execution/Retrieval ic_stop ボタン in the SQL エディター ツールバー 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 ステータスバー of the 結果ウィンドウ, 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 エディターウィンドウ to the database engine as a whole, or as separate batches. プロパティウィンドウ provides the options SQL エディター 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 ダイアログボックス 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 ダイアログボックス may prompt you about this limitation.
SQL Grammar: The 個々の 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.

 

実行グループの表示

The ツールバー in the SQL エディター provides the Show groupings for execution ic_show-groupings ボタン which allows for a graphical illustration of the statement blocks that will be sent to the database engine for execution.

sql_show-groupings

上のスクリーンショットでは、 the groupings for execution are displayed when the "Semicolons" option is selected in the Group statements for execution with ドロップダウンリスト.

 

実行のタイムアウト

You can define a timeout for the execution of queries in the Retrieval settings グループボックス of the SQL エディターオプション. 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 ボタン; however, be aware that some drivers might not support cancelling the execution.

 

Abort execution on error

When the SQL エディターウィンドウ is open, プロパティウィンドウ 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 チェックボックス.

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 チェックボックス 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 チェックボックス 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 メッセージ ウィンドウ, 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 エディター.


(C) 2019 Altova GmbH