java.sql2 DynamicMultiOperation with interlaced exceptions

[Lukas Eder]

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/