Deserializing image from DB-CodePudding

I'm trying to store an image into a database as a BLOB. So I need to serialize and deserialize it after retrieving back from DB. Testing out serializing/deserializing steps without DB works fine but deserializing the data from DB not. I've tried many different ways like the 3 shown, with as.data.frame and many more. Problem occurs with MariaDB and SQLite as well. Datatype in DB is BLOB for SQlite and LONGBLOB, MEDIUMBLOB for MariaDB. So I don't think this has anything to do with the used database as long as the type is BLOB. Thx for any help.

# 1. Loading image   serializing it
pb <- readBin(paste0("S:\\File.png"), what="raw", size = NA_integer_, endian = .Platform$endian, n=1e6)
pbSer <- serialize(pb, NULL)

# 2. Storing it in DB (Code is for MariaDB)
query <- paste0("REPLACE INTO Images (Name, Image) VALUES ", "(\"", "pbSer", "\", \"", list(pbSer), "\")")
dbExecute(con, query)

# 3. Retrieving data
rows <- dbGetQuery(con, paste0("SELECT * FROM Images WHERE Name = \"pbSer\""))

# 4. Unlisting and deserializing
pb2Ser <- unlist(rows$Image)
pb2 <- unserialize(pb2Ser, NULL) # -> Error in unserialize(pb2Ser, NULL) : unknown input format

# Two more versions I've tried
pb2Ser <- as.raw(unlist(rows$Image))
pb2 <- unserialize(pb2Ser, NULL) # -> Error in unserialize(pb2Ser, NULL) : unknown input format

pb2Ser = as.raw(as.integer(paste0('0x', pb2Ser)))
pb2 <- unserialize(pb2Ser, NULL) # -> Error in unserialize(pb2Ser, NULL) : unknown input format

CodePudding user response：

This worked for me (MariaDB):

library(DBI)

con = dbConnect(
  RMariaDB::MariaDB(), dbname="test", username = "root")

# CREATE TABLE Images (Name VARCHAR(256) not null primary key, Image LONGBLOB);
  
pb <- readBin(paste0("C:\\path\\to\\setosa.png"), 
              what="raw", size = NA_integer_, endian = .Platform$endian, n=1e6)

pbSer <- serialize(pb, NULL)

# 2. Storing it in DB (Code is for MariaDB)
query <- paste0("REPLACE INTO Images (Name, Image) VALUES (?, ?)")
dbExecute(con, query, params = list("pbSer", list(pbSer)))

# 3. Retrieving data
rows <- dbGetQuery(con, paste0("SELECT * FROM Images WHERE Name = \"pbSer\""))

# 4. Unlisting and deserializing
pb2Ser <- unlist(rows$Image)

pb2 <- unserialize(pb2Ser, NULL) # OK

writeBin(pb2, paste0("C:\\path\\to\\setosa2.png"),
               endian = .Platform$endian)

# files are the same.