Talend中使用tMysqlRow获取删除受影响行数的问题咨询

Hey there, let's break down your two Talend + MySQL issues and fix them up:

1. 解决"Can not issue data manipulation statements with executeQuery()"报错 #

The root cause here is how Talend's tMysqlRow handles the Propagate query recordset option compared to the MSSQL equivalent:

• When you uncheck this option, the component uses executeUpdate() under the hood, which is designed for DML statements (like DELETE, INSERT, UPDATE) that modify data and return a row count.
• When you check it, Talend switches to executeQuery(), which is meant for SELECT statements that return a recordset. MySQL throws an error because you're trying to run a DELETE with a method that expects a result set, whereas MSSQL's component likely auto-switches execution methods based on the query type.

Fix options: #

• Option 1 (Simplest): Keep "Propagate query recordset" unchecked
  This is the standard approach for DML operations in tMysqlRow. The component will use executeUpdate() correctly, and you can still access the number of deleted rows via the component's built-in output field (more on this below).
• Option 2 (If you must enable propagation): Use MySQL 8.0.19+'s RETURNING clause
  If your MySQL version is 8.0.19 or newer, rewrite your DELETE query to return a valid recordset:

  DELETE FROM your_table WHERE your_condition RETURNING *;
  

  This way, executeQuery() has a result set to process, and the delete operation runs successfully. Note this only works with newer MySQL versions.

2. Directly get the number of deleted rows (avoid pre-delete COUNT(*)) #

You absolutely can skip the redundant pre-delete COUNT(*) query—here are two reliable methods:

Method 1: Use tMysqlRow's built-in row count output #

• Keep Propagate query recordset unchecked.
• Connect the tMysqlRow component to another component (like tLogRow or tJavaRow).
• Access the Nb Line field from tMysqlRow—this value is exactly the number of rows deleted by your query. You can log it, store it in a context variable, or use it directly in downstream logic.

Method 2: Execute DELETE + ROW_COUNT() in a multi-query #

Since you already have AllowMultiQueries=true in your connection parameters, run two statements in one tMysqlRow:

DELETE FROM your_table WHERE your_condition;
SELECT ROW_COUNT() AS deleted_rows;

• Check Propagate query recordset to capture the result of the second SELECT statement.
• Use a component like tExtractDelimited or tMap to pull the deleted_rows value from the result set. This works for all MySQL versions that support multi-queries.

内容的提问来源于stack exchange，提问作者Cascador84