In my calculations I get some results which are stored in a vector. And because these calculations are executed repeatedly I will have some vectors to be stored in my database. In my database table data_storage in each row a result vector should be stored.
Until now I found out that I need a BLOB variable in my table and the vectors must be serialized as mentioned in Storing R Objects in a relational database. On this mentioned source the answer from David Josipovic seems to be perfectly fitting to me but I can't code it right. See the data input in my code...
EDIT_1: when using dbGetQuery, dbExecute() or dbBind() an error message occures. Error in serialize(res_1.v): The connections argument is missing (without default).
For me it is important to know how to get the result vectors in the database and also how to get them out. So I hope, that you can help me.
Many thanks in advance!
My Code:
# needed packages for this script
# install.packages("sqldf") # install this package if necessary
library(sqldf)
# connection to the database
db=dbConnect(SQLite(), ":memory:")
# creation of the database table
dbSendQuery(conn = db,
"CREATE TABLE IF NOT EXISTS data_storage
(ID INTEGER,
data BLOB,
PRIMARY KEY (ID))")
# calculations
# ....
# the first result vector
res_1.v=seq(from=1,to=10,by=1)
# the second result vector
res_2.v=seq(from=5,to=7,by=0.1)
# filling the data_storage table with the result vectors (in two rows)
### here an error occures
dbGetQuery(db, 'INSERT INTO data_storage VALUES (1,:blob)', params = list(blob = list(serialize(res_1.v)))) # the first row with res_1.v
dbGetQuery(db, 'INSERT INTO data_storage VALUES (2,:blob)', params = list(blob = list(serialize(res_2.v)))) # the second row with res_2.v
# getting the serialized result vector out of the database
# and converting them again to the result vector res_1.v respectively res_2.v
#######################################
### the still missing code sequence ###
#######################################
# close the connection
dbDisconnect(db)
Thanks to the two commenter Marius and Tim Biegeleisen and some time of trial and error I found a solution...
In the first part of code has changed nothing
# needed packages for this script
# install.packages("sqldf") # install this package if necessary
library(sqldf)
# connection to the database
db=dbConnect(SQLite(), ":memory:")
# creation of the database table
dbSendQuery(conn = db,
"CREATE TABLE IF NOT EXISTS data_storage
(ID INTEGER,
data BLOB,
PRIMARY KEY (ID))")
# calculations
# ....
# the first result vector
res_1.v=seq(from=1,to=10,by=1)
# the second result vector
res_2.v=seq(from=5,to=7,by=0.1)
Now the second part of code, where I changed, added and completely appended some lines of code...
# filling the data_storage table with the result vectors (in two rows)
### here you can/must use dbExecute() as suggested by Marius
### and in list(serialize(res_1.v,NULL)) the connection NULL is important
dbExecute(db, 'INSERT INTO data_storage VALUES (1,:blob)', params = list(blob = list(serialize(res_1.v,NULL)))) # the first row with res_1.v
dbExecute(db, 'INSERT INTO data_storage VALUES (2,:blob)', params = list(blob = list(serialize(res_2.v,NULL)))) # the second row with res_2.v
# reading out the content of table data_storage
dbReadTable(db,"data_storage")
# It's nearly the same - reading out the content of data_storage
dbGetQuery(db,'SELECT * FROM data_storage')
dbGetQuery(db,'SELECT * FROM data_storage')[,1] # the content of the first column
dbGetQuery(db,'SELECT * FROM data_storage')[,2] # the content of the second column - this is a BLOB
# get the result vector with its original entries
### and unlist() the BLOB entry
### and finally unserialize the unlisted BLOB entry
step_1=unlist(dbGetQuery(db,'SELECT * FROM data_storage')[1,2]) # here you must adjust the row index
step_2=unserialize(step_1)
# for control of equality
### step_2 is the converted res_1.v to BLOB and the reconverted
### so the outcome of identical() is TRUE
identical(res_1.v,step_2)
# close the connection
dbDisconnect(db)