Errorhandling, Transaction, SFTP

["michael.lorenz" <mi...@germanski.net>]

Hello Camel experts,

i'm trying to setup a route for processing files. But I stuck in the details
without any light at the end of the tunnel. The following setup is planned:
/SFTP-Consumer -> rename(start) -> download-file ->
someTrickyFileContentProcessing -> DBstatusInsert -> SFTP-rename(final)/
In case that there happens an exception during the file processing, I want
to write an Error-status message into the database as well as give the
original file an ERROR-extension via SFTP.
Additionally I want to have the whole operation in one transaction
("someTrickyFileContentProcessing" and "DbStatusInsert" do multiple
SQL-updates that should commit or fail as a whole)

*Here are my routes:*

*1. Route:*
from("sftp://user@host/remotefolder/?password=secret&antInclude=*.gz" +
	 "&preMove=${file:onlyname}.READING" +		/// rename file on remote-host
before download to mark file as "inProcess"/
	 "&move=${file:onlyname}.IMPORTED" +		/// rename file on remote-host when
route finished without errors/
	 "&moveFailed=${file:onlyname}.ERROR" + 	/// rename file on remote-host
when route finished WITH errors/
	
"&delay=3000&maximumReconnectAttempts=3&connectTimeout=10000&reconnectDelay=5000&binary=true&disconnect=true&consumer.bridgeErrorHandler=true&throwExceptionOnConnectFailed=true")
.onException(Exception.class)
	.handled(false)				/// when an exception is caught, dont mark it as handled
to let sftp-consumer execute the "moveFailed"/
	.multicast()				/// actually a .to() would be sufficient, but there seems
to be a bug with that -> so I replaced the .to() with an .multicast() (but
this is another story...)/
	
.to("seda:errHandlr","log:errorLog?level=ERROR&showAll=true&multiline=true")
		.end()
	.end()
.transacted("PROPAGATION_REQUIRES_NEW")			/// I want all DB-Operations in
the route to be in one transaction (when my machine crashes: everything or
nothing will be in the DB)/
.routeId("MYROUTE")
.unmarshal().gzip()
.processRef("someTrickyFileContentProcessing")	/// here are a lot of
DB-Calls (INSERT, UPDATE...)/
.processRef("DbStatusInserter")				/	// here are some DB-Calls (INSERT,
UPDATE...)/
.end();

*2. Route:*
from("seda:errHandlr")
.onException(Exception.class)
	.markRollbackOnlyLast()		/// i dont want to rollback the 1. route when
there is an exception in the 2nd one
/	.end()
.transacted("PROPAGATION_REQUIRES_NEW")		/// i want the 2nd route to run in
it's own transaction (in case of an error in the 1st route, the transaction
gets rolled back, but the 2nd route should to an status insert in the DB)/
.routeId("MYERRORROUTE")
.to("DbErrorStatusInserter")		/// here happens the DB insert/
.end();

*Here are my problems:*
In case of an exception in the 1st route the sftp-consumer already renames
(moveFailed) and only then the the 2nd route is called. So i could stuck
with  an inconsistent state if my machine crashed before a commit happens at
the 2nd route (file already *.ERROR but no DB entry with ERROR)
I would like to execute "DbErrorStatusInserter" before the moveFailed-rename
Any chance to accomplish this?
One solution could be to only have one route and to call
"DbErrorStatusInserter" in the "onException" section. But there are the
following drawbacks that make it impossible:
	-> transaction is rolled back and so nothing stayes in the DB. 
	-> if I would tell handled(true) the transaction won't get rolled back. But
the sftp-consumer won't do a moveFailed-rename either
	So, is there any chance to tell to ".transacted()" something like
".rollbackOnlyFor(XyzException.class)" like in the spring transaction
annotation?

*My environment:*
JDK 1.7 SE
Spring 3.2.5-RELEASE
Camel 2.12.2
SFTP via OpenSSH at an Ubuntu-Server-12.04 machine

Any help or hints are greatly appreciated!

Cheers,
Michael



--
View this message in context: http://camel.465427.n5.nabble.com/Errorhandling-Transaction-SFTP-tp5744863.html
Sent from the Camel - Users mailing list archive at Nabble.com.