Database On-Demand SQL statements

Database On-Demand provides an interface for creating, modifying, and running SQL statements. A set of tabs guides you through the process of building and executing a valid SQL statement. The tabs include:

• Logon
• Tables
• Join
• Condition
• Columns
• Sort
• Output
• SQL
• Results
• Insert
• Update
• Delete

Logon

Click the Logon tab to connect to the target database. All fields are required.

• Database URL
  Type the URL for the database you want to work with. Consult your JDBC driver documentation for the format of the database URL. For example, the iSeries Toolbox for Java JDBC driver requires:

  jdbc:as400://iSeriesname

  where iSeriesname is the fully-qualified network name of the database host.

  To use a proxy server when connecting to the iSeries database, include the proxy server name and port number:

    jdbc:as400://iSeriesname;proxy server=HODServerName:proxyServerPort
    

• Userid/Password
  Type your user ID and password for the specified database.
• Driver description
  Select the JDBC driver used to communicate with the specified database.
• Class name
  This field contains the class name of the driver associated with the descriptive name in the Driver field.

Enter the required information and Click Connect to open a connection to the host database.

• If you see this message when logging on: Please disable the JIT compiler and restart the browser.
  you must stop your browser, rename the Netscape jit*.dll file so that it is not a dll file type and restart your browser. This file is located in the \program files\netscape\communicator\program\java\bin\ directory.



• If you see this message when logging on: Please reset the environment variable CLASSPATH to NULL.
  you must stop your browser, reset the CLASSPATH to NULL and restart your browser. Check the documentation on your operating system for information regarding how to reset environment variables.

After connecting, the connect button is replaced by a Disconnect button, which allows you to disconnect from the database. Also, the following set of tabs are added to the Logon tab:

Tables

Click the Tables tab to specify the tables you want to access in your SQL statement, and the type of SQL statement that you will generate.

1. Select the SQL statement type you want to use. Valid statement types include:
   
   
   • Select
     Allows you to select records from host database table(s).
   
   
   
   • Select Unique
     Allows you to select distinct records from host database table(s).
   
   
   
   • Insert
     Allows you to insert a record into the host database table.
   
   
   
   • Update
     Allows you to update the host database table records using the given values.
   
   
   
   • Delete
     Allows you to delete records from the database table, and you can specify a condition for deletion.


2. Select the table(s) you want to access. You can select multiple tables when the statement type is select or select unique.
3. Click Next.

Click View schema(s) to add tables from additional schemas. A schema is similar to a database or library. For iSeries schemas, the defaults are what is in the default library list for the user profile.

Click Refresh to update the list of tables that are displayed. New tables that were added to the schema are displayed in the Table list; deleted tables are removed. Refresh does not reset any selections that have been made.

Join

Click the Join tab to:

• Join fields from multiple selected tables
• Join a table alias
• Remove a join

This tab is used only if two or more tables are selected.

Joining fields

1. Select a column from the first table.
2. Select a column from the next table.
3. Click Join.

A line connects the joined columns and changes color when the join is enabled. Note that the information area keeps you informed of the join status and will let you know if a requested join is not valid. You cannot, for example, join columns with mismatched data types.

By default, a join request is assumed to be an inner join. An inner join joins only the rows where the values of the two columns match. Click Options to request other types of joins. You can select:

• Left outer join
  This is an inner join that includes any rows in the left-most table that are not already included in the inner join.
• Right outer join
  This is an inner join that includes any rows in the right-most table that are not already included in the inner join.

When you are working with multiple joins, use the left and right arrow buttons (< >) to navigate between joins. The selected join is indicated by a line.

Joining a table alias

You can join a table column with an alias column. An alias is an alternate name for a table. Using an alias allows you to join two columns in the same table, or to create a more meaningful name for the column.

1. Select a column from the table.
2. Click Alias. This creates an alias for the selected table and displays the table columns. The two lists of columns will be the same.
3. Select a column in the alias.
4. Click Join.

Removing a join

1. Select the joined columns.
2. Click Unjoin.

The join line disappears.

When you finish with the Join tab, click Next.

Condition

Click the Condition tab to:

• Specify one or more SQL conditions.
• Remove a SQL condition.

Specifying an SQL condition

1. Select the table you want to use from the Selected table(s) drop-down list. The Selected tables(s) list includes only the tables that are selected on the Tables page.
2. Select the column from the Columns list.
3. Select an operator from the Operator list.
4. Specify values. You can type values in the fields, or you can click Find and select from the Value Lookup list. To remove a selected value from the Value Lookup list, click Clear.

The Value Lookup window allows you to find values for a condition.

1. Type a character string in the Search for field and click Find now.
2. Check Case sensitive if you want to search for upper and lower characters exactly as typed in the Search for field.
3. Select a Maximum hits value. This controls the number of values returned for each search.
4. Select a value or values from the list and click Use value.
5. Click OK.

Click Cancel to close the Value Lookup window without adding any of the selected values to the Condition tab.

To specify additional SQL conditions:

