I have a reporting database that is updated using the DB_OUTPUT_TABLE, using an 'Insert Into'. Records are retrieved using a MEMGET and inserted into the table using the above writer.
After the above DB_OUTPUT_TABLE writer job is finished, I would like to update another table with either a 'SUCCESS' or 'FAILURE' flag.
I have tried creating another DB_OUTPUT_TABLE job that contains the query 'UPDATE STATUS_TABLE
set STATUS = 'SUCCESS';' however, it doesn't write once the job is finished.
I am using the same metadata connector to the second DBOutputTable, not sure if that is the problem.
See attached image for a screenshot of the job. The highlighted writer is the newly added one that should be updating the status flag in STATUS_TABLE.
Thanks in advance.
After the above DB_OUTPUT_TABLE writer job is finished, I would like to update another table with either a 'SUCCESS' or 'FAILURE' flag.
I have tried creating another DB_OUTPUT_TABLE job that contains the query 'UPDATE STATUS_TABLE
set STATUS = 'SUCCESS';' however, it doesn't write once the job is finished.
I am using the same metadata connector to the second DBOutputTable, not sure if that is the problem.
See attached image for a screenshot of the job. The highlighted writer is the newly added one that should be updating the status flag in STATUS_TABLE.
Thanks in advance.
-
Hi listsos,
The output port you are utilizing to set the flag is only returns rejected records, which works well for the Failure Flag. Now to set the Success flag, I would recommend using the second output port and set a returning statement to output a record inserted. It's also imported to note that the second out will return a value regardless if the record was added. To resolve this, I would recommend filtering any null values from the return record.
exampleDB.PNG -
Thanks for your reply!
I have moved the SUCCESS job the Port 1. I don't have any metadata selected for the connection as the SUCCESS job will simply be running "UPDATE STATUS_TABLE set STATUS = 'SUCCESS" presuming the graph completes successfully.
When running the job, it now errors stating "No metadata and no metadata stub defined for edge". What should be included in the Metadata, no data is really being passed along to the second database write. -
Hello Listsos,
the DBOutputTable is capable of returning records that were successfully inserted into a database, as well as returning those that failed. You could take advantage of one of these features to achieve the desired result. Attached is an example solution of how this can be approached. The main idea of the graph is that the query (which is updating the status table) is stored in a dictionary as a single string value and is set to "UPDATE STATUS_TABLE set STATUS = 'SUCCESS'" by default. If no record fails in the process of inserting data into the database, the DBOutputTable component will not send any records on the port 0, thus not affecting the query in the dictionary. On the contrary, if there was even a single record that failed during the inserting process, it will result in changing the dictionary value to "UPDATE STATUS_TABLE set STATUS = 'FAIL'". In the next phase, there is a DBExecute component that will run the query in the dictionary and update the status table respectively.
Kind regards,
Please sign in to leave a comment.
Comments 4