This section describes the concepts and tasks for creating a model of your data source, establishing the specific relationships between those data, and how to use Netscape Application Builder to create queries that use a data model and JDBC RowSet calls to allow your application to access and retrieve information from a data source.

• About Data Models and Queries
• Creating a Data Model
• Importing a Third-Party Data Model
• Creating Queries
• Editing Generated Code: Queries
• Adding Conditionals to a Query
• Adding Joins to a Query
• Sorting Query Results
• Creating and Editing JDBC RowSet Objects

• Provides a reference to the structure of your data source(s).
• Defines the relationships that Netscape Application Builder uses as the default table joins in editing queries and running various data source wizards.
• Allows your data architect to define how tables relate to each other.
• Provides a means for breaking up a large data source schema into separate multiple subsets, so you don't have to retrieve large amounts of metadata (data about the tables in your data source) from your data source each time you define your queries.

• Selecting a Data Source
• Adding Data Source Tables
• Viewing or Editing Data Source Table Properties
• Viewing a Column's Properties or Changing the Column Alias
• Specifying Relationships Between Data Source Tables
• Adding Calculated Fields

1. Choose New - Data Model from the File menu.

Netscape Application Builder displays the following Insert Tables dialog box:



2. Click Driver to select a database driver type.

• If you have selected ODBC as your driver type, select the name of the data source in Data Sources.
• Otherwise, type the name of your data source In Data Sources and the name of your database in Database.
3. Type the user ID required by the selected data source in User Name.
4. Type the user password required by the selected data source in Password.

Netscape Application Builder logs you in to the selected data source. If the login is successful, Netscape Application Builder displays the following Add Tables to Data Model dialog box:

1. Select one or more table names in Available Tables.

To select multiple tables, you must perform a Shift + Click or Ctrl + Click key combination for the desired tables.

2. Click >.

Alternatively, you can double-click on an item to move it. The selected tables appear in Tables in Data Model.

3. Click >> to add all available tables to your data model.

All available tables appear in Tables in Data Model.

4. To remove a table from the data model:

1. Select the table in Tables in Data Model.
2. Click <. Or you can double-click on an item to remove it. Alternatively, you may also click << to remove all tables from your data model.

1. Select the table handle (the top area that includes the table name).
2. If Netscape Application Builder displays the properties in the Properties window, go to step 3; otherwise, right-click Properties; or from the Edit menu, choose View, then choose Properties.
3. Type a data source in Data Source.
4. Type a database name in Database, if applicable.
5. Type a user name in User.

1. Select the Columns field.
2. Select Table Properties
3. Type or edit the column alias in Column Alias.

1. Display the Properties window.
2. Select the desired table field and drag it onto a field in another table.

Netscape Application Builder automatically creates a one-to-many relationship between the two fields with the dragged column as the master column and the drop column as the detail column. This relationship is represented by a line linking the two fields. Perform step 2 if you want to change this default relationship type; otherwise, go to step 3.

3. Set the desired relationship type.

The following table describes the types of relationships you can use between your data model tables:

Relationship Type	Description
1-N	One-to-many
N-1	Many-to-one
1-1	One-to-one

To change the relationship type, perform the following steps:

1. Click on the desired relationship line between the two table fields. Netscape Application Builder displays the SQL join information within the Properties window.
2. Click the Relationship drop-down box within the Properties window and choose the desired relationship type.

Netscape Application Builder creates the specified relationship between the two table fields.

4. If your relationship requires more than one column to establish a unique value, repeat step 1.

For example, say you have three tables: Customer, Product, and Sales. Customer has only CustID and other information such as name, address, phone number. Product has only ProductID and other information such as product name, price, and amount in inventory. Sales has CustID, ProductID, SalesID, and AmountSold.

In this example it is clear that you must link Customer.CustID = Sales.CustID and Product.ProductID = Sales.ProductID.

So, the Sales table has multiple relationships with the other tables.

If the Customer table didn't have a CustID field, but instead had only FirstName and LastName fields, then you would need two links from the Customer table to the Sales table. Customer.FirstName = Sales.FirstName and Customer.LastName = Sales.LastName.

In this case, the Customer table requires two relationships with the Sales table in order to establish a unique value.

If a field needs to join another field in the same table or if you want several fields in a table to join with the same field in another table, you can create an alias. To create an alias, right-click on the table and select Insert Table Alias from the pop-up menu. For an example, see "Creating Aliases" on page 85.

1. Click the specific table to which you will add the calculated field.
2. From the Insert menu, choose Calculated Field.

Netscape Application Builder displays the calculation properties dialog box, as shown in the following illustration:



3. Type the name of the calculated field in Column Name.
4. Click Type to select the type of the calculated field. Netscape Application Builder provides the following data types:

