java.sql2 DynamicMultiOperation with interlaced exceptions
Lukas Eder
lukas.eder at gmail.com
Mon Oct 9 12:43:29 UTC 2017
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>:
> 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>
> 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> 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>:
>
>> 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> 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-j
>> dbc-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