[esb-java-user] dbreport question
harm.verhagen at gmail.com
Thu Oct 2 00:31:54 PDT 2008
I am seeing a similar problem when using mysql as db backend.
*How to get the ID of a NEWLY INSERTED ROW ?*
- I insert a new fow in a table with the dbreport module. It gets a new
id. (it is an AUTO_INCREMENT variable).
- further in my sequence, and in the 'out' sequence I need to update that
row in the table, using the id.
I couldn't find a clean (and threadsafe!!!) way to get the newly created id
The wso2 samples don't address this problem.
mysql provides a function LAST_INSERT_ID() to get the newly inserted ID.
However that can only be used in the *_same_* transaction.
So I can use LAST_INSERT_ID() in the _same_ dbreport module (as 2nd or 3rd
sql statement). However dbreport mediator CANNOT save this to a variable.
(only dblookup can).
A following dblookup mediator can however NOT use LAST_INSERT_ID() (it
always returns zero, as this is a new db transaction).
dblookup could do some MAX like magic... but thatis not threadsafe.
I found a (but-ugly) workaround:
sql1: insert into ..... (store my new row... creates a new
sql2: insert into transaction_uuid(id, uuid)
get-property('MessageID') (store the newly created ID in a
new temp table, using the uuid of the message)
dblookup: select id from transaction_uuid where uuid = ?
result: transaction_id (
read the newly created id from the temp table, store it in a variable).
dbreport: delete from transaction_uuid where id = ?
param: get-property('transaction_id') (delete
the temp table).
More information about the Esb-java-user