Easily MODIFY database key field | Uniface Development | Forum

Avatar

Please consider registering
guest

sp_LogInOut Log In sp_Registration Register

Register | Lost password?
Advanced Search

—  Results per page  —








— Match —





— Forum Options —





Minimum search word length is 3 characters - maximum search word length is 84 characters
For a group of consecutive words like 'end of support' use Match phrase

sp_Feed Topic RSS sp_TopicIcon
Easily MODIFY database key field
30 Mar 2018
6:41 pm
Avatar
Joanne Roberson
Member
Forum Posts: 15
Member Since:
24 Jan 2017
sp_UserOfflineSmall Offline

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)

30 Mar 2018
7:05 pm
Avatar
Joanne Roberson
Member
Forum Posts: 15
Member Since:
24 Jan 2017
sp_UserOfflineSmall Offline

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

31 Mar 2018
9:15 am
Avatar
ulrich-merkel
Frankfurt/Germany
Member
Forum Posts: 1756
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

02 Apr 2018
11:44 am
Avatar
ulrich-merkel
Frankfurt/Germany
Member
Forum Posts: 1756
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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.

09 Apr 2018
11:32 am
Avatar
Theo Neeskens
Member
Forum Posts: 353
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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

09 Apr 2018
4:23 pm
Avatar
ulrich-merkel
Frankfurt/Germany
Member
Forum Posts: 1756
Member Since:
01 Oct 2012
sp_UserOfflineSmall Offline

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”

Forum Timezone: Europe/Amsterdam

Most Users Ever Online: 131

Currently Online: Arjen van Vliet
11 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

ulrich-merkel: 1756

Iain Sharp: 644

Theo Neeskens: 353

gianni: 297

rogerw: 249

istiller: 233

lalitpct: 197

Knut: 190

Arjen van Vliet: 175

sochaz: 173

Member Stats:

Guest Posters: 3

Members: 8180

Moderators: 0

Admins: 8

Forum Stats:

Groups: 1

Forums: 62

Topics: 2147

Posts: 9289

Newest Members:

Coironquife, acshvwtrf, Agalasquife, HujloS, Brajindquife, kathrinezy1, Balakquife, StevenZoosy, MiguelBeade, Jamesaccop

Administrators: admin: 23, Adrian Gosbell: 302, diseli: 927, Bob Maier: 3, Nico Peereboom: 74, Michael Rabone: 4, richiet: 406, JanCees: 28