重构在 SQL 数据库中存储为 RAW 的 PNG 文件

Question

我正在努力从 SQL 数据库 (Windows SQL Server) 编写一份报告，该报告需要特定人员在报告提交给客户之前签署。我们希望有一个系统，这些人可以在数据库中授权他们的签名，然后我们可以使用存储在数据库中的他们签名的图像，并将其放在 LaTeX 生成的报告中。

签名图像创建为 PNG，然后存储在数据库中类型为 varbinary 的字段中。为了在报告中使用签名，我需要将 PNG 重构为一个我可以在 LaTeX 中使用 \includegraphics 的文件。

不幸的是，我似乎无法从数据库中重新创建 PNG。由于我无法张贴签名，我们将使用下图作为示例。

有了我电脑上的这张图片，我就可以读取原始文件，将其写入不同的文件，并在打开新文件时得到相同的图片。

#* It works to read the image from a file and rewrite it elsewhere
pal <- readBin("C:/[filepath]/ColorPalette.png",
          what = "raw", n = 1e8)
writeBin(pal,
         "C:/[filepath]/colors.png",
         useBytes=TRUE)

现在，我已将相同的图像保存到数据库中，并使用 RODBC，我可以像这样提取它:

#*** Capture the raw from the database
con <- odbcConnect("DATABASE")
Users <- sqlQuery(con, "SELECT * FROM dbo.[User]")

db_pal <- Users$Signature[Users$LastName == "MyName"]

#*** Write db_pal to a file, but the image won't render
#*** Window Photo Viewer can't open this picture because the file appears to be damaged, corrupted, or is too large (12KB)

writeBin(db_pal[[1]],
         "C:/[filename]/db_colors.png",
         useBytes=TRUE)

对象pal 和db_pal 在this Gist 中定义。 (它们太长了，放不下这里的允许空间)

注意:db_pal 是一个原始向量的列表。此外，它明显不同于原始向量 pal

> length(pal)
[1] 2471
> length(db_pal[[1]])
[1] 9951

关于我可能需要做些什么才能从数据库中取出这张图片有什么想法吗？

Answer 1

好吧，我们已经找到了解决方案。通过 RODBC 返回的原始向量与 SQL 数据库中的内容不匹配。在管道的某个地方，来自 SQL 的 varbinary 对象被扭曲了。我不确定为什么或如何。但是this answer to a different problem启发我们重铸变量。一旦我们重铸它们，我们就能看到正确的表示。

下一个问题是我们所有的图片都超过8000字节，而RODBC一次只允许8000个字符。所以我不得不摸索着解决这个问题。下面的代码执行以下操作:

1. 确定图像文件中的最大字节数
2. 创建一组变量 (ImagePart1, ..., ImagePart[n])，根据需要将图像分成多个部分，每个部分的最大长度为 8000。
3. 在数据库中查询所有图像。
4. 将图像部分组合成一个对象
5. 将图像写入本地文件。

实际代码

library(RODBC)

lims <- odbcConnect("DATABASE")

#* 1. Determine the largest number of bytes in the largest image file
ImageLength <- sqlQuery(lims, 
                            paste0("SELECT MaxLength = MAX(LEN(u.Image)) ",
                                   "FROM dbo.[User] u"))

#* Create a query string to make a set of variables breaking
#* the images into as many parts as necessary, each with 
#* max length 8000
n_img_vars <- ImageLength$MaxLength %/% 8000 + 1

start <- 1 + 8000 * (0:(n_img_vars - 1))
end <- 8000 + 8000 * (0:(n_img_vars - 1))

img_parts <- paste0("ImagePart", 1:n_img_vars, 
                    " = CAST(SUBSTRING(u.Image, ", start,
                    ", ", end, ") AS VARBINARY(8000))")

full_query <- paste0("SELECT u.OID, u.LastName, u.FirstName,\n",
                     paste0(img_parts, collapse =",\n"), "\n",
                     "FROM dbo.[User] u \n",
                     "WHERE LEN(u.Image) > 0")

#* 3. Query the database for all the images
Images <- sqlQuery(lims, full_query)

#* 4. Combine the images parts into a single object
Images$full_image <- 
  apply(Images[, grepl("ImagePart", names(Images))], 1, 
        function(x) do.call("c", x))

#* 5. Write the images to a local file
for(i in seq_len(nrow(Images))){
  DIR <- "[FILE_DIR]"
  FILENAME <- with(Images, paste0(OID[i], "-", LastName[i], ".png"))
  writeBin(unlist(Images$full_image[i]),
           file.path(DIR, FILENAME))
}