Easily MODIFY database key field

Author: jroberson@canberra.com (Joanne Roberson)

I want to modify a key field in a table. I tried release/e/mod and release/e and it INSERTS new rows, but keeps the old rows.  I have tried just modifying a key field and it looks great but doesn't actually store it with the new key. Is there an EASY way in Uniface to UPDATE a key field (i.e. just change the occurrence without having to create a new one and remove the old one)

6 Comments

  1. BTW - the update of the key field takes place in the background (not by an end user). This is what I finally came up with if (bIsKey)    putlistitems/occ sHoldOcc, "%%iTable%%%"    remocc "%%iTable%%%", 0    creocc "%%iTable%%%", 0    getlistitems/occ sHoldOcc, "%%iTable%%%" endif


    Author: Joanne Roberson (jroberson@canberra.com)
  2. Hi Joanne, when you are not depending on uniface foreign key transport, an option with minimal traffic on the database is to use the SQL command with a proprietary SQL update. As the SQL runs in it's own transaction, you have to commit using SQL as well: SQL "Update MYTABLE SET PK1='%%newkey%%%' WHERE PK1 = '%%oldkey%%%' ","DBpath" SQL "cOmmit Work","DBpath" Above text is only to give an impression what the code may look like. The mixed case spelling of the commit clause came from past experience where the database driver (or was it the SQL workbench??) hasn't executed plain commit. After this SQL update is done, discard the old record and retrieve again with the new key. Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  3. And at least in Uniface 10.2 development environment, you can directly change even primary keys. So it looks like the old "PKs are never changeable in Uniface" has disappeared.


    Author: ulrich-merkel (ulrichmerkel@web.de)
  4. Some databases do not allow changing the value of a PK field, and we have to support a lot of different databases. In the development environment we do not really change the primary key of records. "Under the hood" it is a creocc and a remocc. So I think Joanne proposes a valid soloution.   Regards, Theo Neeskens Solution Architect Uniface BV


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  5. For relational databases, there is no "read only" constraint on updating fields belonging to primary keys. As long as there is no NULL value and the komplete key is unique, everything is allowed. But we do not have to move all the data around, delete a record, create a new one, find new locations on the harddrive etc, if all we get at the very end is a change in the value of PK1 from "A" to "B". Just one thing to mention if Uniface has established "overflow" tables. We have to update the PK of the overflow table (tablename prefixed by "O") as well so we have 3 lines of SQL commands: SQL “Update MYTABLE SET PK1=’%%newkey%%%’ WHERE PK1 = ‘%%oldkey%%%’ “,”DBpath” SQL “Update OMYTABLE SET PK1=’%%newkey%%%’ WHERE PK1 = ‘%%oldkey%%%’ “,”DBpath” SQL “cOmmit Work”,”DBpath”


    Author: ulrich-merkel (ulrichmerkel@web.de)
  6. ulrich-merkel said Hi Joanne, when you are not depending on uniface foreign key transport, an option with minimal traffic on the database is to use the SQL command with a proprietary SQL update. As the SQL runs in it's own transaction, you have to commit using SQL as well: SQL "Update MYTABLE SET PK1='%%newkey%%%' WHERE PK1 = '%%oldkey%%%' ","DBpath" SQL "cOmmit Work","DBpath" Above text is only to give an impression what the code may look like. The mixed case spelling of the commit clause came from past experience where the database driver (or was it the SQL workbench??) hasn't executed plain commit. After this SQL update is done, discard the old record and retrieve again with the new key. Success, Uli  

    Thanks. It works!


    Author: spaceg (steven08012017@gmail.com)