Re: [sqlite] How to use "cursors" in c#

[Keith Medcalf]

Subject to the same caveats as normal.  It only returns the ROWID of the last 
insert on the connection.  If you are ABOSLUTELY SURE without a dounbt that the 
last insert on the connection is the one that you want the rowid for, then it 
will work as you intend.  However if you miscompute which insert is the last 
row inserted on the connection, you may get a result that confuses exceeds the 
understanding. 

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Tuesday, 28 June, 2016 11:59
> To: SQLite mailing list
> Subject: Re: [sqlite] How to use "cursors" in c#
> 
> 
> On 28 Jun 2016, at 4:56pm, R Smith <rsm...@rsweb.co.za> wrote:
> 
> > I am not clear on what happens for INSERT OR REPLACE results when a row
> gets updated in stead of inserted, but the documentation should have
> details.
> 
> In SQLite, the REPLACE operation really does delete the original and
> insert a new row.  So last_insert_rowid() will work correctly with it.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Bernard Ertl]

> ... If your application requires concurrent network access, you should be 
> using either a network client/server DBMS or programming your own 
> Client/Server pair with the server process calling the SQLite API to modify 
> the db file held on the servers' local filesystem. ...

There is an open source client/server wrapper for SQLite available here: 

http://www.sqlitening.com/support/index.php
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of R Smith
>
> As an aside - I've always prescribed generating your own ID's. There is no
> requirement for using AUTOINCREMENT in any DB - It's a lazy method for
> creating an easy sure-to-be-unique ID list for simple inserted rows, and
> brilliant for stuff like log files or such, but I would make effort to avoid 
> it
> where one can manage the IDs another way. Palming that responsibility off
> to the DB engine is surely easier, but less optimal, if only in more complex
> and foreign key designs.
>

I agree 100%. I always generate my own ID's. I have a generic insert() function 
that returns the last inserted row id. I never do anything with the returned 
value. It's just there.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Simon Slavin]

On 28 Jun 2016, at 4:56pm, R Smith  wrote:

> I am not clear on what happens for INSERT OR REPLACE results when a row gets 
> updated in stead of inserted, but the documentation should have details.

In SQLite, the REPLACE operation really does delete the original and insert a 
new row.  So last_insert_rowid() will work correctly with it.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[R Smith]

On 2016/06/28 5:10 PM, Drago, William @ CSG - NARDA-MITEQ wrote:


Ah, good thoughts. My thinking was that the last row id wouldn't be valid until 
the transaction was successful. But I guess if the transaction is not 
successful it doesn't matter what the last row is because now there are 
different problems to deal with.


For some extra information - I don't ever use the last_insert_rowid (or 
its ilk in other RDBMS systems) so I might not be 100% accurate in this 
next statement (Someone please correct it if I'm wrong), but I believe 
the last_insert_rowid in SQLite will always be updated directly with the 
INSERT succeeding, and so after every successful INSERT should have the 
value of the row_id that was added in that specific INSERT's case - 
whether during a Transaction or not, but obviously bound to the 
connection making the Inserts.


I am not clear on what happens for INSERT OR REPLACE results when a row 
gets updated in stead of inserted, but the documentation should have 
details.


As an aside - I've always prescribed generating your own ID's. There is 
no requirement for using AUTOINCREMENT in any DB - It's a lazy method 
for creating an easy sure-to-be-unique ID list for simple inserted rows, 
and brilliant for stuff like log files or such, but I would make effort 
to avoid it where one can manage the IDs another way. Palming that 
responsibility off to the DB engine is surely easier, but less optimal, 
if only in more complex and foreign key designs.






___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of R Smith
> Sent: Tuesday, June 28, 2016 7:33 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] How to use "cursors" in c#
>
>
>
> On 2016/06/28 12:17 PM, William Drago wrote:
> >
> >> I am programming in c# and I have to insert into a .sqlite file a new
> >> row and get back the id of this last inserted row. This file
> >> "unfortunately"
> >> might be located on an internal network and other users might try to
> >> add other rows in the meantime.
> >
> >
> > I can't help with your locking issues, but if you are using the
> > System.Data.SQLite ADO.NET provider in your C# program you can get the
> > ID of the last row inserted with the LastInsertRowID property of your
> > connection like this:
> >
> >
> > long rowID = connection.LastInsertRowId;
> >
> > I use it after I commit the transaction.
>
> Oh that's dangerous (sometimes) - it is possible for a second
> process/application/DB connector to insert a row in between you closing the
> transaction and reading the rowID so that it is no longer the last row id...
> which is fine if you simply use it to record a relationship to the inserted 
> data
> in another place, but problematic if you use it to guess/force the id of the
> next row to be inserted (probably in some next transaction that might be a
> mile away).
>
> Of course, in 99% of applications (as in applied instances, not programs), 
> this
> is unlikely to ever be a bother, but it's that other 1% that causes 80% of the
> fails in real life[1], so it pays being diligent.
>
> Thus, I'll add my voice to those forewarning Martina - it is best to retrieve 
> the
> next row ID to be inserted (or whatever value/field you use for primary
> keying) within a transaction before the next inserts (which should all happen
> inside that same transaction). That way can never[2] fail.
>
>
> Cheers!
> Ryan
>
>
> [1]: Probably not 80% exactly, this figure is intended to illustrate the 
> principle,
> and doesn't claim to be empirical.
> [2]: Again, "never" is used here approximate to the principle. In real life, 
> bugs
> happen. :)
>
>

