Whenever one uses an SQLExecutor for inserting, updating and deleting rows (collectively known as DML statements), it is usually handy to know how many rows have actually been affected by that operation. I am by no means the first person to want to do that with FME.
All DBMS I can think of have at least some way of exposing that information.
- Postgres returns a command status tag with the command type and affected row count,
- Oracle has the SQL%ROWCOUNT variable which countains the number of affected row in for the last query in a PL/SQL block,
- Snowflake stores and exposes query result stats on a per-statement basis,
- JDBC drivers have both the Statement.executeUpdate() and the Statement.getUpdateCount() methods returning the affected row count,
- ODBC drivers have the SQLRowCount function
Most notably, none of these are exposed in a way that an FME user could make use of, as they are part of some interface that extends beyond SQL statements. Most workarounds will involve a much slower operation, such as WITH (INSERT … RETURNING *) SELECT COUNT(*) or something similar.
It would be nice if FME could return some output feature for DML SQL statements where the affected row count (potentially separated between updated, deleted and inserted, for databases that keep track of them separately, like Snowflake during an upsert).
