[R-sig-DB] function for updating variables in a MySQL database

[Adrian Dusa]

Dear all,

I recently started to use RMySQL and I noticed there are no functions for 
updating variables (say, when one creates a new variable and decides later to 
change the formula).

I hope I didn't reinvent the wheel, you can find my attempt below 
(undocumented but I guess pretty straighforward):


## START
"dbUpdateVar" <-
function(conn, dbtable, dataframe=NULL, primary, vars) {
    if (is.null(dataframe)) {
        stop("The source dataframe is missing, with no default\n\n", call. = 
FALSE)
        }
    if (!dbExistsTable(conn, dbtable)) {
        stop("The target table doesn't exist\n\n", call. = FALSE)
        }
    if (!(primary %in% names(dataframe))) {
        stop("The primary key variable doesn't exist in the source 
dataframe\n\n", call. = FALSE)
        }
    if (!all(vars %in% names(dataframe))) {
        stop("One or more variables don't exist in the source dataframe\n\n", 
call. = FALSE)
        }
    if (!(primary %in% dbListFields(con, dbtable))) {
        stop("The primary key variable doesn't exist in the target table\n\n", 
call. = FALSE)
        }
    if (!all(vars %in% dbListFields(con, dbtable))) {
        stop("One or more variables don't exist in the target table\n\n", 
call. = FALSE)
        }

    varlist <- paste(dbtable, "(", paste(c(primary, vars), 
collapse=", "), ")", sep="")
    dbstring <- paste("(", paste(apply(dataframe[, c(primary, vars)], 1, 
paste, collapse=", "), collapse="), ("), ")", sep="")
    toupdate <- paste(paste(vars, "=VALUES(", vars, ")", sep=""), 
collapse=", ")
    sqlstring <- paste("INSERT INTO", varlist, "VALUES", dbstring, "ON 
DUPLICATE KEY UPDATE", toupdate)
    dbSendQuery(conn, sqlstring)
    }
## END

If there is a more direct way of doing this, I would very much like to learn 
about it.

Regards,
Adrian

-- 
Adrian Dusa
Romanian Social Data Archive
1, Schitu Magureanu Bd
050025 Bucharest sector 5
Romania
Tel./Fax: +40 21 3126618 \
          +40 21 3120210 / int.101