I have in Oracle a table with a PK that has a trigger that uses a Oracle sequence to fill the PK.
Now I like Servoy to let Oracle to create the PK (like it’s designed to do) but I can’t select the DB Identity in the Sequence combobox in the auto-enter settings.
Do I have to tell Servoy to use the Oracle sequence? What about the trigger ?
what you created is a fake db_ident column yes.
Because oracle doesnt natively support db_idents
But we need natively db_ident support for the database, and the hibernate dialect of that database gives us that information.
We do need it because only then we have a special way to get the inserted pk back to servoy…
for example if a database supports db_idents and we do a insert there is for that database a special query that returns the last inserted pk value (there are different way by doing this thats defined by the database)
But in your case you insert it and we have no way of getting that pk back into servoy, because there is no such call that we can do.
Thats why we only give the option if the database supports it and does have that special call that we can do after an insert.
So the only way is to use directly that sequence that also the trigger uses. And the trigger should just look if the value is set then i do nothing, if it is null then i generate one.
A quick test shows that calling the Oracle sequence works and the standard trigger doesn’t interfere. Thanks!
by ROCLASI » 14 Jan 2011, 05:38
A quick test shows that calling the Oracle sequence works and the standard trigger doesn’t interfere. Thanks!
I agree that this works, but we need to maintain our database agnostic approach. How will this impact non-Oracle databases? If Servoy auto-substitutes “database identity” for other databases, this should work well for us. If not, we have a problem that must be overcome.
If the tables are used outside of servoy (so you can’t use servoy sequences) and you want to support all kind of databases.
Then the only solution that comes up quickly is to use a UUID pk columns…
Let servoy generate uuid’s for you and other programs also do that (or a trigger does that)
so the pk’s are always unique what every program inserts data (servoy or another) and it works over different databases.