Information Center

SQL Wizard (Java 2-enabled browsers)

The SQL Wizard allows you to build an SQL statement, send it to a remote database server, retrieve the resulting data, if any, save the resulting data, and save the SQL statement for re-use.

The remote database server must be running on an iSeries or AS/400, and an appropriate Java Database Connectivity (JDBC) driver must be present on the client workstation. The Host On-Demand client and the Database On-Demand client already include a JDBC driver from the AS/400 Toolbox for Java, which allows the Host On-Demand client or Database On-Demand client to access DB2/400 data on a properly configured iSeries or AS/400 (see the Driver identifier field on the Logon tab).

Database servers running on other host platforms are supported only if the appropriate JDBC driver is installed on the Host On-Demand or Database On-Demand client workstation (see the Driver identifier field on the Logon tab).

The SQL Wizard's user interface helps you to select the tables to work on, to select the columns from which data is to be returned, to specify conditions for selecting the rows to be processed, to select the columns on which the returned data is to be sorted, and to build a valid SQL statement. A new Expression Builder lets you use operators, predefined functions, constants, and programming constructs to define complex conditions for selecting the rows to be processed. The same Expression Builder lets you define column expressions that automatically perform and display the results of complex computations based on returned values. Returned data (including the results of column expressions) can be either displayed or written into a file. Various file formats are supported, including XML. SQL statements can be labeled and saved for re-use.

You can set default values for some of the fields in this wizard, if your administrator has configured your client or your user id to do so:

A sequence of tabs guides you through the process of building and running an SQL statement. These tabs are:

Start

Select
Click Select to select rows from a table or tables.
Insert
Click Insert to insert a row into a table.
Update
Click Update to update one or more rows in a table.
Delete
Click Delete to delete one or more rows from a table.

Logon

Database URL
See Specifying a database URL.

If the SQL Wizard is launched from a 5250 Display session, then the SQL Wizard initially displays in the Database URL field a default database URL containing the name of the current iSeries host.

User ID
If a user ID is required to log on to the remote database server, type the user ID into the User ID field. Otherwise leave the field blank.
Password
If a password is required to log on to the remote database server, type the password into the Password field. Otherwise leave the field blank.
Driver identifier
The listbox includes the names of all the JDBC drivers that have been registered with Host On-Demand. Expand the listbox and click the name of the registered JDBC driver that you want to use, or click Other to specify another driver.

For general information on using a JDBC driver with the Host On-Demand client or the Database On-Demand client, see Registering a JDBC driver.

Other
This field must contain the fully qualified Java class name of the JDBC driver that you specify in the Driver identifier field.

The driver must be installed on the client workstation and must be accessible to the Host On-Demand client or Database On-Demand client (see Deploying a JDBC driver).

Table Filter (SQL Wizard)
This field does not appear unless enabled by the Host On-Demand administrator.

Type a comma-separated list of schema names, schema filters, table names, and table filters. For assistance, see Examples. For a detailed description of schema names, schema filters, table names, and table filters, see Specifying a list of available schemas and tables.

The comma-separated list that you type into this field specifies the schemas and tables that you want to appear in the List of available tables on the Tables tab of the SQL Wizard after you log on to the database.

To use the default value, leave this field blank.

This Table Filter field is one of several levels of filtering for the list of available tables (see Level 4. Value for the current SQL or File Upload statement).

Connect and Disconnect
Click Connect to connect to the remote database server.

If the connection fails and you see a Java error message or are running Netscape 4.7x, see Problems logging on or connecting.

In certain circumstances you may encounter the error message SQL Assist Exception. See SQL Assist Exception.

Click Disconnect to disconnect from the remote database server.

Tables

Available tables
This is a list of the schemas and tables that are available to be used in the current SQL statement (see List of available tables).

The contents of this list are determined by several levels of filtering (see Levels of filtering for schema names and table names).

You will notice that:

To move one or more tables to the list of Selected tables:

To expand or collapse the contents of a schema in the list, click the + or - symbol to the left of the schema name.

Selected tables
This list contains the table or tables to which the SQL statement applies.

If the type of the SQL statement is Select, the SQL Wizard adds the names of the selected tables to the FROM clause of the SQL statement on the Review tab.

A correlation name is an alternate name for a table. When you select the same table (for example, AA01) more than once, the SQL Wizard automatically creates a correlation name (for example, AA01_1). To create a correlation name manually:

  1. Click a table name.
  2. Type the correlation name that you want to use.

To move one or more table names back to the list of Available tables:

To move all tables back to the list of Available tables, click << .

Filter Schemas
Click Filter Schemas to select the names of schemas that you want to appear in the list of Available tables.
Filter Tables
Click Filter tables to specify the names or types of tables that you want to appear in Available tables.
Refresh
Click Refresh to refresh the list of tables in Available tables.

Columns

