[sqlite] insert in sqlite (returning value for inserted row)

[R.Smith]

On 2015-04-01 07:10 PM, Kumar Suraj wrote:
> Hi Richard.. this wont work for me due to following reason.
>
> If a separate thread performs a new INSERT
>  on the same database connection
> while the sqlite3_last_insert_rowid()
>  function is running
> and thus changes the last insert rowid
> , then the value
> returned by sqlite3_last_insert_rowid()
>  is unpredictable and
> might not equal either the old or the new last insert rowid
> .

Yes... this is why transactions exist and why multiple connections used 
by multiple threads is safe - there is no such thing as unpredictable 
inside an ACID engine transaction.

The last row id will  remain valid for the duration of the transaction.

Outside of a transaction any writer can update any row/table at any time 
and produce very many different row id's due to inserts. How did you 
intend to control the last insert concurrency in your application? If 
you explain the rules you have set for your application, we can tell you 
how to get sqlite to play ball.

Also - if you do actually use AUTOINCREMENT as opposed to simply an 
integer primary key, you can look up the next AI number in the sequence 
from the sqlite_sequence table you will find auto-created in your DB 
(which is where sqlite gets it too) and yet another way is to query the 
MAX(rowid) of the table (this will be slower) - All of this however must 
also happen inside of the same transaction (as the insert) to be meaningful.

[sqlite] insert in sqlite (returning value for inserted row)

[Doug Currie]

Suraj,

Don't use the same database connection in multiple threads. Each thread
should use its own connection. Then last insert rowid is predictable.

e


On Wed, Apr 1, 2015 at 1:10 PM, Kumar Suraj  wrote:

> Hi Richard.. this wont work for me due to following reason.
>
> If a separate thread performs a new INSERT
>  on the same database connection
> while the sqlite3_last_insert_rowid()
>  function is running
> and thus changes the last insert rowid
> , then the value
> returned by sqlite3_last_insert_rowid()
>  is unpredictable and
> might not equal either the old or the new last insert rowid
> .
>
> On Tue, Mar 31, 2015 at 6:23 PM, Richard Hipp  wrote:
>
> > https://www.sqlite.org/c3ref/last_insert_rowid.html
> >
> > On Tue, Mar 31, 2015 at 9:19 PM, Kumar Suraj 
> wrote:
> >
> > > Hi
> > >
> > > I am using sqlite C interface for inserting data in the table. The
> > primary
> > > key is a 64 bit integer which i need to auto-increment and get
> populated
> > > automatically as we do not provide that value in insert statement. Is
> > there
> > > a way i can get the autoincremented value for each row inserted when
> > ever i
> > > execute my insert.
> > >
> > > -Suraj
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users at mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] insert in sqlite (returning value for inserted row)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/01/2015 10:10 AM, Kumar Suraj wrote:
> Hi Richard.. this wont work for me due to following reason.

Yes it will.  This is how I do it:

  BEGIN TRANSACTION;
INSERT INTO table .;
SELECT last_insert_rowid();
  COMMIT;

That will always give the right answer no matter how concurrent your
usage is.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUcTPwACgkQmOOfHg372QRmcwCgzSMaabaWvdQGIIa2cSH2UywX
kHIAoN/ovfhvl54Qc/SbJ5NCDc/xb9CT
=4iBF
-END PGP SIGNATURE-

[sqlite] insert in sqlite (returning value for inserted row)

[Kumar Suraj]

Hi Richard.. this wont work for me due to following reason.

If a separate thread performs a new INSERT
 on the same database connection
while the sqlite3_last_insert_rowid()
 function is running
and thus changes the last insert rowid
, then the value
returned by sqlite3_last_insert_rowid()
 is unpredictable and
might not equal either the old or the new last insert rowid
.

On Tue, Mar 31, 2015 at 6:23 PM, Richard Hipp  wrote:

> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Tue, Mar 31, 2015 at 9:19 PM, Kumar Suraj  wrote:
>
> > Hi
> >
> > I am using sqlite C interface for inserting data in the table. The
> primary
> > key is a 64 bit integer which i need to auto-increment and get populated
> > automatically as we do not provide that value in insert statement. Is
> there
> > a way i can get the autoincremented value for each row inserted when
> ever i
> > execute my insert.
> >
> > -Suraj
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] insert in sqlite (returning value for inserted row)

[Simon Slavin]

On 1 Apr 2015, at 2:19am, Kumar Suraj  wrote:

> I am using sqlite C interface for inserting data in the table. The primary
> key is a 64 bit integer which i need to auto-increment and get populated
> automatically as we do not provide that value in insert statement. Is there
> a way i can get the autoincremented value for each row inserted when ever i
> execute my insert.

[explanation simplified because I don't want to have to type three pages]

SQLite does this automatically if you do not declare a primary key for a table. 
 It will create an INTEGER column called 'rowid' for you and increment the 
value for each row you enter.  You will never see that column unless you ask 
for it explicitly by doing something like

SELECT rowid,* FROM myTable LIMIT 10;

but it's a real column and you can select, search and sort on it just like any 
other INTEGER column.  If you have already created your table try the above 
statement on it.

Alternatively you can follow the instructions from FAQ 1 on this page:



This does more or less the same thing, with the advantage that you can specify 
your own name for the column.

Simon.

[sqlite] insert in sqlite (returning value for inserted row)

[Keith Medcalf]

And, only if you assign your own name to the column can you use it in foreign 
key constraints ...


---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Tuesday, 31 March, 2015 19:26
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] insert in sqlite (returning value for inserted row)
>
>
>On 1 Apr 2015, at 2:19am, Kumar Suraj  wrote:
>
>> I am using sqlite C interface for inserting data in the table. The
>primary
>> key is a 64 bit integer which i need to auto-increment and get
>populated
>> automatically as we do not provide that value in insert statement. Is
>there
>> a way i can get the autoincremented value for each row inserted when
>ever i
>> execute my insert.
>
>[explanation simplified because I don't want to have to type three pages]
>
>SQLite does this automatically if you do not declare a primary key for a
>table.  It will create an INTEGER column called 'rowid' for you and
>increment the value for each row you enter.  You will never see that
>column unless you ask for it explicitly by doing something like
>
>SELECT rowid,* FROM myTable LIMIT 10;
>
>but it's a real column and you can select, search and sort on it just
>like any other INTEGER column.  If you have already created your table
>try the above statement on it.
>
>Alternatively you can follow the instructions from FAQ 1 on this page:
>
><https://www.sqlite.org/faq.html>
>
>This does more or less the same thing, with the advantage that you can
>specify your own name for the column.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users