[R] sqlite create new unique id

[Jadhav, Alok]

Thanks for the information. In my case I know that the rows will never
be deleted so logically I could use max(rowid), but I am going to stick
to my original solution of using a table to get the max id. But there is
no in built table to maintain this, I will have to create a new table
and maintain this information myself?

This resolves my problem.

Thanks,

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: Wednesday, February 22, 2012 9:08 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

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

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