Retrieve value of INSERT ... RETURNING ...?

[Paul Dardeau 0]

Hi,

 

I'm using SQLite with Delphi 12.1.

 

I have this table in my SQLite database.

 

CREATE TABLE databases
(
   database_id INTEGER PRIMARY KEY,
   database_name TEXT UNIQUE NOT NULL,
   db_file_name TEXT NOT NULL,
   db_file_path TEXT NOT NULL,
   db_description TEXT NOT NULL,
   created_at TEXT NOT NULL,
   last_update TEXT NULL

) STRICT;

 

When I try to run the following code, the INSERT statement runs fine, but I don't know how to extract the database_id value. Suggestions?

 

  Sql := 'INSERT INTO databases ' +
           '(database_name,' +
            'db_file_name,' +
            'db_file_path,' +
            'db_description,' +
            'created_at) ' +
         'VALUES (:database_name,' +
                 ':db_file_name,' +
                 ':db_file_path,' +
                 ':db_description,' +
                 ':created_at) ' +
         'RETURNING database_id';

 

    SqlQuery.SQL.Text := Sql;
    SqlQuery.ParamByName('database_name').AsString := ADatabase.Name;
    SqlQuery.ParamByName('db_file_name').AsString := ADatabase.FileName;
    SqlQuery.ParamByName('db_file_path').AsString := ADatabase.FilePath;
    SqlQuery.ParamByName('db_description').AsString := ADatabase.Description;
    SqlQuery.ParamByName('created_at').AsString := GetCurrentTimeStamp;
    ExecuteQuery(SqlQuery, false);   // my own wrapper function that does logging and executes "SqlQuery.ExecSQL" in this case
    DatabaseId := SqlQuery.Fields[5].AsInteger;  // <-- this fails. how do i get the RETURNING value?

Edited 17 hours ago by Paul Dardeau
Added tags

[Remy Lebeau 1453]

ExecSQL() is not supposed to be used for SQL statements that return data. You normally have to use Open() instead, or set Active=true.

 

That being said, RETURNING values can be accessed using an output parameter, which ExecSQL() should be able to fill.  Depending on the driver you are using, the syntax may differ slightly, eg:

...
ExecuteQuery(SqlQuery, false);
DatabaseId := SqlQuery.ParamByName('RET_database_id').AsInteger;

Or:

...
with TParam(SqlQuery.Params.Add) do begin
  Name := 'database_id';
  DataType := ftInteger;
  ParamType := ptOutput;
end;
ExecuteQuery(SqlQuery, false);
DatabaseId := SqlQuery.ParamByName('database_id').AsInteger;

 

Edited 14 hours ago by Remy Lebeau

[Paul Dardeau 0]

45 minutes ago, Remy Lebeau said:

You can't use ExecSQL() for SQL that returns data. You have to use Open() instead, or set Active=true.

 

Also, since your ID is the 1st column in the table, try using Fields[0] instead, or use FieldByName('database_id'). 

 

Great! Thank you very much! I really appreciate it.

[Paul Dardeau 0]

Remy's post contained the missing pieces that I needed, but I ran into an unrelated problem. My insert starting failing saying that the UNIQUE constraint was failing. I saw that 2 rows were being inserted. I set breakpoints in my code thinking that I was somehow calling my insert twice. Nope! I was just calling it once, yet 2 records were being inserted. I downloaded the dbExpress driver for SQLite from Devart website and once I started using it (without changing anything except my TSQLConnection parameters), only 1 record is now inserted (as expected).

 

This certainly doesn't inspire a lot of confidence in the code that Embarcadero is publishing to the public. FWIW, I'm using Delphi CE 12.1.

[Remy Lebeau 1453]

8 minutes ago, Paul Dardeau said:

My insert starting failing saying that the UNIQUE constraint was failing. I saw that 2 rows were being inserted. I set breakpoints in my code thinking that I was somehow calling my insert twice. Nope! I was just calling it once, yet 2 records were being inserted.

...

This certainly doesn't inspire a lot of confidence in the code that Embarcadero is publishing to the public.

Feel free to file a bug report with Embarcadero.

Edited 14 hours ago by Remy Lebeau

[Paul Dardeau 0]

10 minutes ago, Remy Lebeau said:

Feel free to file a bug report with Embarcadero.

Good idea! Thanks for the link! I created a bug report for it.

[emileverh 23]

If I was you in SQLite I defined 'database_id' as AUTOINCREMENT. No duplicates anymore!

 

CREATE TABLE databases
(
   database_id INTEGER PRIMARY KEY AUTOINCREMENT

 

And if you want to know the last inserted id, call the following statement: select LAST_INSERT_ID() as seq

Edited 11 hours ago by emileverh

[Serge_G 88]

Which connector is used ? Firedac or ?

If Firedac you can use a direct query on FDConnexion and try a EXECSQLSCALAR 

 

DatabaseId :=FDConnection1.execSQLScalar(SQL,[ADatabase.Name,Adatabase.Filename,ADatabase.FilePath, ADatabase.Description,Now]); 

Checked, using  @emileverh remark about autoincrement

Edited 8 hours ago by Serge_G
Checked

[emileverh 23]

10 minutes ago, Serge_G said:

Which connector is used ? Firedac or ?

FireDAC

[Paul Dardeau 0]

8 hours ago, emileverh said:

If I was you in SQLite I defined 'database_id' as AUTOINCREMENT. No duplicates anymore!

 

CREATE TABLE databases
(
   database_id INTEGER PRIMARY KEY AUTOINCREMENT

 

And if you want to know the last inserted id, call the following statement: select LAST_INSERT_ID() as seq

I recently read that AUTOINCREMENT does not do what it implies.

 

From SQLite's own website:  https://www.sqlite.org/autoinc.html

 

"The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed."

 

"On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used."

 

The reason I experienced UNIQUE constraint violation had nothing to do with the absence of AUTOINCREMENT. Rather, it was because the SQLite driver inserted duplicate rows.