SQL Wizard (Java 1 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 Z and I Emulator for Web client and the Database On-Demand client already include a JDBC driver from the AS/400 Toolbox for Java, which allows the Z and I Emulator for Web client or Database On-Demand client to access DB2/400 data on a properly configured iSeries or AS/400 (see the Database URL field on the Logon tab).

Database servers running on other host platforms are supported only if the appropriate JDBC driver is installed on the Z and I Emulator for Web or Database On-Demand client workstation (see the Database URL 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. Returned data 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:

• If you are running a Z and I Emulator for Web client see Data Transfer Defaults.
• If you are running a Database On-Demand client see Database On-Demand User Options.

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

• Logon
• Tables
• Join
• Condition
• Columns
• Sort
• Output (SQL Wizard)
• SQL
• Results
• Insert
• Update

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.

Table Filter (SQL Wizard)

This field does not appear unless enabled by the Z and I Emulator for Web 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).

Driver description

The listbox includes the names of all the JDBC drivers that have been registered with Z and I Emulator for Web. 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 Z and I Emulator for Web client or the Database On-Demand client, see Registering a JDBC driver.

Class name

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

• If, in the Driver description field, you select the name of a registered driver, then the SQL Wizard displays the fully qualified Java class name for you in this field and does not allow you to modify it.
• But if, in the Driver description field, you selected Other, then you must type the fully qualified class name of the driver into this field. Case is significant (COM is different from com). If you do not know the fully qualified class name, contact the provider of the driver.

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

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.

Click Disconnect to disconnect from the remote database server.

Tables

Statement type

Select

Click Select to select rows from a table or tables.

Select unique

Click Select unique to exclude duplicate rows from the results of a Select statement. When you select this checkbox, the SQL Wizard adds the DISTINCT modifier to the SELECT verb of the SQL statement on the SQL tab.

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.

Select Table(s)

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).

To select a table for use in the current SQL statement, select the checkbox next to the table name.

You will notice that:

• For Insert, Update, and Delete operations, you can select only one table.
• For a Select operation, you can select multiple tables.

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 SQL tab.

View schema(s)

Click Filter schema(s) to select the names of schemas that you want to appear in the list of available tables under Select Table(s).

Filter table(s)

Click Filter table(s) to specify the names or types of tables that you want to appear in the list of available tables under Select Table(s).

Refresh

Click Refresh to refresh the list of tables in the list of available tables under Select Table(s).

Join

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 Join tab is available only when the type of the SQL statement is Select.

The inner area of the Join 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 Join tab, use the buttons on the right side of the tab to create and manipulate joins.

The colored connecting lines have the following meanings:

• Light gray - A proposed join.
• Blue - An existing join.
• Red - The currently selected join.

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.
   • The SQL Wizard displays a light gray line between the two selected columns, to show that the two columns are proposed as candidates for a join.
   • If a join is possible between the two selected columns, then the SQL Wizard enables the Join button.
   • If a join is not possible between the two selected columns, because the two selected columns have different data types, then the SQL Wizard disables the Join button.
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 red 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 =):

• Inner join. The following rows are selected for processing:
  • The rows of the left table and the right table in which the contents of the joined columns are equal.
• Left outer join. The following rows are selected for processing:
  • All the rows of the left table.
  • The rows from the right table in which the contents of the joined columns are equal.
• Right outer join. The following rows are selected for processing:
  • The rows from the left table in which the contents of the joined columns are equal.
  • All the rows of the right table.

Condition

Use the Condition tab to specify one or more conditions for selecting rows. Rows that meet the conditions that you specify are displayed on the Results tab (Select type), or updated (Update type), or deleted (Delete type).

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

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 one condition for a single SQL statement. If you need to specify more than one condition, use the Find on another column button as described below.

Follow these steps to build a condition:

1. Expand the Selected table(s) listbox and select the table that you want to use in the condition. The listbox contains the names of all the tables that you have included in the list of selected tables on the Tables tab.
2. In the Columns listbox, select the column that you want the condition to test.
3. In the Operators listbox, select the operator that you want to use in the condition.
4. In the Values listbox, specify one or more values that you want to use in the condition. You can:
   • Type a value directly into a field; or
   • Click Find to search for values in the table and column that you have selected.
5. When you have finished specifying the condition, go to the next tab that you want to work on, or click Find on another column to specify another condition.

Find

Click Find to open the Value Lookup window and search for values in the table and column that you have selected for this condition. Follow these steps to use the Value Lookup window:

1. Type into the Search for field the character string or value that you want to search for.
2. Check Case sensitive if you want to search for upper and lower characters exactly as typed.
3. Select a Maximum hits value. This controls the number of values returned for each search.
4. Click Find now. The Value Lookup window looks in each row of the table that you have specified, in the column that you have specified, for a value that contains the character string or value that you have specified.
5. The Value Lookup window displays the results of the search in the Available values window. Only one instance of each value is displayed. For example, if the search string is device, and the table contains forty rows having Open device in the specified column, and twelve rows having Close device in the specified column, then the Value Lookup window displays one instance of Open device and one instance of Close device.
6. When you are finished looking at the search results, do one or both of the following actions:
   • To use a value in the condition, select one or more values in the list and then click Use Values.
   • To close the window, click OK.

Clear

Click Clear to clear all the values from the Values fields.

Find on another column

Click Find on another column to create a new condition. The SQL Wizard creates another Condition tab.

Find fewer rows (AND), Find more mores (OR)

