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 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 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 Host On-Demand 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:
A sequence of tabs guides you through the process of building
and running an SQL statement. These tabs are:
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 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).
- Driver description
-
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.
- 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 Host On-Demand 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:
-
Select a column name in the first table that you want to join.
-
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.
-
Use the Join Type key to select
a join type (see Join Type below).
The default type is an inner join.
-
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:
-
Select an existing join using the > or < button.
-
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:
-
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.
-
In the Columns listbox,
select the column that you want the condition to test.
-
In the Operators listbox,
select the operator that you want to use in the condition.
-
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.
-
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:
-
Type into the Search for field the character string or value that you want to search for.
-
Check Case sensitive if you want to search for upper and lower characters exactly as typed.
-
Select a Maximum hits value. This controls the number of values returned for each search.
-
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.
-
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
.
-
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:
-
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.
-
Select one or more columns in the Columns list.
The list contains all the columns of the table that you just selected.
-
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:
-
Select the column or columns that you want to remove.
-
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.
In addition:
-
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:
-
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.
-
Select one or more columns in the Columns list.
The list contains all the columns of the table that you just selected.
-
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:
-
Select the column or columns that you want to remove.
-
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:
- 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 the 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 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