REPOSITION statement

Repositions the cursor associated with a specific query. The query must be associated with a browse widget or defined with the SCROLLING option. The next record to be retrieved is the record following the cursor position.

Syntax 

 

REPOSITION query   {     TO ROWID rowid1 [ , rowid2 ] ...           [ FOR TENANT tenant-expression ] [ NO-ERROR ]      |  TO RECID recid [ NO-ERROR ]      |  ROW n      |  FORWARDS n      |  BACKWARDS n   }

query

The name of the query to reposition. The query must be open.

TO ROWID rowid1 [ , rowid2 ] ...
  [ FOR TENANT tenant-expression ] [ NO-ERROR ]

Repositions the join levels of a query to the corresponding ROWID expressions (rowid1, rowid2, and so on) that you specify, where rowid1 represents the ROWID for the top level of the join, rowid2 represents the ROWID for the next level of the join, and so on. You can specify any number of ROWID expressions up to the number of join levels. If you specify fewer ROWID expressions than the number of join levels, the AVM repositions the join levels of the query to the corresponding ROWID expressions you specify, but positions the remaining join levels for the unspecified ROWID expressions arbitrarily.

The FOR TENANT option is useful only for a multi-tenant database, and primarily one with a connection identity that has super tenant access. If the user has a super-tenant connection identity and you do not specify this option, the query repositions to data owned by the effective tenant. If you do specify this option, the query repositions to data owned by the regular tenant identified by tenant-expression.

If the user has a regular-tenant connection identity, and you specify this option, tenant-expression must match the tenancy of the connection identity. Otherwise, the statement raises ERROR.

If tenant-expression evaluates to an integer, the value must be a valid tenant ID for a regular tenant or zero (0) for the default tenant. If tenant-expression evaluates to a character string, the value must be a valid tenant name for a regular or "Default" for the default tenant. Otherwise, the statement raises ERROR.

TO RECID recid [ NO-ERROR ]

Similar to the TO ROWID option, except that the value recid is an expression that evaluates to a RECID value, and you can specify only one recid. Supported only for backward compatibility.

NO-ERROR suppresses any error messages that result from specifying an illegal value or a value that does not identify any records returned by the query. See the NO-ERROR entry below for more information.

NO-ERROR

Suppresses ABL errors or error messages that would otherwise occur and diverts them to the ERROR-STATUS system handle. If an error occurs, the action of the statement is not done and execution continues with the next statement. If the statement fails, any persistent side-effects of the statement are backed out. If the statement includes an expression that contains other executable elements, like methods, the work performed by these elements may or may not be done, depending on the order the AVM resolves the expression elements and the occurrence of the error.

To check for errors after a statement that uses the NO-ERROR option:

Check the ERROR-STATUS:ERROR attribute to see if the AVM raised the ERROR condition.

Check if the ERROR-STATUS:NUM-MESSAGES attribute is greater than zero to see if the AVM generated error messages. ABL handle methods used in a block without a CATCH end block treat errors as warnings and do not raise ERROR, do not set the ERROR-STATUS:ERROR attribute, but do add messages to the ERROR-STATUS system handle. Therefore, this test is the better test for code using handle methods without CATCH end blocks. ABL handle methods used in a block with a CATCH end block raise ERROR and add messages to the error object generated by the AVM. In this case, the AVM does not update the ERROR-STATUS system handle.

Use ERROR-STATUS:GET-MESSAGE( message-num ) to retrieve a particular message, where message-num is 1 for the first message.

If the statement does not include the NO-ERROR option, you can use a CATCH end block to handle errors raised by the statement.

Some other important usage notes on the NO-ERROR option:

NO-ERROR does not suppress errors that raise the STOP or QUIT condition.

A CATCH statement, which introduces a CATCH end block, is analogous to a NO-ERROR option in that it also suppresses errors, but it does so for an entire block of code. It is different in that the error messages are contained in a class-based error object (generated by the AVM or explicitly thrown), as opposed to the ERROR-STATUS system handle. Also, if errors raised in the block are not handled by a compatible CATCH block, ON ERROR phrase, or UNDO statement, then the error is not suppressed, but handled with the default error processing for that block type.

When a statement contains the NO-ERROR option and resides in a block with a CATCH end block, the NO-ERROR option takes precedence over the CATCH block. That is, an error raised on the statement with the NO-ERROR option will not be handled by a compatible CATCH end block. The error is redirected to the ERROR-STATUS system handle as normal.

If an error object is thrown to a statement that includes the NO-ERROR option, then the information and messages in the error object will be used to set the ERROR-STATUS system handle. This interoperability feature is important for those integrating code that uses the traditional NO-ERROR technique with the newer, structured error handling that features error objects and CATCH end blocks.

TO ROW n

Repositions the cursor to before the specified row in the result list of the query. The value n must be an integer expression that identifies a row in the result list. You cannot use this option with a query opened with the INDEXED-REPOSITION option.

FORWARDS n

Moves the cursor from its current position in the result list to a new position n records forward, where n represents an integer expression.

REPOSITION FORWARDS always places the cursor between two rows. For example:

