This is an automated email from the ASF dual-hosted git repository.

hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git


The following commit(s) were added to refs/heads/main by this push:
     new 211c122239 Database perspective: Generate DDL without quoting, Stop 
doesn't stop DDL/DML, rows of PRAGMA/RETURNING/CALL are discarded, fixes #9314 
(#9369)
211c122239 is described below

commit 211c122239c31f4f9eaef8b7a7117761d920c90c
Author: Bart Maertens <[email protected]>
AuthorDate: Wed Nov 11 12:51:55 2026 +0100

    Database perspective: Generate DDL without quoting, Stop doesn't stop 
DDL/DML, rows of PRAGMA/RETURNING/CALL are discarded, fixes #9314 (#9369)
---
 .../org/apache/hop/core/database/Database.java     | 80 ++++++++++++++++++++--
 .../hop/core/database/ExecutedStatement.java       | 41 +++++++++++
 .../hop/core/database/SqlQueryClassifier.java      | 18 +++++
 .../org/apache/hop/core/database/DatabaseTest.java | 56 +++++++++++++++
 .../hop/core/database/SqlQueryClassifierTest.java  | 11 +++
 .../ROOT/pages/hop-gui/perspective-database.adoc   |  8 +--
 .../hop/ui/core/database/dialog/SqlEditor.java     | 46 +++++++++++--
 .../perspective/database/DatabaseOperation.java    | 12 +++-
 .../perspective/database/DatabaseSqlEditorTab.java | 73 +++++++++++++++++---
 .../perspective/database/DatabaseWorkbench.java    |  4 +-
 .../dialog/messages/messages_en_US.properties      |  1 +
 .../database/messages/messages_en_US.properties    |  1 +
 .../database/DatabaseOperationsPanelTest.java      | 18 +++++
 .../database/DatabaseSqlEditorTabTest.java         | 68 ++++++++++++++++--
 14 files changed, 406 insertions(+), 31 deletions(-)

diff --git a/core/src/main/java/org/apache/hop/core/database/Database.java 
b/core/src/main/java/org/apache/hop/core/database/Database.java
index cd31deb4c1..aba99e0647 100644
--- a/core/src/main/java/org/apache/hop/core/database/Database.java
+++ b/core/src/main/java/org/apache/hop/core/database/Database.java
@@ -152,6 +152,14 @@ public class Database implements IVariables, 
ILoggingObject, AutoCloseable {
 
   private Statement selStmt;
   private PreparedStatement pstmt;
+
+  /**
+   * The statement {@link #execStatement(String, IRowMeta, Object[])} or {@link
+   * #executeStatement(String, int)} is running, so that {@link 
#cancelQuery()} can stop DDL and DML
+   * from another thread as well as queries.
+   */
+  private volatile Statement runningStatement;
+
   @Getter private PreparedStatement prepStatementLookup;
   @Getter private PreparedStatement prepStatementUpdate;
   @Getter private PreparedStatement prepStatementInsert;
@@ -805,6 +813,7 @@ public class Database implements IVariables, 
ILoggingObject, AutoCloseable {
 
     cancelStatement(pstmt);
     cancelStatement(selStmt);
+    cancelStatement(runningStatement);
   }
 
   /**
@@ -1558,18 +1567,24 @@ public class Database implements IVariables, 
ILoggingObject, AutoCloseable {
       boolean resultSet;
       int count;
       if (params != null) {
-        PreparedStatement prepStmt = 
connection.prepareStatement(databaseMeta.stripCR(sql));
-        setValues(params, data, prepStmt); // set the parameters!
-        applyStatementQueryTimeout(prepStmt);
-        resultSet = prepStmt.execute();
-        count = prepStmt.getUpdateCount();
-        prepStmt.close();
+        try (PreparedStatement prepStmt = 
connection.prepareStatement(databaseMeta.stripCR(sql))) {
+          setValues(params, data, prepStmt); // set the parameters!
+          applyStatementQueryTimeout(prepStmt);
+          runningStatement = prepStmt;
+          resultSet = prepStmt.execute();
+          count = prepStmt.getUpdateCount();
+        } finally {
+          runningStatement = null;
+        }
       } else {
         String sqlStripped = databaseMeta.stripCR(sql);
         try (Statement stmt = connection.createStatement()) {
           applyStatementQueryTimeout(stmt);
+          runningStatement = stmt;
           resultSet = stmt.execute(sqlStripped);
           count = stmt.getUpdateCount();
+        } finally {
+          runningStatement = null;
         }
       }
       if (!resultSet) {
@@ -1594,6 +1609,59 @@ public class Database implements IVariables, 
ILoggingObject, AutoCloseable {
     return result;
   }
 
+  /**
+   * Execute one statement of any kind and read the rows it returns, if it 
returns any. Unlike
+   * {@link #execStatement(String)} this keeps the result set of statements 
that are not plain
+   * queries, such as {@code PRAGMA table_info(t)}, {@code INSERT ... 
RETURNING id} or {@code CALL
+   * proc()}. Only the first result is read, up to {@code limit} rows. {@link 
#cancelQuery()} stops
+   * the statement while it runs.
+   *
+   * @param rawsql the statement, comments allowed
+   * @param limit the maximum number of rows to read, {@literal <=0} for no 
limit
+   * @return the update count, the counts per verb and the rows, if any
+   * @throws HopDatabaseException in case anything goes wrong.
+   */
+  public ExecutedStatement executeStatement(String rawsql, int limit) throws 
HopDatabaseException {
+    String sql = 
databaseMeta.getIDatabase().createSqlScriptParser().removeComments(rawsql).trim();
+    Result result = new Result();
+    int count = -1;
+    IRowMeta resultRowMeta = null;
+    List<Object[]> rows = null;
+    try (Statement stmt = connection.createStatement()) {
+      applyStatementQueryTimeout(stmt);
+      // No setMaxRows here: some drivers (jTDS, for one) implement it with 
SET ROWCOUNT, which
+      // would also cap the rows an UPDATE or DELETE touches. The limit 
applies while reading.
+      runningStatement = stmt;
+      boolean hasResultSet = stmt.execute(databaseMeta.stripCR(sql));
+      if (hasResultSet) {
+        try (ResultSet rs = stmt.getResultSet()) {
+          resultRowMeta = getRowInfo(rs.getMetaData(), 
databaseMeta.isMySqlVariant(), false);
+          rows = new ArrayList<>();
+          while (limit <= 0 || rows.size() < limit) {
+            Object[] row = getRow(rs, null, resultRowMeta);
+            if (row == null) {
+              break;
+            }
+            rows.add(row);
+          }
+        }
+      } else {
+        count = stmt.getUpdateCount();
+        countAffectedRows(result, sql, count);
+      }
+      if (SqlQueryClassifier.isSchemaChange(sql)) {
+        DbCache.getInstance().clear(databaseMeta.getName());
+      }
+    } catch (SQLException ex) {
+      throw new HopDatabaseException("Couldn't execute SQL: " + sql + 
Const.CR, ex);
+    } catch (Exception e) {
+      throw new HopDatabaseException("Unexpected error executing SQL: " + 
Const.CR, e);
+    } finally {
+      runningStatement = null;
+    }
+    return new ExecutedStatement(result, count, resultRowMeta, rows);
+  }
+
   /**
    * Attribute the number of rows a statement affected to the matching counter 
of the result. JDBC
    * reports a single count per statement; for MERGE, UPSERT and REPLACE it 
covers the inserted,
diff --git 
a/core/src/main/java/org/apache/hop/core/database/ExecutedStatement.java 
b/core/src/main/java/org/apache/hop/core/database/ExecutedStatement.java
new file mode 100644
index 0000000000..1f6b80f81a
--- /dev/null
+++ b/core/src/main/java/org/apache/hop/core/database/ExecutedStatement.java
@@ -0,0 +1,41 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.core.database;
+
+import java.util.List;
+import org.apache.hop.core.Result;
+import org.apache.hop.core.row.IRowMeta;
+
+/**
+ * What {@link Database#executeStatement(String, int)} found after running one 
statement.
+ *
+ * @param result the counts per verb, as {@link 
Database#execStatement(String)} reports them
+ * @param updateCount the JDBC update count, {@code -1} when the statement 
returned rows
+ * @param rowMeta the layout of the returned rows, {@code null} when there 
were none
+ * @param rows the returned rows, {@code null} when the statement did not 
return a result set
+ */
+public record ExecutedStatement(
+    Result result, int updateCount, IRowMeta rowMeta, List<Object[]> rows) {
+
+  /**
+   * @return {@code true} when the statement returned a result set, even an 
empty one
+   */
+  public boolean hasRows() {
+    return rows != null;
+  }
+}
diff --git 
a/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java 
b/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
index 8dc9ffcb31..9bf63176b9 100644
--- a/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
+++ b/core/src/main/java/org/apache/hop/core/database/SqlQueryClassifier.java
@@ -94,6 +94,10 @@ public final class SqlQueryClassifier {
           "MULTISET",
           "VOLATILE");
 
+  /** Verbs of statements that insert, update or delete rows. */
+  private static final Set<String> ROW_CHANGE_VERBS =
+      Set.of("INSERT", "UPDATE", "DELETE", "MERGE", "UPSERT", "REPLACE");
+
   /** Object types whose layout is reflected in cached row metadata. */
   private static final Set<String> SCHEMA_CHANGE_OBJECTS = Set.of("TABLE", 
"VIEW", "SYNONYM");
 
@@ -215,6 +219,20 @@ public final class SqlQueryClassifier {
     return first;
   }
 
+  /**
+   * Whether the update count of a statement is a number of rows. Some drivers 
report a stale count
+   * for DDL (sqlite-jdbc repeats the count of the previous INSERT after a 
{@code CREATE TABLE}), so
+   * only these verbs are trusted.
+   *
+   * @param sql one statement, comments allowed
+   * @return {@code true} for {@code INSERT}, {@code UPDATE}, {@code DELETE}, 
{@code MERGE}, {@code
+   *     UPSERT} and {@code REPLACE}, also after a {@code WITH} clause
+   */
+  public static boolean isRowChange(String sql) {
+    String verb = statementVerb(sql);
+    return verb != null && ROW_CHANGE_VERBS.contains(verb);
+  }
+
   /**
    * Whether a statement changes the layout of a table or a view, which makes 
any row metadata
    * cached for that connection unreliable.
diff --git a/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java 
b/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
index ad23986036..8cd63712c5 100644
--- a/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
+++ b/core/src/test/java/org/apache/hop/core/database/DatabaseTest.java
@@ -1015,6 +1015,62 @@ class DatabaseTest {
             .getNrLinesDeleted());
   }
 
+  @Test
+  void cancelQueryStopsTheStatementExecStatementIsRunning() throws Exception {
+    when(meta.getIDatabase()).thenReturn(new NoneDatabaseMeta());
+    when(meta.stripCR(anyString())).thenAnswer(invocation -> 
invocation.getArgument(0));
+    Statement statement = mock(Statement.class);
+    when(conn.createStatement()).thenReturn(statement);
+    Database db = new Database(log, variables, meta);
+    db.setConnection(conn);
+    // The Stop button calls cancelQuery from another thread while execute() 
blocks.
+    when(statement.execute(anyString()))
+        .thenAnswer(
+            invocation -> {
+              db.cancelQuery();
+              return false;
+            });
+
+    db.execStatement("CREATE TABLE big AS SELECT * FROM src");
+
+    verify(statement).cancel();
+    // Once the statement is done, cancelling does not touch it again.
+    db.cancelQuery();
+    verify(statement, times(1)).cancel();
+  }
+
+  @Test
+  void cancelQueryStopsTheStatementExecuteStatementIsRunning() throws 
Exception {
+    when(meta.getIDatabase()).thenReturn(new NoneDatabaseMeta());
+    when(meta.stripCR(anyString())).thenAnswer(invocation -> 
invocation.getArgument(0));
+    Statement statement = mock(Statement.class);
+    when(conn.createStatement()).thenReturn(statement);
+    Database db = new Database(log, variables, meta);
+    db.setConnection(conn);
+    when(statement.execute(anyString()))
+        .thenAnswer(
+            invocation -> {
+              db.cancelQuery();
+              return false;
+            });
+    when(statement.getUpdateCount()).thenReturn(0);
+
+    db.executeStatement("UPDATE t SET v = 1", 10);
+
+    verify(statement).cancel();
+  }
+
+  @Test
+  void executeStatementReportsTheUpdateCount() throws Exception {
+    ExecutedStatement executed =
+        databaseAffecting(5).executeStatement("UPDATE t SET v = 1 WHERE id < 
6", 1000);
+
+    assertFalse(executed.hasRows());
+    assertNull(executed.rowMeta());
+    assertEquals(5, executed.updateCount());
+    assertEquals(5, executed.result().getNrLinesUpdated());
+  }
+
   @Test
   void execStatementIgnoresStatementsWithoutAffectedRows() throws Exception {
     Result result = databaseAffecting(0).execStatement("MERGE INTO t USING s 
ON t.id = s.id");
diff --git 
a/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java 
b/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
index 900882ca75..873be4d3ec 100644
--- 
a/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
+++ 
b/core/src/test/java/org/apache/hop/core/database/SqlQueryClassifierTest.java
@@ -261,4 +261,15 @@ class SqlQueryClassifierTest {
     assertFalse(SqlQueryClassifier.isSchemaChange("ALTER SESSION SET x = 1"));
     assertFalse(SqlQueryClassifier.isSchemaChange("DROP INDEX i"));
   }
+
+  @Test
+  void rowChangeVerbs() {
+    assertTrue(SqlQueryClassifier.isRowChange("UPDATE t SET v = 1"));
+    assertTrue(SqlQueryClassifier.isRowChange("-- note\ninsert into t values 
(1)"));
+    assertTrue(SqlQueryClassifier.isRowChange("WITH s AS (SELECT 1) DELETE 
FROM t"));
+    assertTrue(SqlQueryClassifier.isRowChange("MERGE INTO t USING s ON t.id = 
s.id"));
+    assertFalse(SqlQueryClassifier.isRowChange("CREATE TABLE t (id INT)"));
+    assertFalse(SqlQueryClassifier.isRowChange("SELECT 1"));
+    assertFalse(SqlQueryClassifier.isRowChange(""));
+  }
 }
diff --git 
a/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc 
b/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
index d2f9e8f949..6c3964d450 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/hop-gui/perspective-database.adoc
@@ -42,7 +42,7 @@ Toolbar:
 * *Refresh* — reload the selected connected connection, or the connection list
 * *Edit connection* — open this connection in the Metadata perspective (or a 
dialog if that perspective is disabled)
 * *Open SQL editor* — new SQL tab for the selected connection (`SELECT * FROM 
table` when a table is selected)
-* *Generate DDL* — `CREATE TABLE` for the selected table, opened in a SQL tab
+* *Generate DDL* — `CREATE TABLE` for the selected table, with the schema and 
quoted names where the database needs them, opened in a SQL tab
 * *Preview table* — run `SELECT` with a dialect-aware row limit (the **Query 
row limit** in Configuration → Plugins → Database Perspective, default 1000)
 * *Show object information* — columns, indexes, and a **DDL** tab with `CREATE 
TABLE` or `CREATE VIEW`. Views use the catalog definition when the database 
provides it (`INFORMATION_SCHEMA`, `pg_get_viewdef`, `SHOW CREATE TABLE`, and 
similar).
 
@@ -59,12 +59,12 @@ Each SQL tab is bound to one connection.
 * If you have a selection, that selection is executed (all statements in it).
 * If you do not, **Run** takes the blank-line-delimited block around the 
caret, splits on `;`, and runs the statement at the caret. A leftover clause 
after a semicolon (for example `WHERE` on the next line) is not executed; use 
**Run all** to send every statement.
 * **Select the executed SQL in the editor** (Configuration perspective, 
**Plugins → Database Perspective**, on by default) highlights only the 
statement(s) that were sent.
-* Results appear in a sash under the editor (one tab per query, plus a 
messages tab). Close hides the results panel; the next run shows it again. 
**Open in separate window** (left of Close) opens the selected result table in 
a separate window.
-* Queries are capped at the **Query row limit** (Configuration perspective, 
**Plugins → Database Perspective**, default 1000). That cap is sent to the 
driver (`setMaxRows`) as well as applied when reading rows, so a `SELECT *` or 
a SQL `LIMIT` above the option still does not pull the whole table. When the 
cap is hit, the Messages tab says so. Statement timeout 
(`+'${HOP_QUERY_PREVIEW_TIMEOUT}'+`, seconds; `0` is the driver default) 
applies to both queries and DDL/DML.
+* Results appear in a sash under the editor (one tab per query, plus a 
messages tab). Statements that return rows without being a `SELECT` (`PRAGMA 
table_info(t)`, `CALL proc()`, `INSERT ... RETURNING id`) get a result tab too. 
For `INSERT`, `UPDATE`, `DELETE` and `MERGE` the messages tab shows the number 
of rows affected. Close hides the results panel; the next run shows it again. 
**Open in separate window** (left of Close) opens the selected result table in 
a separate window.
+* Queries are capped at the **Query row limit** (Configuration perspective, 
**Plugins → Database Perspective**, default 1000). That cap is sent to the 
driver (`setMaxRows`) as well as applied when reading rows, so a `SELECT *` or 
a SQL `LIMIT` above the option still does not pull the whole table. When a 
result has more rows than the cap, the Messages tab says so. Statement timeout 
(`+'${HOP_QUERY_PREVIEW_TIMEOUT}'+`, seconds; `0` is the driver default) 
applies to both queries and DDL/DML.
 
 image::hop-gui/database/database-perspective-panel.png[SQL editor with query 
results, Open in separate window, and the operations status line,width="90%"]
 
-Long-running work (connect, listing, SQL, DDL, table info) is shown as a 
one-line status at the bottom of the right-hand side (`description - connection 
- Done/Failed - 23 ms`). *Stop* cancels the current operation 
(`Statement.cancel`). *Expand* opens the operations list; *Minimize* on that 
list returns to the status line.
+Long-running work (connect, listing, SQL, DDL, table info) is shown as a 
one-line status at the bottom of the right-hand side (`description - connection 
- Done/Failed - 23 ms`). *Stop* cancels the current operation 
(`Statement.cancel`), queries as well as DDL and DML. A driver that cannot 
interrupt a statement lets it finish; the operation then shows *Done*, because 
the statement ran and was committed. *Expand* opens the operations list; 
*Minimize* on that list returns to the status line.
 
 Open and save `.sql` files with the normal File menu. Explorer `.sql` files 
stay in the File explorer; use the database icon on the content-editor toolbar 
to open the buffer in this perspective (you pick the connection).
 
diff --git 
a/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java 
b/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
index 4d9cf3f9ed..7f215fa9ca 100644
--- a/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
+++ b/ui/src/main/java/org/apache/hop/ui/core/database/dialog/SqlEditor.java
@@ -26,6 +26,8 @@ import org.apache.hop.core.IProgressMonitor;
 import org.apache.hop.core.Props;
 import org.apache.hop.core.database.Database;
 import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.ExecutedStatement;
+import org.apache.hop.core.database.SqlQueryClassifier;
 import org.apache.hop.core.database.SqlScriptStatement;
 import org.apache.hop.core.exception.HopDatabaseException;
 import org.apache.hop.core.logging.HopLogStore;
@@ -368,7 +370,7 @@ public class SqlEditor {
         executeSelectStatement(db, statement, nrStats[0], message, monitor);
       } else {
         nrStats[0]++;
-        aborted = executeDdlStatement(db, statement, databaseMeta, message);
+        aborted = executeDdlStatement(db, statement, nrStats[0], databaseMeta, 
message);
       }
     }
 
@@ -450,14 +452,50 @@ public class SqlEditor {
                     dbe));
   }
 
+  private void showReturnedRows(int statNr, ExecutedStatement executed) {
+    if (executed.rows().isEmpty()) {
+      return;
+    }
+    shell
+        .getDisplay()
+        .syncExec(
+            () ->
+                new ShowRowsDialog(
+                        shell,
+                        variables,
+                        BaseMessages.getString(
+                            PKG, "SQLEditor.ResultRows.Title", 
Integer.toString(statNr)),
+                        BaseMessages.getString(PKG, 
"SQLEditor.ResultRows.Message"),
+                        executed.rowMeta(),
+                        executed.rows())
+                    .open());
+  }
+
   private boolean executeDdlStatement(
-      Database db, SqlScriptStatement sql, DatabaseMeta databaseMeta, 
StringBuilder message) {
+      Database db,
+      SqlScriptStatement sql,
+      int statNr,
+      DatabaseMeta databaseMeta,
+      StringBuilder message) {
     log.logDetailed("launch DDL statement: " + Const.CR + sql);
     int startLogLine = HopLogStore.getLastBufferLineNr();
     try {
       log.logDetailed("Executing SQL: " + Const.CR + sql);
-      db.execStatement(sql.getStatement());
-      message.append(BaseMessages.getString(PKG, "SQLEditor.Log.SQLExecuted", 
sql));
+      // PRAGMA, CALL, INSERT ... RETURNING and the like return rows without 
being a SELECT.
+      ExecutedStatement executed = db.executeStatement(sql.getStatement(), 
1000);
+      if (executed.hasRows()) {
+        showReturnedRows(statNr, executed);
+      }
+      if (executed.updateCount() >= 0 && 
SqlQueryClassifier.isRowChange(sql.getStatement())) {
+        message.append(
+            BaseMessages.getString(
+                PKG,
+                "SQLEditor.Log.SQLExecutedRows",
+                sql,
+                Integer.toString(executed.updateCount())));
+      } else {
+        message.append(BaseMessages.getString(PKG, 
"SQLEditor.Log.SQLExecuted", sql));
+      }
       message.append(Const.CR);
       if (dbcache != null) {
         dbcache.clear(databaseMeta.getName());
diff --git 
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
 
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
index bfbc4b7639..2f94e427b0 100644
--- 
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
+++ 
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperation.java
@@ -52,6 +52,7 @@ public class DatabaseOperation {
   private volatile long endTime;
   private volatile String errorMessage;
   private volatile boolean cancelled;
+  private volatile boolean ranToEnd;
 
   private final AtomicReference<Database> database = new AtomicReference<>();
 
@@ -81,6 +82,15 @@ public class DatabaseOperation {
     return cancelled;
   }
 
+  /**
+   * The work finished everything it set out to do, even if a cancel came in 
meanwhile. A driver
+   * that cannot interrupt a statement lets it run to the end and commit it, 
so the operation is
+   * done rather than cancelled.
+   */
+  public void markRanToEnd() {
+    ranToEnd = true;
+  }
+
   public IProgressMonitor newMonitor() {
     return new CancelMonitor();
   }
@@ -92,7 +102,7 @@ public class DatabaseOperation {
       detachDatabase();
       return;
     }
-    status = cancelled ? Status.CANCELLED : Status.DONE;
+    status = cancelled && !ranToEnd ? Status.CANCELLED : Status.DONE;
     endTime = System.currentTimeMillis();
     detachDatabase();
   }
diff --git 
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
 
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
index 7aa1efc809..edb1b63154 100644
--- 
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
+++ 
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTab.java
@@ -34,6 +34,8 @@ import org.apache.commons.vfs2.FileObject;
 import org.apache.hop.core.Const;
 import org.apache.hop.core.database.Database;
 import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.ExecutedStatement;
+import org.apache.hop.core.database.SqlQueryClassifier;
 import org.apache.hop.core.database.SqlScriptStatement;
 import org.apache.hop.core.exception.HopException;
 import org.apache.hop.core.gui.plugin.GuiPlugin;
@@ -459,31 +461,44 @@ public class DatabaseSqlEditorTab implements 
IHopFileTypeHandler {
             db.setQueryLimit(rowLimit);
             db.connect();
             int nr = 0;
+            // One row more than the limit tells a result that fills the limit 
exactly apart from
+            // one that was cut off.
+            int readLimit = rowLimit > 0 && rowLimit < Integer.MAX_VALUE ? 
rowLimit + 1 : rowLimit;
+            boolean stopped = false;
             for (SqlScriptStatement sql : statements) {
               if (operation.isCancelled()) {
                 messages
                     .append(BaseMessages.getString(PKG, 
"DatabasePerspective.SqlTab.Cancelled"))
                     .append(Const.CR);
+                stopped = true;
                 break;
               }
               nr++;
               if (sql.isQuery()) {
-                List<Object[]> rows = db.getRows(sql.getStatement(), rowLimit);
+                List<Object[]> rows = db.getRows(sql.getStatement(), 
readLimit);
                 if (rows == null) {
                   rows = List.of();
                 }
                 IRowMeta rowMeta = db.getReturnRowMeta();
-                queryResults.add(new DatabaseResultsPanel.QueryResult(nr, 
rowMeta, rows));
-                messages.append(queryResultMessage(nr, rows.size(), 
rowLimit)).append(Const.CR);
+                addQueryResult(nr, rowMeta, rows, rowLimit, queryResults, 
messages);
               } else {
-                db.execStatement(sql.getStatement());
-                messages
-                    .append(
-                        BaseMessages.getString(
-                            PKG, "DatabasePerspective.SqlTab.Executed", 
sql.getStatement().trim()))
-                    .append(Const.CR);
+                // Not every statement that returns rows starts with SELECT: 
PRAGMA, CALL,
+                // INSERT ... RETURNING, ... Show what comes back instead of 
discarding it.
+                ExecutedStatement executed = 
db.executeStatement(sql.getStatement(), readLimit);
+                if (executed.hasRows()) {
+                  addQueryResult(
+                      nr, executed.rowMeta(), executed.rows(), rowLimit, 
queryResults, messages);
+                } else {
+                  messages
+                      .append(executedMessage(sql.getStatement(), 
executed.updateCount()))
+                      .append(Const.CR);
+                }
               }
             }
+            if (!stopped) {
+              // A driver that cannot interrupt a statement runs it to the end 
and commits it.
+              operation.markRanToEnd();
+            }
           } catch (Exception e) {
             // A bad statement is a result, not a GUI failure. Rethrowing it 
makes runOperation log
             // the stack, and on Hop Web that takes the session down (issue 
#9136).
@@ -580,8 +595,44 @@ public class DatabaseSqlEditorTab implements 
IHopFileTypeHandler {
     return false;
   }
 
-  static String queryResultMessage(int statementNr, int rowCount, int 
rowLimit) {
-    if (rowLimit > 0 && rowCount >= rowLimit) {
+  static void addQueryResult(
+      int statementNr,
+      IRowMeta rowMeta,
+      List<Object[]> rows,
+      int rowLimit,
+      List<DatabaseResultsPanel.QueryResult> queryResults,
+      StringBuilder messages) {
+    boolean capped = rowLimit > 0 && rows.size() > rowLimit;
+    List<Object[]> shown = capped ? rows.subList(0, rowLimit) : rows;
+    queryResults.add(new DatabaseResultsPanel.QueryResult(statementNr, 
rowMeta, shown));
+    messages
+        .append(queryResultMessage(statementNr, shown.size(), rowLimit, 
capped))
+        .append(Const.CR);
+  }
+
+  /**
+   * @param statement the statement as written
+   * @param updateCount the JDBC update count, negative when there is none
+   * @return {@code OK: <statement>}, with the number of rows affected for 
DML. The count of other
+   *     statements is not shown: some drivers repeat the count of the 
previous statement.
+   */
+  static String executedMessage(String statement, int updateCount) {
+    String text = statement.trim();
+    if (updateCount >= 0 && SqlQueryClassifier.isRowChange(statement)) {
+      return BaseMessages.getString(
+          PKG, "DatabasePerspective.SqlTab.ExecutedRows", text, 
Integer.toString(updateCount));
+    }
+    return BaseMessages.getString(PKG, "DatabasePerspective.SqlTab.Executed", 
text);
+  }
+
+  /**
+   * @param statementNr the number of the statement in the script
+   * @param rowCount the number of rows shown
+   * @param rowLimit the perspective's row limit
+   * @param capped {@code true} when the result had more rows than the limit
+   */
+  static String queryResultMessage(int statementNr, int rowCount, int 
rowLimit, boolean capped) {
+    if (capped) {
       return BaseMessages.getString(
           PKG,
           "DatabasePerspective.SqlTab.QueryRowsLimited",
diff --git 
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
 
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
index 09695ab4a4..9ea22e65f3 100644
--- 
a/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
+++ 
b/ui/src/main/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseWorkbench.java
@@ -1106,7 +1106,9 @@ public class DatabaseWorkbench extends Composite 
implements TabClosable {
             operation.attachDatabase(db);
             db.connect();
             IRowMeta fields = db.getTableFields(qualified);
-            ddl = db.getCreateTableStatement(node.getObjectName(), fields, 
null, false, null, true);
+            // getTableFields hands out a copy of the cached layout, so 
quoting it is safe.
+            meta.quoteReservedWords(fields);
+            ddl = db.getCreateTableStatement(qualified, fields, null, false, 
null, true);
           }
           String sql = ddl;
           host.asyncExec(() -> openGeneratedSql(meta, sql));
diff --git 
a/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
 
b/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
index 1d495dec36..7ac27935c9 100644
--- 
a/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
+++ 
b/ui/src/main/resources/org/apache/hop/ui/core/database/dialog/messages/messages_en_US.properties
@@ -83,6 +83,7 @@ SQLEditor.LineNr.Label=Line number: {0}
 SQLEditor.Log.SQLExecError=Error while executing: {0}\n{1}
 SQLEditor.Monitor.Execute=Executing SQL statements...
 SQLEditor.Log.SQLExecuted=SQL executed: {0}\n
+SQLEditor.Log.SQLExecutedRows=SQL executed, {1} row(s) affected: {0}\n
 SQLEditor.Log.StatsExecuted={0} SQL statements executed
 SQLEditor.NoRows.Message=No results found for SQL statement:\n\n{0}
 SQLEditor.NoRows.Title=No result
diff --git 
a/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
 
b/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
index bd3583dda7..2d3ddcc179 100644
--- 
a/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
+++ 
b/ui/src/main/resources/org/apache/hop/ui/hopgui/perspective/database/messages/messages_en_US.properties
@@ -53,6 +53,7 @@ DatabasePerspective.SqlTab.Failed=Failed
 DatabasePerspective.SqlTab.QueryRows=Query {0}: {1} row(s)
 DatabasePerspective.SqlTab.QueryRowsLimited=Query {0}: {1} row(s). Results are 
capped at {2} rows by the Database perspective (a SQL LIMIT above this value is 
ignored). Change the query row limit under Configuration > Plugins > Database 
Perspective.
 DatabasePerspective.SqlTab.Executed=OK: {0}
+DatabasePerspective.SqlTab.ExecutedRows=OK, {1} row(s) affected: {0}
 DatabasePerspective.Results.Title=Results
 DatabasePerspective.Results.Close=Close
 DatabasePerspective.Results.Close.Tooltip=Hide the results panel
diff --git 
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
 
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
index f10f4e0bd8..0562204d71 100644
--- 
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
+++ 
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseOperationsPanelTest.java
@@ -87,6 +87,24 @@ class DatabaseOperationsPanelTest {
     assertEquals(DatabaseOperation.Status.CANCELLED, operation.getStatus());
   }
 
+  @Test
+  void completeOfARunThatFinishedDespiteTheCancelIsDone() {
+    // The driver could not stop the statement: it ran to the end and was 
committed.
+    DatabaseOperation operation = new DatabaseOperation("Execute SQL on CRM", 
"CRM");
+    operation.cancel();
+    operation.markRanToEnd();
+    operation.complete();
+    assertEquals(DatabaseOperation.Status.DONE, operation.getStatus());
+  }
+
+  @Test
+  void failOfACancelledStatementIsCancelled() {
+    DatabaseOperation operation = new DatabaseOperation("Execute SQL on CRM", 
"CRM");
+    operation.cancel();
+    operation.fail("Statement was canceled");
+    assertEquals(DatabaseOperation.Status.CANCELLED, operation.getStatus());
+  }
+
   @Test
   void failDetachesDatabase() {
     DatabaseOperation operation = new DatabaseOperation("Connect", "shop");
diff --git 
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
 
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
index 584f38ba55..c2c1ee0551 100644
--- 
a/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
+++ 
b/ui/src/test/java/org/apache/hop/ui/hopgui/perspective/database/DatabaseSqlEditorTabTest.java
@@ -21,19 +21,22 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
 import static org.junit.jupiter.api.Assertions.assertFalse;
 import static org.junit.jupiter.api.Assertions.assertTrue;
 
+import java.util.ArrayList;
+import java.util.List;
 import org.apache.hop.core.exception.HopDatabaseException;
+import org.apache.hop.core.row.RowMeta;
 import org.junit.jupiter.api.Test;
 
 class DatabaseSqlEditorTabTest {
 
   @Test
   void queryResultMessageUnderTheCap() {
-    assertEquals("Query 1: 12 row(s)", 
DatabaseSqlEditorTab.queryResultMessage(1, 12, 1000));
+    assertEquals("Query 1: 12 row(s)", 
DatabaseSqlEditorTab.queryResultMessage(1, 12, 1000, false));
   }
 
   @Test
-  void queryResultMessageAtTheCapExplainsTheLimit() {
-    String message = DatabaseSqlEditorTab.queryResultMessage(1, 1000, 1000);
+  void queryResultMessageAboveTheCapExplainsTheLimit() {
+    String message = DatabaseSqlEditorTab.queryResultMessage(1, 1000, 1000, 
true);
     assertTrue(message.startsWith("Query 1: 1000 row(s)"));
     assertTrue(message.contains("capped at 1000"));
     assertTrue(message.contains("Database Perspective"));
@@ -42,11 +45,68 @@ class DatabaseSqlEditorTabTest {
 
   @Test
   void queryResultMessageBelowCapDoesNotMentionTheOption() {
-    String message = DatabaseSqlEditorTab.queryResultMessage(2, 50, 2000);
+    String message = DatabaseSqlEditorTab.queryResultMessage(2, 50, 2000, 
false);
     assertEquals("Query 2: 50 row(s)", message);
     assertFalse(message.contains("capped"));
   }
 
+  @Test
+  void resultOfExactlyTheLimitIsNotCapped() {
+    List<DatabaseResultsPanel.QueryResult> results = new ArrayList<>();
+    StringBuilder messages = new StringBuilder();
+
+    DatabaseSqlEditorTab.addQueryResult(1, new RowMeta(), rows(3), 3, results, 
messages);
+
+    assertEquals(3, results.get(0).getRows().size());
+    assertFalse(messages.toString().contains("capped"));
+  }
+
+  @Test
+  void resultAboveTheLimitIsCutAndSaysSo() {
+    List<DatabaseResultsPanel.QueryResult> results = new ArrayList<>();
+    StringBuilder messages = new StringBuilder();
+
+    // The editor reads one row more than the limit to tell the two cases 
apart.
+    DatabaseSqlEditorTab.addQueryResult(1, new RowMeta(), rows(4), 3, results, 
messages);
+
+    assertEquals(3, results.get(0).getRows().size());
+    assertTrue(messages.toString().startsWith("Query 1: 3 row(s)"));
+    assertTrue(messages.toString().contains("capped at 3"));
+  }
+
+  @Test
+  void executedMessageReportsAffectedRowsOfDml() {
+    assertEquals(
+        "OK, 5 row(s) affected: UPDATE t SET v = 1",
+        DatabaseSqlEditorTab.executedMessage("UPDATE t SET v = 1\n", 5));
+    assertEquals(
+        "OK, 0 row(s) affected: DELETE FROM t WHERE 1 = 0",
+        DatabaseSqlEditorTab.executedMessage("DELETE FROM t WHERE 1 = 0", 0));
+    assertEquals(
+        "OK, 2 row(s) affected: WITH s AS (SELECT 1) DELETE FROM t",
+        DatabaseSqlEditorTab.executedMessage("WITH s AS (SELECT 1) DELETE FROM 
t", 2));
+  }
+
+  @Test
+  void executedMessageLeavesDdlAlone() {
+    assertEquals(
+        "OK: CREATE TABLE t (id INT)",
+        DatabaseSqlEditorTab.executedMessage("CREATE TABLE t (id INT)", 0));
+    assertEquals("OK: SET SCHEMA s", DatabaseSqlEditorTab.executedMessage("SET 
SCHEMA s", -1));
+    // sqlite-jdbc repeats the count of the previous INSERT after a CREATE 
TABLE.
+    assertEquals(
+        "OK: CREATE TABLE u (x INT)",
+        DatabaseSqlEditorTab.executedMessage("CREATE TABLE u (x INT)", 1));
+  }
+
+  private static List<Object[]> rows(int count) {
+    List<Object[]> rows = new ArrayList<>();
+    for (int i = 0; i < count; i++) {
+      rows.add(new Object[] {(long) i});
+    }
+    return rows;
+  }
+
   @Test
   void restoredOrLoadedTextIsNotAUserEdit() {
     assertFalse(DatabaseSqlEditorTab.isModifiedFromClean("SELECT 1", "SELECT 
1"));

Reply via email to