How to get the id of a newly created record?

[niuage]

Hi,

Maybe it's more of a general SQL question, but I'll still try asking here.

After inserting a new record, I want to immediately retrieve the id. Right now I'm simply fetching the last inserted record and getting its id. That doesnt seem too safe, especially if multiple threads are inserting data for instance. sqlite has a method called `last_insert_rowid` which I think will return the last inserted id of that connection, so I was thinking I could use that, but I haven't seen an example in the doc that would help me get the "return value" of the query: `SELECT last_insert_rowid()`.

Could you help? Thanks!

[niuage]

I can't try it just now but I realized it's possible to alias `last_insert_rowid`, so it's probably the exact same process as getting a count, just need to create a "LastInsertRowId" class and use `QueryFirstRecord`.

[echo17]

Please see this FAQ: link

[niuage]

Ah thanks, sorry for missing that. When you say "The ORM InsertRow command optionally sends back a rowID for you.". Do you mean "Insert" and/or "InsertAll"? I can't seem to find an InsertRow method anywhere.

[echo17]

Yes, sorry. That was a typo. I've update the FAQ to show it means Insert or InsertAll. I personally prefer to use SQL syntax, but those helpers exists for some simplicity. Note that some users have experienced locks on the database when using those helpers. I haven't been able to reproduce.

[niuage]

I'm also gonna use SQL because my classes have fields other than simply the database attributes, and I dont want to have to create separate classes just for that.

Thanks.

[batvink]

<RESOLVED>
And boom, there it is. As soon as you document your problem, you see the error! I'll leave the details below for anyone else having the same issue. Coming from the MsSQL world, I didn't realise that SqlLite is case sensitive. I needed to change field name id to Id.
</RESOLVED>


I'm struggling to get the last record id, I'm using this method from the faq.
The problem is that the result is always zero.

Example code from Faq:

       sql = "SELECT MAX(WeaponID) WeaponID FROM Weapon";
       bool recordExists;
       var record = dbManager.QueryFirstRecord<Weapon>(out recordExists, sql);
       if (recordExists)
       {
           Debug.Log("Last WeaponID: " + record.WeaponID);
       }


My code looks like this:

       bool exists;
       sql = $"SELECT MAX(id) id from {entityType}";
       var res = sqlManager.QueryFirstRecord<Event>(out exists, sql);
       Debug.Log($"Created {entityType} id {res.Id}");


The table Event has 4 records.
I have debugged and {entityType} is event
exists is true
There are no errors reported (I have "Debug Trace" switched on, on the SQL Manager)

Event.Id is Primary Key, Autoincrement, Unique


CREATE TABLE "Event" (
"Id" INTEGER UNIQUE,
"Name" TEXT,
"Description" TEXT,
PRIMARY KEY("Id" AUTOINCREMENT)
   );


I also tried to create a class called SingleInt and use this for the returned result, on the basis that all I am returning is the max Id and no other data.


   bool exists;
   sql = $"SELECT MAX(id) id from {entityType}";
   var res = sqlManager.QueryFirstRecord<SingleInt>(out exists, sql);
   Debug.Log($"Created {entityType} id {res.value}");



   public class SingleInt
   {
       public int value;
   }




Any help appreciated!

[echo17]

Thanks for the update. You just solved this with Rubber Ducking.