Aug 20th, 2007, 04:25 AM
stored procedure - results are not commited
I have a MySql stored procedure which I am running from spring by use of a class that extends StoredProcedure.
The stored procedure is writing and/or updating two tables but the updates are lost once the stored procedure ends. When run directly through the DB the procedure works fine.
After a lot of testing we found out that the changes were not being committed. If we add 'commit' at the end of the procedure the problem is resolved.
Another possible solution was to change the datasource given to the StoredProcedure implementation to be 'autocommit'=true, however this influences other places in the application as well.
Does anyone have any other ideas? Is there a correct way to work with stored procedures to assure their work is committed or are we supposed to commit implicitly once the stored procedure ends?
Thanks in advance,
Aug 20th, 2007, 05:27 AM
You need to specify transaction boundaries around your code. Have a look at the reference section describing how Spring can support you here. I recommend using the declarative transaction support.