How to store .pdf file in Sqlite using R programming

Question

I am creating an application on R-Shiny where i am taking inputs from users which i am storing into SQLite database in backed.But my concern is on my form i have one upload file input which basically accepts files like (.PDF,.jpeg,.png)(screenshot below).

If users upload any file using that i want that file to be stored in my SQLite database table for further use.But i am not aware of how to achieve this using r programming.

Any help would be appreciated.

Answer 1

You can store objects (any R output which is not tabular, model's output for example) as BLOBs in SQLite, in R use serialize/unserialize for this, but before you need to read the raw PDF using readBin, here an example :

path <- file.path(Sys.getenv("R_DOC_DIR"), "NEWS.pdf")

# see the PDF
browseURL(path)

# Read raw PDF
pdf <- readBin(con = path, what = raw(), n = file.info(path)$size)


library(RSQLite)

# Connect to DB
con <- dbConnect(RSQLite::SQLite(), ":memory:")

# Serialize raw pdf
pdf_serialized <- serialize(pdf, NULL)
# Create a data.frame
df <- data.frame(pdfs = I(list(pdf_serialized)))

# Write the table in database
dbWriteTable(conn = con, name = "mytable", value = df, overwrite = TRUE)


# Read your table
out <- dbReadTable(conn = con, name = "mytable")

# unserialize
pdf_out <- unserialize(out$pdfs[[1]])

# Write the PDF in a temporary file
tmp <- tempfile(fileext = ".pdf")
writeBin(object = pdf_out, con = tmp)

# open it
browseURL(tmp)

Answer 2

Similar to Victorp's answer, you can also base64 encode the data and store it as text in a database:

file <- "mypdf.pdf"
con <- dbConnect(...)
table <- "mypdf_table"

bin <- readBin(file, raw(), n = file.size(file))
enc_data <- base64enc::base64encode(bin)
  
dd <- data.frame(
  file = file,
  data = enc_data 
)
  
DBI::dbWriteTable(con, table, dd, append = TRUE)