Database Guide
One of the benefits of the Multi-row Query part is that it enables you to
set fetch and update policies for queries.
Its fetching capability enables you to limit the number of rows that are
returned in the result table and to set the size of blocks that are
retrieved. If you compose a query that returns thousands of rows, for
example, and you know that you are interested in only the first 200, then you
can limit the number of rows returned to 200. Packeting can improve
your application's performance by fetching only a portion of the rows
returned as your application needs them. The default block size is
25. This means that the query retrieves the result table rows in blocks
of 25 at a time.
The Multi-row Query part's update capability enables you to set a
query result table as read-only, or to specify how users can update the
database from the result table.
Use the Fetch pages of the Multi-row Query Settings window
to set the fetch policy of a query. This settings page has the
following options:
Fetch Options - Page 1 of 2
- Maximum number of rows to fetch
- The number of rows to be returned by the query.
- Enable packeting
- Enables the container details part (the quick form for multi-row query
result tables) to request the result table rows as the application needs
them. This option can speed the display of result table data by loading
into memory only those rows that can be displayed in the container details
part (plus a number of buffered rows to make scrolling through the result
table more smooth).
- Enable blocked fetches
- Enables you to set a blocked fetch size.
- Blocked fetch size
- The number of rows to fetch in a block. If packeting is enabled,
blocked fetch size also determines the size of a packet.
If you leave this field blank, then the database manager automatically
determines the block size. If packeting is enabled, this field should
not be left blank.
Note: | If you are using ODBC, there may be specific drivers that do not support
blocked fetch size. Refer to the help for the ODBC driver you are using
for more information.
|
For the sample application, set the Maximum number of rows to
fetch field to 100 and leave the remaining fields at their default
settings.
Fetch Options - Page 2 of 2
These settings are supported for IBM DB2 V2.1 and above and UDB
V5.0 and above..
- Column Name
- Lists the columns in the table that contain large objects.
- LOB Type
- Enables you to determine how the object is to be fetched. This
field has a pop-up menu with the following options:
- LOB Value
- Fetches the object itself and stores it in memory
- LOB Locator
- Fetches a handle to the object and enables you to manipulate the object
(for example, move it from one column to another) by referring to its handle
- LOB File Reference
- Fetches the object itself and stores it in a file
Use the Update pages of the Multi-row Query Settings window
to set the update policy of a query. The settings on this page
determine the following:
- When updates to a result table are applied to the database
- When updates to the database are committed
- When VisualAge obtains a lock on a row
Update Options - Page 1 of 2
- Read only
- Prohibits updates to the query result table. When this option is
selected, the remaining options on this page are disabled.
- Defer updates
- Determines when changes made to a result table are applied to the
database. When you select this setting, VisualAge holds all changes to
the result table until the user performs a specific action (such as selecting
a push button) to apply or cancel them. After the user applies the
changes, VisualAge then obtains a lock on the rows that have been
changed. When this option is selected, the Lock row on edit
option is disabled.
When this setting is not selected, all changes are applied to the database
immediately. They are not committed, however, unless you enable the
Auto commit option.
- Lock row on edit
- Obtains a lock on the row being edited so that no other users can edit
it.
VisualAge obtains locks by opening a cursor on the row. When a
different row is edited, the previous row is updated if it has changed.
Changing to a different row causes an update lock and the cursor is
closed.
- Auto commit
- Commits all changes to the result table without requiring the user to
perform a specific action. The effect of this option varies according
to the Defer updates setting:
- When Auto commit and Defer updates are both
selected, changes are committed when the apply action is
triggered.
- When Auto commit is selected but Defer updates is
not, changes are committed when the user selects another cell or row from the
result table.
| On some database management systems, closing the cursor is sufficient to
release a lock. Others, like ORACLE, require you to commit or rollback
updates to release a lock. If your DBMS requires a commit or rollback,
use the Auto commit setting.
|
Update Options - Page 2 of 2
- Columns to Update
- Displays all columns to be returned in the result table and enables you to
select the columns that the user can update.
- Columns to use as index
- Displays all columns to be returned in the result table and enables you to
select the columns to use as index. These are the columns used to lock
the row. The columns you select must uniquely identify a row.
- Available
- Displays a list of all indexes available in the database and enables you
to select one to use as an update index.
For the sample application, set the following update policy:
- Lock row on edit
- Columns to Update: DEPT, JOB, YEARS, SALARY, COMM
- Columns to use as index: ID, NAME, DEPT
To save the settings of your multi-row query part, select
OK.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]