B4J Question Logic question, getting last transactions information

AKJammer · Dec 10, 2022

Hey All,
I've got a logic question that I'm pretty sure there are multiple answers to, but my way appears to be flawed, so I'm seeking better answers.

I have a registration table that will have one entry per person. They can then purchase many tickets as to need. When I go into ticket sales and they choose 1 or more tickets, and hit save, the first thing is to check the registration table to see if they are registered. If they are, I use the ID from Registration and tie it to the tickets table. If they aren't, I create a record for them, then re-query the MariaDB database to find that record and get the Registration ID.

The problem is that I have multiple computers going registering people and apparently I'm not finding the ID properly because I've got several -1's (the default ID) in the ticket table.

What is the best, or most elegant way, of getting the ID that's generated in the Registration table after I do an insert on a MariaDB database?

Thanks in advance.

Jim

emexes · Dec 10, 2022

What are the SQL statements to "create a record for them, then re-query the database to find that record and get the Registration ID"?

In particular: what is the search query to find "that record"? Are you matching by name and/or phone number? Or by the most-recent creation timestamp?

Probably a good initial "watchdog" safety-net is to flag if the newly-created-and-requeried records comes back with the default ID of -1.

aeric · Dec 10, 2022

It is better to show your code and database schema. It is impossible to imagine what is going on.

walt61 · Dec 10, 2022

If I understand you correctly, based on https://mariadb.com/kb/en/last_insert_id/ and https://dba.stackexchange.com/quest...t-insert-id-function-guaranteed-to-be-correct, you could add an auto_increment column to the table (or use one you already have) and then, immediately after inserting, query LAST_INSERT_ID which appears to be set on a per-connection basis (note: parallel threads inserting from the same connection would throw a spanner in the works!). So you would:
- insert a row
- immediately after the insert, query LAST_INSERT_ID
- then, you can query the table selecting that LAST_INSERT_ID value from the auto_increment column

I've used the similar 'sqlite_sequence' in a single-user program with an SQLite db, and that works fine. The aforementioned links state that LAST_INSERT_ID has some associated caveats so you may want to read up on those before all hell breaks loose

emexes · Dec 10, 2022

The rows should already have

AKJammer said:
What is the best, or most elegant way, of getting the ID that's generated in the Registration table after I do an insert on a MariaDB database?

SQL:

SELECT max(rowid) from RegistrationTable;

would seem a good start, except for the risk that another computer sneaks in and also adds a new registration to the table in the fraction-of-a-second between your INSERT and SELECT operations.

In which case, extend the WHERE rowid = n clause to also match information you've just INSERTed, or back up rowid-by-rowid until you find the row that has matching information.

emexes · Dec 10, 2022

hong on a minute... if you do the INSERT within a transaction, doesn't that resolve the issue of other computers trying to modify the database at the same time?

aeric · Dec 10, 2022

Usually what mentioned by walt61 is the way to go. The table need to be created with an Auto Increment ID.
However how the OP write the code to insert the record also crucial.

emexes · Dec 10, 2022

Or maybe:

SQL:

SELECT max(rowid) from RegistrationTable;
INSERT into RegistrationTable values (value1, value2, value3, ...);
SELECT max(rowid) from RegistrationTable;

and if the second max(rowid) = the first max(rowid) plus one, then we have a winner! ?

emexes · Dec 10, 2022

aeric said:
The table need to be created with an Auto Increment ID.

of which one exists already anyway as Secret Column rowid

emexes · Dec 10, 2022

SQL:

/* get the most-recently added rowid */
select max(rowid) from RegistrationTable;

/* return just the most-recently added record */
select rowid, * from RegistrationTable where rowid = result of previous select;

/* return all records, probably in rowid order */
select rowid, * from RegistrationTable;

aeric · Dec 10, 2022

emexes said:

SQL:

/* get the most-recently added rowid */
select max(rowid) from RegistrationTable;

/* return just the most-recently added record */
select rowid, * from RegistrationTable where rowid = result of previous select;

/* return all records, probably in rowid order */
select rowid, * from RegistrationTable;

I think rowid is specific for SQLite.

emexes · Dec 10, 2022

aeric said:
I think rowid is specific for SQLite.

Drats! Explicit autoincrement id is the go, then. Although it seems you could call it rowid and make it invisible, and end up with the same result.

