[R-sig-DB] last_insert_id() problem

[David James]

Pascal A. Niklaus wrote:
> 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
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db

Hi Pascal,

In this case you should be using dbGetQuery() instead of dbSendQuery(), 
since you don't require the result set object that dbSendQuery() 
returns. (You could dbSendQuery(), but then make sure you
dbClearResult() before requesting the LAST_INSERT_ID()). 

Example:

mysql> describe tmp_foo;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| i     | smallint(5) unsigned |      | PRI | NULL    | auto_increment |
| txt   | char(3)              | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

from R

> dbGetQuery(con, "insert into tmp_foo set txt = 'y'")
NULL

> dbGetQuery(con, "select LAST_INSERT_ID() as I")
  I
1 9

Regards,

--
David