Search code examples
mysqlrrmysql

Not able to Insert chr datatype column using RMySQL

I have five columns. Out of these, am not able to insert column TagName into my database using RMySQL. The various methods I have used are:

sql <- sprintf("insert into Tags (Id, TagName, Count, ExcerptPostId, WikiPostId) values (%d, '%s', %d, %d, %d);", Id, TagName, Count, ExcerptPostId, WikiPostId)

Output: Error in sprintf("insert into Tags (Id, TagName, Count, ExcerptPostId, WikiPostId) values (%d, '%s', %d, %d, %d);", : unsupported type

query <- paste("INSERT INTO Tags (Id, TagName, Count, ExcerptPostId, WikiPostId) VALUES(",Id, "," , TagName, "," ,Count, "," ,ExcerptPostId, "," , WikiPostId, ")")

Output: Error in .local(conn, statement, ...) : could not run statement: Unknown column 'sms' in 'field list' Here, 'sms' is the first value of TagName column

dbWriteTable(con, "Tags", table)

Output: Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"MySQLConnection", "character", "matrix"’

I am using R version 3.1.2 (2014-10-31)

Solution

  • On your second attempt

    query <- paste("INSERT INTO Tags (Id, TagName, Count, ExcerptPostId, WikiPostId)
    VALUES(",Id, "," , TagName, "," ,Count, "," ,ExcerptPostId, "," , WikiPostId, ")")

    You should enclose the TagName by single quotes, like this:

    query <- paste("INSERT INTO Tags (Id, TagName, Count, ExcerptPostId, WikiPostId)
    VALUES(",Id, ",'" , TagName, "'," ,Count, "," ,ExcerptPostId, "," , WikiPostId, ")")

    (mind the single quotes!)