topic Re: Anyone had a problem when trying to update 0 rows in a tVerticaOutput? in Talend Studio

Anyone had a problem when trying to update 0 rows in a tVerticaOutput?

soowork — Wed, 27 Nov 2013 15:44:09 GMT

I have a workflow this is AOK when it has updates to write, but if it has 0 rows to update, then it fails with an "Unexpected message type: ReadyForQuery." error. Has anyone encountered that and know how I can get around it?
I am writing to a Vertica output, using Vertica4.

Starting job j_load_earn_mall_collinson at 10:25 27/11/2013.
connecting to socket on port 3594
connected
2013-11-27 10:25:28 BATCH_KEY="134", ETL_JOB_NM="j_load_earn_mall_collinson", EVNT_DT="2013-11-27 10:25:27", ETL_JOB_VER_NUM="0.1", JOB_STAT_CD="begin", JOB_STAT_DESC="", PROC_TM=""
Exception in component tVerticaOutput_1
java.sql.SQLException: (100023) Unexpected message type: ReadyForQuery.
at com.vertica.io.ProtocolStream.readExpectedMessage(Unknown Source)
at com.vertica.dataengine.VDataEngine.prepareImpl(Unknown Source)
at com.vertica.dataengine.VDataEngine.prepare(Unknown Source)
at com.vertica.dataengine.VDataEngine.prepare(Unknown Source)
at com.vertica.jdbc.SPreparedStatement.<init>(Unknown Source)
at com.vertica.jdbc.VerticaPreparedStatementImpl.<init>(Unknown Source)
at com.vertica.jdbc.VJDBCObjectFactory.createPreparedStatement(Unknown Source)
at com.vertica.jdbc.SConnection.prepareStatement(Unknown Source)
at pdwserver.j_load_earn_mall_collinson_0_1.j_load_earn_mall_collinson.tHashInput_2Process(j_load_earn_mall_collinson.java:21000)
at pdwserver.j_load_earn_mall_collinson_0_1.j_load_earn_mall_collinson.tJava_3Process(j_load_earn_mall_collinson.java:21125)
at pdwserver.j_load_earn_mall_collinson_0_1.j_load_earn_mall_collinson.tJava_4Process(j_load_earn_mall_collinson.java:11117)
at pdwserver.j_load_earn_mall_collinson_0_1.j_load_earn_mall_collinson.tOracleInput_1Process(j_load_earn_mall_collinson.java:11019)
at pdwserver.j_load_earn_mall_collinson_0_1.j_load_earn_mall_collinson.tVerticaRow_1Process(j_load_earn_mall_collinson.java:8529)
at pdwserver.j_load_earn_mall_collinson_0_1.j_load_earn_mall_collinson.runJobInTOS(j_load_earn_mall_collinson.java:22239)
Caused by: com.vertica.support.exceptions.GeneralException: (100023) Unexpected message type: ReadyForQuery.
... 14 more

Re: Anyone had a problem when trying to update 0 rows in a tVerticaOutput?

soowork — Thu, 28 Nov 2013 14:28:57 GMT

In case it is useful to anyone, I worked around this issue by stashing the results of the flow (which will be 0 or more rows) in a tHashOutput. Then I have a second subjob, joined by an If trigger, and I grab the values back from the hash (using a tHashInput) and write the non-zero updates to my Vertica table.
I put this sort of code in the If trigger -
(Integer)globalMap.get("tHashOutput_2_NB_LINE") > 0
This works, but it is inelegant. Feels like there must be a better way.
Note that if I have 0 rows to *insert*, that works fine. The error only comes up when trying to update 0 rows.

Re: Anyone had a problem when trying to update 0 rows in a tVerticaOutput?

soowork — Tue, 11 Mar 2014 01:52:43 GMT

Does Talend, or anyone else, have a better way around this error?? I have encountered the same issue on a different project, and really don't want to use the workaround I found last time.

Re: Anyone had a problem when trying to update 0 rows in a tVerticaOutput?

Anonymous — Tue, 11 Mar 2014 02:56:45 GMT

Hi,
Would you mind giving us a whole job design and job requirement so that we can see if there is an optimistic workflow for your job design.
Best regards
Sabrina

Re: Anyone had a problem when trying to update 0 rows in a tVerticaOutput?

soowork — Tue, 11 Mar 2014 15:47:56 GMT

Hi Sabrina,
Are you able to use tVerticaOutput with an action of either Update or Delete and pass in 0 rows at runtime? When I do that, I get the error described.
I would expect Talend to just recognize that there are no Updates (and/or no Deletes) to do and finish gracefully. Note that if I am do an Insert using tVerticaOutput and I pass it 0 rows, it happily and gracefully handles that.
There are lots of cases where I find that I want to split my flow and do something different for Inserts than for Updates or Deletes. I don't really want a workaround - I have one already by hand-crafting SQL using tVerticaRow or by using the Hash components as described in my post. I just want tVerticaOutput to work.
I simply want to understand why Talend it crashes when doing a tVerticaOutput with either Update or Delete and it happens to have 0 rows flowing in.
Can you assist? I would be so grateful.

Re: Anyone had a problem when trying to update 0 rows in a tVerticaOutput?

Anonymous — Wed, 12 Mar 2014 07:03:31 GMT

Hi,
Please open a jira issue of DI project on Talend Bug Tracker, our developer will check it.
Best regards
Sabrina