Talend get row affected by delete with tmysqlrow

Question

I have an issue with a Talend Job. I need to delete dome rows (the query will be given by a previous component). To simplify, I do this :

If I uncheck "Propagate query recordset", the job run perfectly. But after checking (I use the same kind of component with MSSQL without issue), the job failed with the error message :

Can not issue data manipulation statements with executeQuery().

And I specify "AllowMultiQueries" in additional parameters of component connexion.

Is it a way to get the number of rows deleted with a tmysqlrow ? I would like to prevent to use before a tmysqlinput, then get the count(*), and then delete.

Answer 1

A simple solution would be to use a tMysqlRow to handle the delete, followed by a tMysqlInput to return the number of deleted rows :

The DELETE and SELECT must be issued using the same connection, so a tMysqlConnection is necessary and a tMysqlCommit at the end (tMysqlCommit is necessary to commit the changes, unless you're using autocommit on the connection, which you shouldn't by the way)

Alternative solution

Here's an alternative solution, in which I send a list of IDs (defined in tFixedFlowInput, but could very well be read from file..etc) to be deleted to a tMysqlOutput (action on data set to Delete), and set the id column as key in the component schema:

Then you can simply use the tMysqlOutput_NB_LINE_DELETED global variable, to get the number of deleted rows.