Four Stars

Invalid state, the Statement object is closed

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)

5 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Invalid state, the Statement object is closed

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


TRF
Four Stars

Re: Invalid state, the Statement object is closed

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

Seven Stars

Re: Invalid state, the Statement object is closed

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

 

You can try this, for me its worked!

Four Stars

Re: Invalid state, the Statement object is closed

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

Seven Stars

Re: Invalid state, the Statement object is closed

Hi @pramod_kakade,

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.