ORDA and Sequence Number

I am trying to duplicate a number of records using ORDA. I have an ID field that uses sequence numbers but is not the primary key of the table. The Primary Key is a UUID field. When i attempt to duplicate a record like this in a loop the sequence number does not increment but returns the save value over and over. Is there a way to make this work?

Code :
$esRecs:=ds.Complex.query(“Domain_ID = :1”;$vnFromDomainID)
For each ($eRec;$esRecs)
$eNewComplex:=$eRec.getDataClass().new().fromObject($eRec.toObject())
$eNewComplex.ID:=Sequence number([Complex]) //Does not increment with each orda record created
$eNewComplex.Domain_ID:=$vnToDomainID
$eNewComplex.save()
End for each

Product :4D - 4D Server
OS : Windows

Sequence Number is for classic language - and only for longint (=numbers), while you use a UUID.

Remove that line - and don’t use from/to Object but $entity.clone():

https://doc.4d.com/4Dv18/4D/18/entityclone.305-4505941.en.html

Thomas,

I must not have stated my question correctly. The ID column is a Longint. My UUID is another column altogether. So how do i get the next number and increment it in ORDA manually since sequence number is not a valid command in this case?

Secondly, When i used .clone() and saved that record I never got a new record just changes to the original record. Try it yourself and see. I never got a new record with clone. The documentation states that clone is a duplicated reference to the same entity. Read this thread (https://forums.4d.com/Post/EN/29721979/1/29725346#29725346) clone is not as you say.

Here’s what i did

$eNewComplex:=$eRec.clone()
$eNewComplex.ID:=Sequence number([Complex])  //Does not increment with each orda record created
$eNewComplex.GUID:=Generate UUID
$eNewComplex.Domain_ID:=$vnToDomainID
$eNewComplex.save()

This changed the original record and did not give me a new record in the table. So again the core question is. How do you get the sequence number and increment it manually for an orda entity.

Not the same case but I take the opportunity : a few days ago, I wanted to update a table to which I had just added a primary key. Classic language and longint. I looped through the records and used the Sequence number command -> same result, I got always the same number :disappointed:. You can’t use this command when you want to modify a record ?

Thanks

: Justin WILL

…the sequence number does not increment…

Hi Justin,

With the “ID” field set to auto increment in the structure editor, try the following in the for each loop.

Replace these 2-lines

<code 4D>
$eNewComplex:=$eRec.getDataClass().new().fromObject($eRec.toObject())
$eNewComplex.ID:=Sequence number([Complex]) //Does not increment with each orda record created

</code 4D>

with these 3-lines:

<code 4D>
$o:=fromObject($eRec.toObject()
OB REMOVE($o;“ID”)
$eNewComplex:=$eRec.getDataClass().new().fromObject($o)
</code 4D>

Jeremy,

I assume if i was set to auto increment it would probably work. But the field is not set to auto increment and i don’t want to change it. It has worked for years setting this field manually with sequence number and i don’t want to re-code everywhere to set that property.

So the question still exists. How do I get a sequence number for an ORDA record manually.

Justin

: Eric TROTTA

You can’t use this command when you want to modify a record ?Thanks
I don’t think you can, I think Sequence number returns the last value+1 for a new record. With an existing record, I’m not sure it works. Anyway I never use that command, I always use the checkbox in structure…

In your example, just fill the field with the index of a loop (for example), then set the sequence number to the biggest value.

Justin,

: Justin WILL

… and i don’t want to change it. It has worked for years setting
this field manually with sequence number and i don’t want to re-code
everywhere to set that property.

I don’t want to sound like a twit but maybe this isn’t a good situation to try to apply ORDA. My experience is fitting ORDA into existing, mature code is often problematic and so far has always involved some amount of refactoring to accommodate it.

And I totally support a decision not to refactor.

Kirk

I have already re-written the code using Duplicate Record and Sequence Number. At this point I’m just trying to get more understanding/answers on this process. As a rule of thumb these days I am trying to use ORDA as much as I can as I know it’s the future. It just seemed I must be missing something that I cannot get a Sequence Number with ORDA. Sequence Number has been a thing in 4D since I started back in the 2.2.3 days. It just seems like there should be an entity function for this or some way to get this value. So I figured I should start a thread and see where it goes.

Thanks
Justin

Justin,
I hear ya. It’s tricky. ORDA is such a different way of thinking about accomplishing things to begin with. I’ve been bitten a few times replacing old code with ORDA only to find something somewhere else happened to expect a value set to a process or IP variable.

I’ve had best luck with refactoring a specific task or form. If the task is encapsulated in a method I’ll just make a new version of the method and do it all with ORDA. Usually with 1/3 the code. If it’s a form I’ll try to encapsulate it in a new method to setup and call the form. About the only time I will replace part of a method with ORDA is replacing SQL calls that were used to find something without messing up the Current Selection.

I am still doing a lot of work in a v15 project we plan on moving to v18. So I’m doing stuff there with an eye to how it will look in v18 in terms of structuring objects and such. Really makes me appreciate how fluid things are in v18. And easier.

: Justin WILL

It has worked for years setting this field manually with sequence
number and i don’t want to re-code everywhere to set that property.
So the question still exists. How do I get a sequence number for an
ORDA record manually.
Hi Justin,
in many DB’s using hardcoded [aTable]aKey:=Sequence number([aTable]) I’ve switched such fields to autoincrement (checkbox):

  • search callers of Sequence number, then, for each field
  • comment Sequence number in code
  • check the box of the field (or use SQL, see doc)

I don’t think it took me more than half a day in the biggest one (about 150 tables). I like the DB engine to take care of that, I can work on more interesting things…

Note: with autoincrement, the value is exactly the same as the one returned by Sequence number, but it’s delivered when the record is created (new record, add record…) while Sequence number needs an additional request to the server, I suppose…

Arnaud,

I understand this isn’t a big coding problem. It does however create a set of changes that would require us to test nearly every feature of the system which is a big deal. A full test cycle is at least 40 hours of work. This is why i do not want to do that at this point. I just want the sequence number just like we have in the classic language for ORDA.

Again, I want to know if 4D has a way or is planning a way for us to get the sequence number for a table when using ORDA. Everyone is suggesting changing to auto increment. I understand this exists. I understand how it works but in this case I am not ready to go there.

So 4D, how do I get the Sequence Number for a record created by ORDA without using auto increment on the field?

Thanks
Justin

You could :

  • query with ORDA,
  • get max value +1
  • put it in the field.

I’d use a function, I think, with ‘execute on serveur’ property set on:
c_longint($0;out)
$out:=Sequence number([table])
set database parameter([table];table sequence number;$out) //save
$0:=$out
Not tested, could be useable in orda.

although in principle a nice idea, I’d be a bit apprehensive about adding a thread-unsafe command such as get/set database parameter to the mix…I’d want my ORDA code to be thread-safe.

: Arnaud DE MONTARD

Sequence number needs an additional request to the server, I suppose…

reminds me of another ORDA “gotcha”

dataClass.new() does not auto-generate a primary key on the client side; you must entity.save()

https://doc.4d.com/4Dv17/4D/17.3/dataClassnew.305-4621819.en.html

I guess you could force increment the s.n. by

Code :
START TRANSACTION
CREATE RECORD([Table_1])
SAVE RECORD([Table_1])
CANCEL TRANSACTION

the takeaway message obviously is “don’t do it”


when “auto-increment” was first introduced in v11,
it was explained that its main goal was to eliminate the need for Sequence number (in triggers).
same for the “On Loading Record” database event.

granted, triggers (or the language in general) were not thread-safe back then,
still, you get the feeling that Sequence number is no longer fashionable.

: Arnaud DE MONTARD
: Eric TROTTA

You can’t use this command when you want to modify a record ?Thanks
I don’t think you can, I think Sequence number returns the last
value+1 for a new record. With an existing record, I’m not sure it
works. Anyway I never use that command, I always use the checkbox in
structure…

In your example, just fill the field with the index of a loop (for
example), then set the sequence number to the biggest value.

Of course, I did what you suggest and that’s not a problem. But I was very surprised by this behavior and I saw nothing in the documentation about that. Or only then very indirectly : “It is a non-repeating number that is incremented for each new record created for the table”

I’m not surprised, I don’t remember of 4D filling or even modifying existing values in data when I change something in structure. I understand that property as “on new record”. The automatic UUID works the same.

Miyako:

“dataClass.new() does not auto-generate a primary key on the client side; you must entity.save()”

We discovered the other day that the relational data isn’t available after a .new() followed by a .save(). In the debugger (client-server) the data from the related tables simply doesn’t exist until you .reload() it.

Is this expected?

We’re also finding that for creating records, 4D Classic is much faster than ORDA. Almost an order of magnitude in some cases, and around 2x-3x faster in others. Is it safe to create a record in classic and then expect it to be immediately available in ORDA?