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.
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 :
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. |
Groovy sample scripts are in the Code Samples Dialog accessible from the SQL Editor Help menu.
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');