SQL Editor

Introduction

One of the DbSchema embedded tools is the SQL Editor. The editor opens inside the layout and can be reopen from the menu. SQL editor text is saved in the model file and can be reopen from the Query Tools 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 require to be commited to become effective in the database. Alternative you can 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 table
• Executing the script will show result as plain text. Multiple results will show together in the same pane.
• The Run Script menu option is a combo with options for Ignore Errors and Autocommit. If the ignore errors options is not enabled, the SQL Editor will stop when an error is encounter.

Edit Data in the Result Pane

For most of the databases, data can be edited 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 file. Choosing this will execute the query again and will save the complete content to file.
This option can be very useful when the result is very large and can cause memory issues.

Execute Groovy Scripts

The SQL editor can be also used for running Groovy scripts. In the editor menu there is a combo where you can switch from SQL to Groovy. Groovy is a Java based language used in products like Gradle. Please check Google for Groovy tutorials.

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

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

From Groovy 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 :

Sample Groovy Scripts can be found on the Automation and Scripting page.

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');