Write output from non select query into file

[jensej]

So I have a talend job where I can put an select query into a .sql file and then start my talend job. What it then does is that it saves the sql query into a context.sql variable and then iterates the query over multiple databases and then prints the output in 1 csv file. So if i have a query like select name from persons limit 1 and iterates this over 10 connections i will have 10 rows in my .csv with the name inside.

This works exactly as I want.

Now I want to make it possible to also write non select statements like update persons set name = 'Peter' where name = 'peter'.

To do this I use a sqlparser to determine if the sql is a select query or something else. If it's a non select statement I use a tdbrow togheter with the context.sql and it works fine.

My problem is that I would still like to have a .csv file as output holding some information about what happened. So if it's a non select statement I will first create the output.csv with a header with following titles.

HostDB, Query, Error, RowsAffected, Timestamp then i will run the tdbrow and in the end have a output.csv where i can see if the update where succesfull or not and how many rows that where updated and at what time.

I have tried things like this:

tdbrow --> tjavarow --> tmap --> tfileoutputdelimited

but if i press ctrl+space in tjavarow i don't have any NB_Line or something. For the tdbrow component i only have QUERY or ERROR to choose from.

How can I achieve my goal to print a kind of log into the .csv so I can be sure what was really updated or if the update failed on any of my databases.

[Anonymous]

Hi

No this kind of global variables are available on tDBRow component.

Which DB type are you using?

if it is Mysql, you can use another tDBRow to execute this query to get the number of updated rows.

"select row_count() as updated_rows"

For more details, please the see below screenshots.

​

if it is other types of database, check if there is a similar function.

Regards

Shong

[jensej]

Hi Shong

Thanks for your answer. I'm using Postgres DB. Would your solution also work for that? Can this also be used for example delete statements and be changed to affected_rows instead of updated_rows since I don't know what type of non select statement that will occur. My wish would be to have a affected_rows column in my output.csv with following entries depending on the query.

update = amount of updated rows

delete = amount of rows deleted

create = 0 rows affected

drop = 0 rows affected

insert = amount of rows inserted

[jensej]

Here is how the part of my job looks like at the moment. My tdbrow schema is empty. The thing that's missing is an affectedRows column in the output with the amount of rows that has been affected.