How to get last insert Id in SQLite?

Question

Is there any built in function available in SQLite to fetch last inserted row id. For eg :- In mysql we have LAST_INSERT_ID() this kind of a function. For sqllite any function available for doing the same process.

Please help me.

Thanks

Perforce

#506632 and php.net/manual/en/function.sqlite-last-insert-rowid.php … google is your friend. – Lavonnelaw

Your question may already asked : #506632 – Inchoative

May i ask the reason for up voting? the question is already asked on this same site? – Ddene

Answer 1

SQLite

This is available using the SQLite last_insert_rowid() function:

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

PHP

The PHP version/binding of this function is sqlite_last_insert_rowid():

Returns the rowid of the row that was most recently inserted into the database dbhandle, if it was created as an auto-increment field.

Foreground

beware that using this function is not "threadsafe" so if another thread inserts something at just the same time, could be wrong. – Ecotone

@rogerdpack, do you have a reference stating this is not threadsafe? The reference provided in the answer states that it provides the last row insert "from the database connection which invoked the function" - which would lead me to think that it is threadsafe unless you have multiple threads using the same connection. Whereas if you had multiple threads inserting through different connections, then each thread will correctly see the last row ID that the same thread inserted (which is typically what this would be used for, same as LAST_INSERT_ID() that the OP wants to mimic). – Ephrem

@MikeThomson fair question: sqlite.org/c3ref/last_insert_rowid.html "If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid." See also https://mcmap.net/q/118979/-how-to-retrieve-the-last-autoincremented-id-from-a-sqlite-table/32453 – Ecotone

Answer 2

When Using SQLite version 3 with PDO SQLite, It can be like this:

$insert = "INSERT INTO `module` (`mid`,`description`) VALUES (
            NULL,
            :text
            );
        ";
$stmt = $conn->prepare($insert);
$stmt->execute(array(':text'=> $text));

echo $conn->lastInsertId()

Cath

Answer 3

It has last_insert_rowid()

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function

Counselor

Answer 4

sqlite_last_insert_rowid(resource $dbhandle)

Jinja

Answer 5

This is a short C# method that is working for me. Int32 is large enough for my purposes.

public static Int32 GetNextID( SqliteConnection AConnection )
{
  Int32 result = -1;

  using ( SqliteCommand cmd = AConnection.CreateCommand() )
  {
    cmd.CommandText = "SELECT last_insert_rowid();";
    using ( SqliteDataReader r = cmd.ExecuteReader() )
    {
      if ( r.Read() )
        result = (Int32) r.GetInt64( 0 );
    }
  }

  return result;
}

Outstretched

Answer 6

I'm new to SQLite and this thread is quite old so I thought it may need an update as I feel there is a simpler and shorter method of getting the 'id' of the last inserted row. I used ->querySingle() instead of ->exec() and included at the end of my SQL statement returning id.

I feel this is probably more reliable and thread-safe since it's the result of the actual insert statement. Maybe even lighter processing on resources.

    $id = $db->querySingle("insert into names (first, last) values ('john', 'do') returning id;");

Yearlong