Using Cloudview
Page 3 of 4

Working with Cloudview to Create Dictionary Objects

In this project, you will create two tables in the HelloWorldDB database, store data, retrieve data, and update data.

Start Cloudview in the Home Directory

The Java class name of Cloudview is COM.cloudscape.tools.cview.

  1. Open a command window and change directories to your_tutorial_home.
  2. Run your classPathScript in that directory.
  3. Run the cview program, using the -D parameter to set cloudscape.system.home:

    java -Dcloudscape.system.home= your_tutorial_system
        COM.cloudscape.tools.cview

    The Main window appears:

Figure 3-3 The Main window

Connect to HelloWorldDB

  1. To open a connection to the HelloWorldDB database, choose Open by Name from the File menu.

  1. Type HelloWorldDB, which will connect to a database of that name in the system directory.

    Quiz: Where is the system directory?

    Answer: Wherever cloudscape.system.home points. If not set, the system directory is the current directory (the directory from which you started Cloudview).

  2. Click OK to connect.

    The database screen appears. The left-hand hierarchy window shows the system and database hierarchy.

    Underneath the system icon, all the open databases appear. Only one database is now open, HelloWorldDB.

    Underneath the database icon are some icons representing other database objects such as tables, views, aliases, and more. Since HelloWorldDB has no database objects yet, no entities appear under these icons.

    You will create a table in the next task.

Create the Sayings Table

  1. Select the HelloWorldDB icon.
  2. Choose New->Table from the Edit menu.

    The Table window appears in the right-hand window.

  3. Type Sayings in the Name box to name the table Sayings.

    Tables that you create are case-insensitive; they are always stored as uppercase unless you choose the case-sensitive option (see the Preferences tab).

    The table will have two columns: Number and Saying.

  4. Type Number in the Name box in the Columns section.

  1. Select the Type box of the current row to get a drop-down list of available data types.
  2. Choose SMALLINT as the data type.
  3. Select No from the drop-down list for Nullable. (This column must always contain a value).
  4. Click the + icon to create a new column.
  5. Type Saying in the Name box.
  6. Select the Type box to get a drop-down list of available data types.
  7. Choose VARCHAR as the data type.
  8. Type 50 in the Length box.

    The column can store varying-length strings of up to 50 characters.

  9. Click OK to save the table definition to the database.

Create the Responses Table

  1. Select the HelloWorldDB icon in the left-hand hierarchy window.
  2. Choose New->Table from the Edit menu.

    The Table window appears in the right-hand window.

  3. Type Responses in the Name box to name the table Responses.

    The table will have two columns: Number and Response.

  4. Type Number in the Name box in the Columns section.
  5. Select the Type box to get a drop-down list of available data types.
  6. Choose SMALLINT as the data type.
  7. Select No from the drop-down list for Nullable. (This column must always contain a value).
  8. Click the + icon to create a new column.
  9. Type Response in the Name box.
  10. Select the Type box to get a drop-down list of available data types.
  11. Choose VARCHAR as the data type.
  12. Type 50 in the Length box.

    The column can store strings of up to 50 characters in length.

  13. Click OK to save the table definition to the database.

    Two new icons should now appear under the Tables icon in the left-hand hierarchy window for the two new tables.

Define a Primary Key

A table should have a primary key. A primary key defines that column or columns that uniquely identify a row in a table. When you define a primary key on a column or column, the values in that column or columns must be unique; no two rows can have the same values in that column or columns.

In the Sayings table, the Number column should be the primary key.

  1. Select the Sayings table icon, and click the plus sign to the left of the table.
  2. Select the keys icon.
  3. Choose New->Key from the Edit menu.
  4. Type Sayings_Pk in the Name box.
  5. The Number column should already be selected as the candidate for primary key column, so click OK.

Define a Foreign Key

The Number column in the Responses table corresponds to the Number column in the Sayings table. The Responses table should have a foreign key that enforces that relationship.

  1. Select the Responses table icon, and click the plus sign to the left of the table.
  2. Select the keys icon.
  3. Choose New->Key from the Edit menu.
  4. Type Responses_fk in the Name box.
  5. The Number column should already be selected as the candidate for foreign key column.
  6. Choose Foreign Key from the drop-down list of key types.
  7. Choose Sayings from the drop-down list in the Foreign Tables field.
  8. Its primary key (Sayings_Pk) should already be selected as the correct primary key to reference.
  9. Click OK.

Enter Data Graphically and with an SQL Statement

Cloudview provides a graphical way to enter data.

  1. Select the SAYINGS icon in the left-hand hierarchy window.
  2. Click the Data tab.

    No rows appear, because the table is empty.

  3. Click the + icon to add a new row.
  4. Type 1 in the Number column and Hello, World! in the Saying column.
  5. Click the Save icon to save your changes. The Save icon looks like this:

    You can also enter data with an SQL statement. You will do that next.

  6. Click the HelloWorldDB icon in the left-hand hierarchy window.

    The database screen appears in the right-hand window. The database screen includes an SQL window.

  7. Type the following SQL-J statement in the SQL window:

    INSERT INTO SAYINGS VALUES (2, 'You can''t have your cake')

    If you are doing this tutorial on-line, you can cut the statement directly from this document and paste it into the window.

    This statement will insert the integer 2 and the words "You can't have your cake" into the first row of the database.

NOTE: You must enclose strings in single quotation marks in the SQL-J language. In addition, since a single quotation mark (which is the same as an apostrophe) delimits the statement, you must "escape" the apostrophe within the string with another single quotation mark.

SQL-J commands are case-insensitive. That means that you could type "INSERT INTO" and "VALUES" in uppercase, lowercase, or any mix of the two. For clarity and consistency, this book shows SQL-J commands in ALL CAPS.

  1. Click the lightning bolt to execute.

    After the statement execution has completed, information about how long it took to compile and execute appears at the bottom of the window.

Open and Execute an SQL Batch File

Cloudview allows you to open and execute text files that contain one or more SQL-J statements. In this task, you will execute the world.sql text file to enter more data into the database.

  1. Click the Script icon above the SQL window and choose Open.
  2. Navigate to your home directory, then select world.sql, then click Open.

    A batch of SQL-J statements appears in the window. A batch is a collection of statements. When executing more than one statement at once, you separate statements with semicolons.

  3. Click the lightning bolt to execute.
  4. You can click the left arrow to scroll back to the first statement you executed, if you wish. Click the right arrow to go back to the most recent statement.

Browse the Data in the Sayings Table

Cloudview provides a graphical data browser for viewing data in a table--it is the same window you used to enter your first row.

  1. Select the Sayings table icon in the left-hand hierarchy window.
  2. Click the Data tab in the right-hand window.

    The data you just entered appears in the data grid.

    You may need to enlarge the width of the Sayings column to view all the data.

  3. Select the Responses table icon to view the data in the Responses table.