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:ccampb...@mango-solutions.com] Sent: Tuesday, February 21, 2012 6:14 PM To: Jadhav, Alok; r-help@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-boun...@r-project.org [mailto:r-help-boun...@r-project.org] On Behalf Of Alok Jadhav Sent: 21 February 2012 06:59 To: r-help@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@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}} ______________________________________________ R-help@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.
