[IGNITE-22546] Improve SQL script results processing in CLI

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: cli
• Labels: ignite-3 ignite-3-cli-tool
• Ignite Flags: Docs Required
• Assignee: Maksim Myskov
• Reporter: Pavel Pereslegin
• Created: 20/Jun/24 10:17
• Updated: 12/Sep/24 14:58
• Resolved: 12/Sep/24 14:58

Description

SqlManager#execute does not process all the results of script execution.

        try (Statement statement = connection.createStatement()) {
            if (statement.execute(sql)) {
                ResultSet resultSet = statement.getResultSet();
                return new SqlQueryResult(Table.fromResultSet(resultSet));
            }
            int updateCount = statement.getUpdateCount();
            return new SqlQueryResult(updateCount >= 0 ? "Updated " + updateCount + " rows." : "OK!");
        }

Here we read only the result of executing the first statement. But JDBC supports the ability to read the execution results of all statements.

Example:

    do {
        System.out.println("---");

        if (stmt.getUpdateCount() != -1) {
            System.out.println("update count: " + stmt.getUpdateCount());

            continue;
        }

        try (ResultSet rs = stmt.getResultSet()) {
            while (rs.next()) {
                List<Object> row = new ArrayList<>();
                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    row.add(rs.getObject(i + 1));
                }

                System.out.println("row: " + row);
            }
        }
    } while (stmt.getMoreResults() || stmt.getUpdateCount() != -1);

I suggest to improve script processing in SqlManager#execute.

I'm not sure what to do with the result sets, but beware that current "resultSet" will be closed when getMoreResults() is called (AI3 JDBC implementation limitation).

At least, it's worth returning the correct number of updates for the entire script (but again I'm not sure if it should be one total value or multiple values ​​for each statement).

Issue Links

• links to: GitHub Pull Request #4348