Ah, good thoughts. My thinking was that the last row id wouldn't be valid until 
the transaction was successful. But I guess if the transaction is not 
successful it doesn't matter what the last row is because now there are 
different problems to deal with.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[R Smith]

On 2016/06/28 12:17 PM, William Drago wrote:


I am programming in c# and I have to insert into a .sqlite file a new 
row

and get back the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add
other rows in the meantime.



I can't help with your locking issues, but if you are using the 
System.Data.SQLite ADO.NET provider in your C# program you can get the 
ID of the last row inserted with the LastInsertRowID property of your 
connection like this:



long rowID = connection.LastInsertRowId;

I use it after I commit the transaction.


Oh that's dangerous (sometimes) - it is possible for a second 
process/application/DB connector to insert a row in between you closing 
the transaction and reading the rowID so that it is no longer the last 
row id... which is fine if you simply use it to record a relationship to 
the inserted data in another place, but problematic if you use it to 
guess/force the id of the next row to be inserted (probably in some next 
transaction that might be a mile away).


Of course, in 99% of applications (as in applied instances, not 
programs), this is unlikely to ever be a bother, but it's that other 1% 
that causes 80% of the fails in real life[1], so it pays being diligent.


Thus, I'll add my voice to those forewarning Martina - it is best to 
retrieve the next row ID to be inserted (or whatever value/field you use 
for primary keying) within a transaction before the next inserts (which 
should all happen inside that same transaction). That way can never[2] fail.



Cheers!
Ryan


[1]: Probably not 80% exactly, this figure is intended to illustrate the 
principle, and doesn't claim to be empirical.
[2]: Again, "never" is used here approximate to the principle. In real 
life, bugs happen. :)



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[William Drago]

I am programming in c# and I have to insert into a .sqlite file a new row
and get back the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add
other rows in the meantime.



I can't help with your locking issues, but if you are using 
the System.Data.SQLite ADO.NET provider in your C# program 
you can get the ID of the last row inserted with the 
LastInsertRowID property of your connection like this:



long rowID = connection.LastInsertRowId;

I use it after I commit the transaction.


Good luck,
-Bill
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Jean-Christophe Deschamps]

As another further response and if you use automatic INTEGER PRIMARY 
KEY IDs, then the sqlite3_last_insert_rowid() call will tell you 
unambiguously, provided the call is made within an explicit transaction 
initiated before the insert.


As a further response, regarding that Martina said "insert ... new row 
and get back the id of this last inserted row", I believe that this 
likely reflects a poor design.  Wherever possible, every field of a 
row to insert including its identifiers should be known BEFORE 
inserting the row, and that way, you don't need to ask afterwards what 
the id is, you already know because it is the value you told it to use 
when doing the insert. -- Darren Duncan


JcD 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Darren Duncan]

As a further response, regarding that Martina said "insert ... new row and get 
back the id of this last inserted row", I believe that this likely reflects a 
poor design.  Wherever possible, every field of a row to insert including its 
identifiers should be known BEFORE inserting the row, and that way, you don't 
need to ask afterwards what the id is, you already know because it is the value 
you told it to use when doing the insert. -- Darren Duncan


