[esb-java-user] dbreport question

[Harm Verhagen]

Hi,

 I am seeing a similar problem when using  mysql as db backend.
*How to get the ID of a NEWLY INSERTED ROW ?*

*problem:*
 - 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
with wso2.

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.

*workaround*
I found a (but-ugly) workaround:
dbreport:
        sql1:   insert into .....      (store my new row... creates a new
ID).
        sql2:   insert into transaction_uuid(id, uuid)
values(LAST_INSERT_ID(),?)
                       param:
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 = ?
                      param: get-property('MessageID')
                   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).