SQL Editor

Introduction

One of the DbSchema embedded tools is the SQL Editor. The editor opens inside the layout, is saved in the model file, and can be reopened from the menu.

Auto-Complete

The editor is auto-complete enabled. Just type CTRL + SPACE to get the complete pop-up.

Commit and Rollback

Database operations like UPDATE, INSERT, DELETE, and sometimes CREATE TABLE requires COMMIT to become effective in the database. Alternatively, press ROLLBACK to cancel them.

Execute Single Query or Scripts

The editor menu has two distinct buttons, for executing a query or for executing SQL scripts.

• Executing a query will execute the selected text or the statement at the caret position ( ending with ; )
• Executing a query will show the result as a table
• Executing the script will show the result as plain text. Multiple results will show together in the same pane.
• The Run Script menu option is a combo with options for ignoring errors and auto-commit. If the ignore-errors option is disabled, the SQL Editor will stop by errors.

Edit Data in the Result Pane

For most of the databases, you can edit the data directly in the result pane. Double-click any of the result cells to edit.

Save result data to file

From the result pane, you can choose the save button to save the result directly to the file. This will execute the query one more time and will save the complete content to the file.
This option can be useful when the result is large and cannot be loaded on the screen.

Execute Groovy Scripts

The SQL editor can execute Groovy scripts. In the editor menu, there is an option to switch from SQL to Groovy. Groovy is a Java-based scripting language. In the Help menu, there are Code Samples where you can learn it.

Here a simple groovy script listing a column from the table address:

sql.eachRow("select * from address") { r ->
println "Gromit likes ${r.address_id}"
}

Using Groovy scripts, you can access most of the DbSchema classes and methods. Details in DbSchema API Java Documentation.

The following variables are exposed directly to the Groovy script engine :

Groovy sample scripts are in the Code Samples Dialog accessible from the SQL Editor Help menu.

Execute JS Scripts

The SQL editor can execute JavaScript code. In the editor menu is a combo where you can switch to JavaScript.

The variables below can be accessed directly by the script engine :

Here a simple groovy script listing two columns from a table 'customer':

var stmt = sql.prepareStatement( "SELECT first_name, last_name FROM customer");
var rs = stmt.executeQuery();

while (rs.next()) {
out.print(rs.getString("FIRST_NAME") + " " + rs.getString("LAST_NAME") + "\n")
}
out.print ('Done');

Dropping SQL Editors

SQL Editors can be dropped by right-clicking the editor in the structure tree, under the 'Layouts'.

When closing an SQL Editor, DbSchema will ask if you wish to preserve the editor in the design model ( close the editor but keep a copy in the design model, so it can be reopen at any time ), or close and drop from the design model as well.