1. Derby
  2. DERBY-775

Network client: Add support for scrollable, updatable, insensitive result sets


    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s:
    • Component/s: JDBC, Network Client
    • Labels:
    • Issue & fix info:
      Release Note Needed


      This is a part of the DERBY-690 effort.

      1. 775-writeup.txt
        7 kB
        Dag H. Wanvik
      2. derby-775-1.diff
        94 kB
        Dag H. Wanvik
      3. derby-775-1.stat
        3 kB
        Dag H. Wanvik
      4. derby-775-2.stat
        3 kB
        Dag H. Wanvik
      5. derby-775-2.diff
        96 kB
        Dag H. Wanvik
      6. derby-775-3.stat
        3 kB
        Dag H. Wanvik
      7. derby-775-3.diff
        104 kB
        Dag H. Wanvik
      8. derby-775-4.diff
        134 kB
        Dag H. Wanvik
      9. derby-775-4.stat
        3 kB
        Dag H. Wanvik
      10. derby-775-5.diff
        138 kB
        Andreas Korneliussen
      11. derby-775-5.stat
        3 kB
        Andreas Korneliussen
      12. derby-775-6.diff
        158 kB
        Dag H. Wanvik
      13. derby-775-6.stat
        3 kB
        Dag H. Wanvik
      14. derby-775-7.diff
        158 kB
        Dag H. Wanvik
      15. derby-775-7.stat
        3 kB
        Dag H. Wanvik

        Issue Links


          Dag H. Wanvik created issue -
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Link This issue is part of DERBY-690 [ DERBY-690 ]
          Dag H. Wanvik made changes -
          Comment [ Posting this in the hope that some of you DRDA experts can comment on
          it :)

          I have been trying to figure out how to map JDBC scrollable,
          updatable, insensitive result sets (SUR for short) onto DRDA and have
          three questions (below). I am assuming the semantics described in the
          attachedment to DERBY-690.

          Some background:

          DRDA has no concept of JDBC result sets, so the solution used in Derby
          is to map JDBC result sets onto the DRDA cursor mechanism. Appendix B
          in "DRDA, Version 3, Volume 1: Distributed Relational Database
          Architecture (DRDA)" gives an overview of "Scrollable Cursors" (p

          a) Sensitivity

          I first considered to map SUR to "insensitive scrollable cursors", but
          DRDA states that these are read only (B.2.2). It seems the "sensitive
          static cursors" better matches the semantics we want: For this
          category of cursors, the size of the result set ("result table" in
          DRDA parlance) as well as the ordering of rows are fixed and this
          cursor is updatable, cf. quote on page 656:

               "The cursor is always immediately sensitive to changes made using
               the cursor (that is, positioned updates and deletes using the
               same cursor).

               The size of the result table does not grow after the cursor is
               opened and the rows are materialized. The order of the rows is
               established as the result table is materialized."

          Combined with the proper fetch, i.e. fetch INSENSITIVE (p 658) on
          CNTQRY (i.e. we are INSENSITIVE to changes made by others), the
          semantics match those of JDBC's insensitive result sets as we have
          specified them :

                ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
                ownInsertsAreVisible(TYPE_SCROLL_INSENSITIVE) -> false
                ownUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true

                othersDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> false
                othersInsertsAreVisible(TYPE_SCROLL_INSENSITIVE) -> false
                othersUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> false

          *Question 1*: Can anyone see a problem with mapping JDBC "updatable,
          insensitive" onto DRDA "sensitive static"? Would this be violating
          (the spirit of) the DRDA in any way?

          b) Detectability

          In the spec, we required that we be able to detect deletes and updates
          to the rows in the result set:

                deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
                updatesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true

          (Since inserts are not visible, they can not be detectable, either).
          DRDA supports detection of holes in the following manner (quote,
          P. 656):

              "To present a static size and static ordering for the result table, the
              relational database may return a hole to the application that fetches
              an updated or deleted row in the result table. A hole in the result
              table occurs when there is a difference between the result table and
              the underlying base table. No data can be fetched from a hole, and the
              hole is manifested in the QRYDTA as a row consisting of a non-null
              SQLCARD and a null data group.

              When the current value of a row no longer satisfies the
              select-statement or statement-name, that row is visible in the cursor
              as an update hole , where the SQLCARD has a warning SQLSTATE of 02502.

              When a row of the result table is deleted from the underlying base
              table, the row is visible in the cursor as a delete hole , where the
              SQLCARD has a warning SQLSTATE of 02502."

          For deletes, the "delete hole" is exactly what we need to support

          For updates, is is not exactly what we want, since we do not intend to
          requalify a row after it has been updated (thereby possibly making it
          an "update hole"). On the other hand, when we update a row and let it
          remain in the result table, DRDA offers no means of conveying that the
          row has been changed in the sense of JDBC ResultSet#rowUpdated(), as
          far as I can tell.

          *Question 2*: Is there some way we can detect the latter without
          violating the protocol? One could imagine signalling this using
          another warning SQLSTATE. Would this be an acceptable tweaking of the

          c) Query protocol

          For scrollable result sets, Derby uses the "Limited Block Query
          Protocol". This is allowable also for "static sensitive", so I assume
          we can use that also for SUR in the way it is presently used for
          scrollable, read-only result sets.

          d) Rowset cursors (p 667)

          "Rowset cursors" seem not to be in use by the server, but the client
          has code for handling it. Not sure at this point if this code needs updating
          for SUR..

          *Question 3*: Is it a requirement that the client be able to handle
          row sets to be DRDA compliant? (We know our server doesn't use it for

          d) Equality of result set modification and positioned delete/update.

          The same cursor is being used, and the server (and our prosed
          implementation of SUR) is agnostic to whether an updateRow/deleteRow
          or a positioned update/delete is being invokled by the network client
          driver, the only difference being the autocommit semantics, which is
          handled by the driver.
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Dag H. Wanvik made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Dag H. Wanvik made changes -
          Attachment 775-writeup.txt [ 12324908 ]
          Attachment derby-775-1.diff [ 12324910 ]
          Attachment derby-775-1.stat [ 12324909 ]
          Dag H. Wanvik made changes -
          Derby Info [Patch Available, Release Note Needed]
          Dag H. Wanvik made changes -
          Attachment derby-775-2.diff [ 12324981 ]
          Attachment derby-775-2.stat [ 12324980 ]
          Dag H. Wanvik made changes -
          Attachment derby-775-3.stat [ 12325021 ]
          Attachment derby-775-3.diff [ 12325022 ]
          Dag H. Wanvik made changes -
          Attachment derby-775-4.diff [ 12325092 ]
          Attachment derby-775-4.stat [ 12325091 ]
          Andreas Korneliussen made changes -
          Attachment derby-775-5.diff [ 12325133 ]
          Attachment derby-775-5.stat [ 12325134 ]
          Dag H. Wanvik made changes -
          Attachment derby-775-6.diff [ 12325561 ]
          Attachment derby-775-6.stat [ 12325560 ]
          Dag H. Wanvik made changes -
          Attachment derby-775-7.diff [ 12325622 ]
          Attachment derby-775-7.stat [ 12325621 ]
          Bernt M. Johnsen made changes -
          Resolution Fixed [ 1 ]
          Derby Info [Release Note Needed, Patch Available] [Patch Available, Release Note Needed]
          Status In Progress [ 3 ] Resolved [ 5 ]
          Dag H. Wanvik made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Dag H. Wanvik made changes -
          Derby Info [Release Note Needed, Patch Available] [Release Note Needed]
          Dag H. Wanvik made changes -
          Issue Type New Feature [ 2 ] Improvement [ 4 ]
          Dag H. Wanvik made changes -
          Fix Version/s [ 11187 ]
          Dag H. Wanvik made changes -
          Affects Version/s [ 11187 ]
          Gavin made changes -
          Workflow jira [ 12344983 ] Default workflow, editable Closed status [ 12801360 ]


            • Assignee:
              Dag H. Wanvik
              Dag H. Wanvik
            • Votes:
              1 Vote for this issue
              1 Start watching this issue


              • Created: