Seven Stars

[resolved] How to handle zero record output from tMysqlInput

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: [resolved] How to handle zero record output from tMysqlInput

No Sabrina.... no progress on the issue... we decided to insert a dummy id in the database table to avoid the "null" record error.
10 REPLIES
Moderator

Re: [resolved] How to handle zero record output from tMysqlInput

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: [resolved] How to handle zero record output from tMysqlInput

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.
Four Stars

Re: [resolved] How to handle zero record output from tMysqlInput

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

Re: [resolved] How to handle zero record output from tMysqlInput

@Vaibhav: Can you elaborate please?
Seventeen Stars

Re: [resolved] How to handle zero record output from tMysqlInput

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 Smiley Wink
regards
laurent
Seven Stars

Re: [resolved] How to handle zero record output from tMysqlInput

@Laurent: the SQL syntax is correct. Smiley Happy
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.
Seventeen Stars

Re: [resolved] How to handle zero record output from tMysqlInput

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

Re: [resolved] How to handle zero record output from tMysqlInput

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
Moderator

Re: [resolved] How to handle zero record output from tMysqlInput

Hi ketan.chachad,
Is there any update for your issue?
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.
Seven Stars

Re: [resolved] How to handle zero record output from tMysqlInput

No Sabrina.... no progress on the issue... we decided to insert a dummy id in the database table to avoid the "null" record error.