Data Type	Description
Float	A decimal number, or number too large to fit in a long integer.
Integer	An integer.
VarChar	A series of readable characters.
TimeStamp	Month, day, year, hours, minutes, seconds, and nanoseconds.

5. Type the specific calculation formula you want to assign to this field in the Calculation edit box. You can use calculation formulas similar to the following examples:

• count(*)
• sum(Sales.cost_of_goods_sold)
• avg(Sales.price * Sales.units_sold)/1000

• add a built-in function to a calculation
• add an operator or delimiter to a calculation
• include additional table columns within a calculation

1. From the File menu, choose Open File.
2. Find the data model you want to open.

1. From the File menu, choose New, then choose Query.
2. Enter the desired information and click Next.
3. Choose a data model to use as the basis for this query and click Next. For more information on data models, see Creating a Data Model.

You are now ready to create a specific type of data source query.

4. The New Query wizard displays the Create a New Query - Initial Query Type dialog box. Refer to the following sections that detail how to create the type of query you require.

• Creating a SELECT Query
• Creating an INSERT Query
• Creating an UPDATE Query
• Creating a DELETE Query
• Testing Queries

1. Click Select in the "Create a New Query - Initial Query Type" dialog box and click Continue (or click the Insert Select Query toolbar button; or choose Insert, and then choose Select Query). Netscape Application Builder displays the following dialog box:


As you use Netscape Application Builder to create a SELECT query, you use the tabs displayed within the upper portion of the Select Query Properties dialog box. While you are filling out information in the tabs of this dialog box, Netscape Application Builder automatically generates the corresponding query file.

Note that you can view and/or edit the generated query at any time by clicking the SQL tab located along the top of the query properties dialog box, or by using the Netscape Application Builder properties window. For information that describes how to edit your queries, see Editing Generated Code: Queries.

2. Type a name for your query in Query Name, then click Next. Netscape Application Builder displays the Select Query Properties Fields information:

3. Follow these steps for each table containing fields you want to display:

1. Select a table from the Table box. The available fields for that table appear in the Fields box.
2. Move fields into or out of the Fields to Display box by clicking the arrow icons. The double-arrow icons move all fields from one box to the other. Fields that appear in the Fields to Display box are selected for display by the query.
3. For query fields, check the Select Distinct checkbox if you want to perform a restricted search for all fields. A SELECT DISTINCT query retrieves only the unique instances of the requested target search items (for example, if you were searching for customers from different states, and your data source contained two customers named "Jerone Garcia," both from California, the select distinct query would only return the first instance). For more information, consult the SQL reference for your data source.
4. You can specify conditional behavior for the query by adding conditions.

1. Click the Conditionals tab on the Select Query Properties dialog box. Netscape Application Builder displays the information displayed in the following illustration:

2. Add a condition by clicking Add Condition.
3. Build the condition by selecting the table and field to use, selecting a conditional operator, and entering a value to limit the field's scope.

Note that if a specific field is preceded with a colon (":"), Netscape Application Builder displays the default field values for the following operators:

• =
• <>
• <=
• >=

For information about these operators, see Simple Conditional Operators.

5. You can sort the results of your query by specifying which fields to sort and moving them into the Sorted Fields box.

1. Click the Sorting tab on the Select Query Properties dialog box. Netscape Application Builder displays the associated data source fields:


6. Change the sort order on a given field by clicking the Ascending (A to Z) or Descending (Z to A) button. For additional information that describes how to sort your query results, see Sorting Query ResultsFields must be joined in order to show the relationship between tables in the selection. For each set of fields to be joined, click the cell between them in the Join column and select the appropriate operator. For additional information regarding the possible join conditionals, see Adding Conditionals to a Query.
7. Click the SQL tab to review the generated SQL (or you can edit the query directly). For information that describes how to edit your queries, see Editing Generated Code: Queries.
8. To test the generated SQL, see Testing Queries.
9. Click Finish. Netscape Application Builder adds the query to your project and displays it on the main window. The properties for your query are shown in the Properties window, if it is visible.

1. Click Insert in the "Create a new query" dialog box (or click the Insert Query toolbar button; or choose Insert, and then choose Insert Query). Netscape Application Builder displays the following dialog box:


As you use Netscape Application Builder to create an INSERT query, you use the tabs displayed within the upper portion of the Insert Query Properties dialog box. While you are filling out information in the tabs of this dialog box, Netscape Application Builder automatically generates the corresponding query file.

Note that you can view and/or edit the generated query at any time by clicking the SQL tab located along the top of the query properties dialog box, or by using the Netscape Application Builder properties window. For information that describes how to edit your queries, see Editing Generated Code: Queries.

2. Choose a name for your query, then click Next.
3. Follow these steps for each table containing fields you want to display:

• Select the data source.
• Select a table from the Table box. The available fields for that table appear in the Fields box.
• Type the specific value for each field in Value. Note that Netscape Application Builder displays the default values for selected fields in quotes for string, date, time, and datetime field types.
• Click Next.
4. Click the SQL tab to review the generated SQL.
5. For information that describes how to edit your queries, see Editing Generated Code: QueriesTo test the generated SQL, see Testing Queries.
6. Click Finish.

Netscape Application Builder adds the query to your project and displays it on the main window. The properties for your query are shown in the properties window, if it is visible.

1. Click Update in the "Create a new query" dialog box (or click the Insert Update Query toolbar button; or choose Insert, and then choose Update Query). Netscape Application Builder displays the following dialog box:


As you use Netscape Application Builder to create an UPDATE query, you use the tabs displayed within the upper portion of the Update Query Properties dialog box. While you are filling out information in the tabs of this dialog box, Netscape Application Builder automatically generates the corresponding query file or method calls.

Note that you can view and/or edit the generated query at any time by clicking the SQL tab located along the top of the query properties dialog box, or by using the Netscape Application Builder properties window. For information that describes how to edit your queries, see Editing Generated Code: Queries.

2. Choose a name for your query, then click Next.
3. Follow these steps for each table containing fields you want to display:

• Select the data source.
• Select a table from the Table box. The available fields for that table appear in the Fields box.
• Type the specific value for each field in Value. Click Next.
4. You can specify conditional behavior for the query by adding conditions.

• Add a condition by clicking Add Condition.
• Build the condition by selecting the table and field, selecting a conditional operator, and entering a value to delimit the field's scope. For additional information regarding the possible join conditionals, see Adding Conditionals to a Query.
5. Click the SQL tab to review the generated SQL. For information that describes how to edit your queries, see Editing Generated Code: Queries.
6. To test the generated SQL, see Testing Queries.
7. Click Finish. Netscape Application Builder adds the query to your project and displays it on the main window. The properties for your query are shown in the properties window, if it is visible.

1. Click Delete in the "Create a new query" dialog box (or click the Insert Delete Query toolbar button; or choose Insert, and then choose Delete Query). Netscape Application Builder displays the following dialog box:


As you use Netscape Application Builder to create a DELETE query, you use the tabs displayed within the upper portion of the Delete Query Properties dialog box. While you are filling out information in the tabs of this dialog box, Netscape Application Builder automatically generates the corresponding query file or method calls.

2. Choose a name for your query, then click Next.
3. Follow these steps for each table containing fields you want to delete:

1. Select the data source.
2. Select a table from the Table box and click Next.
4. You can specify conditional behavior for the query by adding conditions. For additional information regarding the possible join conditionals, see Adding Conditionals to a Query.

1. Add a condition by clicking Add Condition.
2. Build the condition by selecting the table and field, selecting a conditional operator, and entering a value to delimit the field's scope.
5. Click the SQL tab to review the generated SQL. For information that describes how to edit your queries, see Editing Generated Code: Queries.
6. To test the generated SQL, see Testing Queries.
7. Click Finish. Netscape Application Builder adds the query to your project and displays it on the main window. The properties for your query are shown in the properties window, if it is visible.

1. From the File menu, choose Open to open the desired query file or double-click the query file in the project map window or within the Project window.
2. Right-click the specific query you want to edit within the Outline tab and select the Goto option. Netscape Application Builder displays the Source tab.
3. Edit the generated SQL.

1. From the File menu, choose Open to open the desired query file or double-click the query file in the project map window or within the Project window.
2. Click the Query Properties SQL tab.

Netscape Application Builder displays the associated SQL code for the specified query within the Query Properties SQL dialog box, as shown in the following illustration:

If you add incorrect or unsupported SQL code, Netscape Application Builder disables the other available tabs until you correct the error.

1. From the File menu, choose Open to open the desired query file or double-click the query file in the project map window or within the Project window.
2. Click the specific query you want to edit within the left portion of the query window.

Netscape Application Builder displays the following information within the Properties window:

• query name
• description
• type
3. Make the desired changes within the properties window.

• Simple condition.

For example: WHERE lastName='Garcia'

• Boolean clause. This clause can be a set of simple conditions linked together by a logical Boolean operator (AND, OR):

For example: WHERE lastName='Garcia' OR city='half moon bay' OR acctNum=1234567890

1. From the File menu, choose Open to open the desired query file or double-click the query file in the project map window or within the Project window.
2. Click the Query Properties Conditionals tab.

Netscape Application Builder displays the Query Properties Conditionals dialog box, as shown in the following illustration:



3. Click Add And or Add Or to add an AND or OR operator.
4. Click Add Condition to add a condition to the AND or OR operator.

