Quantcast

Retrieve INTEGER PRIMARY KEY

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
For OLE DB SQL, I have retrieved the primary key:

-----------------------------
  using (System.Data.OleDb.OleDbConnection oledbConnect = new
System.Data.OleDb.OleDbConnection(stConnectString))
  {
    using (System.Data.OleDb.OleDbCommand oledbCmd =
oledbConnect.CreateCommand())
    {
      ...
      oledbCmd.ExecuteNonQuery();
      //Retrieve the ID
      oledbCmd.CommandText = "Select @@Identity";
      int iKeyID = (int)oledbCmd.ExecuteScalar();
      stKeyID = iKeyID.ToString();
-----------------------------

What is the correct nomenclature for SQLite?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Chris Locke
Last_insert_rowid()

https://www.sqlite.org/c3ref/last_insert_rowid.html

On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]> wrote:

> For OLE DB SQL, I have retrieved the primary key:
>
> -----------------------------
>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
> System.Data.OleDb.OleDbConnection(stConnectString))
>   {
>     using (System.Data.OleDb.OleDbCommand oledbCmd =
> oledbConnect.CreateCommand())
>     {
>       ...
>       oledbCmd.ExecuteNonQuery();
>       //Retrieve the ID
>       oledbCmd.CommandText = "Select @@Identity";
>       int iKeyID = (int)oledbCmd.ExecuteScalar();
>       stKeyID = iKeyID.ToString();
> -----------------------------
>
> What is the correct nomenclature for SQLite?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
When I enter last_insert_rowid(), the compiler complains.  I think
last_insert_rowid() is SQLite3.

Is there an equivalent for System.Data.SQLite?



On Fri, Feb 3, 2017 at 8:40 AM, Chris Locke <[hidden email]> wrote:

> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]> wrote:
>
>> For OLE DB SQL, I have retrieved the primary key:
>>
>> -----------------------------
>>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> System.Data.OleDb.OleDbConnection(stConnectString))
>>   {
>>     using (System.Data.OleDb.OleDbCommand oledbCmd =
>> oledbConnect.CreateCommand())
>>     {
>>       ...
>>       oledbCmd.ExecuteNonQuery();
>>       //Retrieve the ID
>>       oledbCmd.CommandText = "Select @@Identity";
>>       int iKeyID = (int)oledbCmd.ExecuteScalar();
>>       stKeyID = iKeyID.ToString();
>> -----------------------------
>>
>> What is the correct nomenclature for SQLite?
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Wolfgang Enzinger
Am Sat, 4 Feb 2017 09:04:58 -0600 schrieb Clyde Eisenbeis:

> When I enter last_insert_rowid(), the compiler complains.  I think
> last_insert_rowid() is SQLite3.
>
> Is there an equivalent for System.Data.SQLite?

I don't know, but you can use the SQL function of the same name:
https://www.sqlite.org/lang_corefunc.html#last_insert_rowid

SELECT last_insert_rowid();

HTH, Wolfgang

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
The compiler complains about "SELECT last_insert_rowid()" ... which
appears to be limited to SQLite3.  Perhaps I'm missing something?

On Sat, Feb 4, 2017 at 10:28 AM, Wolfgang Enzinger <[hidden email]> wrote:

> Am Sat, 4 Feb 2017 09:04:58 -0600 schrieb Clyde Eisenbeis:
>
>> When I enter last_insert_rowid(), the compiler complains.  I think
>> last_insert_rowid() is SQLite3.
>>
>> Is there an equivalent for System.Data.SQLite?
>
> I don't know, but you can use the SQL function of the same name:
> https://www.sqlite.org/lang_corefunc.html#last_insert_rowid
>
> SELECT last_insert_rowid();
>
> HTH, Wolfgang
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Simon Slavin-3

On 5 Feb 2017, at 1:26pm, Clyde Eisenbeis <[hidden email]> wrote:

> The compiler complains about "SELECT last_insert_rowid()" ... which
> appears to be limited to SQLite3.

The compiler should never have got that string.  The string is executed when the program is already compiled, just like any other SELECT command.

Find some context where you can execute any other SELECT command, and use "last_insert_rowid()" as a column name.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

J Decker
http://data.sqlite.org/c3ref/last_insert_rowid.html

there is an api call to get it; or you can use select and get it....

the .net library has it as a connection property  LastInsertRowId

https://www.crestron.com/reference/simpl_sharp/html/P_Crestron_SimplSharp_SQLite_SQLiteConnection_LastInsertRowId.htm

On Sun, Feb 5, 2017 at 5:54 AM, Simon Slavin <[hidden email]> wrote:

>
> On 5 Feb 2017, at 1:26pm, Clyde Eisenbeis <[hidden email]> wrote:
>
> > The compiler complains about "SELECT last_insert_rowid()" ... which
> > appears to be limited to SQLite3.
>
> The compiler should never have got that string.  The string is executed
> when the program is already compiled, just like any other SELECT command.
>
> Find some context where you can execute any other SELECT command, and use
> "last_insert_rowid()" as a column name.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
To clarify further, the SQLite function is attached.  Retrieving the
primary key is commented (used by System.Data.OleDb function).

On Sun, Feb 5, 2017 at 10:40 AM, J Decker <[hidden email]> wrote:

> http://data.sqlite.org/c3ref/last_insert_rowid.html
>
> there is an api call to get it; or you can use select and get it....
>
> the .net library has it as a connection property  LastInsertRowId
>
> https://www.crestron.com/reference/simpl_sharp/html/P_Crestron_SimplSharp_SQLite_SQLiteConnection_LastInsertRowId.htm
>
> On Sun, Feb 5, 2017 at 5:54 AM, Simon Slavin <[hidden email]> wrote:
>
>>
>> On 5 Feb 2017, at 1:26pm, Clyde Eisenbeis <[hidden email]> wrote:
>>
>> > The compiler complains about "SELECT last_insert_rowid()" ... which
>> > appears to be limited to SQLite3.
>>
>> The compiler should never have got that string.  The string is executed
>> when the program is already compiled, just like any other SELECT command.
>>
>> Find some context where you can execute any other SELECT command, and use
>> "last_insert_rowid()" as a column name.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Hick Gunter
In reply to this post by Chris Locke
But only if you can guarantee that your statement inserts exactly one record and that nothing is executed on your connection between the insert and the call.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Chris Locke
Gesendet: Freitag, 03. Februar 2017 15:41
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY

Last_insert_rowid()

https://www.sqlite.org/c3ref/last_insert_rowid.html

On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]> wrote:

> For OLE DB SQL, I have retrieved the primary key:
>
> -----------------------------
>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
> System.Data.OleDb.OleDbConnection(stConnectString))
>   {
>     using (System.Data.OleDb.OleDbCommand oledbCmd =
> oledbConnect.CreateCommand())
>     {
>       ...
>       oledbCmd.ExecuteNonQuery();
>       //Retrieve the ID
>       oledbCmd.CommandText = "Select @@Identity";
>       int iKeyID = (int)oledbCmd.ExecuteScalar();
>       stKeyID = iKeyID.ToString();
> -----------------------------
>
> What is the correct nomenclature for SQLite?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Olivier Mascia
In reply to this post by Clyde Eisenbeis
> Le 5 févr. 2017 à 18:26, Clyde Eisenbeis <[hidden email]> a écrit :
>
> To clarify further, the SQLite function is attached.

Attachments to this mailing list are stripped.
Better inline in the email itself if short or send privately to the recipient.
:)

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
In reply to this post by Hick Gunter
In this case, there is only one record added ... no one else has
access to this database.

In the past, I have locked a record, so no one else can access that
record while it is being modified.  Is locking an option in SQLite?

Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
"Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?

On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <[hidden email]> wrote:

> But only if you can guarantee that your statement inserts exactly one record and that nothing is executed on your connection between the insert and the call.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Chris Locke
> Gesendet: Freitag, 03. Februar 2017 15:41
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
>
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]> wrote:
>
>> For OLE DB SQL, I have retrieved the primary key:
>>
>> -----------------------------
>>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> System.Data.OleDb.OleDbConnection(stConnectString))
>>   {
>>     using (System.Data.OleDb.OleDbCommand oledbCmd =
>> oledbConnect.CreateCommand())
>>     {
>>       ...
>>       oledbCmd.ExecuteNonQuery();
>>       //Retrieve the ID
>>       oledbCmd.CommandText = "Select @@Identity";
>>       int iKeyID = (int)oledbCmd.ExecuteScalar();
>>       stKeyID = iKeyID.ToString();
>> -----------------------------
>>
>> What is the correct nomenclature for SQLite?
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clemens Ladisch
Clyde Eisenbeis wrote:
> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?

This has *nothing* to do with OLE DB; @@Identity is an SQL Server
specific thing.  SQLite's is last_insert_rowid(), and both have
exactly the same restrictions.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Hick Gunter
In reply to this post by Clyde Eisenbeis
There is no record locking in SQLite

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Clyde Eisenbeis
Gesendet: Montag, 06. Februar 2017 14:19
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY

In this case, there is only one record added ... no one else has access to this database.

In the past, I have locked a record, so no one else can access that record while it is being modified.  Is locking an option in SQLite?

Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText = "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?

On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <[hidden email]> wrote:

> But only if you can guarantee that your statement inserts exactly one record and that nothing is executed on your connection between the insert and the call.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]] Im Auftrag von
> Chris Locke
> Gesendet: Freitag, 03. Februar 2017 15:41
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
>
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]> wrote:
>
>> For OLE DB SQL, I have retrieved the primary key:
>>
>> -----------------------------
>>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> System.Data.OleDb.OleDbConnection(stConnectString))
>>   {
>>     using (System.Data.OleDb.OleDbCommand oledbCmd =
>> oledbConnect.CreateCommand())
>>     {
>>       ...
>>       oledbCmd.ExecuteNonQuery();
>>       //Retrieve the ID
>>       oledbCmd.CommandText = "Select @@Identity";
>>       int iKeyID = (int)oledbCmd.ExecuteScalar();
>>       stKeyID = iKeyID.ToString();
>> -----------------------------
>>
>> What is the correct nomenclature for SQLite?
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
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: [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
In reply to this post by Clemens Ladisch
What is the correct nomenclature for using last_insert_rowid() for
SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
@@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().

On Mon, Feb 6, 2017 at 7:24 AM, Clemens Ladisch <[hidden email]> wrote:

> Clyde Eisenbeis wrote:
>> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
>> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>
> This has *nothing* to do with OLE DB; @@Identity is an SQL Server
> specific thing.  SQLite's is last_insert_rowid(), and both have
> exactly the same restrictions.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Chris Locke
In reply to this post by Clyde Eisenbeis
Why do you say 'there is no equivalence' ?
Have you read the link I posted in the reply to your question nearly 3 days
ago?

Last_insert_rowid()

https://www.sqlite.org/c3ref/last_insert_rowid.html

select @@identity and 'select last_insert_rowid()' perform the same action
- retrieving the last unique row reference.  It was the answer to your
query.  The link provides further research.


On Mon, Feb 6, 2017 at 1:19 PM, Clyde Eisenbeis <[hidden email]> wrote:

> In this case, there is only one record added ... no one else has
> access to this database.
>
> In the past, I have locked a record, so no one else can access that
> record while it is being modified.  Is locking an option in SQLite?
>
> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>
> On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <[hidden email]> wrote:
> > But only if you can guarantee that your statement inserts exactly one
> record and that nothing is executed on your connection between the insert
> and the call.
> >
> > -----Ursprüngliche Nachricht-----
> > Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Chris Locke
> > Gesendet: Freitag, 03. Februar 2017 15:41
> > An: SQLite mailing list <[hidden email]>
> > Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
> >
> > Last_insert_rowid()
> >
> > https://www.sqlite.org/c3ref/last_insert_rowid.html
> >
> > On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]>
> wrote:
> >
> >> For OLE DB SQL, I have retrieved the primary key:
> >>
> >> -----------------------------
> >>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
> >> System.Data.OleDb.OleDbConnection(stConnectString))
> >>   {
> >>     using (System.Data.OleDb.OleDbCommand oledbCmd =
> >> oledbConnect.CreateCommand())
> >>     {
> >>       ...
> >>       oledbCmd.ExecuteNonQuery();
> >>       //Retrieve the ID
> >>       oledbCmd.CommandText = "Select @@Identity";
> >>       int iKeyID = (int)oledbCmd.ExecuteScalar();
> >>       stKeyID = iKeyID.ToString();
> >> -----------------------------
> >>
> >> What is the correct nomenclature for SQLite?
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > 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: [hidden email]
> >
> > 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Simon Slavin-3
In reply to this post by Clyde Eisenbeis

On 6 Feb 2017, at 1:30pm, Clyde Eisenbeis <[hidden email]> wrote:

> What is the correct nomenclature for using last_insert_rowid() for
> SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
> @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().

last_insert_row() is an SQL variable.  In whatever API you’re using, do whatever you’d do to execute a SELECT command but instead of selecting data from a table do

SELECT last_insert_row()

instead.  Please note that the standard SQLite API is C code.  If you’re not programming in C you have to tell us what you are programming in, or we have no clue how your programming language accesses SQLite databases.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
I'm writing WPF / C# code.  This is part of a function that is called
whenever the user enters data that is saved.  The primary key is used
whenever the user changes that record ... to ensure the correct data
is changed.

On Mon, Feb 6, 2017 at 10:11 AM, Simon Slavin <[hidden email]> wrote:

>
> On 6 Feb 2017, at 1:30pm, Clyde Eisenbeis <[hidden email]> wrote:
>
>> What is the correct nomenclature for using last_insert_rowid() for
>> SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
>> @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().
>
> last_insert_row() is an SQL variable.  In whatever API you’re using, do whatever you’d do to execute a SELECT command but instead of selecting data from a table do
>
> SELECT last_insert_row()
>
> instead.  Please note that the standard SQLite API is C code.  If you’re not programming in C you have to tell us what you are programming in, or we have no clue how your programming language accesses SQLite databases.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Clyde Eisenbeis
In reply to this post by Chris Locke
Chris,

I have tried:

    using (System.Data.SQLite.SQLiteConnection sqliteConnection = new
System.Data.SQLite.SQLiteConnection("Data Source=" + stPathFilename +
";")) {
    using (System.Data.SQLite.SQLiteCommand sqliteCmd =
sqliteConnection.CreateCommand()) {
    ...
    sqliteCmd.CommandText = "INSERT INTO ..."
    ...
    sqliteCmd.ExecuteNonQuery();
    sqliteCmd.CommandText = "SELECT last_insert_rowid()";
    int iKeyID = (int)sqliteCmd.ExecuteScalar();

End up with an exception: "Specified cast is not valid."

Could you provide a specific example for SQLite that does work?  Thanks!



On Mon, Feb 6, 2017 at 9:55 AM, Chris Locke <[hidden email]> wrote:

> Why do you say 'there is no equivalence' ?
> Have you read the link I posted in the reply to your question nearly 3 days
> ago?
>
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> select @@identity and 'select last_insert_rowid()' perform the same action
> - retrieving the last unique row reference.  It was the answer to your
> query.  The link provides further research.
>
>
> On Mon, Feb 6, 2017 at 1:19 PM, Clyde Eisenbeis <[hidden email]> wrote:
>
>> In this case, there is only one record added ... no one else has
>> access to this database.
>>
>> In the past, I have locked a record, so no one else can access that
>> record while it is being modified.  Is locking an option in SQLite?
>>
>> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
>> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>>
>> On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <[hidden email]> wrote:
>> > But only if you can guarantee that your statement inserts exactly one
>> record and that nothing is executed on your connection between the insert
>> and the call.
>> >
>> > -----Ursprüngliche Nachricht-----
>> > Von: sqlite-users [mailto:[hidden email]]
>> Im Auftrag von Chris Locke
>> > Gesendet: Freitag, 03. Februar 2017 15:41
>> > An: SQLite mailing list <[hidden email]>
>> > Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
>> >
>> > Last_insert_rowid()
>> >
>> > https://www.sqlite.org/c3ref/last_insert_rowid.html
>> >
>> > On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis <[hidden email]>
>> wrote:
>> >
>> >> For OLE DB SQL, I have retrieved the primary key:
>> >>
>> >> -----------------------------
>> >>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> >> System.Data.OleDb.OleDbConnection(stConnectString))
>> >>   {
>> >>     using (System.Data.OleDb.OleDbCommand oledbCmd =
>> >> oledbConnect.CreateCommand())
>> >>     {
>> >>       ...
>> >>       oledbCmd.ExecuteNonQuery();
>> >>       //Retrieve the ID
>> >>       oledbCmd.CommandText = "Select @@Identity";
>> >>       int iKeyID = (int)oledbCmd.ExecuteScalar();
>> >>       stKeyID = iKeyID.ToString();
>> >> -----------------------------
>> >>
>> >> What is the correct nomenclature for SQLite?
>> >> _______________________________________________
>> >> sqlite-users mailing list
>> >> [hidden email]
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > 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: [hidden email]
>> >
>> > 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
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Joe Mistachkin-3

Clyde Eisenbeis wrote:

>
>     using (System.Data.SQLite.SQLiteConnection sqliteConnection = new
> System.Data.SQLite.SQLiteConnection("Data Source=" + stPathFilename +
> ";")) {
>     using (System.Data.SQLite.SQLiteCommand sqliteCmd =
> sqliteConnection.CreateCommand()) {
>     ...
>     sqliteCmd.CommandText = "INSERT INTO ..."
>     ...
>     sqliteCmd.ExecuteNonQuery();
>     sqliteCmd.CommandText = "SELECT last_insert_rowid()";
>     int iKeyID = (int)sqliteCmd.ExecuteScalar();
>
> End up with an exception: "Specified cast is not valid."
>

Changing the type of iKeyID to "long" should make it work, e.g.:

        long iKeyID = (long)sqliteCmd.ExecuteScalar();

Alternatively, you should also be able to use the LastInsertRowId
property of the SQLiteConnection object, e.g.:

        long iKeyID = sqliteConnection.LastInsertRowId;

--
Joe Mistachkin @ https://urn.to/r/mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Retrieve INTEGER PRIMARY KEY

Kevin Benson
In reply to this post by Clyde Eisenbeis
On Tue, Feb 7, 2017 at 4:11 PM, Clyde Eisenbeis <[hidden email]> wrote:

> int iKeyID = (int)sqliteCmd.ExecuteScalar();
>

I believe the type of last_insert_rowid() is *always* INT64

--
   --
      --
         --Ö¿Ö--
        K e V i N
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12
Loading...