One Star

How to retrieve getNextException ?

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
13 REPLIES
Community Manager

Re: How to retrieve getNextException ?

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: How to retrieve getNextException ?

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.
One Star

Re: How to retrieve getNextException ?

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.
One Star

Re: How to retrieve getNextException ?

Are those forums dead?
One Star

Re: How to retrieve getNextException ?

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
One Star

Re: How to retrieve getNextException ?

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
One Star

Re: How to retrieve getNextException ?

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

Re: How to retrieve getNextException ?

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 :-)
Seventeen Stars

Re: How to retrieve getNextException ?

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.
One Star

Re: How to retrieve getNextException ?

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 )
One Star

Re: How to retrieve getNextException ?

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

Re: How to retrieve getNextException ?

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;
}
}
One Star

Re: How to retrieve getNextException ?

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.