IIRC, there was once a time when it was necessary to have Primary Keys managed by Servoy - is that no longer the case?
What are the current restrictions and Best Current Practices with respect to Primary Keys and servoy-managed -vs- DB-managed (i.e. SQL auto-increment type PK fields) for an environment where records need to be created/modified/deleted by both Servoy and by non-servoy applications (e.g. perl scripts)?
Thanks,
Neale.
As far as I know, this PK restriction is still in effect with MySQL.
The rules then:
(1) If table is managed (create, update & delete) with Servoy, only searchable and viewable via web.
(2) If table is managed via web, only searchable and viewable in Servoy.
In the case of where information is only added via the web, you can use a duplicate table to capture the web input and then in Servoy, bring the information over to the Servoy managed table. Use this table to display data to the web. Unfortunately, this will not work if you need a real time turn around but it works for things like class registrations.
If this is no longer a restriction with another database vendor, please let me know as well!
Thanks David - that sounds like the restrictions I was recalling.
Nothing personal, but I really hope you’re wrong Restrictions like this can severely cramp the style.
Regards,
Neale.
Database sequences are implemented differently by each database system, which is the main reason why they are not supported in the version of Servoy that ships today. We are in the process of implementing an additional database isolation layer that will enable support of database specific features such as the support of database based sequences. This functionality won’t make it into the upcoming 1.2 release but most certainly in the version after that.
Here’s a workaround for postgres that lets the database handle pk.
It’s very database dependent though, and I’ve been told this doesn’t work for mySQL.
//create a sequence in postgres named “company_id”
//and catch a new sequence in servoy with “select nextval”
//create a new record and overwrite the pk(company_id)
//with the new sequence.
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), “select nextval(‘companyid’)”, null, 1);
controller.newRecord()
company_id = dataset.getValue(1,1);
Maarten,
can this be done in Sybase (IAnywhere) too?
I don’t think so.
Any updates/ETA for this one?
Also, where do these considerations fit in relation to a DB “autoincrement” type field and Servoy’s setting of “managed by database” in dataproviders/column/properties/Auto-Enter/Value?
Thanks,
Neale.
Neale:
Any updates/ETA for this one?Also, where do these considerations fit in relation to a DB “autoincrement” type field and Servoy’s setting of “managed by database” in dataproviders/column/properties/Auto-Enter/Value?
Thanks,
Neale.
PK managed by database is going to be supported in the next version of Servoy. You can expect beta’s to appear in a few weeks.
DB “autoincrement” type field is to have all NON-PK’s filled by a database sequence.
Ah… Ok… I think I get it… a DB “autoincrement” is (and has always been?) supported behind a Servoy “managed by database” - BUT only for non-PKs?
And the “non-PK” restriction should go away “soon”? At which point we can delegate the PK sequence-generation to the underlying DB and Servoy will be happy for records to be created by non-Servoy processes?
Thanks,
Neale.
A column defined as dbmanaged is never updated by Servoy but only retrieved, our development team is currently in the process of building the db sequence support (which is done differently by each db vendor )
Hmmm… now I’m not sure if we are talking about the same thing
I’m not particularly interested in Servoy being able to do the SQL to setup these kind of fields (I’d much rather manually setup the SQL tables exactly how we need them).
If we set up an autoincrement PK field directly in SQL and then configure this PK field in Servoy as “managed by database” will that work OK today? If not, when?
Thanks,
Neale.
Servoy 2.0 will use autoincrement PK field from DB
FYI - In Sybase SQLAnywhere there are a number of built in PK auto increment features. The DBA can create a “Key Pool” that reserves a number of ‘increments’ based on any number of criteria. Using this feature one could reserve a set of PKs for different users, branches, company types, … When an entity exhausts its supply the DBA can have a new set of increments reserved for that entity.
I haven’t used this feature but it was thoroughly explained to me by one of their engineers in a pre-sales conversation. I’m sure the information is available from the online docs at Sybase.
In the 2.0beta4 announcement there is: “[enh]-db dependent layer (hibernate) […]”
Any relevance to this issue?
Thanks,
Neale.
yes, that is relevant to this issue. Hibernate enables us to implement database specific functionality like auto incrementing primary keys. You’ll see support for this added in soon to come beta versions.
Neale,
YEP.
Bob Cusick
Servoy 2.0 release candidates do support the following seqences:
-Servoy seqences
-Database Indentity column (database does seq increment and does autoinsert the value into the column)
-Database seqence (some databases do have seqence generators from which seq. can be retrieved and used as value in a column)
There is also another autoenter option in Servoy called “databaseManaged”, this option specifies to Servoy that a column is filled for example by a database trigger (or something else) and Servoy should never update that column but only retrieve the data.