R dbGetQuery insert blob data with other text data-CodePudding

library(DBI)
library(RSQLite)
db <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExecute(db , "create table if not exists drug_rank (
    _id integer primary key autoincrement,
    pertData_type text,
    pertData_name text,
    pathway_name text,
    drug_name text,
    drug_rank_RData blob
    )"
    )

I want to insert the data using a prepared statement. I have all the drug_rank_RData saved as .RData, which essentially is a list, I want to put them all in the database. How can I do so?

I tried the following but does not work:

df <- list(a =c(1, 2,3), b = c(2, 4, 6), c = c(3, 6, 9)) |> as.data.frame()
drug_rank_obj <- list(sig_drugs = df, name = "test_drug_rank_obj")
dbGetQuery(db,
    "insert into drug_rank values (?, ?, ?, ?, ?, ?)",
    params = list(
        1,
        "test type",
        "test name",
        "test pathway",
        "test drugname",
        drug_rank_obj
    )
)

Thank you.

CodePudding user response：

The only issue is that you need to serialize your data into a binary format in order to insert it into a blog. This example should work

library(DBI)
library(RSQLite)
db <- dbConnect(RSQLite::SQLite(), ":memory:")

dbExecute(db , "create table if not exists drug_rank (
    _id integer primary key autoincrement,
    name text,
    object blob)"
)

# serialize data to binary
drug_rank_obj <- list(a =c(1, 2,3), b = c(2, 4, 6), c = c(3, 6, 9)) |> 
  as.data.frame() |> serialize(NULL)

# insert into data base
dbSendQuery(db,
    "insert into drug_rank values (?, ?, ?)",
    params = list(1, "my data", list(drug_rank_obj))
)

## retrieve data
rows <- dbGetQuery(db, "select * from drug_rank where _id==?",
            params=list(1))

## un-serialize data into object
data <- unserialize(rows$object[[1]])

When doing an insert, use dbSendQuery rather than dbGetQuery