Previous     Contents     Index     DocHome     Next     
Application Builder 6.0 iPlanet Application Builder User's Guide



Chapter 4   Creating Data Access Logic


This chapter describes the concepts and tasks for creating a model of your data source, establishing the specific relationships between those data, and how to use iPlanet Application Builder to create queries that use a data model to access and retrieve information from a data source.

The following topics are described in this chapter:



About Data Models and Queries

This section provides an overview for data models and queries. For a more complete discussion of how to create and edit data models, see "Creating a Data Model." For more information regarding queries, see "Creating Queries."


About Data Models

A data model, indicated by the .kdm file extension, is an entity relationship (ER) diagram that specifies the data source tables and relationships used in your application (an ER diagram describes the attributes of database entities and the relationships among them). You create your data source queries by first constructing or importing a data model that includes tables and the relationships (joins) between them from a specific portion of your data source. You must define a data model before creating query files or pages that access database data.

To build a data model, you select data sources, and then add and/or remove tables, fields, and relationships. Your iPlanet Application Builder queries are based on the information contained within your data model(s). Once you create a data model for a data source, you can reuse that model extensively.


About Data Access Queries

A query is a statement that specifies which data to retrieve from, insert into, delete from, or update within a data source. A data source is a collection of data electronically stored within a relational database, legacy system, or object database.

Typically, the results of a query are displayed in a report. iPlanet Application Builder stores queries in files of type .gxq that can be subsequently executed by an associated servlet or JavaServer Page (JSP) file (via a JDBC method) running in iPlanet Enterprise Server. For more information, see the Programmer's Guide.

The iPlanet Application Builder query window has two view tabs: Outline and Source. The Outline view shows each query. The Source view shows the actual SQL in the file.

The query command options (via the Insert menu) are enabled along the top portion of the iPlanet Application Builder screen once you open or create a query file. Additionally, you can display the Query toolbar by choosing Toolbars - Query from the View menu.

You edit the SQL code that corresponds to a query directly, either by opening the query and choosing the source tab or double clicking on the query and choosing the SQL tab from the outline view. The following illustrations show the outline and source views for an example query:






Using Data Models and Queries

iPlanet Application Builder enables you to design queries based on one or more data models that map the high-level relationships between your data to the actual data themselves. This data model represents the physical data that is stored in the data source.

A data model offers you the following benefits for your project's database query (a file of type .gxq) design:

  • Provides a reference to the structure of your data source(s).

  • Defines the relationships that iPlanet 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.

Using iPlanet Application Builder, you can create database queries by importing an existing PowerDesigner data model (a file of type .pdm) or by generating a new data model (a file of type .kdm).

The following figure shows the various data model inputs you can use for designing a data model and the resulting output file (a file of type .kdm). Once you have created a data model, you can begin designing the database queries.



Figure 4-1    Data Model Inputs

You can create one or more data models, depending on the level of organization that you require for your data source queries. For example, you might decide to have separate data models for the accounting, inventory, and sales organizations within your company rather than having one data model for your entire company data source.

Dividing data source tables into multiple data models enables query designers to view a smaller subset of the entire database each time a new query is created. By including only the relevant data source tables required for a particular department, a query designer can design queries faster, since iPlanet Application Builder displays only the relevant tables and fields within that department's data model.

In addition, if your database is particularly large, yet a specific data model represents only a small subset of your data, application performance may be improved, since this multiple data model architecture enables the query designer to avoid searching through the entire database when a new query is only associated with a small subset of the database.



Creating a Data Model


To create a new data model, use iPlanet Application Builder to perform the following tasks:


Selecting a Data Source

You use a data source to define the specific database tables you want to use. To select a data source, perform the following steps:

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

    iPlanet Application Builder prompts you for the type of connection. Once you select the connection type, the following Insert Tables dialog box appears:




    Note The query's connection information must match the data model's connection information; othewise, iPlanet Application Builder considers the query to be a custom SQL query.


    • If you selected ODBC as your driver type, select the name of the data source in Data Sources field.

  2. Type the user ID required by the selected data source in User Name.

  3. Type the user password required by the selected data source in Password.

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




Adding Data Source Tables

You specify data tables for your data model to determine which portion of your data source you want to use to model your data. To use iPlanet Application Builder to add data source tables, perform the following steps:

  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.

      iPlanet Application Builder displays all of your selected data source tables in the data model window.


Viewing or Editing Data Source Table Properties

