[R] sqlite create new unique id

[Chris Campbell]

Hi Alok

Yes, last_insert_rowid() will always look for the last row inserted in this connection, so you will always get 0 before inserting a new row in a new connection. Maintaining the connection may cause other problems as the last inserted rowid might not be relevant to your query.

Getting max(rowid) does not work because it does not take deleted rows into account; you will want to avoid inserting a row with rowid that was previously in the database. Sqlite performs autoincrement in a similar manner to your solution by using another table to store the largest rowid, and increments the number each time you insert a new row. Since you have text you will need to manually increment the value each time you insert a new row. You should be able to automate this step in a satisfactory way by building the new rowid for the table based on the expected structure of your field values. 

Best wishes

Chris

Chris Campbell
MANGO SOLUTIONS
Data Analysis that Delivers
+44 1249 767700

-----Original Message-----
From: Jadhav, Alok [mailto:alok.jadhav at credit-suisse.com] 
Sent: 22 February 2012 01:30
To: Chris Campbell; r-help at r-project.org
Cc: Rory Arneil; Jakub Barszczewski
Subject: RE: [R] sqlite create new unique id


Hi Chris, 

Apologies for getting back late. In my case I am creating a new connection everytime I insert a new trade. I thought last_insert_rowid() would insert max(rowid) for given table? I can try to insert bunch of rows in a data frame, but whenever I create a new connection, I will get
last_insert_rowid() as 0? Then in this case it wont be a unique number.
I thought last_insert_rowid() is used to get the unique number for given table (similar to AUTOINCREMENT feature).  
If my understanding is incorrect then what is the right way to gereate a unique number for given table? 

As I mentioned in my first post, I don't want to use INTEGER PRIMARY KEY because it has to be a text. For now, I have another table where I stored last max number and whenever I insert new transaction I get max number from this table. This approach works but there has to be a better way to do this.

Regards,
Alok 




Please follow the attached hyperlink to an important disclaimer http://www.credit-suisse.com/asiapac/legal/securities/ 


-----Original Message-----
From: Chris Campbell [mailto:ccampbell at mango-solutions.com]
Sent: Tuesday, February 21, 2012 6:14 PM
To: Jadhav, Alok; r-help at r-project.org
Cc: Rory Arneil; Jakub Barszczewski
Subject: RE: [R] sqlite create new unique id

Hi Alok

Are you certain that you are keeping your connection open?
last_insert_rowid() returns id of last inserted row from the current database connection (which invoked the function). Is it possible that every time you run the query you making a new connection?

Hope this helps,
  
Chris Campbell
MANGO SOLUTIONS
Data Analysis that Delivers
+44 1249 767700

-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
On Behalf Of Alok Jadhav
Sent: 21 February 2012 06:59
To: r-help at r-project.org
Subject: [R] sqlite create new unique id

Hi everyone,

 I am trying to insert a row in sqlite table with my own unique id. I want to create unique id using sqlite internal function
last_insert_rowid()  which returns the next max rowid of the table which is always unique. I tested this using sqlite and it works fine but when i run the same query using RSQlite from r prompt, my query doesn't create new unique id. 
last_insert_rowid()  always returns 0 value from RSQLite always returns 0.
How can I get a unique id using RSQLite ? 

my query looks like this
 "insert into PrimaryIdTable values ('AMP AT Equity','bbg','2001-01-01','2099-01-01','P'||last_insert_rowid())"

reason i am not using INTEGER PRIMARY KEY  is because i want to keep my key as TEXT datatype. This is because another table has same field for secondary id and both these keys appear in same table so i want to call them primary key (P1, P2 ) or secondary key (S1, S2 etc) . 

Thanks for your help,

Alok


--
View this message in context:
http://r.789695.n4.nabble.com/sqlite-create-new-unique-id-tp4406114p4406
114.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
LEGAL NOTICE
This message is intended for the use of the named recipient(s) only and may contain confidential and / or privileged information. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorised use of the information contained in this message is prohibited.
Mango Business Solutions Limited is registered in England under No.
4560258 with its registered office at Suite 3, Middlesex House, Rutherford Close, Stevenage, Herts, SG1 2EF, UK.

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

===============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer: 
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
=============================================================================== 

LEGAL NOTICE
This message is intended for the use of the named recipient(s) only and may contain confidential and / or privileged information. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorised use of the information contained in this message is prohibited.
Mango Business Solutions Limited is registered in England under No. 4560258 with its registered office at Suite 3, Middlesex House, Rutherford Close, Stevenage, Herts, SG1 2EF, UK.

PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL