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