You can view the properties of your data source tables to determine how to specify the interrelationships between your data model tables. In addition, if you import a data model or if you're moving from an application testing phase to a production phase, you can modify the database and user name while viewing the table properties. In the case of an imported data model, the .pdm file doesn't specify the database and user name information, so you must supply it; in the latter case, your production database is probably different from the one you use for testing and debugging your application, so this feature enables you to make the required changes without having to modify your original data model.

To view or change the data source table properties, perform the following steps:

  1. Select the table handle (the top area that includes the table name).

  2. If iPlanet 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.


Viewing a Column's Properties or Changing the Column Alias

You can view the column properties of your database tables in case you don't know whether a particular field is a number or string type. If you are creating a condition, you have to know whether to add single quotes, since string, date, time, and datetime data types must be single quoted. You may also want to know what the alias is for a specific column.

You use a column alias for a column name to rename the column title when you use a SELECT statement in a query. iPlanet Application Builder uses the column alias for referencing calculated columns and joins in hierarchical queries.

To use iPlanet Application Builder to view the database column properties or to change a database table's column alias, perform the following steps:

  1. Select the Columns field.

  2. In the properties window, type or edit the column alias in Column Alias.


Specifying Relationships Between Data Source Tables

You use iPlanet Application Builder to specify the relationships between the tables in your data model.

A relationship is a named connection between data source tables. For example, you might create a relationship between the customer and order tables by using a field common to both tables such as customerID. When you run a SELECT query, the application returns a result set in which key fields, such as customer and order records with identical customerID values, are combined into a single record.


Creating Relationships

To create a relationship between tables, perform the following steps:

  1. Display the Properties window.

  2. Select the desired table field and drag it onto a field in another table.

    iPlanet 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:


Table 4-1 Relationship Types

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

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

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


Modifying or Deleting Relationships

You can use the iPlanet Application Builder Properties window to modify or delete relationships.

To modify the relationship, use the iPlanet Application Builder Properties window and modify the relationship as desired.

To remove a relationship, select the relationship you want to remove, and then press the Delete key, choose Delete from the Edit menu, or press the Backspace key.


Adding Calculated Fields

To reuse calculations across multiple queries in your application:

  1. Select the table you want add a calculated field.

  2. Right-click to display the table menu.

  3. Select Insert Calculated Field.

    iPlanet Application Builder displays the calculation properties dialog box:



  4. Type the name of the calculated field in Column Name.

  5. Select the type of the calculated field.

    The following data types are available:


    Table 4-2 Data Types 

    Data Type

    Description

    Float  

    A decimal number, or number too large to fit in a long integer.  

    Integer  

    An integer.  

    Numeric  

    A roman numeral.  

    VarChar  

    A series of readable characters.  

    TimeStamp  

    Month, day, year, hours, minutes, seconds, and nanoseconds.  

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

    iPlanet Application Builder also enables you to make the following additional changes to a calculation:

    • add a built-in function to a calculation

    • add an operator or delimiter to a calculation

    • include additional table columns within a calculation

The associated procedures for editing calculations are described in the following sections.


Adding a Built-in Function to a Calculation

To add a built-in function to your calculation, you can type it in the Calculation box or click on the specific function displayed in the Add to Calculation portion of the dialog box.

The built-in functions are described in the following table:


Table 4-3 Built-in Functions

Function Name

Description

AVG  

Computes the average value of specified items.  

COUNT  

Computes the total number of specified items.  

MAX  

Computes the maximum value of the specified items.  

MIN  

Computes the minimum value of the specified items.  

SUM  

Computes the sum of all specified values.  


Adding an Operator or Delimiter to a Calculation

To add an operator or delimiter to your calculation, you can type it in the Calculation edit box or click on the specific item displayed in the Add to Calculation portion of the dialog box.

The built-in operators are described in the following table:


Table 4-4 Built-in Operators

Name

Symbol

Addition  

+  

Subtraction  

-  

Multiplication  

*  

Division  

/  

Opening Parenthetic Delimiter  

(  

Closing Parenthetic Delimiter  

)  


Including Additional Table Columns Within a Calculation

To include additional table columns within the calculation, click on the column combo box displayed just below the operators within the Add to Calculation portion of the dialog box. iPlanet Application Builder adds each column name that you select to the calculation you create.



Importing a Third-Party Data Model


You can also import a Powersoft Physical Data Model (a file of type .pdm) from a third-party application. iPlanet Application Builder imports and displays the .pdm file as a .kdm file in the data model window.

