25

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

Benoit
  • 70,220
  • 21
  • 189
  • 223
DEVOPS
  • 16,170
  • 29
  • 91
  • 115
  • http://stackoverflow.com/questions/506132/how-to-get-last-inserted-id-of-a-sqlite-database-using-zend-db and http://php.net/manual/en/function.sqlite-last-insert-rowid.php … google is your friend. – Aif Jan 17 '12 at 10:27
  • Your question may already asked : http://stackoverflow.com/questions/506132/how-to-get-last-inserted-id-of-a-sqlite-database-using-zend-db – Kannika Jan 17 '12 at 10:30
  • May i ask the reason for up voting? the question is already asked on this same site? – Yaqub Ahmad Jan 17 '12 at 11:09

5 Answers5

26

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.

Treffynnon
  • 20,415
  • 5
  • 59
  • 95
  • 1
    beware that using this function is not "threadsafe" so if another thread inserts something at just the same time, could be wrong. – rogerdpack Oct 04 '16 at 23:21
  • 1
    @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). – mith Jan 26 '17 at 18:07
  • 1
    @MikeThomson fair question: http://www.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 http://stackoverflow.com/q/2127138/32453 – rogerdpack Jan 26 '17 at 20:41
16

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()
Behzad-Ravanbakhsh
  • 944
  • 10
  • 12
4

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

Alex K.
  • 159,548
  • 29
  • 245
  • 267
2

sqlite_last_insert_rowid(resource $dbhandle)

Alex Pliutau
  • 19,672
  • 26
  • 103
  • 139
1

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;
}
Gary Z
  • 145
  • 2
  • 6