java.sql2 DynamicMultiOperation with interlaced exceptions

Douglas Surber douglas.surber at oracle.com
Mon Oct 9 16:54:08 UTC 2017 

It’s not clear to me why you would think Operation does too much. Operation only has three methods and one convenience method. An Operation encapsulates a database action and how the result of that action is processed. No Operation subclass does any more. Yes, full specification of the action can be complex, e.g. the various Parameterized subclasses, and in the case of StaticMultiOperation and DynamicMultiOperation the result processing is complex. But every method on Operation either adds to the specification of the action or to the processing of the result.

It would be possible to split Operation into Action and Result components. Something like

  conn.parameterizedAction(selectSql)
    .set(arg, value, type)
    .rowResult()
    .initialValue( () -> new ArrayList() )
    .rowAggregator( (p, r) -> { p.add(r.get(column, javaType); return p; } )
    .submit();

I don’t see the value. Negotiating the path of fooAction/barResult doesn’t seem to add anything.

As I responded to Jens, PublisherOperation is a mistake and should not have been uploaded. This is not a Flow based API. It is a CompletableFuture based API. There is a need for back pressure in two places and we have tried, perhaps unfortunately, to shoehorn Flow into those places. For those that want a Flow based API this is not that API. We explored that problem space and did not come up with anything that met our goals. Not to say that it isn’t possible, but we did not succeed. 

If the community wants an async database access API based on Flow in the Java 10 equivalent release, someone else is going to have to develop a fairly complete initial draft. The EG needs to make progress on what we have to have any hope of inclusion in the Java 10 equivalent release.

Douglas


> On Oct 9, 2017, at 9:25 AM, Lukas Eder <lukas.eder at gmail.com> wrote:
> 
> Yes, that would be your original suggestion again, plus the fact that the two onError methods mutually exclude each other. I think that's the best approach given the current idea of the Operation type hierarchy.
> 
> An alternative might be to remove onError() from Operation entirely, because the direct Operation subtypes might not have a comparable error handler in common. For instance, we could have a similar discussion about BatchCountOperation, in case of which there might be errors on a per-batch-element basis.
> 
> I think this discussion might be a good point in favour of re-thinking the entire type hierarchy again. I think this was criticised in a different discussion, recently:
> http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2017-October/000102.html <http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2017-October/000102.html>
> 
> I currently don't have a solution for this, but I do agree with Jens that the feeling is: Operation currently does too much.
> 
> 2017-10-09 18:11 GMT+02:00 Douglas Surber <douglas.surber at oracle.com <mailto:douglas.surber at oracle.com>>:
> How about this:
> 
>   - if onError(BiConsumer) is called all errors are passed to that handler
>   - if onError(Consumer) is called all errors are passed to that handler
>   - if both are called whichever is called second throws IllegalStateException
> 
> Douglas
> 
>> On Oct 9, 2017, at 8:33 AM, Lukas Eder <lukas.eder at gmail.com <mailto:lukas.eder at gmail.com>> wrote:
>> 
>> Yes, I had seen that inherited onError() method, which is why I raised the concern:
>> 
>> > However, now I wonder how the new 
>> > DynamicMultiOperation.onError(BiConsumer<Integer, Throwable>) 
>> > method would communicate with the pre-existing 
>> > Operation.onError(Consumer<Throable>) method...
>> 
>> Specifically, because the new one has a useful Integer argument indicating what the result number is. If that is dropped, then the inherited onError() method no longer has such an Integer argument, which is a bit inconsistent. Also, this leads to another question: Will those Integer indexes be skipped onError, or not?
>> 
>> 2017-10-09 17:04 GMT+02:00 Douglas Surber <douglas.surber at oracle.com <mailto:douglas.surber at oracle.com>>:
>> A DynamicMultiOperation is an Operation and so inherits onError. We can specify that the onError handler is called for any error and after it returns processing continues instead of completing the Operation exceptionally.
>> 
>> Douglas
>> 
>>> On Oct 9, 2017, at 5:43 AM, Lukas Eder <lukas.eder at gmail.com <mailto:lukas.eder at gmail.com>> wrote:
>>> 
>>> Hi Douglas,
>>> 
>>> I'm not sure if this is an improvement over the previous version. Again, my T-SQL example:
>>> 
>>> DECLARE @t TABLE(i INT);
>>> INSERT INTO @t VALUES (1),(2),(3);
>>> RAISERROR('message 1', 16, 2, 3);
>>> RAISERROR('message 2', 16, 2, 3);
>>> SELECT * FROM @t
>>> RAISERROR('message 3', 16, 2, 3);
>>> 
>>> As you can see, in such a batch, there is no strict notion of an error belonging to a count operation, or to a result operation. It's just an error. There might as well be no result at all, yet there could still be errors. E.g. when you run this:
>>> 
>>> RAISERROR('message 1', 16, 2, 3);
>>> RAISERROR('message 2', 16, 2, 3);
>>> RAISERROR('message 3', 16, 2, 3);
>>> 
>>> In the presence of both onCount and onRows handlers, would you pass the errors along with a synthetic count or a synthetic result, or both? What if there are several errors? Neither seems very compelling to me. There are three distinct types of result, which are not strictly connected:
>>> 
>>> - Results / rows
>>> - Update counts
>>> - Errors
>>> 
>>> Oh, and there are warnings too (SQLWarning), which are a special kind of error, which I currently don't see in the API.
>>> 
>>> Lukas
>>> 
>>> 2017-10-05 17:40 GMT+02:00 Douglas Surber <douglas.surber at oracle.com <mailto:douglas.surber at oracle.com>>:
>>> Lukas,
>>> 
>>> On further thought, the onError handler is not needed. Each Operation has an error handler so the onCount and onRows handlers have an opportunity to configure an error handler specific to a result.
>>> 
>>>   conn.dynamicMultiOperation(sql)
>>>     .onCount( (n, op) -> {
>>>       op.onError( t -> handle(t) )
>>>       ….
>>>         .submit();
>>>     })
>>>   .submit();
>>> 
>>> Given the design guideline that one way to do things is enough, I have eliminated the onError handler. I do like having separate onCount and onRows handlers so that is retained.
>>> 
>>> Douglas
>>> 
>>>> On Oct 5, 2017, at 8:12 AM, Douglas Surber <DOUGLAS.SURBER at oracle.com <mailto:DOUGLAS.SURBER at oracle.com>> wrote:
>>>> 
>>>> Lukas,
>>>> 
>>>> If there is no handler for a particular result, e.g. no onRows handler and a rows result is returned, that result is ignored. If an error is limited to a specific result it would be passed to the onError handler if one exists. If there is no onError handler, such an error is ignored. If an error is not limited to a specific result then the Operation is completed exceptionally.
>>>> 
>>>> Does that work?
>>>> 
>>>> Does there need to be a way to abort processing subsequent results? What happens when a handler throws an exception?
>>>> 
>>>> Douglas
>>>> 
>>>> 
>>>>> On Oct 5, 2017, at 2:14 AM, Lukas Eder <lukas.eder at gmail.com <mailto:lukas.eder at gmail.com>> wrote:
>>>>> 
>>>>> I like your suggestion of having three separate handlers (which may point to the same handler if the API user so desires). It is definitely cleaner than having a three-arg handler.
>>>>> 
>>>>> In classic JDBC, the relevant call is statement.getMoreResults() and the user is free to completely ignore the result, e.g. by not calling statement.getResultSet() or statement.getUpdateCount(). However, now I wonder how the new DynamicMultiOperation.onError(BiConsumer<Integer, Throwable>) method would communicate with the pre-existing Operation.onError(Consumer<Throable>) method...
>>>>> 
>>>>> 2017-10-04 18:21 GMT+02:00 Douglas Surber <douglas.surber at oracle.com <mailto:douglas.surber at oracle.com>>:
>>>>> Interlaced errors was not considered. It would be simple to add a Throwable as a third arg to ResultHandler.handle. Having two args of which one was always null is a bit of a hack. Three is definitely a hack. So I would eliminate ResultHandler and add the following methods to DynamicMultiOperation:
>>>>> 
>>>>>   public DynamicMultiOperation<T> onCount(BiConsumer<Integer, CountOperation<T>> handler);
>>>>>   public DynamicMultiOperation<T> onRows(BiConsumer<Integer, RowOperation<T>> handler);
>>>>>   public DynamicMultiOperation<T> onError(BiConsumer<Integer, Throwable> handler);
>>>>> 
>>>>> If there is no handler for a result the result is ignored or the Operation completes exceptionally. Not sure which would be better.
>>>>> 
>>>>> Douglas
>>>>> 
>>>>> 
>>>>> > On Oct 4, 2017, at 2:22 AM, Lukas Eder <lukas.eder at gmail.com <mailto:lukas.eder at gmail.com>> wrote:
>>>>> >
>>>>> > Hello,
>>>>> >
>>>>> > The java.sql2 API contains a DynamicMultiOperation type which seems
>>>>> > intended for use with statement batches or procedure calls that produce an
>>>>> > unknown set of:
>>>>> >
>>>>> > - Update counts
>>>>> > - Result sets
>>>>> > - Exceptions
>>>>> >
>>>>> > Unfortunately, I'm missing the part where interlaced exceptions can be
>>>>> > received. This may be SQL Server (and perhaps MySQL) specific, as I haven't
>>>>> > really encountered this in any other databases. But in SQL Server, when
>>>>> > running the following statement batch:
>>>>> >
>>>>> > DECLARE @t TABLE(i INT);
>>>>> > INSERT INTO @t VALUES (1),(2),(3);
>>>>> > RAISERROR('message 1', 16, 2, 3);
>>>>> > RAISERROR('message 2', 16, 2, 3);
>>>>> > SELECT * FROM @t
>>>>> > RAISERROR('message 3', 16, 2, 3);
>>>>> >
>>>>> > We can run the following, complete program (taken from a blog post I've
>>>>> > written recently [1]):
>>>>> >
>>>>> > String sql = "DECLARE @t TABLE(i INT);\r\n" +
>>>>> >    "INSERT INTO @t VALUES (1),(2),(3);\r\n" +
>>>>> >    "RAISERROR('message 1', 16, 2, 3);\r\n" +
>>>>> >    "RAISERROR('message 2', 16, 2, 3);\r\n" +
>>>>> >    "SELECT * FROM @t\r\n" +
>>>>> >    "RAISERROR('message 3', 16, 2, 3);";
>>>>> > try (Connection c = new
>>>>> > com.microsoft.sqlserver.jdbc.SQLServerDriver().connect(url, properties);
>>>>> >     Statement s = c.createStatement()) {
>>>>> >
>>>>> >    fetchLoop:
>>>>> >        for (int i = 0, updateCount = 0; i < 256; i++) {
>>>>> >            try {
>>>>> >                boolean result = (i == 0)
>>>>> >                    ? s.execute(sql)
>>>>> >                    : s.getMoreResults();
>>>>> >
>>>>> >                SQLWarning w = s.getWarnings();
>>>>> >                for (int j = 0; j < 255 && w != null; j++) {
>>>>> >                    System.out.println("Warning     : " + w.getMessage());
>>>>> >                    w = w.getNextWarning();
>>>>> >                }
>>>>> >
>>>>> >                s.clearWarnings();
>>>>> >
>>>>> >                if (result)
>>>>> >                    try (ResultSet rs = s.getResultSet()) {
>>>>> >                        System.out.println("Result      :");
>>>>> >                        ResultSetMetaData m = rs.getMetaData();
>>>>> >
>>>>> >                        while (rs.next())
>>>>> >                            for (int col = 1; col <= m.getColumnCount();
>>>>> > col++)
>>>>> >                                System.out.println(
>>>>> >                                    "  " + m.getColumnName(col) +
>>>>> >                                    ": " + rs.getInt(col));
>>>>> >                    }
>>>>> >                else if ((updateCount = s.getUpdateCount()) != -1)
>>>>> >                    System.out.println("Update Count: " + updateCount);
>>>>> >                else
>>>>> >                    break fetchLoop;
>>>>> >                }
>>>>> >            catch (SQLException e) {
>>>>> >                System.out.println("Exception   : " + e.getMessage());
>>>>> >            }
>>>>> >        }
>>>>> > }
>>>>> >
>>>>> >
>>>>> > And get this result:
>>>>> >
>>>>> > Update Count: 3
>>>>> > Exception   : message 1
>>>>> > Exception   : message 2
>>>>> > Result      :
>>>>> >  i: 1
>>>>> >  i: 2
>>>>> >  i: 3
>>>>> > Exception   : message 3
>>>>> >
>>>>> > I currently don't see a way how the exception messages "message 1" /
>>>>> > "message 2" / "message 3" can be fetched through the DynamicMultiOperation.
>>>>> > Could you perhaps show an example?
>>>>> >
>>>>> > Thanks,
>>>>> > Lukas
>>>>> >
>>>>> > [1]:
>>>>> > https://blog.jooq.org/2017/07/13/how-i-incorrectly-fetched-jdbc-resultsets-again/ <https://urldefense.proofpoint.com/v2/url?u=https-3A__blog.jooq.org_2017_07_13_how-2Di-2Dincorrectly-2Dfetched-2Djdbc-2Dresultsets-2Dagain_&d=DwMFaQ&c=RoP1YumCXCgaWHvlZYR8PQcxBKCX5YTpkKY057SbK10&r=ChRVNBZ3Ru5F7CzL9kG_sNBRUO0uuqD6z6ltcMO-LbA&m=56RBDwLoPx1hjE7ozVe90yWivV6VyNiMfXWMyf3pnLw&s=jCcOZl10pZh4vO0d15g0aat2s7O9RrEPwMWcP8IVfl0&e=>

More information about the jdbc-spec-discuss mailing list