On 2016-06-27 11:58 PM, Hick Gunter wrote:

Do not use SQLite for concurrent access over a network connection. Locking 
semantics are broken for most network filesystems, so you will have corruption 
issues that are no fault of SQLite. If your application requires concurrent 
network access, you should be using either a network client/server DBMS or 
programming your own Client/Server pair with the server process calling the 
SQLite API to modify the db file held on the servers' local filesystem.

SQLite uses file level locking as documented on the SQLite main page.

A cursor (CURrent Set Of Records) is a concept designed for reading data, not writing. SQLite does not 
support navigation other than retrieving the result set in the order specified in the select statement. No 
"paging" backwards. It is possible to simulate a cursor, but the "simple" solutions may 
be imperformant for large result sets (e.g. producing half the result set to display a small number of 
records on a "middle page").

I assume you are looking for "transactions". SQLite supports both explicit transactions 
(BEGIN ... COMMIT/ROLLBACK) and "automatic" Transactions (each statement is ist own 
transaction). If you are just inserting/modfying a set of rows (this also includes a single row) 
within a single table, automatic transactions are sufficient. If you require multiple changes to 
multiple rows in one or more tables to complete together or not at all, then you need an explicit 
transaction.

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Martina 
Pasini
Gesendet: Montag, 27. Juni 2016 20:51
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] How to use "cursors" in c#

Hello to Everyone!

I am sorry to bother you, but I am new to DB and SQLite and I have a doubt.

I am programming in c# and I have to insert into a .sqlite file a new row and get back 
the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add other 
rows in the meantime.

I have not studied yet how to manage locking etc (will I need it only when 
editing a row or also when adding a new one?)

For this case, when I add a new row, could it be sufficient to use a cursor or 
something similar? Could you give me some advice/links I should read to 
understand how to manage this process?

Thank you very much for you kind attention!

Best Regards,

Martina


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use "cursors" in c#

[Hick Gunter]

Do not use SQLite for concurrent access over a network connection. Locking 
semantics are broken for most network filesystems, so you will have corruption 
issues that are no fault of SQLite. If your application requires concurrent 
network access, you should be using either a network client/server DBMS or 
programming your own Client/Server pair with the server process calling the 
SQLite API to modify the db file held on the servers' local filesystem.

SQLite uses file level locking as documented on the SQLite main page.

A cursor (CURrent Set Of Records) is a concept designed for reading data, not 
writing. SQLite does not support navigation other than retrieving the result 
set in the order specified in the select statement. No "paging" backwards. It 
is possible to simulate a cursor, but the "simple" solutions may be 
imperformant for large result sets (e.g. producing half the result set to 
display a small number of records on a "middle page").

I assume you are looking for "transactions". SQLite supports both explicit 
transactions (BEGIN ... COMMIT/ROLLBACK) and "automatic" Transactions (each 
statement is ist own transaction). If you are just inserting/modfying a set of 
rows (this also includes a single row) within a single table, automatic 
transactions are sufficient. If you require multiple changes to multiple rows 
in one or more tables to complete together or not at all, then you need an 
explicit transaction.

Gunter

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Martina 
Pasini
Gesendet: Montag, 27. Juni 2016 20:51
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] How to use "cursors" in c#

Hello to Everyone!

I am sorry to bother you, but I am new to DB and SQLite and I have a doubt.

I am programming in c# and I have to insert into a .sqlite file a new row and 
get back the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add other 
rows in the meantime.

I have not studied yet how to manage locking etc (will I need it only when 
editing a row or also when adding a new one?)

For this case, when I add a new row, could it be sufficient to use a cursor or 
something similar? Could you give me some advice/links I should read to 
understand how to manage this process?

Thank you very much for you kind attention!

Best Regards,

Martina
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use "cursors" in c#

[Martina Pasini]

Hello to Everyone!

I am sorry to bother you, but I am new to DB and SQLite and I have a doubt.

I am programming in c# and I have to insert into a .sqlite file a new row
and get back the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add
other rows in the meantime.

I have not studied yet how to manage locking etc (will I need it only when
editing a row or also when adding a new one?)

For this case, when I add a new row, could it be sufficient to use a cursor
or something similar? Could you give me some advice/links I should read to
understand how to manage this process?

Thank you very much for you kind attention!

Best Regards,

Martina
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users