I want to create an ID generator, but I’m not sure how to go about it. Here’s my goal. A user will input their information into a database and if they don’t already have an ID assigned for that input, they can click “Create ID” while filling out their information. The Create ID button will search the database ID inputs and if the number 1000 is available, it will create it. The next input will search for 1001. If it’s taken it will use 1002 instead. The lowest possible ID (generated) should be 1000, with no upper limit. This generated number will display in the input line next to it.
Has anyone worked with this before, or have an example to reference?
If you work with a database, it is recommended to use a key Index field anyway. You could set the initial counter of it to 1000.
If you work with a Xojo class instead, that’s a case for a shared class property of an Integer type. You can set its initial value to 1000 in the inspector and then just increase its counter which is valid for the whole class. And you would need an instance property too of the same type that gets this value assigned on some CreateID method.
What sort of database are you using ?
There may be some things that are really easy to implement for specific databases
Some support sequences which you can literally ask the sequence for “the next available ID” and it will generate that and even if several processes ask for an id at the same time they will all get unique ID’s
There are manual ways to do exactly this as well
[quote=194244:@Norman Palardy]What sort of database are you using ?
There may be some things that are really easy to implement for specific databases
Some support sequences which you can literally ask the sequence for “the next available ID” and it will generate that and even if several processes ask for an id at the same time they will all get unique ID’s
There are manual ways to do exactly this as well[/quote]
SQLite supports integer primary keys that auto increment BUT thats not quite the same since you actually have to insert the data then somehow know the key you just generated from the insert
You might try use something like selecting the max of you ID and adding one - but if your app is single user but has many threads doing work simultaneously etc then you CAN have issues.
Its usually good to plan for the future when maybe your app needs to be multiuser (or multithreaded)
On database that do not have sequences built in what I have done before is “fake” a sequence using a secondary table set up like
create table sequences( tablename varchar, currentValue integer)
You put all the tables in your database that you NEED “sequences” in there with their starting value
Then to access it you have a single method that EVERYTHING uses to get the next ID
Unfortunately for a db like SQLIte there are no stored procedures so its more by convention in your code than enforced at the DB level
The access to get a new id is written as a transaction (this is what makes it multi user safe too)
And its important the order is this way - update FIRST so that any multi user access to the table is synchronized
begin transaction
update sequence set currentValue = currentValue + 1 where tableName = ? <the ? is the name of the table you need the NEXT value for)
select currentValue from sequence where tableName = ? <the ? is the name of the table you need the NEXT value for)
commit transaction
I’ve used this on a wide variety of databases when they did not have sequences built in
In your case
create the table
insert the tablename and starting id for each table when you initially create the db
create a method in your application, NextIDForTable( tablename as string ) as Integer
the code IN the method implements the transaction I outlined above - you’ll also want to have SOMETHING in this method for the case where a table is NOT one you set up a sequence for - I’d have it just throw an exception since you’re using it in code expecting a sequence but haven’t set that up
i just did this on SQLiteManager “UPDATE sqlite_sequence SET seq=100 WHERE name=‘abc’” where abc is the name of the data file. and then add a new record and it start with 101