Talend中如何通过TMSSqlRow获取SQL语句的受影响行数？

Alright, let's break down how to fix this issue and capture the row count for each of your SQL statements. First, let's understand why you're seeing that error:

Why the Error Happens #

The "Propagate QUERY's recordset" option in TMSSqlRow is designed only for SELECT queries that return a result set. When you run DELETE/INSERT/UPDATE statements, Talend uses the executeUpdate() method (which returns an integer of affected rows) instead of executeQuery() (which returns a result set). Enabling that option forces Talend to expect a result set, hence the error: "The executeQuery method must return a result set."

Step-by-Step Solution #

Here's how to structure your job to process each SQL statement individually and capture its affected row count:

1. Split Your Multi-SQL File into Individual Statements #

First, you need to split the input file (with semicolon-separated SQL) into separate, executable statements.

• Use tFileInputDelimited to read your file. Set the Field Separator to ;, and disable "Header" if your file doesn't have one.
• Add a tJavaRow to clean up each statement (trim whitespace, remove trailing semicolons if needed):

  // Clean up the SQL statement
  String cleanSql = input_row.sqlStatement.trim();
  if (cleanSql.endsWith(";")) {
      cleanSql = cleanSql.substring(0, cleanSql.length() - 1);
  }
  output_row.cleanSql = cleanSql;
  

  Note: If your SQL contains semicolons inside string literals (e.g., INSERT INTO ... VALUES ('Hello;World')), you'll need a more robust regex-based split to avoid breaking those statements. A simple regex like ;(?=(?:[^']*'[^']*')*[^']*$) can help split only on semicolons outside quotes.

2. Configure TMSSqlRow to Execute Statements #

• Drag a tMSSqlRow component and connect it to your cleaned SQL stream.
• In the Basic Settings tab, set the SQL query to row1.cleanSql (replace row1 with your actual input row name).
• Do NOT enable "Propagate QUERY's recordset" in the Advanced Settings tab—this is critical to avoid the error.

3. Capture Affected Row Counts #

Talend stores the SQL Statement object in the global map, which we can use to get the update count:

• Add a tJavaRow right after the tMSSqlRow.
• In the code area, use this snippet to get the affected rows and generate a human-readable message (like SSMS does):

  // Get the statement object from the global map (replace tMSSqlRow_1 with your component name)
  java.sql.Statement stmt = (java.sql.Statement) globalMap.get("tMSSqlRow_1_STATEMENT");
  int affectedRows = stmt.getUpdateCount();
  
  // Assign values to output fields
  output_row.sqlStatement = input_row.cleanSql;
  output_row.affectedRows = affectedRows;
  
  // Generate a message matching SSMS style
  String sqlUpper = input_row.cleanSql.toUpperCase();
  if (sqlUpper.startsWith("INSERT")) {
      output_row.resultMessage = affectedRows + " 行已插入";
  } else if (sqlUpper.startsWith("UPDATE")) {
      output_row.resultMessage = affectedRows + " 行已更新";
  } else if (sqlUpper.startsWith("DELETE")) {
      output_row.resultMessage = affectedRows + " 行已删除";
  } else {
      output_row.resultMessage = affectedRows + " 行已处理";
  }
  

4. Output the Results #

Now you can write the results to a file or database:

• To write to a file: Use tFileOutputDelimited, mapping the sqlStatement, affectedRows, and resultMessage fields.
• To insert into a database: Use another tMSSqlRow with an INSERT query that uses the output fields from the tJavaRow.

Bonus: Error Handling #

Add a tTryCatch component around the tMSSqlRow to catch any invalid SQL statements:

• In the Catch section, use a tJavaRow to capture the error message and log it alongside the problematic SQL statement.
• Example error handling snippet:

  String errorMsg = (String) globalMap.get("tTryCatch_1_ERROR_MESSAGE");
  output_row.sqlStatement = input_row.cleanSql;
  output_row.resultMessage = "执行失败: " + errorMsg;
  output_row.affectedRows = -1; // Mark as failed
  

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