If the cursor is on a row—say, row 5—REPOSITION FORWARDS 1 moves the cursor to row 6, then to half way between rows 6 and 7. From this position, GET PREVIOUS moves the cursor to row 6, while GET-NEXT moves the cursor to row 7.

If the cursor is already between two rows—say, between rows 5 and 6— REPOSITION FORWARDS 1 moves the cursor to half way between rows 6 and 7. From this position, GET PREVIOUS moves the cursor to row 6, while GET-NEXT moves the cursor to row 7.

BACKWARDS n

Moves the cursor from its current position in the result list to a new position n records back, where n represents an integer expression.

REPOSITION BACKWARDS always places the cursor between two rows. For example:

If the cursor is on a row—say, row 5—REPOSITION BACKWARDS 1 moves the cursor to row 4, then to half way between rows 4 and 5. From this position, GET PREVIOUS moves the cursor to row 4, while GET-NEXT moves the cursor to row 5.

If the cursor is already between two rows—say, between rows 5 and 6— REPOSITION BACKWARDS 1 moves the cursor to half way between rows 4 and 5. From this position, GET PREVIOUS moves the cursor to row 4, while GET-NEXT moves the cursor to row 5.

Example 

The following example uses the REPOSITION statement to move forward or backward within a query:

 

r-repos.p  

DEFINE VARIABLE num AS INTEGER NO-UNDO INITIAL 1.   DEFINE QUERY q-order FOR Customer, Order SCROLLING.   DEFINE BUTTON b_quit LABEL "Quit". DEFINE BUTTON b_frwd LABEL "FORWARD". DEFINE BUTTON b_back LABEL "BACKWARD".   FORM b_frwd b_back b_quit WITH FRAME butt-frame ROW 1.   ON CHOOSE OF b_back, b_frwd DO: PROMPT-FOR num LABEL "Records To Skip" WITH FRAME pos-info CENTERED ROW 5 overlay. HIDE FRAME pos-info NO-PAUSE. IF SELF:LABEL = "BACKWARD" THEN REPOSITION q-order BACKWARDS INPUT num + 1. ELSE     REPOSITION q-order FORWARDS INPUT num - 1. RUN getone. END.   OPEN QUERY q-order FOR EACH Customer NO-LOCK,   EACH Order OF Customer NO-LOCK. RUN getone.   ENABLE b_back b_frwd b_quit WITH FRAME butt-frame. WAIT-FOR CHOOSE OF b_quit OR WINDOW-CLOSE OF CURRENT-WINDOW.   PROCEDURE getone: GET NEXT q-order. IF NOT AVAILABLE Customer THEN DO: REPOSITION q-order BACKWARDS 1. GET NEXT q-order. END. DISPLAY Customer.CustNum Customer.Name SKIP     Order.OrderNum Order.OrderDate WITH FRAME order-info CENTERED ROW 5 SIDE-LABELS OVERLAY. END PROCEDURE.

Notes 

The REPOSITION statement does not fetch a record, except when the query is associated with a browse. The REPOSITION statement positions the cursor for the query so that a subsequent GET NEXT statement fetches the specified record, and GET PREV fetches the record before it.

After executing a REPOSITON statement that involves a multi-table join, the bottom-most buffer will not be available, as is the case for a query built on a single table. You then need to execute a GET NEXT statement to make the row you want available. The availability of non-bottom level buffers following the REPOSITION, however, is undetermined. That is, non-bottom level buffers may or may not be available.

If you reposition a query associated with a browse widget, the browse widget data is refreshed with the record after the new position at the top.

If you try to position the cursor outside the list of records that satisfy the query, the AVM does not raise the ERROR condition. If you try to position the cursor before the first record, the AVM positions the query to just before the first record. If you try to position the cursor beyond the last record, the AVM positions it just beyond the last record.

The REPOSITION statement might be slow if the record you position to has not yet been fetched.

The REPOSITION TO ROWID statement might be especially slow. If the record has not yet been fetched, the AVM performs a series of GET NEXT operations until the record is found. You can optimize the performance of a REPOSITION TO ROWID statement by opening the query using the INDEXED-REPOSITION option of the OPEN QUERY statement.

The INDEXED-REPOSITION option of the OPEN QUERY statement, followed by REPOSITION TO ROWID or GET LAST, causes the query results list to change dramatically. Subsequent use of the CURRENT-RESULT-ROW or NUM-RESULTS functions might produce unknown or unexpected results.

The order of the records in the query is determined by the options specified in the OPEN QUERY statement.

For SpeedScript, the on-endkey-phrase and the on-quit-phrase do not apply.

When specifying the FOR TENANT option, the AVM looks up tenant-expression in the database with a share lock. The AVM waits 60 seconds to get the share lock and raises ERROR if it fails to obtain the share lock in that amount of time. The AVM releases the share lock immediately after successfully fetching the row. This share lock is released even if the statement is called while in the scope of a transaction.

See also 

CLOSE QUERY statement, CURRENT-RESULT-ROW function, DEFINE QUERY statement, GET statement, NUM-RESULTS function, OPEN QUERY statement