Netscape Application Builder displays the Conditionals Properties dialog box, as shown in the following illustration:



5. Select the specific table, fields, and operators.

For a list of the available operators and their definitions, see Simple Conditional Operators.

6. Click in the text field in Values.

The Conditionals Properties dialog box highlights the Values text field.

7. Type the required values in the text boxes. Note that Netscape Application Builder displays the default query parameter.

For example, if you selected "equals to", type a value which the data source field must match, such as 100 or `Glenn'.

Note that single quotes are required around string and date values.

1. From the File menu, choose Open to open the desired query file, or double-click the query file in the project map window or within the Project window. The Select Query Properties window appears.
2. Click the Joins tab on the Query Properties dialog box.

Netscape Application Builder displays the Query Properties Joins dialog box, as shown in the following illustration:



3. For each column, click the Join field to select the desired join operator.
4. Click Next to continue editing the selected query, or click Finish if you have finished editing the query.

1. From the File menu, choose Open to open the desired query file, or double-click the query file in the project map window or within the Project window.
2. Click the Sorting tab on the Query Properties dialog box.

The following illustration shows the Query Properties Sorting dialog box:



3. In Fields, select one or more field names that you want to use as a sort key.

To select multiple fields, simply click one field name after another.

4. Click >.

Netscape Application Builder adds the selected field to the Sorted Fields list. Note that the default sort order is A-Z (ascending order). To change the field sort order to Z-A (descending order), click Sort (Z-A), located between the Fields and Sorted Fields lists.

Alternatively, you can also select a field and then click Sort (A-Z) or click Sort (Z-A) to add that field to the Sorted Fields list with either respective sort order.

The field names subsequently appear in Sorted Fields.

5. To change the sort order on a particular column:

1. Select the field name in Sorted Fields.
2. Click Sort (A-Z) or Sort (Z-A), for ascending or descending order.
6. To remove a field from the sort key:

1. Select the field name in Sorted Fields.
2. Click <.

You can also remove all the fields by clicking <<.

• Setting Data Connections
• Manipulating Result Sets

To use a custom property editor to create the result set property, click the custom property editor button in the property definition. The custom property editor, shown below, appears:

• A row set created from a query to a relational database causes a DBRowSet object to be created. You can either start with an existing query file or create a new one from a data model.
• A row set created from a stored procedure causes a DBRowSet object to be created. You can either start with an existing query file or create a new one from a data model.
• An in-memory row set that the user populates by hand causes a MemRowSet object to be created. You must create the set of data (or the linkage to it) by hand in the servlet.

1. Choose DBRowSet in the result set class dialog box.
2. Click Next. Enter a name for the new result set.

Note that after you specify the result set type and name, this process is very similar to using the Database Forms wizard, described in "Using the Database Forms Wizard".

3. Click Next. Choose "Use an existing query file" and select a query file from the drop-down list.
4. Click Next. Specify a connection to a data source. If an appropriate connection already exists, select it from the drop-down list. Otherwise, select "Create a new connection" and click Next. You are prompted for a database driver and related data source, along with a user name and password to connect to the data source.
5. Click Next. Move one or more queries from the Available Queries column to the Queries to Use column with the arrow buttons.
6. Click Next. The following dialog box appears if you are editing an existing result set, but not if you creating a new one:


This dialog box allows you to specify the binding between the servlet parameters in the Source column and the query parameters in the Query Parameters column.

The available source items depend on the type of query listed in the Source Type column.

You can insert or delete rows using the buttons at the bottom of the dialog box.

7. Click Finish to create the DBRowSet.

1. Choose DBRowSet in the result set Class dialog box.
2. Click Next. Enter a name for the new result set.

Note that after you specify the result set type and name, this process is very similar to using the Database Forms wizard, described in "Using the Database Forms Wizard".

3. Click Next. Choose "Use a data model" to create a new query.
4. Click Next. Specify the data model on which to base the new query. If you need to create a new data model, click Create a New Data Model. The property editor exits; you must run it again after your data model has been created.
5. Click Next. Specify a connection to a data source. If an appropriate connection already exists, select it from the drop-down list. Otherwise, choose "Create a new connection" and click Next. You are prompted for a database driver and related data source, along with a user name and password to connect to the data source.
6. Click Next to select fields to display. First select the table to which the field belongs, then move the field from the Fields column to the Fields to Display column using the arrow buttons.
7. Click Next to select one or more fields on which to sort the incoming data. Fields are shown in sort order from top to bottom; you can rearrange their order using the up and down buttons on the right. You can adjust whether each field is sorted in ascending or descending order by clicking the A-Z (ascending) or Z-A (descending) button.
8. Click Finish to create the DBRowSet.