Output parameters in INSERT query

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQLite in Delphi and C++Builder
Post Reply
dbxDmitry
Posts: 3
Joined: Tue 09 Jul 2013 09:43

Output parameters in INSERT query

Post by dbxDmitry » Tue 09 Jul 2013 09:57

Delphi XE4. dbExpress for Sqlite3.

Hello everyone. I'm trying to obtain autoincremented primary key value of inserted row. Like this:
SQLQuery.CommandText := 'INSERT INTO tbl1 (id, name) VALUES (:id, :name)';
SQLQuery.ParamByName('id').ParamType := ptInputOutput;
SQLQuery.ExecSql();

Inserting works fine, but when I'm trying to get back my id parameter, it is always 0:
SQLQuery.ParamByName('id').Value <--- 0

How can I get autogenerated key for inserted row atomically?
Thank you!

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Output parameters in INSERT query

Post by AlexP » Wed 10 Jul 2013 13:15

Hello,

To retrieve the value of the auto-incremental field, you should use the SQLite last_insert_rowid function

Code: Select all

SELECT last_insert_rowid()

dbxDmitry
Posts: 3
Joined: Tue 09 Jul 2013 09:43

Re: Output parameters in INSERT query

Post by dbxDmitry » Wed 10 Jul 2013 15:50

AlexP wrote:Hello,

To retrieve the value of the auto-incremental field, you should use the SQLite last_insert_rowid function

Code: Select all

SELECT last_insert_rowid()
Thank you. But is this will be an atomic operation? I mean what if someone else inserted something in this table?

dbxDmitry
Posts: 3
Joined: Tue 09 Jul 2013 09:43

Re: Output parameters in INSERT query

Post by dbxDmitry » Thu 11 Jul 2013 10:05

Found answer on last question myself here http://www.sqlite.org/c3ref/last_insert_rowid.html

Thank you.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Output parameters in INSERT query

Post by AlexP » Fri 12 Jul 2013 07:16

Hello,

Glad to see that you solved the problem. If you have any other questions, feel free to contact us

Post Reply