Use the Columns tab to select the columns that you want included in the data returned by the SQL statement. The Columns tab is available only when the type of the SQL statement is Select.

When you select a column on the Columns tab, the SQL Wizard adds the column name to the SELECT clause of the SQL statement on the Review tab.

If you do not select any columns on the Columns tab, then by default the SQL statement selects all the columns in the table or tables that you specify. The reason is that the default SQL statement for a Select operation is SELECT * from tablename, where * means "all columns".

To select one or more columns:

  1. Click one or more columns in the Available columns list. The list contains all the columns of all the tables selected on the Tables tab. (To expand or collapse the contents of a table in the list, click the + or - symbol to the left of the table name.)
  2. Click > to add the selected column or columns to the list of Selected columns.
Click >> to add all the columns in the list of Available columns to the list of Selected columns.

To remove one or more columns from the list of Selected columns

  1. Click one or more columns in the Selected columns list.
  2. Click < to remove the selected column or columns from the list of Selected columns.
Click << to remove all the selected columns from the list of Selected columns.

Move Up, Move Down
Use these buttons to change the order in which the selected columns are displayed.

When you run the SQL statement, Host On-Demand displays the columns left to right on the Results tab in the same order as you specify on the Columns tab.

Add
Use this button to launch the Expression Builder and build a column expression.

A column expression is an expression whose evaluated value you want to appear as a column in the result.

Column expressions are similar to operations in a software spreadsheet, because you can combine the values in the columns of a table with:

Column expressions provide an extremely powerful tool for automatically performing and displaying the results of computations based on table values.

As a very simple example, if table HODTEST.AA01 contains a column named SALES, and you want to compute the value of a 6% sales commission that is based on the value stored in the SALES column of each row of the table, then you can use the Expression Builder to build or type the expression HODTEST.AA01.SALES * .06. When you run the SQL statement, a column of output is generated for this column expression showing the sales commission for each entry in the table.

You can also use multiple columns in a column expression. For example, to add the values in the columns SALARY, BONUS, and COMM, you could build or type the column expression SALARY + BONUS + COMM.

For more information see Building expressions.

Edit
Use this button to edit an already existing column expression. Follow these steps:
  1. Click the column expression that you want to edit.
  2. Click Edit.
Delete
Use this button to delete a column expression. Follow these steps:
  1. Click the column expression that you want to delete.
  2. Click Delete.

Joins

A join is a mechanism for selecting which rows from two related tables are included in an SQL statement, based on equality (or some other relationship, such as inequality, greater than, and so on) between the contents of selected columns.

The Joins tab is available only when the type of the SQL statement is Select.

The inner area of the Joins tab displays a small window for each of the tables that you have included in the list of selected tables on the Tables tab. In the small window for each table the names of the columns of that table are listed.

In the outer area of the Joins tab, use the buttons on the right side of the tab to create and manipulate joins.

The colored connecting lines have the following meanings:

Join
Click Join to create a new join. Follow these steps:
  1. Select a column name in the first table that you want to join.
  2. Select a column name in the second table that you want to join.
    • If a join is possible between the two selected columns, then the SQL Wizard displays a gray line between the column names.
    • If a join is not possible between the two selected columns, because the two selected columns have different data types, then the SQL Wizard displays a red line between the column names.
  3. Use the Join Type key to select a join type (see Join Type below). The default type is an inner join.
  4. Click Join to create the join. The SQL Wizard displays a blue line between the column names to indicate a completed join.
<, >
Use these buttons to select a Join to operate on. The SQL Wizard indicates the currently selected join by displaying a green line between the column names.
Unjoin
Use this button to dissolve an existing join. Follow these steps:
  1. Select an existing join using the > or < button.
  2. Click Unjoin.
Join Type
Use this button to select the join operator and the join type. The join operator is one of the following: The join type is one of the following (in these descriptions, the join operator is assumed to be =):

Conditions

Use the Conditions tab for the following two purposes:

The Conditions tab is available only when the type of the SQL statement is Select, Update, or Delete.

To exclude duplicate rows from the results of a Select statement, select the checkbox Exclude duplicate rows (SELECT DISTINCT). When you select this checkbox, the SQL Wizard adds the DISTINCT modifier to the SELECT verb of the SQL statement on the Review tab.

A condition is a criterion that you define for the remote database server to use in selecting rows from the tables that you have included in the list of selected tables on the Tables tab. You can specify one or more conditions. If a row meets all the conditions that you specify, then the remote database server includes that row in the operation (Select, Update, or Delete).

Use this tab to specify all the conditions for a single SQL statement.

You can use the controls to build a condition, or you can type a condition directly into the Conditions field.

