Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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