I’m using the following code to create a new database record using PSL code.
set pID=Db.nextVal("ZPERSON")
type RecordZPERSON oPerson=Class.new("RecordZPERSON","pID")
set oPerson.Forename="Sean"
set oPerson.Surname="Connelly"
set oPerson.DoB=%SystemDate
do oPerson.save()
Looking at the generated code the nextVal performs a $Order on the globals first key. From what I can see this ID is not reserved by this particular method. If I run the following code
set pID1=Db.nextVal("ZPERSON")
set pID2=Db.nextVal("ZPERSON")
type RecordZPERSON oPerson1=Class.new("RecordZPERSON","pID1")
type RecordZPERSON oPerson2=Class.new("RecordZPERSON","pID2")
set oPerson1.Forename="Sean"
set oPerson1.Surname="Connelly"
set oPerson1.DoB=%SystemDate
set oPerson2.Forename="Debi"
set oPerson2.Surname="Connelly"
set oPerson2.DoB=%SystemDate
do oPerson1.save()
do oPerson2.save()
both pID1 and pID2 grap the same ID value, resulting in object 2 overwriting object 1. Am I using the right method to create these ID’s? Is there a flag on the table that will auto generate an ID at save() time?
Also “agclubb” raised a similar question that went unanswered to do with Key auto generation via SQL. This is also important to me to.
Specifically, I want to create tables that have an auto incrementing primary key called ID. How can I get a SQL insert action to auto update this value without having to pass in an ID value?
Thanks,
Sean.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Db.nextVal does indeed do the equivalent of $O(,-1) + 1, works only against numeric keys, and is designed to give you the next key value (current last key + 1). It does not reserve that value.
Until you save the record, you (or any other process) can use Db.nextVal over and over and it will return the same value (since the database hasn't changed).
Generally, you would put the transaction under TP, i.e., the entire first block of code shown above and commit after the .save(). Any other process also under TP that collides will restart and get the next Db.nextVal.
With regard to autoincrement, try using table.NEXTVAL, for example:
INSERT INTO UTBLBRCD (BRCD,DESC) VALUES (UTBLBRCD.NEXTVAL,'ABC')
You could also consider putting the assignment of the key into a before insert trigger.
Dan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I’m using the following code to create a new database record using PSL code.
set pID=Db.nextVal("ZPERSON")
type RecordZPERSON oPerson=Class.new("RecordZPERSON","pID")
set oPerson.Forename="Sean"
set oPerson.Surname="Connelly"
set oPerson.DoB=%SystemDate
do oPerson.save()
Looking at the generated code the nextVal performs a $Order on the globals first key. From what I can see this ID is not reserved by this particular method. If I run the following code
set pID1=Db.nextVal("ZPERSON")
set pID2=Db.nextVal("ZPERSON")
type RecordZPERSON oPerson1=Class.new("RecordZPERSON","pID1")
type RecordZPERSON oPerson2=Class.new("RecordZPERSON","pID2")
set oPerson1.Forename="Sean"
set oPerson1.Surname="Connelly"
set oPerson1.DoB=%SystemDate
set oPerson2.Forename="Debi"
set oPerson2.Surname="Connelly"
set oPerson2.DoB=%SystemDate
do oPerson1.save()
do oPerson2.save()
both pID1 and pID2 grap the same ID value, resulting in object 2 overwriting object 1. Am I using the right method to create these ID’s? Is there a flag on the table that will auto generate an ID at save() time?
Also “agclubb” raised a similar question that went unanswered to do with Key auto generation via SQL. This is also important to me to.
Specifically, I want to create tables that have an auto incrementing primary key called ID. How can I get a SQL insert action to auto update this value without having to pass in an ID value?
Thanks,
Sean.
Sean,
Db.nextVal does indeed do the equivalent of $O(,-1) + 1, works only against numeric keys, and is designed to give you the next key value (current last key + 1). It does not reserve that value.
Until you save the record, you (or any other process) can use Db.nextVal over and over and it will return the same value (since the database hasn't changed).
Generally, you would put the transaction under TP, i.e., the entire first block of code shown above and commit after the .save(). Any other process also under TP that collides will restart and get the next Db.nextVal.
With regard to autoincrement, try using table.NEXTVAL, for example:
INSERT INTO UTBLBRCD (BRCD,DESC) VALUES (UTBLBRCD.NEXTVAL,'ABC')
You could also consider putting the assignment of the key into a before insert trigger.
Dan