20.2 Using SQL Coding Assistant

20.2.1 Starting / Pausing / Stopping SQL Assistant^TM

By default, Coding Assistance is enabled at Editor's Startup. You can change this behaviour using Tools / Editor Options form.

When starting, SQL Assistant^TM opens a MySQL^TM connection.

Note: select privileges on database mysql and information_schema are required in order to use coding assistance.

Note 2: SQL Assistant^TM requires MySQL^TM v.5.0 or higher.

If ''Use Code Assistance on Editor's startup'' option is disabled and you need to start SQL Assistant^TM services manually:

1. right-click the SQL Editor you are using;
2. select ''Activate'' command from the popup menu.

Alternately, you may use the default hot key to start Coding Assistance: Ctrl+F5.

To pause SQL Assistant^TM services:

1. right-click the SQL Editor you are using;
2. select ''Suspend'' command from the popup menu. SQL Assistant^TM suspends its activities and does not display any SQL Assistance popups in this mode.

To resume SQL Assistant^TM services:

1. right-click the SQL Editor you are using;
2. select ''Resume'' command from the popup menu. SQL Assistant^TM resumes its normal activities.

To stop SQL Assistant^TM services (and close connection):

1. right-click the SQL Editor you are using;
2. select ''Disconnect'' command from the popup menu.

20.2.2 Manually Invoking SQL Assistant^TM Popups

By default, SQL Assistant^TM displays help popups automatically. It monitors text entered in the SQL editor and as you type SQL commands it display context-based popups with item that you may want to insert in the text.

If for whatever reason such popup is not displayed and you want to display it where the cursor is, you can invoke it

• using the Keyboard Hot Keys (sec. 20.2.2)
• using Editor Right-click Context Menus (sec. 20.2.2).

20.2.2.1 Using the Keyboard Hot Keys

Ctrl+Space is the default hot key to open SQL Code Assistant^TM popup. The type of the popup displayed is context driven (read sec. 20.2.3 for more details).

Press the Esc key at any time to immediately close SQL Assistant^TM's popup.

20.2.2.2 Using Right-click Context Menus

In order to improve your SQL Assistant^TM experience and relieve you from the need to remember various hot keys, you can invoke SQL Assistant^TM using SQL Editor popup menus.

To use SQL Assistant^TM functions right-click on the text in the Editor where you want to invoke SQL Assistant^TM then select the ''SQL Assistant (TM)'' item in the context menu. The item leads to the next menu level containing specific SQL Assistant^TM functions (Fig. 20.2).

20.2.3 Using Object Name Code Completion Features

The contents of the SQL Assistant^TM popup is context-driven. For example, when you type FROM keyword in a SELECT statement or an UPDATE keyword the popup list is populated with items that may want to insert into the text. Such items may include table and view names, procedure names, schema names, and so on.

The following object types are supported:

• Databases
• Tables
• Views
• Procedures
• Functions
• Users

For your convenience, items of different types are displayed in different colors and indicated by different icons displayed on the left side of the popup list.

If the item you want is below the visible area of the popup, scroll through the list to locate the item and then double-click or press Enter key to insert it into the text. Alternatively, you can start typing the first characters of the item to display only those items that begin with the typed characters.

If the item you want is not in the popup list, continue typing normally and the popup will disappear automatically.

The popup containing server's object can appear when you type space character after SELECT, FROM, JOIN, USE or CALL keywords or after database name following by a dot character (Fig. 20.3 and 20.4).

20.2.3.1 Using Object Name Auto-Completion

You can use the auto-completion feature when modifying the existing code. Using Ctrl+Space shortcut you can make SQL Assistant^TM to auto-complete partially entered object name, for example, if you type

SELECT * FROM inform

and then press Ctrl+space, SQL Assistant^TM will automatically completes the text as

SELECT * FROM information_schema

because there is only single object in the catalog whose name begins with ''inform''. In case if multiple matching objects are found, a regular SQL Assistant^TM popup will appear with the list of items whose names begin with ''inform'' text (Fig. 20.5 and 20.6).

20.2.3.2 Using Column Names Completion Features

