如何在 R 中将图像上传到 SQL Server
我正在创建一些图表，我想将其更新到数据库表中。我遵循的过程是：
- 将图形创建为 png/jpeg 文件。
- 将该文件作为二进制向量读取
- sqlUpdate
我的步骤 2 和 2 的代码3：
pngfile <- file(<filename>, "rb")
N <- 1e6
repeat{
pngfilecontents <- readBin(pngfile, what="raw", n=N)
if(length(pngfilecontents) == N) N <- 5 * N else break
}
close(pngfile)
数据库中有一个表 df_DemandPatternMaster，其主键 DemandPatternID，在 pngFile 字段中具有 NULL 值的适当记录。
update.query <- "update df_DemandPatternMaster set "
update.query <- paste( update.query, " pngFile = '", serialize(pngfilecontents, NULL) , "' where DemandPatternID = ", , sep="")
d <- sqlQuery(connection, update.query)
我最终只插入一个字节的数据。原因似乎是粘贴看到序列化向量并创建一个带有前缀 & 的向量。后缀文本。 我也尝试过直接传递 png 文件句柄
pngfile <- file(<filename>, "rb")
update.query <- paste( update.query, " pngFile = '", pngfile, "' where DemandPatternID = ", , sep="")
这也失败了。
请指教。
I am creating some graphs which I want to update into a database table. The procedure I am following is:
- create the graphs as a png/jpeg file.
- Read that file as a binary vector
- sqlUpdate
My code for steps 2 & 3:
pngfile <- file(<filename>, "rb")
N <- 1e6
repeat{
pngfilecontents <- readBin(pngfile, what="raw", n=N)
if(length(pngfilecontents) == N) N <- 5 * N else break
}
close(pngfile)
There is a table df_DemandPatternMaster in the database with primary key DemandPatternID, with appropriate record in place with NULL value in pngFile field.
update.query <- "update df_DemandPatternMaster set "
update.query <- paste( update.query, " pngFile = '", serialize(pngfilecontents, NULL) , "' where DemandPatternID = ", , sep="")
d <- sqlQuery(connection, update.query)
I end up inserting only a byte of data. The reason it seems is that paste sees the serialized vector and creates a vector with the prefix & suffix text.
I have also tried passing the png file handle directly
pngfile <- file(<filename>, "rb")
update.query <- paste( update.query, " pngFile = '", pngfile, "' where DemandPatternID = ", , sep="")
This also fails.
Please advise.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
也许如果您将 pngfilecontents 向量折叠成单个字符串。像这样的东西：
Perhaps if you collapse the pngfilecontents vector into a single string. Something like:
我还没有在数据库中尝试过这一点，但最近在序列化到文本文件或从文本文件序列化时遇到了一些挑战。这是我问的可能相关的问题。您是否尝试过将 ascii=T 开关与序列化一起使用？然后尝试使用和不使用 rawToChar。
我没有一个简单的环境来测试您的代码，但我对您的想法很感兴趣。我正在编写一些代码，最终将序列化对象并将它们放入数据库中。我只是还没到那一步。
I have not tried this with a database, but I had some challenges recently when serializing to/from a text file. Here's a question I asked that might be related. Have you tried using the ascii=T switch with serialize? Then try it both with and without rawToChar.
I don't have an easy environment to test your code, but I am interested in what you come up with. I'm working on some code where I will eventually be serializing objects and putting them in a DB. I'm just not to that point yet.