topic Re: Invalid state, the Statement object is closed in Talend Studio

Invalid state, the Statement object is closed

Anonymous — Sat, 16 Nov 2024 09:43:39 GMT

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)

Re: Invalid state, the Statement object is closed

TRF — Fri, 26 May 2017 20:38:08 GMT

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

Re: Invalid state, the Statement object is closed

Anonymous — Mon, 29 May 2017 08:00:56 GMT

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

Re: Invalid state, the Statement object is closed

Anonymous — Mon, 29 May 2017 08:06:38 GMT

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

You can try this, for me its worked!

Re: Invalid state, the Statement object is closed

Anonymous — Mon, 29 May 2017 11:30:25 GMT

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

Re: Invalid state, the Statement object is closed

Anonymous — Mon, 29 May 2017 14:01:54 GMT

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.

Re: Invalid state, the Statement object is closed

Anonymous — Sun, 10 Dec 2017 18:20:04 GMT

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.