The table/view column list popup appears expanded automatically when SQL Assistant^TM is invoked after a dot character, comma or equal sign. The popup item list is normally limited to column names of the referenced table (Fig. 20.7 and 20.8).

20.2.3.3 Using Function Argument Hints Features

The function argument list popup appears expanded automatically when SQL Assistant^TM is invoked after an open-parenthesis character ''('' or after comma within function arguments. The popup item list is normally limited to hints describing function arguments and the function return code, and also in a separate section, list of column names of tables and views referenced in the same SQL statement.

Fig. 20.9 reports an example popup demonstrating function argument completion feature.

Tips:

• Function argument names and the return code are displayed in a different color and provided as hints to help you enter values having correct data types and in the correct order.
• Optional arguments are displayed in [ ] brackets.
• In case if multiple versions of the same function are available having different argument types or different argument numbers, each version is listed in separate section.

20.2.3.4 Using User Names Completion Features

The user name list popup appears expanded automatically when SQL Assistant^TM is invoked after GRANT...TO, REVOKE...FROM commands. Type for example,

GRANT SELECT ON *.* TO

SQL Assistant^TM will display a list of users to which you can grant the SELECT privilege (Fig. 20.10). Note that SQL Assistant^TM also automatically displays object list popup after ON keyword (Fig. 20.11).

20.2.3.5 Using Code Auto-Expansion and Auto-Generation Features

20.2.3.5.1 Automatic Generation of Statement Code

SQL Assistant^TM supports several handy code auto-generation features. Code auto-generation is context-based and triggered after you activate SQL Assistant^TM is certain places of the SQL code. Below are several examples.

If you use SQL Assistant^TM popup displayed immediately after the SELECT keyword and pick a table or view or table function name in the popup list, SQL Assistant^TM will automatically insert into the code the complete SELECT statement for chosen object, adding object columns immediately after the SELECT keyword and following by FROM clause with the selected object. Similarly if you choose an object in SQL Assistant^TM popup after INSERT or UPDATE statements, SQL Assistant^TM will generate and automatically insert into the code the complete text of the statement.

Fig. 20.12 demonstrates the working of the automatic SQL statement code generation feature.

Tips:

• Hold down the SHIFT key while choosing an item in the SQL Assistant^TM popup appearing after the initial SQL statement keyword to paste just the selected object name without generating additional code.
• If for whatever reason you don't want to paste or auto-generate any code when the popup appears after the initial SQL statement keyword, just continue typing the code normally. The popup will disappear automatically.
• You can also press the Esc key to dismiss the popup.

20.2.3.5.2 Automatic Generation of Variable Declarations

SQL Assistant^TM can automatically generate variables for table, view or table function columns. This feature can be useful when coding cursors, batch selects and updates and similar SQL operations requiring declaration of a value holder variable for every column in a given object.

To use this feature:

1. Type DECLARE keyword then type space. SQL Assistant^TM will display a list of objects in the database.
2. In the popup list choose the object that you want to target. SQL Assistant^TM will automatically insert as many variable declarations into the code as many columns are available in the chosen object.

Note: The names and data types of the declared variables match exactly names and data types of corresponding columns in the chosen database object.

Fig. 20.13 demonstrates the working of the automatic code generation feature.

Tips:

• If for whatever reason you don't want to paste or auto-generate any code when the popup appears after the DECLARE keyword, just continue typing the code normally. The popup will disappear automatically.
• Hold down the SHIFT key while choosing an item in the SQL Assistant^TM popup appearing after the DECLARE keyword to paste just the selected object name without generating additional code.
• You can also press the Esc key to dismiss the popup.

20.2.4 Working with SQL Assistant^TM Popups

A typical SQL Assistant^TM popup looks like that on Fig. 20.14.


The following topics describe how you can use the keyboard and mouse actions to work with the popup.

20.2.4.1 Navigation Keys

The following navigation keys are supported in the SQL Assistant^TM popups:

• Arrow Down moves the logical selection to the next item. If pressed immediately after the popup appears on the screen, it selects the top item. If pressed while the last visible item is selected and there are more items available in the list, then it scrolls the contents down to the next item and selects it. Can be repeated until the last available item is reached.
• Arrow Up works just like the Arrow Down but in an opposite direction.
• Page Down scrolls the contents by one logical page and moves the logical selection to the top visible item. The logical page size is controlled by the popup list size and can be adjusted as described in sec. 20.2.4.
• Page Up works just like the Page Down but in an opposite direction.

Tips:

• The most efficient way to use SQL Assistant^TM popups is to start typing the item you want so that only items beginning with that text remain in the list and then using the Arrow Down key move the selection to required item and then hit the Enter key to paste the selected item text into the Editor.
• You can also use the computer mouse to scroll the SQL Assistant^TM popup contents and then click the item you want to paste into the Editor.

20.2.4.2 Selection Keys

The SQL Assistant^TM supports the following item selection keys:

• Enter key is the default key, which is the standard key used in all Windows controls to select an item in a list and other multiple-choice selection control. This key also allows you to tab through the text while SQL Assistant^TM popup remains displayed on the screen.

20.2.4.3 Scrolling Contents

The contents of the SQL Assistant^TM popup can be scrolled using either the keyboard navigation keys Arrow Up/Down and Page Up/Down or using the mouse. When using the mouse you can scroll the popup contents by dragging its scrollbar handles. See Fig. 20.14 for information on where to locate scrollbar handles.

20.2.4.4 Resizing Contents

To resize the SQL Assistant^TM popup, drag the resizer handle in the bottom-right corner of the popup window. See Fig. 20.14 for information on where to locate the resizer handle.

20.2.4.5 Resizing Individual Columns

Depending on the popup type several columns of text can be displayed in the item list. Moreover different parts of the list can have different number of columns and column width and positions.

In case if the column width is insufficient and some parts of item text appear to be cut you can resize these columns so you can see the complete text. Note that light gray verticallines indicate column boundaries. To change size of a particular column, rest the mouse pointer over the vertical line indicating right boundary of that column. The mouse pointer should change. Press the left mouse button and while holding it pressed drag the line to the desired position. See Fig. 20.14 for information on where to locate lines indicating column boundaries.

20.2.4.6 Moving Contents

In case if the SQL Assistant^TM popup covers part of the Editor windows that you want to see, click on any empty area within the popup window and while holding the left mouse button pressed drag the popup window to the part of the screen where it is convenient for you.

20.2.4.7 Refreshing Contents

For performance reasons SQL Assistance uses in-memory cache for catalog data retrieved from the database so that it does not need to query the database each time it needs to display the SQL Assistant^TM popup. This internal cache is not updated automatically when changes occur in the database catalog data during active SQL Assistant^TM sessions.

For example, when new stored procedures or tables are created in the database or table columns are altered, the SQL Assistant^TM is not aware of these changes and so they are not reflected in the contents of popups. Use either of the following methods to refresh the internal SQL Assistant^TM cache:

• Method 1: Use Refresh Cache item in SQL Editor's popup menu.
• Method 2: While the popup is displayed on the Editor screen, press the F5 hot key.
• Method 3: While the popup is displayed on the Editor screen, right click on the popup and then click the Refresh menu that appears on top of the popup window.

20.2.5 Using SQL Syntax Checker

SQL Assistant^TM provides integrated SQL Syntax Checker utility. This utility can be used to check the SQL code syntax correctness before the code is actually executed in the database.

To perform syntax check for a portion of the code, for example, for a single SQL statement, for multiple statements, or a procedure, use the following method:

1. Highlight the required portion of the text;
2. Press Ctrl+F9 hot key or right click on the Editor and select the ''Check SQL Syntax''.

To perform syntax check for the entire code, use the following method:

1. Ensure no text is highlighted in the Editor;
2. Press Ctrl+F9 hot key or right click on the Editor and select the ''Check SQL Syntax''.

The Check SQL Syntax command checks the code syntax, and in case if errors are found, it displays list of found errors at the bottom of the editor screen (Fig 20.15). Each error line starts with the line number in the editor where the syntax error has been found and following by the error message text. Clicking the error text makes SQL Assistant^TM scroll the Editor content and highlight the line with the error.

---

Copyright © 2007 - 2009 HoneySoftware - All Rights Reserved

Submit feedback on this topic.