Working with database views

A database view gives a different (and usually limited) perspective of the data in one or more tables. Although you see existing database views listed in the Objects view, a database view does not physically exist in the database as a table does. Each time you select a database view and use the view’s data, PocketBuilder executes a SQL SELECT statement to retrieve the data and creates the database view.

Views are not supported in UltraLite databases.

Using database views in PocketBuilder

You can define and manipulate database views in PocketBuilder. Typically you use database views for the following reasons:

To give names to frequently executed SELECT statements.
To limit access to data in a table. For example, you can create a database view of all the columns in the Employee table except Salary. Users of the database view can see and update all information except the employee's salary.
To combine information from multiple tables for easy access.

In PocketBuilder, you can create single- or multiple-table database views. You can also use a database view when you define data to create a new database view.

Figure 16-6: Creating a multiple-table view in the View painter

The sample shows the View painter with the department and employee tables opened, with a join between the emp _ i d column in the employee table and the dept _ head _ i d column in the department table. Beneath them, the Syntax tab displays the sequel that defines the view.

To create a database view:

Click the Create View drop-down toolbar button, select Object>Insert>View from the Database painter menu, or select New View from the pop-up menu on the Views folder in the Objects view.

The Select Tables dialog box displays, listing all tables and views that you can access in the database.
Select the tables and views from which you will create the view by doing one of the following:
- Click the name of each table or view you want to open in the list displayed in the Select Tables dialog box, then click the Open button to open them. The Select Tables dialog box closes.
- Double-click the name of each table or view you want to open. Each object is opened immediately. Then click the Cancel button to close the Select Tables dialog box.
Representations of the selected tables and views display in the View painter workspace.
Select the columns to include in the view and include computed columns as needed.
Join the tables if there is more than one table in the view.

For information, see “Joining tables”.
Specify criteria to limit rows retrieved (Where tab), group retrieved rows (Group tab), and limit the retrieved groups (Having tab) if appropriate.

For information about using selection and grouping criteria, see “Specifying selection, sorting, and grouping criteria”.
When the view has been completed, click the Return button.
Name the view.

Include “view” or some other identifier in the view’s name so that you will be able to distinguish it from a table in the Select Tables dialog box.
Click the Create button.

PocketBuilder generates a CREATE VIEW statement and submits it to the DBMS. The view definition is created in the database. You return to the Database painter workspace with the new view displayed in the workspace.

Opening a database view

You define, open, and manipulate database views in the View painter, which is similar to the Select painter. For more information about the Select painter, see “Selecting a data source”.

To open a database view:

In the Objects view of the database painter, expand the list of Views for your database.
Highlight the view you want to open and select Add To Layout from the pop-up menu, or drag the view’s icon to the Object Layout view.

Updating database views Some database views are logically updatable and others are not. Views are not supported in UltraLite. For SQL Anywhere you cannot update views containing aggregate functions, such as COUNT(*), or a GROUP BY clause in the SELECT statement, or views containing a UNION operation.

Displaying a database view’s SQL statement

You can display the SQL statement that defines a database view. How you do it depends on whether you are creating a new view in the View painter or want to look at the definition of an existing view.

Figure 16-7: Displaying a view definition in the Database painter

The sample shows the General tab of the view definition in the Database painter. It lists Owner as d b a, and View as emp _ dep _ view. It also displays the Definition field with the sequel statement used to create the database view.

You cannot alter the view definition in the Object Details view. To alter a view, drop it and create another view.

To display the SQL statement from the View painter:

Select the Syntax tab in the View painter

PocketBuilder displays the SQL it is generating. The display is updated each time you change the view.

To display the SQL statement from the Database painter:

Highlight the name of the database view in the Objects view and select Properties from the pop-up menu, or drag the view’s icon to the Object Details view.

The completed CREATE statement used to create the database view displays in the Definition field on the General page. The view definition in the Object Details view is read-only.

Joining tables

If the database view contains more than one table, you should join the tables on their common columns. When the View painter is first opened for a database view containing more than one table, PocketBuilder makes its best guess as to the join columns, as follows:

If there is a primary/foreign key relationship between the tables, PocketBuilder automatically joins them.
If there are no keys, PocketBuilder tries to join tables based on common column names and types.

In the following screen, the Employee and Department tables are joined on the dept_id column:.

Figure 16-8: Equality join between two tables

The sample screen shows the Employee and Department tables and lists their columns. The tables are joined on the dept _ i d column.

To join tables:

Click the Join button.
Click the columns on which you want to join the tables.
To create a join other than the equality join, click the join representation in the workspace.

The Join dialog box displays.

Figure 16-9: Join dialog box showing types of join allowed
Select the join operator you want from the Join dialog box.

You can select outer joins for SQL Anywhere databases. For example, in the preceding dialog box (which uses the Employee and Department tables), you can choose to include rows from the Employee table where there are no matching departments, or rows from the Department table where there are no matching employees.

For more about outer joins, see “Using ANSI outer joins”.

Dropping a database view

Dropping a database view removes its definition from the database.

To drop a view:

In the Objects view, select the database view you want to drop.
Click the Drop Object button in PainterBar1 or select Drop View from the pop-up menu.

PocketBuilder prompts you to confirm the drop, then generates a DROP VIEW statement and submits it to the DBMS.

Exporting view syntax

You can export the syntax for a view to the log. This feature is useful when you want to create a backup definition of the view before you alter it or when you want to create the same view in another DBMS.

To export the syntax of an existing view to a log:

Select the view in the painter workspace.
Select Export Syntax from the Object menu or the pop-up menu.

For more information about the log, see “Logging your work”.