SQL 将 varbinary 数据保存到磁盘的脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4056050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Script to save varbinary data to disk
提问by SFun28
I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the bytes to disk (one file per row?) I'm sure this has been asked a thousand times before, but Googling comes up with mostly .net solutions. I want an SQL solution.
我有一些 varbinary 数据存储在 MS Sql Server 2005 的表中。有没有人有将查询作为输入的 SQL 代码（假设查询保证返回单列 varbinary）并将字节输出到磁盘（一个文件每行？）我确定这之前已经被问过一千次了，但是谷歌搜索主要是 .net 解决方案。我想要一个 SQL 解决方案。
回答by SFun28
The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.
BCP 方法对我不起作用。它写入磁盘的字节不能反序列化回我存储的 .net 对象。这意味着磁盘上的字节不等于存储的字节。也许 BCP 正在编写某种标题。我不知道。
I found the following code hereat the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.
我发现下面的代码在这里在文章底部。它工作得很好！尽管它用于存储的 BMP 图像，但它适用于任何 varbinary。
DECLARE @SQLIMG VARCHAR(MAX),
@IMG_PATH VARBINARY(MAX),
@TIMESTAMP VARCHAR(MAX),
@ObjectToken INT
DECLARE IMGPATH CURSOR FAST_FORWARD FOR
SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations
OPEN IMGPATH
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'
PRINT @TIMESTAMP
PRINT @SQLIMG
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM IMGPATH INTO @IMG_PATH
END
CLOSE IMGPATH
DEALLOCATE IMGPATH
回答by Atron Seige
I am adding this to build on JohnOpincar's answer, so that others who want to use LinqPad can get a working solution faster.
我在JohnOpincar's answer 的基础上添加了这个，以便其他想要使用 LinqPad 的人可以更快地获得有效的解决方案。
/*
This LinqPad script saves data stored in a VARBINARY field to the specified folder.
1. Connect to SQL server and select the correct database in the connection dropdown (top right)
2. Change the Language to C# Program
3. Change "Attachments" to the name of your table that holds the VARBINARY data
4. Change "AttachmentBuffer" to the name of the field that holds the data
5. Change "Id" to the unique identifier field name
6. Change "1090" to the identity of the record you want to save
7. Change the path to where you want to save the file. Make sure you choose the right extension.
Notes: Windows 10 may give you "Access Denied" error when trying to save directly to C:\. Rather save to a subfolder.
*/
void Main()
{
var context = this;
var query =
from ci in context.Attachments
where ci.Id == 1090
select ci.AttachmentBuffer
;
byte[] result = query.Single().ToArray();
File.WriteAllBytes(@"c:\DEV\dumpfile.xlsx", result);
Console.WriteLine("Done");
}
回答by Dustin Laine
You can use BCP, not T-SQL, but works well.
您可以使用 BCP，而不是 T-SQL，但效果很好。
BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.txt" -T
回答by Sean Zhu
I know it's an old post, but I figured out why the following doesn't work and how to fix it:
我知道这是一个旧帖子，但我想出了为什么以下不起作用以及如何解决它：
BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.JPG" -T -N
The reason is bcp put Prefix Length at the very beginning of the file. It is either 4 bytes or 8 bytes, depends on data type of FileContent column (text, ntext, image: 4 varchar(max), varbinary(max) : 8 Refer to https://msdn.microsoft.com/en-us/library/ms190779.aspx)
原因是 bcp 将 Prefix Length 放在文件的最开头。它是 4 字节还是 8 字节，取决于 FileContent 列的数据类型（text、ntext、image: 4 varchar(max)、varbinary(max) : 8 请参阅https://msdn.microsoft.com/en-us /library/ms190779.aspx)
Use a binary editor, like the one in Visual Studio, to remove the prefix bytes, and everything runs perfectly. :-)
使用二进制编辑器，如 Visual Studio 中的编辑器，删除前缀字节，一切运行完美。:-)
回答by JohnOpincar
If you have linqpad, this works:
如果你有 linqpad，这有效：
void Main()
{
var context = this;
var query =
from ci in context.Images
where ci.ImageId == 10
select ci.Image
;
var result = query.Single ();
var bytes = Convert.FromBase64String(result);
File.WriteAllBytes(@"c:\image.bmp", bytes);
}
回答by Vilhelm
Just an alternative. You can use the freeware Toad for SQL server and save directly from the editor.
只是一个替代方案。您可以使用免费软件 Toad for SQL server 并直接从编辑器中保存。
You can go to their website https://www.toadworld.comand get the freeware there or a 30 day trial of the full version. Its under Download and pick Toad for SQL server.
您可以访问他们的网站https://www.toadworld.com并在那里获取免费软件或完整版的 30 天试用版。它在下载并为 SQL 服务器选择 Toad 下。
You do a regular select statement in Toad on the line that has the image you want to save. When you see the results you can click on the byte image column and on the right you see a PDF tab if this is a PDF document or on the left you see a Image tab. When you click the tab you can see the save logo at the bottom to save the image or file.
在 Toad 中，您在包含要保存的图像的行上执行常规选择语句。当您看到结果时，您可以单击字节图像列，如果这是 PDF 文档，则在右侧您会看到一个 PDF 选项卡，或者在左侧您会看到一个图像选项卡。当您单击选项卡时，您可以在底部看到用于保存图像或文件的保存徽标。
回答by Alberto Martinez
SQL is designed to work with database objects, so from it's point of view anything else doesn't exists. Sure, there are extended procedures like xp_cmdshellthat allow you interact with the operating system, but they are proprietary extensions and not part of T-SQL.
SQL 旨在与数据库对象一起使用，因此从它的角度来看，其他任何东西都不存在。当然，有类似的扩展过程xp_cmdshell允许您与操作系统交互，但它们是专有扩展而不是 T-SQL 的一部分。
Maybe the closest approach would be using the FILESTREAM attribute for binary types of SQL Server 2008, which allow storing some columns directly as files in a folder instead of using the database:
也许最接近的方法是对 SQL Server 2008 的二进制类型使用 FILESTREAM 属性，它允许将某些列直接作为文件存储在文件夹中，而不是使用数据库：
Notethat the FILESTREAM storage is designed for maintain large files out of the database in order to increase performance, and not for allowing direct access to files (i.e. T-SQL still doesn't have the concept of a filesystem). I my opinion, direct access to the filesystem from SQL will defeat some of the purposes of a database (mainly having data stored in a structured way).
请注意，FILESTREAM 存储旨在维护数据库外的大文件以提高性能，而不是允许直接访问文件（即 T-SQL 仍然没有文件系统的概念）。我认为，从 SQL 直接访问文件系统会破坏数据库的某些目的（主要是以结构化方式存储数据）。
So I would recommend following the advice of Dustin and use a tool like BCP or any other data dumper.
因此，我建议遵循 Dustin 的建议并使用 BCP 或任何其他数据转储程序之类的工具。
