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 10 hours ago by Paul Dardeau
Added tags

[Remy Lebeau 1451]

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 6 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 1451]

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 6 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 3 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 34 minutes ago by Serge_G
Checked

[emileverh 23]

10 minutes ago, Serge_G said:

Which connector is used ? Firedac or ?

FireDAC