Mule ESB Database connector - select after insert

Question

I came across a problem with mule database connector. I have a flow that receives JSON with some data, i take some of that data and use it to insert a record to a database. The database generates it's own ID when inserting record. Now in the same flow I want to select that ID, but sometimes it doesn't return any results. Is it possible that when I'm executing 'select' query right after insert, it's not inserted/hasn't generated ID yet? What can I do to make it work properly? How can I make the 'select' database connector to wait for that data?

Answer 1

When in Insert mode the Database Connector has three properties autoGeneratedKeys and autoGeneratedKeysColumnIndexes or autoGeneratedKeysColumnNames.

As well as the row count in the payload, it returns the auto generated values of the Insert

Mule doc here

Answer 2

I did similar steps using oracle connector and worked fine with stored procedure. The steps would be

a. Call Stored procedure 1
1. Call stored procedure to insert record based on parameters.
2. Commit the transaction.
3. Return 1(success) or 0(fail) based on outcome

b. Call Stored procedure 2 only if you get 1 (success) from Stored procedure 
1. Retrieve id for the record inserted in stored procedure 1

Answer 3

first of all:

Have you checked that the database connector retrieved 1 in the response (or the way your database shows the amount of rows affected)?

If your flow is configured as asynchronous (mule's default) and it has a proper connection timeout, it should have added the information when it goes into the next connector.

Also, did you check if its indeed inserting that row in the db?
And did you check that in fact you are doing the select with the correct ID generated by db?

I recommend you do a step by step debugging in order to check this information.

Hope it helps!

Answer 4

You Should do a "Commit" after inserting a record otherwise the record is not available for Immediate Select .I agree with user3366906 Answer.