Storing and retrieving files/images in a database from Shiny app

Awesome reprex!! Thank you!! That made my life so much easier :smiley: And now we get a functional example!! (I think). I have to admit my testing is pretty minimal. I think we're super close though! This is exciting stuff!

You were trying to write a vector to the database, and RPostgres was complaining. Postgres wants a string representation of the hex (prefixed with \x), which I just did with a little paste(..., collapse = ""). I'm sure there are other ways to marshal all of this w/ string manipulation, hex / binary conversion, etc. It looks like it's working though!!

  
library(RPostgres)
library(hexView)
library(readr)

con <- dbConnect(RPostgres::Postgres(),host="localhost",
                 port=5432,
                 user= "postgres",
                 password="postgres",
                 dbname="postgres")

x<-c("a,b,c","d,e,f")
write_lines(x,path="sample_text.txt")
z<-readRaw('sample_text.txt')
writeBin(z$fileRaw,'test_output.txt') #confirm identical file can be created

my_file_name<-"sample_text.txt"
my_file_type<-"txt"
my_report_id<-"c9f19a27-ff23-44a5-8217-40dc95a1594e"
my_file<-z$fileRaw

my_file
#>  [1] 61 2c 62 2c 63 0a 64 2c 65 2c 66 0a


res <-
  dbExecute(
    con,
    "INSERT INTO file_upload (file_name,file_type,report_id,file) VALUES ( $1,$2,
$3,$4)",
    list(my_file_name,my_file_type,my_report_id,paste0("\\x", paste(my_file, collapse = "")))
  )

get_res <- dbGetQuery(con, "SELECT * from file_upload")

identical(get_res$file[[1]], my_file)
#> [1] TRUE

Created on 2019-04-22 by the reprex package (v0.2.1)

3 Likes