SQL Window
The SQL Window can be used to execute bespoke SQL statements against a DB Connection. Any SQL statements that changes the database will be executed in the context of a transaction, so the transaction will need to be committed before the underlying database is updated.
Any SQL statements that are executed are stored in memory so you can scroll backwards and forwards through them with the Next and Previous buttons.
The SQL Window sends commands to the target database in the right format for that database, potentially handling any nuances across the driver types. See an example below.
This window can also be accessed through the DB Connection module. Right-click on the individual connection under DB Connection to bring up the pop-up menu, then select Open SQL Window,
The name of the connection that you clicked on will pre-populate the DB Connection field.
Fields
Field Name | Description |
|---|---|
DB Connection | Select the DB Connection which you wish to execute the SQL statements against. |
SQL Statement Pane | Enter a SQL statement to execute against the DB Connection. This can be any SQL statement that is valid for the Connection Type of the DB Connection. |
SQL Result Pane | The results of the SQL statement that is executed will be displayed here in the lower portion of the pane. If the SQL statement returns a result data set, this will be displayed in grid form. You can use the filter and sorting functions of the grid. If the SQL statement has changed data, it will inform you of the number of records affected. |
Buttons
Button Name | Description |
|---|---|
Previous | Click to display the previously executed SQL statement. |
Next | Click to display the next SQL statement. Only available if you have scrolled backwards through the previous SQL statements |
Execute | Click to Execute the SQL statement currently displayed in the SQL statement pane. If it has changed the database, you will need to click Commit prior to the data being saved to the underlying database. |
Commit | Click to Commit the transaction against the database. This will Commit any SQL statements executed that have changed the database. If multiple SQL statements have been executed they will all be Committed. |
Rollback | Click to Rollback the transaction against the database. This will undo any changes you have caused by executing SQL statements. |
Close | Click to Close the SQL Command Window. |
For example, the DB Connection MYOB Exo has been selected, and the command Select used to query the MS SQL database.
Resulting in the following from the DR_ACCS table.
