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

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.

Answer 2

sqlite_last_insert_rowid(resource $dbhandle)

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

Answer 4

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;");

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;
}

Answer 6

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()