In my solution, creating a new record often involves several steps, and much of the data requires validation before the new record can be finalized. To achieve this, I would start a transaction, create a new record and load it into a formInDialog and guide the user through the steps. The transaction is committed when the user clicks a ‘FINISH’ button and everything is validated or rolled back when the user clicks a “CANCEL” button. I then discovered that starting a transaction in MS SQL locks all tables for all Servoy clients, essentially freezing the solution while the transaction is active. I am also unable to user Servoy’s autosave function, as many new records require child records. Since turning autosave off prevents Servoy from retrieving the primary key value that is set by the database, there is no way to populate a child record’s foreign key.
How have other developers solved this problem? These are my requirements:
- Ensure that data for a new record is present and/or valid
- Allow user to cancel new record creation process, removing the unneeded record(s) from the database
- Allow child records of the new record to be created while the new record is still uncommitted.
- Assure that no other client can create a new record with the same primary key.
Servoy Developer
Version 3.5.5-build 518
Java version 1.5.0_13-121 (Mac OS X)
Any advice is appreciated.
Thanks,
Steve in LA
A transaction is the easiest way to accomplish this. Unfortunately, MS SQL has a very rigorous way of ensuring integrity, simply by locking the whole fetch. Since we use a lot of transactions, we have moved away from SQL Server for that reason.
I think you can change the default behavior of MS SQL. You need to do some research on that. Also, it might help that the foundset on which you are starting the transaction is small…
What you could do is set the auto save of before you make the record:
databaseManager.setAutoSave(false)
Then make the record let the user do stuff ect.
Then validate, if it went wel do a:
databaseManager.saveData()
If the user wants to cancel:
databaseManager.rollbackEditedRecords()
This way there is no transaction needed.
Since my primary key sequences are set using dbIdentity, databaseManager.setAutoSave(false) won’t work for me, as it does not allow me to create related records. In this case, what is a good alternative to MS SQL? I have looked into changing MS SQL’s behavior and had no luck so far.
Steve in LA
I can say that Oracle does that very well.
SteveInLA:
Since my primary key sequences are set using dbIdentity, databaseManager.setAutoSave(false) won’t work for me, as it does not allow me to create related records.
I noticed that same and somehow it is logical.
DBIdentity ON within SQL-Server determines the ID when record is written to database. When you want to create related records (also when using table events), you can’t use DBIdentity.
I faced the same problem. I changed all my sequencenumbers to Servoy Sequences. Servoy gives an ID when doing newRecord() en not on saveData(). So after the newRecord() you have an ID, so you can use this ID within script, also before writing the record(s) to the database.
The rollback mechanism works well (just a small thing to fix by Servoy, see Rollback doesn't work as before since version 3.5.4/3.5.5 - Classic Servoy - Servoy Community, but using rollback AND rollbackEditedRecords() on the correct moments, will keep your database consistent.
But keep in mind that the Servoy Sequence numbers can skip numbers, so don’t rely on sequence numbers without gaps. If you need sequencenumbers without gaps, you need a counter in a table which is updated during your transaction
I am unable to use Servoy for my sequence numbers, as I have other non-Servoy solutions that are creating records in the same tables.
Agree with Patrick, with Oracle it works fine.
PostgreSQL is another RDBMS that works without (locking) problems when using database transactions.
DB Idents should work fine with creating related records, we create a place holder for that value and when you save the parent and its childs we first save the parent and replace the place holder with the parent generated pk and then insert the parents. This all happens in one time on the server.