Creating SQL menus

An SQL menu pulls values from a database table by using an SQL query. The database values make up the SQL menu items. By using an SQL query to create a menu, your application can do the following:

• Select data from databases other than AR System server databases.
• Issue complex queries to the database. This is useful for customers who want to use database features specific to a particular database platform.

To use SQL queries effectively, you must understand relational databases in general and your relational database in particular. If an SQL query is database-specific instead of generic, moving the SQL menu definition to another environment might be difficult.

Before creating an SQL menu, determine what information you want to search for in the database and what information your SQL query should return. For example, see Sample SQL menu below.

To create an SQL menu

1. In Developer Studio, select File > New > SQL Menu.
2. Select the server on which to create the menu, and click Finish.
   A new SQL menu appears as shown in the following figure.
   New SQL menu
   
3. In the Refresh list, select the appropriate refresh mode.
   See Setting-a-refresh-mode-for-menus.
4. In the Server list, select the database server to which the SQL query will be issued.
5. In the Label Index List field, enter the numerical index of the database column that contains the information to display as menu item labels.
   To create a hierarchical menu, enter up to five index numbers separated by commas. The first number becomes the first level, the second number the second level, and so on.
   Only the first 80 characters of each field value are displayed.
   In multilevel menus, items in all levels (except the last level) that do not have a value appear blank. If the last-level menu item does not have a value, it does not appear.
   In single-level menus, if the first or last item does not have a value, it does not appear. If items in the middle of the menu do not have values, they appear blank.
6. In the Value Index field, enter the numerical index of the database column that contains the information to load into the field when users select a menu item.
   Only the first 255 bytes of each field value are displayed.
7. Click the ellipsis button next to the SQL Query field, and use the Expression Editor to build the SQL query to issue to the database to create the menu.
   For more information, see Expression-editor-and-content-assist.
   Consider these tips when building the query:
   • You can enter only one query for each menu. You cannot enter two queries separated by a semicolon and have both queries run. To run a set of queries, you must create a stored procedure or function and run that.
   • Because AR System does not verify the validity of your query, run the query directly against the database (as a test) before you enter it into the SQL Query field, and then copy and paste the tested query into the SQL Query field.
   • If the query returns unexpected values or results, turn on SQL logging in the database to debug the SQL syntax. An additional debugging strategy is to start an SQL interpreter, for example, SQL*Plus for Oracle, Query Analyzer or Microsoft ISQL/w for Microsoft SQL Server, and enter the same SQL query directly into the database to see what results are returned.
   • If the SQL operation fails, a AR System error message and the underlying database error message appear.

   • You can greatly affect database performance by the way that you write an SQL query. If a row has many columns, a SELECT* SQL command can have a greater performance impact than if you select specific columns. For more information, see your relational database documentation.
     See also Database security issues below.

     If the query includes a field value from the specified form in its WHERE clause, you cannot use the character field pattern $MENU$ for any field to which the menu is attached. Because the server cannot resolve the field references, that value is always rejected.

8. (Optional) Modify the menu's change history.
   See Updating-the-change-history-of-fields-forms-or-objects.
9. (Optional) Add Help text to the menu.
   See Creating-help-for-applications.
10. Select File > Save.
11. In the Save Menu As dialog box, enter a name for the menu, and click OK.
    Menu names must be unique on each AR System server.
    Names can have as many as 80 characters, including spaces. Names can include double-byte characters, but avoid using numbers at the beginning of the name.
12. Attach the menu to any character field in any form.
    See Creating-common-data-fields and Menu Name.

Sample SQL menu

Suppose your SQL menu uses this query:

And the query returns the results shown in the following figure. 

Results returned from the sample SQL query

Each column has a corresponding numerical index:

• 1 corresponds to the BUG_ID column
• 2 to the FIRST_NAME column
• 3 to the TECHNCN column

Entering 2 into the Label Index List field creates a menu with the contents of the FIRST_NAME column as its menu items (see the following figure). 

Menu created by the sample SQL query

Entering 1 into the Value Index field inserts values from the BUG_ID column into the field. For example, if a user selects John from the menu, the BUG_ID value associated with John is entered into the field (see the following figure). 

Field value if Value Index is set to 1 in sample SQL menu

Entering 3 into the Value Index field inserts values from the TECHNCN column into the field (see the following figure). 

Field value if Value Index is set to 3 in sample SQL menu

Entering 2,3 into the Label Index List field creates a hierarchical menu (see the following figure). 

Hierarchical SQL menu

Database security issues

To use SQL queries, familiarize yourself with your underlying database.

All SQL queries are issued by the following users:

If you run AR System as one of these users without permission to access the database, you cannot issue the SQL query. 

To access non-AR System server databases, use the database name as part of the SQL query syntax. For example, for Microsoft SQL Server database: