CODEWITHSUNDEEP
sqlite
Tester101
Tester101

Reputation: 8172

Can SQLite return the id when inserting data?

I'm using sqlite3.exe to execute queries against my DB, using the following code.

public static string QueryDB(string query)
{
    string output = System.String.Empty;
    string error = System.String.Empty;

    System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
    startInfo.FileName = "C:\\sqlite\\sqlite3.exe";
    startInfo.Arguments = "test.db " + query;
    startInfo.UseShellExecute = false;
    startInfo.CreateNoWindow = true;
    startInfo.RedirectStandardError = true;
    startInfo.RedirectStandardOutput = true;
    startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;

    try
    {
        using(System.Diagnostics.Process sqlite3 = System.Diagnostics.Process.Start(startInfo))
        {
            output = sqlite3.StandardOutput.ReadToEnd();
            error = sqlite3.StandardError.ReadToEnd();
            sqlite3.WaitForExit();
        }
    }
    catch (System.Exception ex)
    {
        System.Console.WriteLine(ex.ToString());
        return null;
    }
    return output;
}

I'm inserting data into a table, and I'd like it to return the id of the inserted data. Is there a way to get SQLite to do this?

For example, my query might look like this "INSERT INTO mytable (some_values) VALUES ('some value');". After this query is run, I'd like output to contain the rowid of the inserted data. Is there any way to do this (a command line switch, etc.)?

A possible workaround, is to run two commands against the DB. First insert the data, then get the last inserted row id. In which case, the query would look like this "\"INSERT INTO mytable (some_values) VALUES ('some value'); SELECT last_insert_rowid();\""

Upvotes: 3

Views: 3070

Answers (2)

Anton Prokofiev
Anton Prokofiev

Reputation: 957

You should not use max(id) or similar function in DB. In this specific case it can work, under the condition that you use ONE connection and ONE thread to write data to DB.

In case of multiple connections you can get wrong answer.

From version SQLite 3.35.0 it supports returning close in the insert statement (SQLite Returning Close)

create table test (
  id integer not null primary key autoincrement,
  val text
);

insert into table test(val) values (val) returning id;

Upvotes: 2

Anthony
Anthony

Reputation: 419

Would you consider this:

select max(id) from your_table_name;

or embedded function last_insert_rowid()?

Upvotes: -1

Related Questions