Is there any mechanism inside Servoy to deal with ‘unique fields’ - that is fields that should contain unique data against all other records in a given table?
I know that I can setup an index on the table in my database externally (MySQL in my case) and make the key ‘unique’, but then when a user makes a record in Servoy that is duplicate they just get a general database error which, even if they read the details, will not understand.
Is this the proper way to do it (with the index) and then to write my own method for ‘on error method’ in the solution, or am I just missing something obvious?
Sounds like a good approach. Another way to do it is to attach a method onDataChange of that field, fire a query to determine if the value is unique and display a message and / or revert the value.
Looks like I might have to do the query myself anyway-- haven’t tested it out yet but reading more in the docs seem to imply that the onError method is only for catching errors at solution-open-time.
I have usually used an index (but sometimes not with the unique state on for the index) and issue a SQL Count with the appropriate Where clause against the table. If the SQL Count returns 1 then I know the record already exists and proceed accordingly. I do not always want the db to throw an index error … and so I do not always use unique indexes.
Yes, and a “however” to my own comments, there is a point here where one wants to decide what level of data integrity needs to be managed via the database (triggers, stored proc’s, index constraints etc and their associated “throw errors back at you” activity) VERSUS managing a portion of the error trapping via the application layer (ie - Servoy passing an SQL Select query down and the solution testing the result). As I have suggested in this situation, I am obviously ignoring the (often recommended) db architecture best practices of trapping at the db layer and suggesting you trap via the application layer.
Practically speaking, while I come from a “let’s trap lots at the db layer” background, I do find a blend of error trapping shared between the 2 layers to be the most practical and workable. In my experience, good systems tend to have “a bit of both” going on and the 2 layers work together cooperatively.