To import an existing data model from a third-party application, perform the following steps:

  1. From the File menu, choose Open File.

  2. Find the data model you want to open.

    iPlanet Application Builder displays the data model that you've selected in the data model window.



Creating Queries

iPlanet Application Builder enables you to design several types of standard queries for accessing and/or modifying the data within your data source. You can select, insert, update, or delete information accessed from your data source.

You create your queries by first constructing or importing a data model that includes tables and the relationships between them from a specific portion of your data source. For a complete description of how to use iPlanet Application Builder to create a data model, see "Creating a Data Model."

iPlanet Application Server applications can include four standard query types, as described in the following table:


Table 4-5 Query Types

Query Type

Description

SELECT query  

Retrieves selected information from your data source, as specified by your data model.  

INSERT query  

Adds specific information to your data source.  

UPDATE query  

Modifies specific information within your data source.  

DELETE query  

Deletes specific information from your data source.  

To create a new query, perform the following steps:

  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.

This section includes the following topics:


Creating a SELECT Query

You use a SELECT query to retrieve information (columns) from specific tables within your data source.

To create a SELECT query, perform the following steps:

  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). iPlanet Application Builder displays the following dialog box:



    As you use iPlanet 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, iPlanet 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 iPlanet 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. iPlanet Application Builder displays the Select Query Properties Fields information:



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. iPlanet 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 (":"), iPlanet 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.

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



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

  8. Fields 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."

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

  10. To test the generated SQL, see "Testing Queries."

  11. Click Finish. iPlanet 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.


Creating an INSERT Query

You use an INSERT query to add information to your data source.

To create an INSERT query, perform the following steps:

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

    As you use iPlanet 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, iPlanet 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 iPlanet 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 iPlanet 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.

    For information that describes how to edit your queries, see "Editing Generated Code: Queries."

  5. To test the generated SQL, see "Testing Queries."

  6. Click Finish.

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


Creating an UPDATE Query

You use an UPDATE query to update information for specific data source tables.

To create an UPDATE query, perform the following steps:

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

    As you use iPlanet 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, iPlanet 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 iPlanet 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. iPlanet 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.


Creating a DELETE Query

You use a DELETE query to delete information from specific tables within your data source.

To create a DELETE query, perform the following steps:

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

    As you use iPlanet 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, iPlanet 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. iPlanet 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.


Testing Queries

Once you've created your queries, you can use iPlanet Application Builder to test them and view the results using the Results tab.



  1. Select New Login to use a different login name than was used in the previous query.

  2. Select Execute SQL to identify the driver and data source.

    The following dialog box appears:



  3. Select a driver and data source. Also, identify the username and password.

    The results of the query are displayed in the results field.

For more information about how to test queries, see in "Compiling, Testing, and Debugging Applications."



Editing Generated Code: Queries


You can edit the SQL code that you create (or that iPlanet Application Builder automatically creates) via the SQL tab on the query properties dialog box.


Editing Queries via the SQL Tab

You can edit the SQL code that makes up your query by selecting the SQL tab in the Query Properties dialog box. This tab displays the query file or SQL that iPlanet Application Builder is generating in response to your selections in the other query creation dialog tabs.

To edit a specific query using the Query Properties SQL tab, perform the following steps:

  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. iPlanet Application Builder displays the Source tab.

  3. Edit the generated SQL.

    Note that table and field names are case sensitive and must match the case of the corresponding names in the data model.

To preview your query SQL code, perform the following steps:

  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.

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




    Note

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



Editing Query Descriptions

You can edit query descriptions that appear in the Properties window. To edit a specific query description, perform the following steps:

  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.

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

    • query name

    • description

    • type

  3. Make the desired changes within the properties window.

    The displayed information will be read-only if custom SQL is present.



Adding Conditionals to a Query

You can specify conditions that are used to match rows in the data source to help focus your query to obtain more precise search results. For example, rather than retrieving information about all customers, you can use a condition to retrieve only information about customers in a certain city.

The Conditionals tab of the Query Properties dialog box allows you to set up the WHERE clause of a SQL query.

The WHERE clause of a SQL query can take multiple forms:

  • 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

    A boolean clause can consist of any combination of simple conditions and other boolean clauses linked together by a logical Boolean operator. For example: WHERE lastName='Garcia' OR city='half moon bay' OR (firstName='Jerone' AND homePhone='999-999-9876').

    Note that parentheses are used in a condition statement to force iPlanet Application Builder to interpret specific conditions first. In this example, the AND condition must be interpreted prior to the preceding OR conditions.


