I am developing a solution where I think I need to use nested transactions on the database.
For example the below code is possible or not?
databaseManager.startTransaction();
forms.frmBrandDetail.controller.newRecord();
forms.frmBrandDetail.title = "new brand";
//**************************************
databaseManager.startTransaction();
forms.frmLocationDetail.controller.newRecord();
forms.frmLocationDetail.title = "hong kong - island";
databaseManager.rollbackTransaction();
//**************************************
databaseManager.commitTransaction();
In my solution I have a table called brands where all the brand names of the company are stored and another table called locations where the all the store locations are stored. Then I need to link each brands to locations. (ie I need to create the history so that we know what brands available at which locations. Links are stored in brand_to_location table).
To do this the user clicks modify button on the brand form. That starts a transaction & take him to the modify form. From the brand modify form he has to click a button to open a formInDialog that will show the list of available locations. He can click the link button there to link a location or many to the current brand record. Please see the attached pic.
(There are confirm / cancel buttons in the brand modify form that will commit / rollback the transaction)
In the formInDialog I am allowing the users to create/modify location records and they are allowed to click confirm / cancel buttons that will commit / rollback the transactions respectively. This can cause both the locations table in the formInDialog and the brands table in the main form to commit/rollback their changes to the database. While I want only the location table in the formInDialog to commit/rollback. Here is where I need the nested transaction like the example I have shown above. In this case the nested transaction is really important for me.
Is this possible with Servoy & also the databases sysbase, mysql, sql server?
Hameed, I know this is not what you are looking for but I would set a variable at the point you decide where and you are going to commit or rollback (part of the) transaction.
Then, in the end you can use this variable to delete the parts you don’t want/need…
In looking at this and also the other forum link mentioned by Jan, one
thing is not clear to me. If one has a method during the course of which
a parent record is updated and then various child records are created, is
it OK to have just one ‘.startTransaction’ at the beginning of the method
and just one ‘commitTransaction’ at the very end of the method as in:
var success = databaseManager.commitTransaction();
if (!success)
{
plugins.dialogs.showWarningDialog(‘Problem committing data, OK’);
databaseManager.rollbackTransaction();
}
Or should there be/is it better to have a separate ‘startTransaction’ for
the parent record updates and then separate ‘startTransaction’ for the
child records as one loops through them as in:
for ( var i = 1 ; i <= relation.getMaxRecordIndex(); i++ )
{
..startTransaction..
do some child record updates
var success = databaseManager.commitTransaction();
etc.
}
If you want the user to be able to roll back every step separatly, then go for the multiple Transaction variation.
If you want the user to rollback all transactions at once, from any point in the updtae proces, go for the single Transaction variant.
You have to see it like this: From the moment that you start a transaction, all changes afterwards are in the transaction. If you commit the transaction, all changes are irrivokable saved in the DB. If you do a rollback, they are all undone.
So, if you make a Transaction for every step of the update proces, the user will not be able to rollback the entire updata operation, but only the last update.
startTransaction is globally for all database connections that client has, and it lasts till commit or rollback is given.
calling startTransaction a second time before giving a commit or rollback is a NOP (no operation)
Your idea does not work perfectly for me. I would rather cut off the functionality for the time being in my project.
Also I was thinking about using as many global fields for each real field and not use the transcations. But the problem comes with the portal. I could reproduce a global portal which is similar to a grid object in visual basic. Is there any grid beans that can hold my portal values temporarily?. After the form is confirmed I can read the values from the grid and create appropriate detail records (portal records)
Also I do not like to create hundreds of global fields for this pupose.
I would rather use .text property of normal objects (fields, combo, text area etc) if Servoy guys add this (.text) property to all the available objects. Currently they provide this property only for label field.