Output parameters in INSERT query

Output parameters in INSERT query

Postby 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!
dbxDmitry
 
Posts: 3
Joined: Tue 09 Jul 2013 09:43

Re: Output parameters in INSERT query

Postby 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()
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Output parameters in INSERT query

Postby 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

Postby 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.
dbxDmitry
 
Posts: 3
Joined: Tue 09 Jul 2013 09:43

Re: Output parameters in INSERT query

Postby 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
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for SQLite