topic [resolved] How to handle zero record output from tMysqlInput in Talend Studio

[resolved] How to handle zero record output from tMysqlInput

Anonymous — Thu, 26 Jun 2014 09:39:30 GMT

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?

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

Anonymous — Fri, 27 Jun 2014 04:43:21 GMT

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

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

Anonymous — Fri, 27 Jun 2014 10:45:23 GMT

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.

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

Anonymous — Fri, 27 Jun 2014 10:50:32 GMT

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

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

Anonymous — Fri, 27 Jun 2014 10:55:01 GMT

@Vaibhav: Can you elaborate please?

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

Anonymous — Fri, 27 Jun 2014 10:55:55 GMT

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

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

Anonymous — Fri, 27 Jun 2014 11:02:26 GMT

@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.

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

Anonymous — Fri, 27 Jun 2014 11:07:16 GMT

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

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

Anonymous — Fri, 27 Jun 2014 11:49:56 GMT

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

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

Anonymous — Mon, 30 Jun 2014 11:24:13 GMT

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

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

Anonymous — Mon, 30 Jun 2014 11:36:00 GMT

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