0

If I have serialized data like:

> a <- 1:5 
> a
[1] 1 2 3 4 5
> b <- serialize(a,NULL)
> b
[1] 58 0a 00 00 00 02 00 02 0f 02 00 02 03 00 00 00 00 0d 00 00 00 05 00 00 00 01 00 00 00 02 00 00 00 03 00 00 00 04 00 00 00 05
> b[1]
[1] 58
> b[8]
[1] 02

How can I put that serialized data into a MySQL table? I have other info there also. I read that it can be done as blob, but I don't know how it works. I am using RMySQL. I have tried: 

dbGetQuery(con, "INSERT INTO table(",b," info, moreInfo, otherStuff, more, date )")

but it won't work.

If I use 

query <- paste ("INSERT INTO table(",b," info, moreInfo, otherStuff, more, date )")
dbGetQuery(con,query)

it still won't work.

nograpes
  • 18,623
  • 1
  • 44
  • 67
TheLaama
  • 307
  • 2
  • 4
  • 12
  • I think you may need to use `dbWriteTable`. – joran May 13 '13 at 21:09
  • I alredy have table where I want to insert the data. – TheLaama May 13 '13 at 21:37
  • 1
    Yes, I imagine you do. You typically do inserts by specifying `append = TRUE` in `dbWriteTable`. – joran May 13 '13 at 21:49
  • There are more than a few things wrong with how you are going about this. Before you try inserting serialized data into a table, try figuring out how to insert a typical value into a table in SQL. I think you are missing a comma before `info` in your query, but even that wouldn't make sense.. you would need a `VALUES` statement after that to actually put stuff in. Also, instead of saying "it won't work", try telling us the exact error message. – nograpes May 13 '13 at 23:41

1 Answers1

0

try this:

library(RODBC)
dt=data.table(a=sample(10),b=sample(10)*10)
sqlSave(con, dt, tablename='sampletablename') # overwrites existing sampletablename table
sqlSave(con, dt, tablename='sampletablename', append=TRUE) # append instead of overwrite
Jun Folledo
  • 101
  • 3
  • But is it possible to put that serialized data to mysql as one object. so that on every row there will be one serialized data with other stuff also – TheLaama May 14 '13 at 07:12