java.sql2 DynamicMultiOperation with interlaced exceptions

[Douglas Surber]

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-jdbc-resultsets-again/