On every Condition tab after the first, click one of these radio buttons to indicate the logical relationship (AND or OR) between the current condition and the preceding ones.

Delete condition

Click Delete condition to delete the current condition.

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 SQL 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".

Add >>

Use this button to select columns to be included in the data returned by the SQL Select statement. To add columns to the list of Columns to include:

1. Click the name of a table in the Selected table(s) list. The list contains the names of all the tables that you selected on the Tables tab.
2. Select one or more columns in the Columns list. The list contains all the columns of the table that you just selected.
3. Click Add >>.

<< Remove

Use this button to remove columns from the list of Columns to include. To remove one or more columns from the list:

1. Select the column or columns that you want to remove.
2. Click << Remove.

Select all, Deselect all

Use these buttons to select or deselect all the columns in both lists.

Move Up, Move Down

Use these buttons to change the order in which the selected columns are displayed.

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

Sort

Use the Sort 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 in response to the SQL Select statement.

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

Normally (that is, without the Sort 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 Sort 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.

• You can select more than one column for sorting the returned rows.
  • The rows are first sorted according to the contents of the first column that you specify (such as OBJECTID).
  • Then, for each group of rows that have the same value in the first column (for example, for all rows that have a value of 00007 in the column OBJECTID), the rows are sorted according to the values in the second column that you specify (such as SEVRITY).
  • This process is continued for each additional column that you specify.
• For each column, you can specify sorting in ascending order or descending order.

Add >>

Use this button to select columns for sorting. To add columns to the list of Columns to sort on:

1. Click the name of a table in the Selected table(s) list. The list contains the names of all the tables that you selected on the Tables tab.
2. Select one or more columns in the Columns list. The list contains all the columns of the table that you just selected.
3. Click Add >>.

<< Remove

Use this button to remove columns from the list of Columns to sort on. To remove one or more columns from the list:

1. Select the column or columns that you want to remove.
2. Click << Remove.

Select all, Deselect all

Use these buttons to select or deselect all the columns in both lists.

Sort order

Select Ascending or Descending to specify sorting in ascending or descending order.

Move Up, Move Down

Use these buttons to change the order in which the selected columns are used for sorting.

When you run the SQL statement, the SQL Wizard sorts the returned data first on the first column specified in the Columns to sort on list, then on the second column specified in the list, and so on.

SQL

The primary use of this tab is to allow you to run the generated SQL statement. You can also perform the following operations:

• Type changes into the generated SQL statement.

  • Be aware that, if you type changes into the generated SQL statement (by adding or deleting characters), and then save the statement using the Save button, then you will no longer be able to modify the SQL statement using the controls on other tabs of the SQL Wizard (such as the operators on the Condition tab).
  • The SQL Wizard displays a warning message to this effect when you first try to type changes into the generated SQL statement.
  • When you type changes into the generated SQL statement, the changes do not become permanent until you click Save to save the statement.
  • You can undo typed changes only if you have not clicked Save. To undo the typed changes, do either of the following operations:
    • Click Undo; or
    • Click another tab of the SQL Wizard.

• Copy the generated SQL statement to the clipboard. Once copied, the contents of the clipboard can be pasted into any other application that accepts text data from the clipboard. This feature is useful if you have another application that will execute a SQL query, but does not provide for easy generation or testing of an SQL query.

Undo

Undoes a change that you have typed into the SQL statement. Undo works only if you have not saved the SQL statement using the Save button. You can also undo a typed change by clicking another tab of the SQL wizard, if you have not clicked Save.

Run

Sends the SQL statement to the remote database server for execution.

Save

Saves the SQL statement for reuse at a later time. You can use this feature to save common SQL statements that you run multiple times. Queries for getting monthly reports of sales or generating lists of customers who made purchases in the last six months are examples of queries that are good candidates for saving.

• If you type changes into the generated SQL statement, and then click Save, you will no longer be able to modify the SQL statement using the controls on the other tabs of the SQL Wizard (such as the operators on the Condition tab).

Insert

This tab displays only if you select an Insert SQL statement type on the Tables tab. Insert allows you to insert a new row in the selected table.

When you finish with the Insert tab, click Next.

The Insert column information is as follows:

1. Column 1 indicates the name of the column in the database row. This can be something generic such as FIELD1 or FIELD2 or it can have a descriptive meaning such as NAME or AGE.
2. Column 2 indicates the type of data that exists in this column in the database. For example, CHAR(4) indicates that up to four characters can be placed in this column.
3. Column 3 is prefaced with an equal sign (=). This column is used to enter the data you want to update in your database column when you create this new row. For example, if your database contains automobile parts, and there is a field called PART# with a type of DOUBLE(8), you could type 10345 to represent a new part number for a steering wheel.

Update

This tab displays only if you select an Update SQL statement type on the Tables tab. Update allows you to modify data in an existing database row.

When you finish with the Update tab, click Next.

The Update column information is as follows:

• Column 1 indicates the name of the column in the database row. This can be something generic such as FIELD1 or FIELD2, or it can have a descriptive meaning such as NAME or AGE.
• Column 2 indicates the type of data that exists in this column in your database. For example, CHAR(4) indicates that up to four characters can be placed in this column.
• Column 3 is prefaced with an equal sign (=). This column is used to enter the data you want to update in your database column when you create this new row. For example, if your database contains automobile parts, and there is a steering wheel part number listed incorrectly as 01234 instead of 10345 in a field called PART#, you would type 10345 on the PART# line containing in the first column.

Related topics