One Star

Count of Inserted rows

Hi All,
I have a job which reads more than 5000 records daily. I want to create a log table which captures job status ( OK/FAILED) , No of Rows Inserted , No of Rows Updated , No of Rows Deleted.
So I can be able to identify the no of rows inserted daily.
Kindly assist how to do this.

Thanks,
Saty.
16 REPLIES
Moderator

Re: Count of Inserted rows

Hi,
When inserted item is chosen, there will be a global variale such as ((Integer)globalMap.get("tOracleRow_1_NB_LINE_INSERTED")) will be avaible, which counts the total number of records have been inserted into database.
Press ctrl+space to access all the global variables
See my screenshot
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Count of Inserted rows

Thanks Sabrina,

I tried to use that parameter in my job. But I'm getting null in my output. Kindly go through my screenshots.

Kindly assist.

Thanks,
Saty.
One Star

Re: Count of Inserted rows

You need to make sure the log process is running after the other processes - use an on subjob ok.
One Star

Re: Count of Inserted rows

You need to make sure the log process is running after the other processes - use an on subjob ok.

Hi Janhess,
Once the main job execution is done and Job status is captured by tAssert components. And then Log Process works.
So it is the last process to run in the job. No need to use on Subjob Ok for Log Process I guess.
Thanks,
Saty.
One Star

Re: Count of Inserted rows

Shouldn't you be using tMySQLOutput1 for the stats?
One Star

Re: Count of Inserted rows

Shouldn't you be using tMySQLOutput1 for the stats?

Hi Janhess,
I'm not clear. Are u asking me whether I'm using these functions using tMySQLOutput component.? If so, Yes. I'm using it to call these functions in tMap ( in Log Process ) component.

Thanks,
Saty.
One Star

Re: Count of Inserted rows

No the tMySQLOutput you reference in your rule is 2. Looking at your picture I thought it should be 1.
One Star

Re: Count of Inserted rows

No the tMySQLOutput you reference in your rule is 2. Looking at your picture I thought it should be 1.

Thanks Janhess. I did not observe that. I've changed to 1 and now I got it. I've got values under
' TOTAL_NO_OF_ROWS ' and ' NO_OF_ROWS_INSERTED '. Because I'm only inserting the data.
I've changed the job as in Pic 1. And I have used two queries in tMySqlRow component. One Update and one Delete query. U can see the properties of tMySqlRow component in Pic 2.
Now I want to capture deleted count and updated count.
I hope I've explained clearly. Kindly assist.
Thanks,
Saty.
Community Manager

Re: Count of Inserted rows

Hi Saty
There is no global variable like (Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED") available if you use tMysqlRow to execute the statements, if you want to count the number of rows are deleted or updated, you have to use tMysqlOutput to do it. To do:
1. Query the records from table with tMysqlInput, and link it to a tMap.
2. On tMap, define two output table, one for deleting, and another one for updating, set the filter expression of deleting output as:
row1.id<2000
set the fitler expression of updating output as:
row1.id>5000
3. Link each output to a tMysqlOutput component and select corresponding action 'delete'/'update'.
The job design looks like:
tMysqlInput--main--tMap--out1--tMysqlOutput_1(for deleting)
--out2--tMysqlOutput_2(for updating)
Then, you can use the global variables on other subjob.
(Integer)globalMap.get("tMysqlOutput_1_NB_LINE_DELETED")
(Integer)globalMap.get("tMysqlOutput_2_NB_LINE_UPDATED")
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Count of Inserted rows

There is no global variable like (Integer)globalMap.get("tMysqlOutput_1_NB_LINE_UPDATED") available if you use tMysqlRow to execute the statements, if you want to count the number of rows are deleted or updated, you have to use tMysqlOutput to do it.

Thanks Shong.Your reply is very helpful. I've understood the usage of these variables.
One Star

Re: Count of Inserted rows

hi,
is it possible to use function inet_aton before inserting data into a mysql database on a col ,if possible can anyone show how to use it and the component used.................
Moderator

Re: Count of Inserted rows

Hi banu,
is it possible to use function inet_aton before inserting data into a mysql database on a col ,if possible can anyone show how to use it and the component used.................

Could you please elaborate your case with an example with input and expected output values, which helps us to understand your requirement much better.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Count of Inserted rows

Hi ,
the data file contains ip address like 10.58.25.26 in a column ,in the table for this column i have given the data type as integer . The inet_aton function normally converts ip address to integer value based on some logic .
Since i want the address to be converted into integer i want to know which component can be used to perform this operation while inserting into database table... is it possible
Community Manager

Re: Count of Inserted rows

hi,
is it possible to use function inet_aton before inserting data into a mysql database on a col ,if possible can anyone show how to use it and the component used.................

Hi
inet_aton is a function of Mysql database, you can't call a SQL function in a Java expression, as a workaround, use a tMysqlRow to execute a query for inserting like this:
"insert into person values ("+row1.id+", INET_ATON('"+row1.ip+"'))"

Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Count of Inserted rows

Hi,

Thank u ... and il check on user routines ....
One Star

Re: Count of Inserted rows

Hi id4dsc,
Thank you for ur post.
i got my query resolved with the help of ur post.
Thanks and Regards,
Anand Yadav