get output of TMSSqlRow in Talend

问题:

i would like to get the number of row affected / deleted / updated with a TMSSqlRow.

Here is how the job is :

the file use contains a lot of sql statement like DELETE ... INSERT ... UPDATE... each row are separate by ";"

But now, I would like to get result of each statement (x rows updated, like results are display in management studio).

When I go to "advanced settings" tab of tmssqlrow, I select " Propagate QUERY's recordset" and select a column I created before (Object Type).

On execution, I have this error : "The executeQuery method must return a result set".

So, How I can get the result of each statement and insert it (by example) in a database / file ?

thanks in advance. Regards, Xavier

回答1:

The option "Propagate QUERY's recordset" must be used in combination with a tParseRecordSet in order to extract info from the returned recordset. However, that is not sufficent: you must explicitly write the query to return the number of records updated/deleted.

Here's what I did:

My tJDBCRow (same as tMSSqlRow) query looks like this (notice how I had to add 'set nocount on' before the update query, and 'select @@rowcount' after)

tParseRecordSet retrieves the number of lines from the column resultset (nbLines is the alias of my rowcount)