1. Click Find on another column to display a second condition tab. This tab is labeled Condition 2.
2. Follow the preceding steps to specify the second condition.

Click Find on another column for each additional condition until you have specified all the conditions for the SQL statement.

Removing an SQL condition

Select the appropriate condition tab, then click Delete.

When you finish with the Condition tab, click Next.

Columns

Click the Columns tab to select the columns you want to include in the query results.

1. Select a table from the Selected table(s) drop-down list.
2. Select one or more columns from the Columns list.
   • Click Select all to select all columns in the list.
   • Click Deselect all to deselect all columns in the list.
3. Select Add to add selected columns to the list.

Use the Add<< and >>Remove buttons to move column names from one list to another.

When you finish with the Columns tab, click Next.

Sort

Click the Sort tab to specify the column(s) used to sort the results.

1. Select a table from the Selected table(s) drop-down list.
2. Select one or more columns from the Columns list.
   • Click Select all to select all columns in the list.
   • Click Deselect all to deselect all columns in the list.
3. Select Add to add selected columns to the Columns to sort on list.

Use the Add>> and <<Remove buttons to move column names from one list to another.

You can select Ascending or Descending from the Sort Order field for each of the lines in the Columns to sort on list. Columns sorted in ascending order have leading characters of a-through-z; descending order columns have leading characters of z-through-a. Columns are sorted in ascending order by default.

The sort rules apply in the order they appear in the Columns to sort on list. The primary sort column is at the top of the list, the secondary sort column is second in the list, and so on, until there are no more lines in the list. If you want to adjust the order in which the sort rules apply, use the following buttons:

• To make a column's sort order earlier:
  Select the column in the Columns to sort on list, then click Move up.
• To make a column's sort order later:
  Select the column in the Columns to sort on list, then click Move down.

When you finish with the Sort tab, click Next.

Output

Click the Output tab to direct the output (results) of the SQL query to your display or to a file.

1. Display
   Choose Display if you want the output to be directed to the display. The results of the query appear on the Results tab.
2. File
   Choose File if you want the output to be directed to a file.

When you finish with the Output tab, click Next.

You can specify whether you want the results of the SQL statement directed to the Results tab or to a file. The main selection options are Display or File.

1. Display
   Directs the output to the display. The query results appear on the Results tab after you run the SQL statement.

   You can limit the number of rows displayed on the Results tab by adjusting up or down the Display Options - Maximum number of rows to display field. The maximum number of rows that can display is 1000. If you have queries that generate more than 1000 rows, it is recommended that you direct the query output to a file rather than to a display. If the query generates more rows than the maximum specified in this field, the additional rows are ignored.

2. File
   Directs the output to a file.

   Several fields are required when you save the results to a file.

   • File name:
     Specify the file name, a drive and a directory path name for the target file. Click Browse to select a file name, a path name, and a drive, if you desire.

   • File Type:
     Select the format for the stored results.
     • ASCII Text
       Stores the results in plain text format.

     • Comma separated values
       Separates columns separated by commas. Many spreadsheet and database programs allow this format to be imported. It is commonly abbreviated as CSV Format.

     • Lotus 1-2-3(WK1)
       Select Lotus 1-2-3(WK1) if you are using the file with Lotus 1-2-3.

     • Microsoft Excel 3.0 (BIFF3)
       Select Microsoft Excel 3.0 (BIFF3) if you are using the file with a version of Microsoft Excel that supports importing of data in BIFF3 format.

     • Microsoft Excel 4.0 (BIFF4)
       Select Microsoft Excel 4.0 (BIFF4) if you are using the file with a version of Microsoft Excel that supports importing of data in BIFF4 format.

     • HTML
       Select HTML if you will be using the file with a program that supports HTML formatted files. HTML files are typically displayed using Web browser programs such as Microsoft Internet Explorer or Netscape Navigator.

3. Select Overwrite if file exists if you want to create a new file each time this query is run.

4. Select Append to file if file exists if you want to append the results of the SQL query to an existing file each time the SQL query is run. Append to file is only valid for ASCII text and CSV file formats
   

SQL

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

• Review or edit the generated SQL statement.



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



• Save the SQL statement for reuse at a later time. This statement is available to you each time you log on to the Database On-Demand applet. You can use this 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.

Results

Click the Results tab to see query results directed to the display.

Query results appear on this tab after you click Run on the SQL tab or in the Database On-Demand Access window. Each row is represented as a row in the table.

You can change the sort order of any column by clicking on the column header in the table. Clicking again restores the table to the previous ascending or descending order. You may also change the displayed width of any column by dragging the column margin to the right to increase the size or to the left to decrease the size.

Sorting by clicking on the column header is available only in Java 1 browsers without a Java 2 plug-in.

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 your database.

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

Delete

This tab displays only if you select a Delete SQL statement type on the Tables tab. Delete allows you to delete records from an existing database table. You can also specify a condition which selects records for the Delete statement to act upon.

When you finish with the Delete tab, click Next.