storing a vector in a relational database

Question

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)

You probably need `dbExecute()` instead of `dbGetQuery()` if you're trying to insert. — Marius, Jun 18 '18 at 06:20
@Marius with `dbExecute()` the error occures too. The connection is missing and in brackets 'without default' — tueftla, Jun 18 '18 at 06:27
@tueftla If you want to use a statement, then I think you need `dbSendStatement`. `dbExecute` is for straight DML commands with no parameters. — Tim Biegeleisen, Jun 18 '18 at 06:27

Tim Biegeleisen · Answer 1 · 2018-06-18T06:30:06.173

0

Perhaps your syntax has a problem. This RSQLite documentation page uses dbSendStatement to build a prepared statement for doing a DML command:

rs <- dbSendStatement(db, 'INSERT INTO data_storage VALUES (1, :blob)')
dbBind(rs, param = list(blob = serialize(res_1.v)))
dbGetRowsAffected(rs)
dbClearResult(rs)

This answer assumes that the API will correctly know how to bind a BLOB into the statement.

edited Jun 18 '18 at 06:30

answered Jun 18 '18 at 06:24

Tim Biegeleisen

502,043
27
286
360

`dbBind()` causes also an error. The connection is missing and in brackets 'without default' – tueftla Jun 18 '18 at 06:35
Then maybe there is some issue with your DB connection object. – Tim Biegeleisen Jun 18 '18 at 06:36
when typing the code in my post it works everything fine until I want to store the result vector. With `dbListFields(db,"data_storage")` the two columns ID and data are shown... – tueftla Jun 18 '18 at 06:39
@tueftla That doesn't mean that there isn't a problem. You might not see a problem with the connection until you actually go to use it. But, I'm not an expert in RSQLite, just wanted to point out what I think the correct syntax is for using DML prepared statements. – Tim Biegeleisen Jun 18 '18 at 06:40

tueftla · Accepted Answer · 2018-06-18T13:54:31.593

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)

storing a vector in a relational database

2 Answers2