SQL Editor

One of the DbSchema embedded tools is the SQL Editor. The editor is auto complete enabled. Just type CTRL + SPACE to get the complete pop-up.

The execute single query will execute the current selected text or the phrase ending with an ';'. The execute script will execute the complete SQL Editor text. By default the editor does not commit the database changes. You have to press the commit or rollback buttons in menu for this. For execute script option, there are two options:

Executing one single query will paginate the result. This because of memory limits. Get the complete result using 'save' from the result pane

Press one of the column in the result to order. The order will work for the displayed rows, not for the complete query. If the number of the records exceeds the result pane capacity, you have to use ORDER BY in the executed statement.

For scripts, the execution works than like this:

  • First is checked if the current text is a PLSQL block. A PLSQL block should be ended with an / . Before and after its content should have at least one empty line.
  • If is no PLSQL, DbSchema considers each sentance ended with ; and executes it.

Java Script Editor

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 project, 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.

Groovy Editor

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.
project The DbSchema project, 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.