SQL Editor

The SQL Editor runs SQL in the database that DbSchema is connected to. You write a query, run it, and read the rows in a grid under the editor. The SQL Editor works in every edition of DbSchema, Community included.

The SQL Editor with a query that counts the orders of each customer, and the result grid under it

Write and run a query

Auto-complete suggests table and column names while you type, so you do not need to remember them.

  1. Press Editor in the toolbar and choose SQL Editor. You can also choose Query → SQL Editor, or press Ctrl+E.
  2. The editor opens under the diagram. To give it the whole diagram area, double-click its tab.
  3. Type the query. A list of matching names opens under the cursor.
  4. Press Enter to take the highlighted name, or keep typing.
  5. Press Run, or Ctrl+Enter.

To start from one table, right-click it on the diagram and choose SQL Editor. The editor opens with a SELECT of that table's columns. Double-click the editor's tab again to bring the diagram back.

Run runs the selected text. If nothing is selected, it runs the statement at the cursor, which DbSchema highlights. The rows show in the result grid under the editor.

If the list does not open while you type, press Ctrl+Space. Edit → Auto Popup Completion Menu turns the automatic list on or off.

Run a script

A script is several statements, each ending with ;. Run Script, or Ctrl+Shift+Enter, runs the selected text or the whole editor, one statement after the other. The results show as text, one block for each statement.

Two SELECT statements run as a script, with the text result of the first one under the editor

The arrow next to Run Script has two options:

  • Ignore Errors: the script goes on after a statement fails. Without it, the script stops at the first error.
  • Auto Commit: each statement that succeeds is committed at once.

The SQL button in the editor's toolbar switches the language to Java Groovy or JavaScript. A script in these languages can read the database and the DbSchema model. See the Automation API for what such a script can use.

Save the result to a file

The save button in the toolbar of the result grid writes the rows to a file.

  1. Run the query.
  2. Open the menu of the save button in the result grid.
  3. Choose a format: Save as Comma Separated File, Save as <TAB> Separated File, Save as '|' Separated File, Save as XML, Save as Markdown, Save as JSON, Save as Excel 2003 File (.xls) or Save as Excel 2007 File (.xlsx).
  4. Choose the file.
The result grid of the SQL Editor, with the save button in its toolbar

DbSchema runs the query again and writes every row, not only the rows that the grid shows. To copy the rows instead, use Copy As in the same menu: CSV, JSON, XML or Markdown.

Commit or roll back a change

An INSERT, UPDATE or DELETE that you run with Run stays in an open transaction. A transaction is a group of changes that the database keeps private until you confirm them. While it is open, the Commit button blinks, and other users of the database do not see your change.

  • Press Commit, or Ctrl+Shift+C, to keep the change.
  • Press Rollback, or Ctrl+Shift+R, to undo it.
An UPDATE of one customer after Run: the result says Modified 1 rows and the Commit button is lit

Keep the editor for later

When you close an editor, DbSchema asks whether to keep it.

  • Save and Close keeps the editor and its text in the model. Open it again with Query → Reopen. The model file keeps it once you save the model with File → Save Model to File.
  • Don't Save closes the editor and drops its text.
The Save SQL Editor dialog with the buttons Save and Close, Don't Save and Cancel

To keep the SQL in a separate file instead, use File → Save File in the editor's toolbar.

Next steps