Invalid state, the Statement object is closed

[Anonymous]

Hi,

I am running a job which has a tmssql input and one tmssql output component.

After running for few minitues my job gets failed with below error.

I am using Talend Enterprise BigData 5.6.1.

Checked with Database Team there is no Tunnel down or connection drop happening at that time.

Exception in component tMSSqlInput_1
java.sql.SQLException: Invalid state, the Statement object is closed.
at net.sourceforge.jtds.jdbc.JtdsStatement.checkOpen(JtdsStatement.java:220)
at net.sourceforge.jtds.jdbc.JtdsStatement.getConnection(JtdsStatement.java:1207)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getConnection(JtdsResultSet.java:409)
at net.sourceforge.jtds.jdbc.JtdsResultSet.close(JtdsResultSet.java:470)
at net.sourceforge.jtds.jdbc.JtdsStatement.closeCurrentResultSet(JtdsStatement.java:291)
at net.sourceforge.jtds.jdbc.JtdsStatement.closeAllResultSets(JtdsStatement.java:313)
at net.sourceforge.jtds.jdbc.JtdsStatement.close(JtdsStatement.java:842)
at rge_asp.job_to_load_stg_to_aip_vbfa_0_1.job_to_load_Stg_to_AIP_VBFA.tMSSqlInput_1Process(job_to_load_Stg_to_AIP_VBFA.java:2108)
at rge_asp.job_to_load_stg_to_aip_vbfa_0_1.job_to_load_Stg_to_AIP_VBFA.tMSSqlConnection_2Process(job_to_load_Stg_to_AIP_VBFA.java:1579)
at rge_asp.job_to_load_stg_to_aip_vbfa_0_1.job_to_load_Stg_to_AIP_VBFA.tMSSqlConnection_1Process(job_to_load_Stg_to_AIP_VBFA.java:1378)
at rge_asp.job_to_load_stg_to_aip_vbfa_0_1.job_to_load_Stg_to_AIP_VBFA.tFileInputDelimited_1Process(job_to_load_Stg_to_AIP_VBFA.java:1153)
at rge_asp.job_to_load_stg_to_aip_vbfa_0_1.job_to_load_Stg_to_AIP_VBFA.runJobInTOS(job_to_load_Stg_to_AIP_VBFA.java:2859)
at rge_asp.job_to_load_stg_to_aip_vbfa_0_1.job_to_load_Stg_to_AIP_VBFA.main(job_to_load_Stg_to_AIP_VBFA.java:2662)

[TRF]

Depends of your job design, but you have to know that if you're reading the result set of a Select statement and try to engage a transaction (an update for example), the cursor associated to the result set will be closed.

See this link https://stackoverflow.com/questions/7263240/invalid-state-the-resultset-object-is-closed

As a turnover, try to have 2 separated connections (1 for select, 1 for update).

[Anonymous]

Hi TRF,

Thanks for the reply. 

I am reading from a different database and writing in another. So there are two seperate connections created.

Also there is no update used just read from source and then truncate load in target.

Thanks,

Pramod

[Anonymous]

Try by changing Batch size as 50 or 100 at some point it may work.

You can try this, for me its worked!

[Anonymous]

Hi Sara,

Yes it may work if batch size is reduced but my job pulls about half a million records so it would run for several hours.

currently my batch size is 10000 and it takes around 3 hrs to complete.

Thanks,

Pramod

[Anonymous]

Hi @Moe,

I too faced the same issue and reducing batch size and running is the temporary solution only.

I have also asked for the permanent solution. 

[Anonymous]

I'm experiencing the same issue, but with tMSSqlOutput. I tried to run the job again with no changes in the batch size or any coniguration and it succeeded during the 3rd run.

I don't know what happened, but now I don't trust the job. 

I am also looking for advice or help on this. thank you.