[R-sig-DB] last_insert_id() problem

[Pascal A. Niklaus]

Hi all,

I am trying to insert a row into a table and then to get the value of an 
auto_increment field back. From the mysql command line, this looks like this:

mysql> insert into results set fileid='abc';
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)

However, I cannot successfully translate this into R. I tried:
  
  dbSendQuery(con,paste("insert into results set userID=",userID,sep="") )
  id <- dbGetQuery(con, "select LAST_INSERT_ID() AS I");
      
However, while a new row of data is inserted, id always returns 0. I wonder 
whether this is because dbSendQuery is not the appropriate method to simply 
execute a command, but I could not find an alternative.

Thanks for your help

Pascal

-- 
Pascal A. Niklaus
Institute of Botany
University of Basel
Schönbeinstrasse 6
CH-4056 Basel / Switzerland

ph. +41 61 267 3506
fax +41 61 267 3504

GPG public key: http://www.bot.unibas.ch/~pascal/pascal_niklaus.key

Please avoid sending me Word or PowerPoint attachments.
See http://www.fsf.org/philosophy/no-word-attachments.html