Logging errors and affected rows from INSERT,UPDATE,DELETE statements.

[jensej]

Hi Community

My goal with the job I need help with is to first add a "title" to a output.csv file. Then in the next step I have a list of database connections that I iterate over and for each of the connection I will run a INSERT query with the tdbrow component for each connection. For each connection I want to append a row to the output.csv file for each connection where I log following. Database connection succesful yes or no. Was there any SQL errors? If yes print errorMessage if no leave empty. If the execution was successful I also want to log the amount of Affected rows. 

In the pictures you can see what I tried so far.

In tJavaFlex_1 I catch if a connection Problem occurs and if that's true I will print that in tLogRow3 (works fine).
The tDBRow_4 is set to not die on error and it will return a resultSet. I always add returning '1' as rows in the end of my Inserts queries. In tLogRow4 I am then able to see the amount of Inserted rows. 
From tDBRow_4 I also have a reject link that should catch SQL Error and their errorMessages. 

To try everything I have created a test where I have 3 connections where connection 1 and 2 are ok but the 3rd  should fail. In this example im running a INSERT INTO custom.test_etl (msg) VALUES('test'), ('jf') returning '1' as rows; query in tdbrow_4. The second connection should cause a SQL Error since the table doesn't exists in that database.

So my wished/expected output should be:
Connection 1 should successfully insert 2 rows and give two lines (rows) with information about the resultSet object and the rows im returning with returning '1' as rows. 
Connection 2 should be rejected an print errorMessage in tLogRow_1
Connection 3 to show an errorMessage in tLogRow_3

This is what it actually returns: 

CONNECTION 1
tLogRow_4

+-------------------------------------------+------+
| resultSet | rows |
+-------------------------------------------+------+
| org.postgresql.jdbc.PgResultSet@50675690 | 1 |
| org.postgresql.jdbc.PgResultSet@50675690 | 1 |
+-------------------------------------------+------+

tLogRow_1

+-----------+------+-----------+-------------+
| resultSet | rows | errorCode | errorMessage |
+-----------+------+-----------+-------------+
| | | | |
+-----------+------+-----------+-------------+

CONNECTION 2

tLogRow_4

+-----------+------+
| resultSet | rows |
+-----------+------+
|  |  |
+-----------+------+

tLogRow_1
+-----------+------+-----------+-------------------------------------------------------------------+
| resultSet | rows | errorCode | errorMessage |
+-----------+------+-----------+-------------------------------------------------------------------+
| null | null | 42P01 | ERROR: relation "custom.test_etl" does not exist Position: 13 - Line: 0 |
+-----------+------+-----------+-------------------------------------------------------------------+

CONNECTION 3
tLogRow_3
+---------------------------------+
| errorMessage |
+---------------------------------+
| The connection attempt failed. |
+---------------------------------+


So first question is why does connection 1 and 2 return a result from both tLogRow_1 and tLogRow_4 when connection 1 wasn't rejected and connection 2 was rejected? I was expecting connection 1 not to trigger the reject path and the same for connection 2 but to not trigger the "success" path. 

Next question: When the first question is solved I have the infos that I need to make a insert in the output.csv file. I could add a tmap after each tlogrow component and then map it to tfileoutputdelimited and append my rows. But I have one big challenge left. As it is now i would insert two rows for the Success story since tLogRow_4 returns two rows. That's not what I want. I want the Total amount of rows to be inserted with only 1 entry in the output.csv. The total I can only access after the subjob is finished with globalMap.get("tParseRecordSet_3_NB_LINE"). I have no idea how to solve this since I have to somehow be able to get this value inside the loop for each connection but still it's not accessable inside the loop...Does someone have a good idea? 

I add some pictures with important information. Thanks!

[Shicong_Hong]

If I understand the problem well, the reject row will not be triggered if the connection is OK, in your case, you mentioned the connection 1 and connection 2 is ok, but the connection 3 is failed. 

About the second question, you can add a tJavaFlex replace of tLogRow (or after tLogRow), define a counter on tJavaFlex, +1 for each row, store the counter to a global variable for used later in next subjob. eg:

in the begin part:

int total_rows=0;

in the main part:

total_rows++;

in the end part:

globalMap.put("key",total_rows);

After all the connections are iterated completed, write the Total amount of rows in the output.csv.

...

listConnections....

|onsubjobok

tFixedFlowInput--main--tFileOutputDelimited