topic Re: How to retrieve getNextException ? in Talend Studio

How to retrieve getNextException ?

_AnonymousUser — Sat, 16 Nov 2024 12:51:12 GMT

During our integration process, we are inserting data into a postgresql table (tPostgreSQLOutput)
If, for any reason (violated FK, null inserted in a not nullable field, etc ...), the insert fails, we are shown a message telling us to call "getNextException",
How can we actually call this method ? (I don't mind having to modify the javanet files of the component if I have to). As it stands, we have no method at all to tell what is the error that is crashing the insert, and this is cripling our developpement.
Thank you

Re: How to retrieve getNextException ?

Anonymous — Tue, 21 Jun 2011 03:50:48 GMT

Hi
Can you show us the complete error message? What you need to to is capture the exception and handle error, not to call the method. In Talend, there is a component called tLogCatcher can be used to capture exception and log them into file or database.
Best regards
Shong

Re: How to retrieve getNextException ?

_AnonymousUser — Tue, 21 Jun 2011 08:50:39 GMT

Hi shong, thank you for your answer.
I'm afraid the message I posted*is* the excpetion trace. here is the full thing from system.err :

Exception in component tPostgresqlOutput_1
java.sql.BatchUpdateException: L'élément du batch 0 INSERT INTO "alim_ods"."ass_ods_copy_appel" ("columns") VALUES (values) a été annulé. Appeler getNextException pour en connaître la cause.
	at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2537)
	at org.postgresql.core.v3.QueryExecutorImpl$ErrorTrackingResultHandler.handleError(QueryExecutorImpl.java:283)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1328)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:332)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2674)
	at dev.testappel_0_1.testappel.tMSSqlInput_1Process(testappel.java:4622)
	at dev.testappel_0_1.testappel.runJobInTOS(testappel.java:4852)
	at dev.testappel_0_1.testappel.main(testappel.java:4726)

And here is the message catched by the tLogCatcher :

java.sql.BatchUpdateException:L'élément du batch 0 INSERT INTO "alim_ods"."ass_ods_copy_appel" ("columns") VALUES (values) a été annulé. Appeler getNextException pour en connaître la cause.

Re: How to retrieve getNextException ?

Anonymous — Wed, 19 Oct 2011 13:52:57 GMT

Facing the same issue, any feedback?
If i try to make an insert with a too long varchar field, to trigger an error, the component actualy shows in error but doesn't trigger the "onComponentError" link, and there is no trace in the log either.
Just that cheesy error message....
L'élément du batch 42 INSERT INTO "public"."myTable" (MyColumns) VALUES (My Values) a été annulé. Appeler getNextException pour en connaître la cause.

Re: How to retrieve getNextException ?

Anonymous — Thu, 20 Oct 2011 15:23:45 GMT

Are those forums dead?

Re: How to retrieve getNextException ?

Anonymous — Thu, 29 Dec 2011 17:26:24 GMT

Same issue here.. Here's the lay of my job
tFileInputDelimited --> tPostgresqlOutput
file:
1, a
2, b
Table:
col a character varying(255) not null,
col b character varying(255) not null
unique constraint on (a, b)
the same (non descript) error message about GetNextException. If I enable DieOnError for the DB component, I get the line of code that is failing, but nothing more from the DB side.
Besides, I don't want it to DIE if one of the inserts fails, just want it to continue to the next insert statement..which seems to be the problem.
ie.. If the first record from the file exists, then the contraint fires but Talend stops on this error. (I know what the error is only from running each statement in pgadmin..)
My expectation (erroneous apparently) is that the job would continue to the next insert statement (which doesn't exist in the table)
It's like Talend is batching the statements rather than sending them individually.. (I know this only because setting the commit to (1) has no affect)..
insert into table
values (1,a), (2,b)
rather than
insert into table
values (1,a)
insert into table
values (2,b)
Searching for an explanation of this error message: "Call getNextException to see the cause." I find several blogs that indicate the Java code behind this message may be lacking detail/sophistication. I'm not sure how to add this level of code to uncover the real message being passed by postgres.. Here's a link to a fairly succinct explanation..
http://www.oreillynet.com/onjava/blog/2006/03/proper_handling_of_databaserel.html
Regards,
Mark

Re: How to retrieve getNextException ?

Anonymous — Thu, 29 Dec 2011 18:45:00 GMT

update...
On the ADVANCED SETTINGS tab for the tPostgresqlOutput component, I disabled USE BATCH SIZE.
This not only allows individual insert (probably any DML), but allows the DB error message to be passed up the stack to STD OUT...
woohoo.. thanks to Derek from Talend Tech Support for schooling me on this...
Mark

Re: How to retrieve getNextException ?

Anonymous — Sat, 29 Sep 2012 12:24:00 GMT

thank you so much msutfin for this last trick ... you saved my day !!

Re: How to retrieve getNextException ?

Anonymous — Wed, 07 Aug 2013 16:03:40 GMT

thanks for the tips. I was disappointed looking for anasty bug but not trace. And now, a very clear error message:
ERROR: numeric field overflow
Détail : A field with precision 8, scale 2 must round to an absolute value less than 10^6.ERROR: current transaction is aborted,
thanks a lot 🙂

Re: How to retrieve getNextException ?

Anonymous — Wed, 07 Aug 2013 20:51:56 GMT

Actually disabling the batch mode is not always the solution. Sometimes it could happens, the problem occurs after a large number of rows and in this case you have to wait a long time.
It is so easy to implement this. In my database related components this is always done and I am still alive 😉
Unfortunately this affects all database output components and thats why we probably could not hope for a quick solution.

Re: How to retrieve getNextException ?

_AnonymousUser — Mon, 06 Jan 2014 06:42:03 GMT

Problem is with the schema , make sure that schema is correct and more over in target datatypes must be higher or equal in their respective source columns, and cross check with not null columns, they should have default values or provide any value in transformation. In my case i have used 0000-00-00 for purchasedate or handle in tmap as if(purchasedate = null , 'default(0000-00-00)' , purchasedate )

Re: How to retrieve getNextException ?

Anonymous — Fri, 18 Apr 2014 15:01:57 GMT

HI all,
I want to load data into a postgresql database but when I execute the job the error message appears
Best regards,

Re: How to retrieve getNextException ?

Anonymous — Fri, 18 Apr 2014 23:43:22 GMT

You could use a workaround:
after your tPostgresqlOutput send the flow to a tJavaFlex component.
--> tPostgresqlOutput --main flow--> tJavaFlex
In the Begin part of tJavaFlex write:

try {

In the End part of the tJavaFlex write:

} catch (Exception e) {
   if (e instanceof java.sql.SQLException) {
       java.sql.SQLException ne = ((java.sql.SQLException) e).getNextException();
       if (ne != null) {
           throw new Exception(e.getMessage() + ", next message:" + ne.getMessage(), e);
       } else {
           throw e;
       }
   } else {
      throw e;
   }
}

Re: How to retrieve getNextException ?

Anonymous — Tue, 14 Jul 2015 09:30:43 GMT

Hi jlolling,
As you have mentioned unchecking batch size is not always the solution, do you suggest anything else for it.
I am loading huge no of records so unchecking batch size is not an option for me.
while running the job in the production If i have to figure out the exact error,can you help me with that.

Re: How to retrieve getNextException ?

asheppardwork — Mon, 09 Mar 2020 18:42:45 GMT

thank you, thank you, thank you, was trying for an hour to find out how to get this stupid message and then found it was a typo in a field name.