I have been doing some testing on the best way to populate a column with data upon record creation. I will be creating a Servoy solution that uses tables that have new records created from outside Servoy. My concern is that there are columns where I want to be sure a value is entered upon record creation no matter how the record is created. Here is what I am noticing:
If a column is set to have a custom value auto-entered in Servoy and nothing set as an Initial Value in SQL and Servoy creates a new record, the column is populated with the Servoy auto-enter value.
If a column is set to have a custom value auto-entered in Servoy and nothing set as an Initial Value in SQL and SQL creates a new record, the column is populated with a null value.
If a column is set to have an Initial Value in SQL and ‘None’ set in the auto-enter options in Servoy and Servoy creates a new record, the column is populated with a null value.
If a column is set to have an Initial Value in SQL and ‘None’ set in the auto-enter options in Servoy and SQL creates a new record, the column is populated with the SQL Initial Value.
If a column is set to have a custom value auto-entered in Servoy and a different value set as an Initial Value in SQL, the value that the column is populated with is determined by who created the record (if Servoy, column gets Servoy value, if SQL, column gets SQL value)
If all this is intiention behavior, does that mean that in order to be assured that a column gets the same value auto-entered if records will be created in the tables from both inside and outside Servoy, I will need to specify BOTH a Servoy auto-enter and a SQL Initial Value? Also, if I set the values to be different in Servoy and SQL, does that mean I could create a column called ‘Source’ that auto-enters the string ‘Servoy’ if the record was created in Servoy and the string ‘SQL’ if it is created in SQL?
All my PK’s are managed buy Servoy. I have not done ant testing butI would assume that if you set your backend up to create sequences, then set Servoy up:
Value = Database Managed
Sequence = Database Identity
Either end can create and see the correct sequential record. I may decide to manage my PK’s through the backend, so I was hoping it would be easy to change if I decide to. I would expect the above scenrio to be the behavior.
Erich
I am not referrring to pk fields. Those I always let SQL do and set them to db identity in Servoy. The pk is sequencial, although Servoy doesn’t display it in a new record until a savedata event happens.
I am talking about fields other than a primary key where I want a value to be set automatically when a record is created.
If a column is set to have an Initial Value in SQL and ‘None’ set in the auto-enter options in Servoy and Servoy creates a new record, the column is populated with a null value.
This is the only case that might be worth discussing (but I don’t think it is). This means, that Servoy explicitly sets a NULL value to that column. It could also simply omit that column from the VALUES part of the statement. Then I suppose, the database would populate it.
In general I would say that auto enter values inside the database are not best practice. What happens, for example, if you migrate to another database? If you need this because data is inserted using SQL, I would rather complete that SQL statement with those values that you want auto entered.
patrick:
If a column is set to have an Initial Value in SQL and ‘None’ set in the auto-enter options in Servoy and Servoy creates a new record, the column is populated with a null value.
This is the only case that might be worth discussing (but I don’t think it is). This means, that Servoy explicitly sets a NULL value to that column. It could also simply omit that column from the VALUES part of the statement. Then I suppose, the database would populate it.
In general I would say that auto enter values inside the database are not best practice. What happens, for example, if you migrate to another database? If you need this because data is inserted using SQL, I would rather complete that SQL statement with those values that you want auto entered.
I would think there might be scenarios in which one would indeed wish an autoentered value to be specified in the back-end db, such as in the case where you wish a universally true business rule to be enforced regardless of what front-end creates the record. In this case, apart from PKs, how to handle in Servoy?
kazar
Hi,
I’m using PostgreSQL as backend for eveything. Have experimented with using ON INSERT/ON UPDATE triggers to keep data consistent within the database.
The only issue I have come across is that you have to refresh the record from the database each time the record is updated, otherwise Servoy will not know about the changes made by the triggers.
I was hoping Servoy will add a ‘database managed field’… to get around this…
Christian