如何使用简单的插入语句将二进制文件数据插入二进制 SQL 字段? [英] How can I insert binary file data into a binary SQL field using a simple insert statement?

问题描述

我有一个带有 image 列的表的 SQL Server 2000.

I have a SQL Server 2000 with a table containing an image column.

如何通过指定文件路径将文件的二进制数据插入到该列中?

How do I insert the binary data of a file into that column by specifying the path of the file?

CREATE TABLE Files
(
  FileId int,
  FileData image
)

推荐答案

如果您的意思是使用文字，您只需创建一个二进制字符串:

If you mean using a literal, you simply have to create a binary string:

insert into Files (FileId, FileData) values (1, 0x010203040506)

您将拥有一个包含六字节值的 FileData 字段记录.

And you will have a record with a six byte value for the FileData field.

您在注释中指出您只想指定文件名，而在 SQL Server 2000(或我知道的任何其他版本)中无法做到这一点.

You indicate in the comments that you want to just specify the file name, which you can't do with SQL Server 2000 (or any other version that I am aware of).

您需要一个 CLR 存储过程来在 SQL Server 2005/2008 或扩展存储过程中执行此操作(但我会不惜一切代价避免这样做，除非您必须这样做)，它采用文件名然后插入数据(或返回字节字符串，但可能很长).

You would need a CLR stored procedure to do this in SQL Server 2005/2008 or an extended stored procedure (but I'd avoid that at all costs unless you have to) which takes the filename and then inserts the data (or returns the byte string, but that can possibly be quite long).

关于只能从 SP/query 获取数据的问题，我会说答案是肯定的，因为如果你让 SQL Server 能够从文件系统中读取文件，你在什么时候做什么?您没有通过 Windows 身份验证连接，使用什么用户来确定权限?如果您以管理员身份运行该服务(上帝保佑)，那么您可以拥有不应被允许的权限提升.

In regards to the question of only being able to get data from a SP/query, I would say the answer is yes, because if you give SQL Server the ability to read files from the file system, what do you do when you aren't connected through Windows Authentication, what user is used to determine the rights? If you are running the service as an admin (God forbid) then you can have an elevation of rights which shouldn't be allowed.

这篇关于如何使用简单的插入语句将二进制文件数据插入二进制 SQL 字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！