From r-sig-db Tue Mar 08 15:52:14 2005 From: "Pascal A. Niklaus" Date: Tue, 08 Mar 2005 15:52:14 +0000 To: r-sig-db Subject: [R-sig-DB] last_insert_id() problem Message-Id: <200503081652.14760.Pascal.Niklaus () unibas ! ch> X-MARC-Message: https://marc.info/?l=r-sig-db&m=118536853418620 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