[R] sqlite create new unique id

[Jadhav, Alok]

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 th...{{dropped:17}}