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: 20
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: 20
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: 1890
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: 1890
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: 371
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

Theo Neeskens, Uniface Consultant and general problem solver at ITBLOCKZ
09 Apr 2018
4:23 pm
Avatar
ulrich-merkel
Frankfurt/Germany
Member
Forum Posts: 1890
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”

11 Jul 2018
12:23 pm
Avatar
spaceg
New Member
Forum Posts: 1
Member Since:
11 Jul 2018
sp_UserOfflineSmall Offline

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!

gmail sign up a new account
Forum Timezone: Europe/Amsterdam

Most Users Ever Online: 131

Currently Online:
31 Guest(s)

Currently Browsing this Page:
1 Guest(s)

Top Posters:

ulrich-merkel: 1890

Iain Sharp: 680

Theo Neeskens: 371

gianni: 357

Adrian Gosbell: 318

istiller: 299

rogerw: 272

Knut: 229

lalitpct: 197

Arjen van Vliet: 184

Member Stats:

Guest Posters: 3

Members: 10982

Moderators: 0

Admins: 6

Forum Stats:

Groups: 1

Forums: 62

Topics: 2323

Posts: 10052

Newest Members:

greentipodent1976, NicolasHib, gipipufen1981, Alfredvax, Lesterpioto, crystalst3, otogsa12, Ramonmaw, harolddz1, Lariskawo

Administrators: admin: 23, diseli: 1045, Nico Peereboom: 84, richiet: 406, Mike Taylor: 38, JanCees: 39