SQL Execute - how to identify the record just added

or if you are using 3rd party tools that require it. Currently ActiveRecord only support INT PKs.

[quote=323671:@Norman Palardy]UUID is just a lot of bits for “uniquely identifying a row in a table” which IS “primary key” by definition
UUID really shouldn’t be necessary as the PK in a well designed db[/quote]

I dont disagree (in theory). But sometimes (in reality) you have to have 2 PKs. one that the database uses as the PK and one that your code uses. Do I like this? never. but you have to deal with the limitations you are dealt with.

dont disagree. But I have run into limitations/issues when I dont have control over the Unique IDentifier of the record. Something happens and now I have a new PK ID although the record is the same and now any references to that record are broken. upsert or insert or replace type updates is where I have been bitten the most on it.

Yes
But then I suspect Carol also had a hand in that and she is a DB person first (hence why when she does a DB talk at XDC I dont)

Then you dont have a primary - maybe a primary & another candidate but …
Shouldn’t your code use the right primary ???

Sounds like this is relying on a db autogenerated rowid or PK ?
And yes some db’s do these in ways that you get hurt

If you work with MS SQL Server (very solid, professional database).

General rules for surrogate PK (if you have a natural PK, then it is what it is):

  • PK should be smallest possible, unique, and growing,
  • an integer column with IDENTITY attribute ON is perfect for it.
  • after you add a new row, you get its PK from SCOPE_IDENTITY().

Problems with other types of PK:

  • UUID/GUID - this will generate unique values, but it is large and its values are random which causes page fragmentations (usually you want to add new rows “at the end” and have them in an order),
  • “list of table names and the current unique id number” - that creates an unnecessary point of contention. It is a good solution if you don’t need big data volume.

S.

[quote=323697:@Stan F]If you work with MS SQL Server (very solid, professional database).
[/quote]
Its been a bunch of years but I have in the past (when I could just use the Sybase reference manuals for it because they were literally identical)
Thats a ton of years ago (1995/1996 ish first time around and 2000/2004 ish)

[quote=323697:@Stan F]Problems with other types of PK:

  • UUID/GUID - this will generate unique values, but it is large and its values are random which causes page fragmentations (usually you want to add new rows “at the end” and have them in an order),
    [/quote]
    Just dont make the physical layout use that otherwise you get hot spots (this is true for any PK though)
    All the inserts take places on a small number of pages but require the entire index to split to keep the trees balanced - this is a really performance killer
    spread the physical keys out so they hit as many possible pages as you can so an split one one then causes splits across them all and the rest of the leaves will fill before require another split.
    We made that mistake early on in a big db for a near real time pipeline expert system gathering pipeline telemetry and the DB simply couldn’t keep up. Changed the index so the physical order was more spread out and had no issues with 50 - 60K inserts a minute.

Something to note… RETURNING does not work in prepared statements for some reason.

We’ve been using it through prepared statements without issue. A bug that’s been fixed perhaps?

We were having issues with it in 2016r4.1. If you are not, I’ll be excited to try it again.

I have unit tests that would fail but they pass consistently.

And now I’m concerned anyway…

What I like about UUIDs for PKs is that I can have an offline system create records that, when it synchronizes with an online system, won’t conflict.

For instance, I have a raspberry pi in my car. All it does it storing GPS, date/time and Odometer data. Whenever it connects to the internet via wifi, it syncs the data with a web-app. The web-app knows when I’m away from work or the area where my clients reside. So, it can figure out if a trip is a business trip or something else. It helps a lot…
I can have several cars doing this. By using autoincrement numbers as PKs, they will conflict when records are being transferred to another system.

UUIDs are long strings of bytes. But I never had PK-collisions before.

What I do first, is generate a UUID myself. I will populate the PK field with this value. If the DB executes an insert without an error, I know the UUID is the record that I have added.
My Save method simply returns this UUID on success or an empty string when in case an error occurred.

does this work with cubesql??

it should as if I am not mistaken, cubeSQL is built on top of SQLite

let me try now and get back here

i got value 0 when using LastRowID on CubeSQL

Try “SHOW LASTROWID” .

Like this: Dim rs as RecordSet = db.SQLSelect(“SHOW LASTROWID”)