Follow these steps to build a condition using the controls:

  1. Select the column for the search from the Available columns list.

  2. Select an operator from the Operators list.

  3. Type one or more values in the Values list. Enter one value per line. Click Clear to remove all of the values from the Values list.

    If you selected the Between operator in the Operators list, you must enter two values per line in the Values list. The values are separated by an ampersand (&).

    You can search for appropriate values by clicking Find.

    You can specify host variables in the Values list. To do this, click on a line in the Values list, then click Add Variable. The Add Variable window is displayed. Type the variable name, then click OK. The Add Variables window closes, and the variable is added to the Values list on the Conditions tab.

    You can specify parameters in the Values list. If a parameter is specified, its value is used in the search condition. A parameter is specified in the format :parm, where parm is the parameter name. For example, :empid is a valid specification for a parameter named empid.

  4. Click Add to add the condition to the Conditions field.

  5. To specify additional search conditions for your statement, click And or Or. Then, build the second search condition as described in the previous steps. When you click Add, the subsequent conditions are appended to those that already are displayed in the Conditions field.

To type a condition into the Conditions field, move the input focus to the Conditions field and type the condition.

You can also use the Conditions field to modify or delete a condition that you built using the controls. To modify a condition, use the insert, delete, backspace, cursor movement, and letter keys to add or remove text. To delete a condition, use the delete key or backspace key, or follow these steps:

  1. Use the mouse to highlight the condition.
  2. Press the Delete key.

You can also copy and paste text between the system clipboard and the Conditions field.

To build complex conditions, click Advanced Expression to open the Expression Builder - Conditions window (see Building expressions).

Groups

Use the Groups page to specify which columns to use for grouping results. When you group results, the data in the result set is divided into groups by the values in the specified columns. One row of data is returned for each group.

The Groups tab is available only when the type of the SQL statement is a Select statement.

You can group more than one column. However, the groups will not be sorted; use the Order page to sort the result set.

To include groups in your result set:

  1. Select the Include grouping columns checkbox. The columns that are specified in the Selected columns list on the Columns page are displayed in the Grouping columns list. These are mandatory grouping columns.

    If this checkbox is selected and no columns are specified in the Selected columns list on the Columns page, then all of the available columns are displayed in the Grouping columns list.

  2. To add more columns to the Grouping columns list:

    1. Select one or more columns in the Available columns list. The list displays all of the columns in a particular table. You can expand or collapse a table to display or hide the columns that are included in that table.

    2. Click > to add the columns to the Grouping columns list. Click >> to add all of the columns from all of the tables in the Available columns list to the Grouping columns list.

  3. To remove one or more columns from the Selected columns list, click on the columns, then click <. To remove all of the columns from the Selected columns list, click <<.

    To remove mandatory grouping columns from the Selected columns list on the Groups page, you must first remove those columns from the Selected columns list on the Columns page.

Filtering grouping results

You can eliminate unwanted grouped data by filtering the results that are returned by the grouping columns. To do this, either type the conditions in the Group conditions field, or click Advanced Expression to build one or more conditions.

Order

Use the Order tab to sort the rows that appear on the Output tab. The rows on the Output tab are the rows returned by the remote database server when you run an SQL Select statement.

The Order tab is available only when the type of the SQL statement is Select.

Normally (that is, without the Order tab), the rows on the Output tab are displayed in whatever order the rows happen to occur in the table or tables to which they belong.

With the Order tab, you can select one or more columns that you want to be used for sorting the returned rows. For example, if you select a column named OBJECTID and specify ascending order, and if the contents of OBJECTID are positive numbers, then the returned rows are sorted starting with the row or rows that have the smallest value in OBJECTID (such as 00001), followed by the row or rows containing the next smallest value in OBJECTID (such as 00004), and so on.

In addition:

To select a column for sorting:
  1. Select one or more columns in the list of Available columns.
  2. Click > to move the column or columns to the list of Selected columns.
  3. Use the Order column to specify sorting in ascending or descending order.

The radio button Display output columns only is always selected. This means that the Output tab displays only columns that are included in the list of Selected columns on the Columns tab (including the results of any column expressions).

Review

The primary use of this tab is to allow you to run the generated SQL statement. You can also do the following operations (unless the administrator has disabled the operation):

Insert

Use the Insert tab to specify the values that you want to add to the specified table. When you insert values, a row is created in the table specified on the Tables tab, and the values that you specify on the Insert tab are placed into that row.

To specify the values to insert, type the value that will be inserted for that column in the Value field.

You must type a value for every non-null column. Non-null columns are indicated by a plus sign (+).

You do not have to provide values for null columns.

Update

Use the Update tab to specify the values that you want to change in the specified table. When you update values, the values that you specify on the Update tab are placed into the table specified on the Tables tab.

To specify the values to change, in the Value field, type the value that will be updated for that column.

You do not have to update all the values in a row; you only need to enter values for the columns that you want to update.

You must enter a value for at least one column.

Related topics

  • Overview of database access
  • Output (SQL Wizard)
  • Results (SQL Wizard)