CODEWITHSUNDEEP
outputtalend
Cascador84
Cascador84

Reputation: 151

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:

enter image description here

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?

Upvotes: 1

Views: 3219

Answers (2)

RealHowTo
RealHowTo

Reputation: 35372

If you need the number of rows affected, a better option is to use the tMSSqlOutput component which can update,insert or delete rows. After execution, the component provides global variables to show how many rows were affected by the operation.

((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE")) 
((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_UPDATED"))   
((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_INSERTED"))    
((Integer)globalMap.get("tMSSqlOutput_1_NB_LINE_DELETED"))

Upvotes: 1

Ibrahim Mezouar
Ibrahim Mezouar

Reputation: 4051

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:

enter image description here

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)

enter image description here

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

enter image description here

Upvotes: 3

Related Questions