Update / Sync Table Sequence Number

Classic Servoy
1

Is there a good way of updating or syncing the servoy sequence number for a table with the database?

I have SQL inserts that bulk load large numbers of records into tables (so I am not using a servoy loop to add records), affecting the max pk used in the table. I now need to update the servoy sequence for that table. This can be done one of three ways, as far as I know:

  • manually go to the database designer in Servoy and push the button to sync the servoy sequence with the database (not very useful from a method)

  • use a URL post to the server (hack, updates all tables, where only one table is needed, and you have to drop all other clients):

var poster = plugins.http.getPoster('http://127.0.0.1:8080/servoy-admin/dbservers/sequences-all');
var didAddParam = poster.addParameter('confirm','Update!');
var httpCode = poster.doPost('<account>','<password>);
var pageData = poster.getPageData();
  • directly update the servoy repository’s next sequence value for that table using a SQL update (seems like the only choice)

Not sure why there is no method that allows us to sync the servoy sequence to the table.

Any advice?

2

Um…no other ideas? Feature request?

3

This is one of the reasons I personally prefer DB sequences over Servoy sequences. Have you considered using DB sequences instead?

Updating the repository is an option (unsupported, of course), but I think you have to restart the Servoy server to be sure everything is OK.

4

patrick:
This is one of the reasons I personally prefer DB sequences over Servoy sequences. Have you considered using DB sequences instead?

The problem with DB sequences is that you only have your sequence only after a saveData() and sometimes you need to have the sequence already before the saveData()
In the beginning I started with DB sequences, but that gave me lot of troubles.

Now I don’t use sequences anymore. I use UUID for all PK-fields.
And these UUID’s are also useful in case one day you need to merge records together. Not very likely that you have a duplicate.

And you have never to recalc your sequence nrs. But on the other hand, not very readable values and not in correct order. So you’ll haver to use an alternate value to order your records.

5

The problem with DB sequences is that you only have your sequence only after a saveData()

I am not 100% up to date with this. I had this problem, too, but never had trouble calling saveData() right away. I think it depends a bit what kind of sequence it is that you are using. I can say for sure that if you use “real” DB sequences, you have that value right away (without calling saveData). That might not be the case for SQL Server (which I see you are using), but there you are actually using a DB Identity, which is different from a sequence.

UUIDs are the best solution, I agree. It allows replication and merging data, but, on the other hand, you have to “teach” your DB to populate those if other tools than Servoy insert data as well.

6

martinh:
The problem with DB sequences is that you only have your sequence only after a saveData() and sometimes you need to have the sequence already before the saveData()
In the beginning I started with DB sequences, but that gave me lot of troubles.

what do you want to do with those sequences?

you can use the columns that are filled in by the db sequence already just fine

so

record.acolum = mynewrecord.dbseqcolumn

before saving… that will just work fine.