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.
SQL editor auto-completion

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.
DML data changes require commit or rollback

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.
How to run a script from the SQL Editor

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.
How to edit data in the SQL editor result pane

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.
How to save query execution result to file

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 :

sql The physical connection to the database.
model The DbSchema model with schemes, tables, columns, etc..
out The PrintStream out console output stream, visible in the Script Result Pane.

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 :

sql The physical connection to the database.
project The DbSchema model, with schemes, tables, columns, etc..
out The PrintStream out console output stream, visible in the Script Result Pane.

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');
About JavaScript language you will find plenty of documentation on web.