Working with the Console

SQL syntax highlighting

If the 'sqlSyntaxHighlight' option is selected (see the (A) section in the 'Key Console Features' topic) , SQL statements will be syntax highlighted. The highlighting is individual: The reserved words, functions, literals, comments, data types, table names - each have a different color.

Executing SQL statements directly from the Console

You can execute SQL statements directly from the console by pressing the 'F9' key. See bellow for more SQL commands.

Executing the SQL statements directly from the console has several advantages compared to using dedicated software.

The main advantages are as follows:

• No need to buy an expensive DB tool like TOAD (even the 'Free TOAD' version is for non-commercial use only).
• No need to have several DB tools for each DB platform. It works for all of them.
• No need to install any DB tool.
• No need to search for and install any JDBC drivers (the driver which Valuemation uses is used).
• No need to configure a connection to the DB. You are ready to execute any SQL statements on the DB to which Valuemation is connected (but you can also execute SQL statements on other schemas).
• It is good for the performance testing because the same driver, session instance (with some special settings) as the one used by VM are used. Therefore the measured times are reliable.
• It is not necessary to copy and paste SQL statements from Valuemation to any dedicated DB tool. You can execute directly any of the logged SQL statements and inspect the results. Note that SQL statements are formatted in such a way that you don't need to make any adjustment (dates and timestamps are converted to their corresponding string format or function).
• Useful when adding a new table or column to a generic table during customization.
• It has a simplistic design yet it covers a vast majority of the features you would expect from a DB tool. However, it's not supposed to replace a DB tool completely.
• It has some unique features which are not included in dedicated DB tools:
  • Navigation to referenced records
  • Generation of primary keys when inserting/copying records (using AMT_HIGHESTKEY)
  • Showing corresponding attribute/object type names for a given table/column
  • Possibility to create templates

The first time you execute an SQL statement, you will be prompted for the DB password (for the DB user used to connect the DB – this is usually specified in AuthenticationManager_jaas.config file) unless you are using LoginConfigurations.xml with the filled, unencrypted DB password.

When you execute a SELECT statement, then the result will be displayed in a table on a new tab at the bottom of the console. The tab tooltip will show the select statement, the execution time and number of returned rows. The status bar will show 'Selected <N> rows in <t> ms' where <t> is the execution time in milliseconds.

When you execute an UPDATE, DELETE or INSERT statement, the status bar will show e.g. 'Updated/Delete/Inserted <N> rows in <t> ms‘, where <N> is the number of rows affected by statement and <t> is the execution time in milliseconds. For other statements like CREATE, ALTER... the status bar will just show 'Executed in <t> ms'.

If the SQL statement could not be executed, the status bar will show Error and the SQL exception message will appear on the Status tab (always the first tab).

If the executed SQL statement contains special variables (e.g. $QUALIFIER), they will be replaced automatically.

SQL statements are executed in a new thread, which means that you can continue working while a long running SQL statement is being executed (you can also cancel it by pressing Alt+C). However, you cannot execute another SQL statement while one is being executed.

SQL-related commands in the text area

The key commands are as follows:

• F9

  Executes the SQL statement on the current line or in the selected text. Note that the individual SQL statements have to be separated by by a semicolon (;). You can execute even the SQL statements logged by Valuemation (the log time, number of rows etc. will be removed automatically).

• Ctrl + F9

  Executes all SQL statements from the current line. Note that the individual SQL statements have to be separated by a semicolon (;). For example, you can paste a script into the console, place the cursor on the first line and press Ctrl+F9 to execute the whole script.

• Alt + C

  Cancels the execution of the current statement. It is usually applied when the process takes too long.

• F11

  Commit. Don't forget to commit changes after executing UPDATE, DELETE, INSERT statements or editing/removing/inserting records in the table!

• F12

  Rollback.

• F5

  Refreshes the metadata (list of templates, tables, columns etc.). This is useful if you have created a new table, column or a template and you cannot see it in the code complete pop-up window.

Note: The commands are also shown in the SQL menu.

Tables

The table has a context menu which enables you to filter tables by selected values. The result will be shown in a new tab.

Use the appropriate commands to delete or insert rows and references to navigate to other tables. Note that it takes some time to read the Foreign Key (FK) information so the 'References' and 'Referenced By' submenu will be initially disabled and will become enabled as soon the FK information is available.

Pressing 'F9' on the select statement above the table executes the statement again.

You can also do the following:

• Edit the values in the table (the update statements are executed immediately).
• Delete selected rows from the table (the delete statements are executed immediately).

• Insert a new row into the table (the insert statements are executed before committing the changes by pressing F11). Number primary keys are generated automatically by Valuemation.

Code Completion

The Code Completion works both in the main text area and in the line with SQL statement above the table.

If you press Ctrl+Space, you'll see a pop-up window with items beginning with the letters to the left of the cursor. If there is exactly one item starting with the typed letters, the pop-up window will not appear and the item will be inserted immediately into the script.

The following items are listed:

• Tables and views in the current schema, schemas and templates (please see also the next chapter). Each of them have a different icon: A table, table with glasses, person and the yellow icon respectively. The yellowish pane on the right shows additional information for the selected item. For tables and views, it shows untranslated and translated name of the corresponding object type in Valuemation.

  

• After schema: Only the tables and views in the schema.
• After table or table alias: Columns in the table. Columns are shown with their DB type. Primary key columns have a 'key' icon, foreign key columns have a gray icon and the name of the referenced table. The yellowish pane on the right shows additional information (the untranslated and translated name of the corresponding attribute in VM), NOT NULL if the column isn't nullable.

  It also shows the delete rule for the Foreign Key columns:

  

Templates

Just like in the Script Editor, templates can be used. They appear in the code completion pop-up window marked with a yellow dot icon. Templates are stored in the 'consoleTemplates.xml' file placed in <user home>/.valueamtion. Currently you have to modify this XML file manually to add, modify or remove the templates.

The following picture shows a sample 'consoleTemplates.xml' file:

After typing 'co' and pressing Ctrl+Space, the code complete pop-up window appears. Now it also contains the template 'count'. Press 'Enter' and the text 'select count(*) from' will be inserted into the console:

SQL Options

The SQL options are as follows:

• sqlSyntaxHighlight

  If selected, SQL statements will be syntax-highlighted (reserved words, functions, literals, comments, data types, table names - each having a different color).

• sqlReadLimit

  The maximum number of rows to be fetched from the DB when executing a SELECT statements (by default it is 10000). If the limit is reached, the SQL statement line above the table will have pink background and the status bar will indicate this, too.

• sqlAutoCurrentSchema

  If selected, the current schema (qualifier) will be set as the default schema so you will be able to write SQL statements without specifying the schema (e.g. select * from amt_catalog).