Setting fetch and update policies
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.
Setting a fetch policy
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
Setting an update policy
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 VA Smalltalk 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, VA Smalltalk 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, VA Smalltalk 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.
VA Smalltalk 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:
o When Auto commit and Defer updates are both selected, changes are committed when the apply action is triggered.
o 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.
Last modified date: 05/20/2020