FWD » Core Development » Database

DEFINE TEMP-TABLE tt1 NO-UNDO

  FIELD id  AS INTEGER

  FIELD val AS CHARACTER

  INDEX idx AS UNIQUE id

.

DEFINE TEMP-TABLE tt2 NO-UNDO

  FIELD id  AS INTEGER

  FIELD ref AS CHARACTER

  FIELD val AS CHARACTER

  INDEX idx AS UNIQUE id

.  

DEFINE TEMP-TABLE tt3 NO-UNDO

  FIELD id  AS INTEGER

  FIELD ref AS CHARACTER

  FIELD val AS CHARACTER

  INDEX idx AS UNIQUE id

.  

CREATE tt1. tt1.id = 1. tt1.val = 'a1'.

CREATE tt1. tt1.id = 2. tt1.val = 'a2'.

CREATE tt1. tt1.id = 3. tt1.val = 'a3'.

CREATE tt1. tt1.id = 4. tt1.val = 'a4'.

CREATE tt2. tt2.id = 10. tt2.ref = 'a1'. tt2.val = 'b10'.

CREATE tt2. tt2.id = 11. tt2.ref = 'a1'. tt2.val = 'b11'.

CREATE tt2. tt2.id = 12. tt2.ref = 'a1'. tt2.val = 'b12'.

CREATE tt2. tt2.id = 13. tt2.ref = 'a1'. tt2.val = 'b13'.

CREATE tt2. tt2.id = 20. tt2.ref = 'a2'. tt2.val = 'b20'.

CREATE tt2. tt2.id = 21. tt2.ref = 'a2'. tt2.val = 'b21'.

CREATE tt2. tt2.id = 22. tt2.ref = 'a2'. tt2.val = 'b22'.

CREATE tt2. tt2.id = 23. tt2.ref = 'a2'. tt2.val = 'b23'.

CREATE tt3. tt3.id = 100. tt3.ref = 'b10'. tt3.val = 'c100'.

CREATE tt3. tt3.id = 101. tt3.ref = 'b11'. tt3.val = 'c101'.

CREATE tt3. tt3.id = 102. tt3.ref = 'b12'. tt3.val = 'c102'.

CREATE tt3. tt3.id = 103. tt3.ref = 'b13'. tt3.val = 'c103'.

CREATE tt3. tt3.id = 200. tt3.ref = 'b20'. tt3.val = 'c200'.

CREATE tt3. tt3.id = 201. tt3.ref = 'b21'. tt3.val = 'c201'.

CREATE tt3. tt3.id = 202. tt3.ref = 'b22'. tt3.val = 'c202'.

CREATE tt3. tt3.id = 203. tt3.ref = 'b23'. tt3.val = 'c203'.

DEFINE DATASET ds FOR tt1, tt2, tt3

    DATA-RELATION r12 FOR tt1, tt2 RELATION-FIELDS(val, ref) // REPOSITION

    DATA-RELATION r23 FOR tt2, tt3 RELATION-FIELDS(val, ref). // REPOSITION.

DEF VAR hDS        AS HANDLE  NO-UNDO.

DEF VAR hQuery     AS HANDLE  NO-UNDO.

DEF VAR hQ         AS HANDLE  NO-UNDO.

DEF VAR hBuffer    AS HANDLE  NO-UNDO.

DEF VAR hB         AS HANDLE  NO-UNDO.

DEF VAR iBuffer    AS INTEGER NO-UNDO.

DEF VAR nR         AS INTEGER NO-UNDO.

DEF VAR lc         AS LOGICAL NO-UNDO.

DEF VAR rid        AS ROWID   NO-UNDO.

DEF VAR crid       AS CHAR    NO-UNDO.

DEF VAR qstr       AS CHAR    NO-UNDO.

hDS = DATASET ds:HANDLE. 

OUTPUT TO ds-sync.txt.

CREATE QUERY hQuery.

hBuffer = hDS:GET-BUFFER-HANDLE(1).

hQuery:ADD-BUFFER(hBuffer).

hQuery:QUERY-PREPARE("FOR EACH " + hBuffer:NAME).

  hQuery:QUERY-OPEN().

  hQuery:GET-FIRST().

  nR = 0.

  MESSAGE 'Start iterate'.

  DO WHILE NOT hQuery:QUERY-OFF-END:

      MESSAGE nR.

      nR = nR + 1.

      MESSAGE hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

      .

        IF hBuffer:ERROR THEN

          MESSAGE hBuffer:ERROR-STRING.

        hQuery:GET-NEXT().

  END.

  hQuery:QUERY-CLOSE().

  hQuery:QUERY-OPEN().

  hQuery:GET-FIRST().

  nR = 0.

  MESSAGE 'Start iterate' iBuffer.

  DO WHILE NOT hQuery:QUERY-OFF-END:

      hB = hDS:GET-BUFFER-HANDLE(2).

      crid = STRING(hBuffer:ROWID).

//      qstr = "FOR EACH " + hBuffer:NAME + " WHERE ROWID(" + hBuffer:NAME + ") = TO-ROWID('" + crid + "'), EACH " + hB:NAME + " WHERE " +  hB:NAME + ".ref = " + hBuffer:NAME + ".val".

      qstr = "FOR  EACH " + hB:NAME + " WHERE " +  hB:NAME + ".ref = " + hBuffer:NAME + ".val".

      MESSAGE nR crid qstr.

      nR = nR + 1.

      CREATE QUERY hQ.

//      hQ:ADD-BUFFER(hBuffer).

      hQ:ADD-BUFFER(hB).

      hQ:QUERY-PREPARE(qstr).

      hQ:QUERY-OPEN().

      hQ:GET-LAST().

      MESSAGE '0' hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

      .

      hQ:QUERY-CLOSE().

      DELETE OBJECT hQ.

      lc = hDS:GET-BUFFER-HANDLE(1):SYNCHRONIZE().

      MESSAGE '1' lc hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

      .

      lc = hDS:GET-BUFFER-HANDLE(1):SYNCHRONIZE().

      MESSAGE '2' lc  hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

              hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

      .

        IF hBuffer:ERROR THEN

          MESSAGE hBuffer:ERROR-STRING.

        hQuery:GET-NEXT().

  END.

  hQuery:QUERY-CLOSE().

  MESSAGE 'End iterate'.

  hQuery:QUERY-OPEN().

  hQuery:GET-FIRST().

  lc = hDS:GET-BUFFER-HANDLE(1):SYNCHRONIZE().

  MESSAGE 'A' lc hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

          hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

          hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

  .

  hQuery:GET-LAST().

  lc = hDS:GET-BUFFER-HANDLE(1):SYNCHRONIZE().

  MESSAGE 'B' lc hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

          hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

          hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

  .

  hQuery:GET-NEXT().

  lc = hDS:GET-BUFFER-HANDLE(1):SYNCHRONIZE().

  MESSAGE 'C' lc hDS:GET-BUFFER-HANDLE(1):BUFFER-FIELD('val'):BUFFER-VALUE()

          hDS:GET-BUFFER-HANDLE(2):BUFFER-FIELD('val'):BUFFER-VALUE()

          hDS:GET-BUFFER-HANDLE(3):BUFFER-FIELD('val'):BUFFER-VALUE()

  .

  hQuery:QUERY-CLOSE().

  DELETE OBJECT hQuery.

  OUTPUT CLOSE.