SQL Editor

Running Queries

Run as Individual Queries

When the editor attempts to run the query, it can either run the query as a single batch, or it canparse the query (on ';' characters) and run each individual query in a separate session. Some databasesdo not allow query batches, or will stall on the first error in a query batch. Therefore, if you choose to run the text as individual queries (this is the default) each query will be run in a separatesession. If errors are found, you will be prompted on in the error and you can choose to finish runningthe rest of the queries or abort the operation. It is important to note that when queries are run individually, each query gets a new session. Queries that rely on transactions or session data and variables should always be run as batches.

Result Set Type & Concurrency

There are numerous ways for JDBC drivers to select information from a database, and each of thedrivers will support different methods. The editor allows you to set the result set type and concurrencythat you prefer for each query. The result set type can be one of the following:

• Forward Only - This provides a file-hose style select with no cursors. This is the fastest way to select data. However, most JDBC drivers do not allow updating information in this mode.
• Scroll Insensitive or Scroll Sensitive - This provides a result set that is updatable.

For more information on these settings, see the JDBC specifications and your JDBC driver's documentation.

Parameters in Queries

The SQL Editor allows you to insert parameters in queries. Once you run the query, you will be prompted for the values that those parameters should contain. You can insert unnamed parameters by using a '?' character, or you can insert a named parameter using the '%' character as a delimiter.

# Unnamed parameterSELECT * FROM product WHERE product_id = ?;# Named ParameterSELECT * FROM product WHERE product_id = %id%;

Result Sets

If the result set is updatable, you can insert a new row, edit the currently selected row, or delete multiple selected rows using the menu accelerators or the buttons provided at the bottomof the result set. When attempting to insert or edit a row, if the update fails for any particularreason, the editing window will re-appear. You can then change the data to fix any errors or you can have the editor generate an SQL statement using the controls at the bottom of the window. The editor will generate the statement and insert it into the editor so that you can further customizethe statement or run it.

Viewing Data as Text

Sometimes it is valuable to view the result set data as fixed-width text in order to quickly copyit to another application. You can use the View As Text button at the bottom of the result setto generate the text based on a number of settings. You can then print, copy or save the text.

Viewing Column Properties

To view the column properties of a result set, click on the View Properties button. You will be ableto see all the metadata that the JDBC driver provides for the current result set.

Saving and Exporting Data Using DMS

You can use the Data Migration Services to export data from a result set table into any format or destination that the installed data consumers provide. Use the tool bar or menu item Export Datato launch the DMS exporter window.

Showing & Hiding Widgets

Viewing The SQL Log

This log is only held while that particular SQL Editor window is open and is cleared when the window is closed.