LucaMs · Dec 10, 2022

AKJammer said:
The problem is that I have multiple computers going registering people and apparently I'm not finding the ID properly because I've got several -1's (the default ID) in the ticket table.

Before writing a record in the ticket table, your sw checks if the person (id) exists otherwise it is created. How is it possible that the record is saved without a valid id? Avoiding the default value (-1) would be a good choice.

Adding a "registration date" field, expressed in milliseconds, would be useful not only for your purpose (have you noticed that it is one of our data as B4X.com members? ? ).

AKJammer · Dec 10, 2022

Hey All,

Thanks!! I am using an auto-increment ID for the primary keys on all my tables. I was also selecting max(id) after the insert, but with multiple users, that appeared problematic. I didn't know about LAST_INSERT_ID though. Reading the notes provided by @walt61, I think this will work for me. I'm only doing a single insert, not multiple, and I don't have to worry about other clients as the call is tied to a single client. I'll run some test scenarios and make sure it's a good fit, but I think that's the ticket.
As for how I ended up getting -1's in my ticket table, yeah, that's bad. I need to do some more digging to find out how that slipped through. We just finished the first production run of the software at a dance event in Lake Tahoe, so I've got a few bugs that popped up in production that I didn't catch in testing.

Thanks again!
Jim

emexes · Dec 10, 2022

AKJammer said:
I was also selecting max(id) after the insert, but with multiple users, that appeared problematic.

It makes me nervous that this wasn't working. I agree that it's not the whole solution, and that LAST_INSERT_ID will probably fix the problem, but still: the odds of two new registrations happening within the same second in real life is low, so I'm surprised you encountered the problem seemingly multiple times. Unless your testing had lots of simulated users continually banging out new registrations: then I would expect you to see the problem.

It feels like one of those clues that doesn't seem important at the time, but suddenly makes sense later when things go wrong and an underlying cause is discovered.

The joys of programming, ay?

AKJammer said:
I didn't know about LAST_INSERT_ID though. Reading the notes provided by @walt61, I think this will work for me.

I'd have recommended avoiding SQL extensions, that tie you in to using a particular database engine/provider, but SQLite has the equivalent(-enough) last_insert_rowid(), so I won't. ?

AKJammer · Dec 10, 2022

@emexes , yah, I had four terminals running registrations on Thursday night when we opened with about 100 people in line. My testing only had little ol' me running stuff. I didn't really expect to have multiple registrations so close together, but my bad for not planning for it.

vmag · Dec 12, 2022

AKJammer said:
@emexes , yah, I had four terminals running registrations on Thursday night when we opened with about 100 people in line. My testing only had little ol' me running stuff. I didn't really expect to have multiple registrations so close together, but my bad for not planning for it.

The simplest solution is to record the terminal ID when registering, then you need to look for max(id) with a specific terminal and an error is not possible here. As a bonus, you get statistics on terminals

vmag · Dec 12, 2022

And also, if you have SQLite, you need to keep in mind that when writing, the entire table is blocked for other users, you need to use either transactions or the Try operator in a loop, I think 5-10 times will be enough.

MrKim · Dec 20, 2022

emexes said:
the odds of two new registrations happening within the same second in real life is low

No its not.
That is why you write the record and then IMMEDIATELY get LAST_INSERT_ID. That should do it.
Also, I am not familiar with MariaDB but you probably can't get the CORRECT LAST_INSERT_ID until you COMMIT your transaction (If you are using transactions). You need to check that as well. If you don't COMMIT what you may get is the PREVIOUS last insert.

josejad · Dec 20, 2022

Not sure if this thread can help or if you're using jRDC2, I've never tested with lot of simultaneous users, but it uses a transaction and it rolls back if there was some problem.

jRDC2 Add MySql Master / Detail

Hi: Searching several threads in the forum, I've used this code to add a row in a "master" table, and then the "children rows" in a "detail" table. May be useful to someone. Improvements are welcome. Table Master: id (primary, autoincrement), field 1, field 2... Table Detail: id, master_id...

www.b4x.com

Maybe @OliverA can help here, he's quite good with db's, and specially with jRDC2.

B4J Question Logic question, getting last transactions information

Active Member

Expert

Expert

Active Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Active Member

Expert

Active Member

Active Member

Active Member

Well-Known Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency