Once you are connected to your database, you can use the SQL Editor to write and execute SQL statements. The SQL Editor displays any SQL statements that you may have generated automatically, loaded from existing SQL scripts, or written manually. The SQL Editor supports auto-completion, regions, and line/block comments.
SQL Editor toolbar buttons
The SQL Editor toolbar has the following buttons:
The Toggle Browser button makes the Browser pane visible/invisible.
The Toggle Result button makes the Result visible/invisible.
The Execute Query button executes the SQL statements that are currently selected. If multiple statements exist and none are selected, only the result of the last statement will be shown in the Results tab. For multiple results, MapForce will suggest opening these results in Altova DatabaseSpy.
The Undo button allows you to undo an unlimited number of edits in the SQL window.
The Redo button allows you to redo previously undone commands.
The Import SQL file command opens an external SQL script, which can then be executed.
The Export SQL file command saves an SQL script to a desired location.
The Open SQL script in DatabaseSpy command opens the current SQL script in Altova DatabaseSpy (must be installed).
The Options button opens the Options dialog box that allows you to define general database query settings as well as SQL Editor settings.
You can query your database using the following methods:
•You can import an SQL script or copy some SQL statements and paste them into the SQL Editor (The Import SQL file toolbar command).
•You can write SQL statements in the SQL Editor manually.
•You can also right-click an object in the Database Browser and generate a query (typically, SELECT).
To generate an SQL SELECT statement in the Database Browser, do one of the following:
•Press and hold a database object (e.g., a table) or a folder in the Database Browser and drag this object of folder into the SQL Editor.
•Right-click a database object in the Database Browser and select Show in SQL Editor | SELECT.
Execute SQL statements
The SQL statements in the SQL Editor can be executed with immediate effect. To execute an SQL statement, click the button. The result of the SQL query and the number of affected rows are displayed in the Messages tab of the DB Query pane.
When multiple SQL statements appear in the SQL Editor, only the result of the selected statement or the result of the last statement (if no statements have been selected) will be displayed in the Results tab. In case there are multiple results, MapForce will suggest opening them in Altova DatabaseSpy.
Import/Export SQL scripts
You can import and export SQL scripts. To import an external SQL file, click the toolbar button and select the SQL file you want to open. To export the contents of the SQL Editor pane to an SQL file, click the toolbar button and enter the name of the SQL script.
Add/Remove SQL line/block comments
The SQL Editor allows you to comment out statements, parts of statements, or groups of statements. Such statements are skipped when the SQL script is executed. You can insert a line or a block comment. The line comment indicates that the current line or the remaining part of it is commented out. The block comment can span multiple lines. The block comment can also be used to comment out individual words.
To insert a line/block comment, take the following steps:
1.Select a statement or part of a statement.
2.Right-click the selected statement and select Insert/Remove Line/Block Comment from the context menu.
The screenshot below illustrates a block comment (green text).
To remove a line/block comment, take the steps below:
1.Select the part of the statement that is commented out.
2.Right-click the selected part and select Insert/Remove Block (or Line) Comment from the context menu.
You can also remove the comment characters manually.
Bookmarks are used to mark items of interest in scripts. To add a bookmark, right-click the line you want to have bookmarked and select Insert/Remove Bookmark from the context menu. The bookmark icon is displayed in the margin at the beginning of the bookmarked line.
To remove a bookmark, right-click the line from which you want to remove the bookmark and select Insert/Remove Bookmark from the context menu. To remove all bookmarks, right-click anywhere inside the SQL Editor and select Remove all Bookmarks from the context menu.
To jump to the next/previous bookmark, right-click the relevant line and select Go to Next/Previous Bookmark.
Insert SQL regions
Regions are sections of text that you mark and declare as a unit to structure your SQL scripts. Regions can be collapsed and expanded to display or hide parts of SQL scripts. It is also possible to nest regions within other regions. When you insert a region, an expand/collapse icon and a --region comment are inserted above the selected text. You can change the name of a region by adding descriptive text to the --region comment. The word region must not be deleted.
To add a region, follow the instructions below:
1.Select the statements you want to transform into a region.
2.Right-click the selected statements and select Insert Region from the context menu. An example of a region is illustrated below.
To remove a region, delete the -- region and -- endregion comments.