[resolved] How to handle zero record output from tMysqlInput

[Anonymous]

Hi,
I have a job that is set to truncate records from a set of tables based on a specific id.
The first time this job is run, the table that contains the specific id is empty.
In other words there is no specific id against which data from the other tables is to be deleted.
The job flow is as follows:
tMysqlConnection_1--OnSubjobOk-->tMysqlInput_1--row1(Main)-->tFlowToIterate_1--Iterate-->tMysqlInput2--row2(Main)-->tFlowToIterate_2--Iterate-->tSQLTemplate_1--OnComponentOk-->tSQLTemplateCommit_1--OnSubjobOk-->tMysqlRow_1
In this case, I get the following error code:
Exception in component tSQLTemplate_1
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null WHERE run_id=null' at line 1
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1193)
at crm_staging.truncatestaging2_0_1.TruncateStaging2.tSQLTemplate_1Process(TruncateStaging2.java:1438)
at crm_staging.truncatestaging2_0_1.TruncateStaging2.runJobInTOS(TruncateStaging2.java:1962)
at crm_staging.truncatestaging2_0_1.TruncateStaging2.main(TruncateStaging2.java:1789)
Is there a way to check if the first tMysqlInput has returned any records?
And if there are no records simply terminate the job.
Also when I add tMysqlClose to the end of the job, it fails at tSQLTemplate stating that the recordset is already closed? Is it because I am using 2 tMysqlInput?

[Anonymous]

Hi,
If I understand your requirement well, you can use "Run if" trigger in your work flow.
TalendHelpCenter:Connection types.
Please see my screenshots for details. Feel free to let me know if I miss or misunderstand something.
Best regards
Sabrina

[Anonymous]

Thanks Sabrina for this solution.
But unfortunately it does not work.
It still gives an error at the tSQLTemplate_1 component if there are no records in the ID database table.
Also if there are multiple IDs, then the iteration is executed only for the last ID retrieved from the first tMysqlInput component.

[Anonymous]

Hi ketan,
Have you checked COALESCE function for your use?
Vaibhav

[Anonymous]

@Vaibhav: Can you elaborate please?

[Anonymous]

hi all,

check the manual that corresponds to your MySQL server version for the right syntax to use near 'null WHERE run_id=null' at line 1

first you have to correct sql syntax
regards
laurent

[Anonymous]

@Laurent: the SQL syntax is correct.
The "null" is coming from the tMysqlInput components in the job as there no records retrieved from the database table.
I want the job to stop if there are no records to be processed.

[Anonymous]

ohhh sorry
count number of row Before with a tMysqlRow and use if trigger depending if result = 0 or not ...
hope it helps
regards
laurent

[Anonymous]

Hi ketan,
Use coalesce function inside your tmysqlinput, use tjavarow to set the incoming row value to some context variable.
Use if flow from tMySQLOutput and check if the context variable value == 0 if then connect it to tdie...
Or use inside tJavarow to perform null handling like below
context.your_column = Relational.ISNULL(input_row.yourcolumn)?0:input_row.yourcolumn;
Vaibhav

[Anonymous]

Hi ketan.chachad,
Is there any update for your issue?
Best regards
Sabrina