[R-sig-DB] last_insert_id() problem

[Prof Brian Ripley]

Which R package ...?  Please read the R posting guide ....

On Tue, 8 Mar 2005, 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
>
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595