Handle BAS_IDGEN stored proc insert into plus via aware UI

swiftinitpvtltd

If we have to insert data into a BO via stored procedure(large complex bulk insert-its easy to do via stored proc vs aware processrule) and also user needs to add new entry via Aware UI then how to handle BAS_IDGEN in stored procedure insert into(ID) because when inserted via stored procedure its not updating BAS_IDGEN when done as last ID.
We used autoincremental settings to "ON" in mysql to make that insert into work via stored procedure.

BLOMASKY

have you upgraded to 8.3 yet? It supports sequences for most (not mysql) databases.

If not, then you need to have code similar to below
declare @nextID integer
update BAS_IDGEN set max_ID = Max_ID + 1
select @nextID = max_ID from BAS_IDGEN
now use @nextID to assign the ID in your S.P.

Bruce

Markfre1

Bruce,

MySQL seems like a problem for these stored procedures.

So we are trying MariaDB but there is no BAS_IDGEN. There is a Bas_idgen_seq but its empty other than a min, max and a few other values. There seems to be one in every bsv.

We are trying to insert records and with Mysql, the idgen_seq did not update. Also, we are concerned with multiple tenants and hundreds of users, plus some users Adding manually through AIM, the timing of the update of the idgen. So a 'batch' insert could be taking place as well as users entering into the same file.

MariaDB with sequences seems to handle that but it's beyond my expertise.

Are we speaking something like this:

INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Helsinki', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Espoo', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Kirkkonummi', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Hanko', 10);

One last thing. I was able to use the same ID if multiple tables and did not seem to get a crash but Vlad says they must be unique.

In summary, I need to insert records and make sure the sequences continue to bump up and 'Do I not have to update the Bas_IdGe'n because it doesn't seem to display in MariaBD, and maybe it's handled behind the scenes in the db.

Lastly, it has to be bulletproof as its a multi-tenant app with expected hundreds or thousands of users.

Is this making sense:
1 . Create sequence file

Just use the type of statements above to get unique ids
Is this going to keep incrementing the bas-idgen...somewhere.

I'm an AIM and Magic programmer, so stored procedures, sql statements, its not my expertise. Any help is appreciated.

Mark F

Markfre1

Bruce,

So in MariaDB AIM creates bas_idgen_seq object for each bsv. This never changes and just storest min, max, etc value.

I can't find where it stores the last ID value?

I'm wondering if I do a stored procedure.
ex; INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Helsinki', 10);

that this updates wherever it stores the last ID.

And I assume, if you add it through an AIM add, not sp, it just continues where it left off.?

In MySQL you have to update the idgen, but I think this can create a lot of issues in a multi-user environment.

So I think I have to take off auto increment for the two files I am inserting into and use the above statement. But then again, if Auto Increment is off and the record is added through AIM ADD, is that going to work? I assume AIM grabs the last ID and automatically updates wherever the Last ID is stored.

I'm trying to figure this out. Am I on Pluto, or am I getting this right? Any guidance is appreciated.

Thanks, Mark F

Markfre1

I think I figured it out.

In MariaDB, the bas_idgen_seq just stores the min, max, and some other information. It does not store the last ID. I don't know where this is stored.

When I use and insert:
INSERT INTO CustomerDetail (Id, FirstName, LastName) VALUES (NEXT VALUE FOR bas_idgen_seq, 'John', 'Smith');

It increments the ID in the CustomerDetail correctly. Then if I go into AIM and add one through an add button, it just continues where it left off.

In MySql, the bas_idgen does not get updated, and you have to manage that. In addition, something I read about MariaDB and caching that you won't have a collision with users, multiple tenants, adding at the same time.

If you are doing stored procedures inserting records, it seems that you need to be using one of the databases that uses sequences, provides a situation where you don't have to manage anything.

I'm not sure on all this but so far my testing seems to confirm the above. Any and all feedback appreciated.

Mark F.

BLOMASKY

MY SQL does not support sequences. Other DBs do.

In the past, (prior to 8.3) Aware has a table called bas_idgen which has one record with the value of the last ID created. If you wanted to use it outside of aware (i.e. in a stored proc, you had to get the value and increment it.)

Now with 8.3, a sequence is created for each BSV. A sequence is not like a table, where you can see the value, instead a sequence has some parameters, like min / max value, amount to increment by, last value, etc.) using the "next value for SEQUENCE_NAME" gets the next value which you can use in an insert or use in a set statement if you need to save the value.

As far as duplicate IDs. If you have a BSV group, then all the IDS in all of the grouped BSVs have to be unique. However, if you just have plain old ordinary BSVs there seems to be no problem if 2 or more BSVs have the same IDS. (in my case, I imported a lot of data from an old system, so I numbered my customers, 1 to 2000, my inventory records, 1 to 10,000. my old orders, 1 to 300,000. etc.

Bruce

Markfre1

Bruce,

Did a lot of testing with sequences using MariaDB this weekend. So you know the problem is if you use an sp it does not automatically update the bas_idgen, and therefore in you do not then the next user adding a record crashes, and its a mess. Also, if you have many one db with many tenants and users running sp, and others adding, it seems that if one user grabs the last idgen, then another user grabs it, and users are updating, I think you may have a problem threre as well.

With MariaBD and sequences it seems to use some type of caching and many users can be doing work and then it commits them correctly. ( I read about this but did not understand all the technical jargon).

But it looks like you just need to take off autoincrement, and use the nextval(bas_idgen_seq) and everything seems to work. I added a sp batch, then went manually to add entries in AIM and it started with the correct last id. It seemed so simple and nothing to manage.

I also read that you can make the default ID for a table, using the nextval so you do not have to put it in the insert statements. Although I am not a sql person so I was unsucessful.

My test was with a simple insert with a customer, and then adding manually customers, staff, etc. and it all worked.

I am working with someone with sql and we will be testing it with our more sophisticated sp.

If this works as it seems, and someone is planning to have a system with many tenants, users, companies, then you need to use a database with sequences with AIM 8.3

I think I am on the right track and will repost as soon as we do our final testing.

Thanks, Mark F

Markfre1

Okay this is the conclusion. If you are using stored procedures inserting records, and users can also insert at the same time, as well as multiple users running the same sp then you must be in 8.3 and use a database the uses sequences. I'm switching our development version to MariaDB.

All we had to do was add:

insert into timeentry(ID,BASVERSION,BASTIMESTAMP,TimeEntryDate,xxxxxxxxxxxx
select NEXT VALUE FOR bas_idgen_seq,2,CURRENT_TIMESTAMP,xxxxxxxxx

That's it. The sequences automatically increment correctly, always picking up where the last one left off. No management involved. I did read something where you can put the statement in the Default ID of the table but could not get that to work. So we put it in the stored procedure instead.

Trying to maintain the bas_idgen in MySQL, concerns for multi-users, etc is not something I wanted to contend with or worry about. The app we are building is a heavy multi-tenant app so we now feel pretty good about this. One last comment, I believe the limit in MySQL ID is 2 billion. But if you are like Xero, FreshBooks accounting with 100k plus users, you can get there. MariaDB is like in the trillions.

If you see any flaws in these comments, please let me know. Thanks

Mark F