FWD » Core Development » Database

This ticket is derived from #8912-188.

define temp-table tt no-undo
    field f1 as character 
    index idx is primary unique f1.

create tt.
assign 
tt.f1 = ?.

create tt.
assign 
tt.f1 = ?.

define buffer btt for tt.
for each tt on error undo, throw:
    find btt where btt.f1 = tt.f1 no-error.
    if ambiguous btt then do:
        message "ambiguous".
    end.
end.
message "finished".

If instead of the find btt where btt.f1 = tt.f1, we use find btt of tt, there is also a conversion issue. Instead of the field f1, the recid is used in the where clause of the find query.
First I will investigate the runtime issue.

In this case, DirectAccessHelper.findByUniqueIndex() is used, it assumes that if there is a unique index, only one record can be accessed with the same value for the indexed field.
getUniqueRow is used and the response is returned, I added a simple check to see if there are more results after the first record is accessed

if( driver.nextPrimaryKey(tableName, response.getRecid().intValue(), true) != 0){
   throw new DirectAccessException("Error direct accessing for finding record by unique index. More than one records found.");
}

Using 9034b this query

define buffer btt for tt.
...
    find btt of tt no-error.

silent(() -> new FindQuery(btt, (String) null, null, "btt.f1 asc", tt).unique());

Alexandru, please review 9034a/15373. The only issue I see is a minor coding standard format issue, but you are more familiar with the H2 direct access interface.

The minor format issue:

if( response.getRecid() ...

should be:

if (response.getRecid() ...

Code review 9034b/15373:

The conversion change looks good.

Alexandru, please review.

Alexandru Lungu wrote:

• I am a bit confused with the state of this branch. I already reviewed 9034a/15373 in #9034-9. Maybe you missed committing the changes as 9034a/ 15374?

Sorry for that, I indeed forgot to commit, 9034a/15374 is now committed.

Review of 9034a:

These were change local on my side, ignore review.

Alexandru, during testing of 9134a_h2, I encountered some issue with the next method in TreeCursor:

 if (beforeFirst) {
    beforeFirst = false;
    if (node == null) {
       return false;
    }

if (node == null) {
    return false;
    }
    if (beforeFirst) {
       beforeFirst = false;

This is how the code looked before any FWD-H2 specific changes:

            |     @Override
            |     public boolean next() {
            |         if (beforeFirst) {
            |             beforeFirst = false;
            |             if (node == null) {
            |                 return false;
            |             }
            |             if (first != null && tree.compareRows(node.row, first) < 0) {
            |                 node = next(node);
            |             }
            |         } else {
            |             node = next(node);
            |         }
            |         if (node != null && last != null) {
            |             if (tree.compareRows(node.row, last) > 0) {
            |                 node = null;
            |             }
            |         }
            |         return node != null;
            |     }

You are suggesting changing the code H2, which doesn't seem right. I think the problem is node.active false without a null-check. It should be rewritten as node != null && node.active false.

I committed 9034a_h2/53, it addressed the review points from #9034-12 and the issue discussed in #9034-17.

deleted

I am ready to merge 9034a_h2.

Please go ahead and merge 9034a_h2 to FWD-H2 trunk.

9034a_h2 was merged to FWD-H2 trunk as rev 54.