Are there any caveats with auto-incrementing keys in Sybase rather than in Servoy? We have been having problems with records created in different sources (instances of Developer). But I wonder, if we switch over to incrementing in Sybase, do new dangers await us?
Also, we will have much importing of customers’ existing data (after being tweaked in FMP), as well as adding new clients & initial users, and permissions in Security. [Databases are being hosted externally.] How helpful is it likely to be to do this kind of records management in a separate solution (on a separate version of Developer)?
Hoping to leverage group mind on this, as information is not easy (for me) to find (manuals, etc.) Your thoughts, links, etc. are most appreciated.
Best practice for us is to use a backend database managed primary key… for a lot of reasons. One of the key reasons is that database tables may have other solutions/technologies (i.e. a PHP interface) that also create new rows, and thus we do not want the solution to be responsible for assigning PK’s.
I tend to base my decision on what scope the database has.
As Rich says, if the data is used by both LAN and via a web interface (a la PHP) then use DB managed keys as it is the only safe way to control the sequencing which is influenced from more than one direction.
However if there is a single point of access to the db which is via Servoy front end then I just let Servoy take the strain and handle the keys itself
Using DB keys is no problem at all, but you have to be aware of one thing: if you use Servoy sequences, Servoy knows the next ID if you create a new record. If you use DB sequences, Servoy only knows the ID if the record is saved to the database. So what will not work is for example doing a controller.newRecord and immediately afterwards create a new related record. So “best practice” for using DB sequences is
controller.newRecord()
controller.saveData()
... do more stuff
What Patrick writes has me wondering: how are rollbacks handled, with DB keys? For example, if I have a FID wherein a record is created, and other stuff happens (like, a related record is created within it), then the user chooses to cancel rather than commit, what occurs? The record had to have data saved in order to make the related record, but then in canceling (and exiting the FID)… will it go away along with its child record, just leaving a gap in the key sequences? Or does that rollback have to be dealt with programmatically?
In addition to Patrick’s comment: Yes, you will have a gap in your PK Sequence.
That should not be a problem. If you need a continues sequence of numbers (for example invoicenumbers), then you should never use the primary key.
In such a case, you have to handle the setting of the number yourself and only get a number from the sequence when you’re 100% sure the record that needs it will be created (for example when you commit your transaction).
In such a case, your table will have, for example both an invoiceid (PK) and an invoiceno (continues sequence).