Choosing Between Simple and Boolean Conditional Operators

The Conditionals tab displays both simple conditions and boolean clauses in a tree format. The parent nodes within this tree represent boolean operators which form boolean clauses by linking together the child nodes representing simple conditions and the child subtrees representing boolean clauses.


Simple Conditional Operators

iPlanet Application Builder includes a complete set of simple conditional operators, as described in the following table:


Table 4-6 Simple Conditional Operators

Name

Symbol

Description

Equals  

=  

Specified field and value is equivalent  

Not equals  

<>  

Specified field and value is not equivalent  

Less than  

<  

Specified field is less than value  

Less than or equal to  

<=  

Specified field is less than or equal to value  

Greater than  

>  

Specified field is greater than value  

Greater than or equal to  

>=  

Specified field is greater than or equal to value  

Is between  

IS BETWEEN  

Specified field value is between value  

In  

IN  

Specified field is part of the value set  

Not in  

NOT IN  

Specified field is not part of value set  

Is null  

IS NULL  

Specified field is null  

Is not null  

IS NOT NULL  

Specified field is not null  

Is like  

IS LIKE  

Specified field is similar to value  

iPlanet Application Builder displays these simple conditional operators within the Conditions Property dialog box, as seen when you add a condition to a query.


Boolean Conditional Operators

iPlanet Application Builder provides two boolean conditional operators within the Conditions Property dialog box: AND and OR.

The AND boolean operator returns TRUE if and only if both operands are TRUE.

The OR boolean operator returns TRUE if one or both operands are TRUE.


MS SQL SmallDateTime and DateTime Columns

If you retrieve the data type for smalldatetime or datetime columns in MS SQL, iPlanet Application Builder maps the actual data type (type 11) to the ODBC Timestamp data type (type 93).


Adding a Conditional Operator to a Query

To add a conditional operator to a query, perform the following steps:

  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.

    iPlanet Application Builder displays the Query Properties Conditionals dialog:



  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.

    iPlanet Application Builder displays the Conditionals Properties dialog box:



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

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

  2. Click in the text field in Values.

    The Conditionals Properties dialog box highlights the Values text field.

  3. Type the required values in the text boxes. Note that iPlanet 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 `Hanan'.

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


Using Query Parameters

The Query Properties Conditionals tab allows you to instruct a query to accept input from the calling servlet.

This function is achieved by entering :<variable> (such as a valIn, session variable, or constant) as you work with the Query Properties Conditional tab.

When you create a Data Access servlet and add a query, you are prompted for query parameters. You can pass in a valIn variable or a session variable, or you can enter a constant for the query parameter. For a complete description of servlets, see Editing Servlets in "Creating Presentation Logic."



Adding Joins to a Query


You can specify joins that define how queries relate to each other. A join sets the relationship between two tables or two queries.

iPlanet Application Builder provides the following types of join operators:


Table 4-7 Join Operators

Name

Symbol

Description

Simple join  

=  

Returns rows from two tables based on an equality condition.  

Left join  

(+) =  

Returns rows from two tables based on the equality condition plus those unique rows from the left table.  

Right join  

= (+)  

Returns rows from two tables based on the equality condition plus those unique rows from the right table.  

No join  

No join  

Specifies that no joins are used in the table.  

Joins can be made only if relationships have been defined in the associated data model. For a complete description of how to define relationships within a data model, see "Specifying Relationships Between Data Source Tables".

To add a join to a query, perform the following steps:

  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.

    iPlanet Application Builder displays the Query Properties Joins dialog box:



  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.



Sorting Query Results

You can change the order of the rows retrieved by a query. Normally, when you work with retrieved data, you want it to be organized in a particular sequence. This sequence is determined by a sort key, which is a group of one or more column names.

If no field names appear in this tab, go back and fill out the appropriate Tables and Fields tabs. Those tabs must be completed before you can use the Sorting tab.

To sort query results, perform the following steps:

  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.



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

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

  4. Click >.

    Application Builder adds the selected field to the Sorted Fields list. 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. You can also select a field, then click Sort (A-Z) or click Sort (Z-A) to add that field to the Sorted Fields list in the specified sort order.

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


      Note While executing a query string, parameters must be specified with single quotation marks `xxx'.



Previous     Contents     Index     DocHome     Next     
Copyright © 2000 Sun Microsystems, Inc. Some preexisting portions Copyright © 2000 Netscape Communications Corp. All rights reserved.

Last Updated April 28, 2000