SQL Server：服务器端blob游标？

i'd like to read binary data from a blob, using the Stream interface around it.

我想使用它周围的Stream接口从blob中读取二进制数据。

But i don't want the blob to have to be loaded entirely client side and stored in memory, or in a file.

但我不希望blob必须完全加载客户端并存储在内存或文件中。

i want the code that uses the blob to be able to seek and read, and only as much data that is needed to support seek/read is brought over the wire.

我希望使用blob的代码能够搜索和读取,并且只有支持搜索/读取所需的数据才能通过网络传输。

i.e. Pretend the blob is a 250MB Photoshop image. The thumbnailer code knows how to read the first 8 bytes of an image, recognize it's a PSD file, seek to the offset that will contain the 3k thumbnail, and read that.

即假装blob是一个250MB的Photoshop图像。缩略图代码知道如何读取图像的前8个字节,识别它是PSD文件,寻找包含3k缩略图的偏移量,并读取它。

So rather than trying to allocate 250MB of memory, or having to create a temporary file, and having to wait for 250MB to be brought over the wire: the hypothetical SQLServerBlobStreamServerCursor class knows how to data traffic to what which is actually asked for.

因此,不是试图分配250MB的内存,或者必须创建一个临时文件,而是必须等待250MB才能通过线路:假设的SQLServerBlobStreamServerCursor类知道如何将流量数据传输到实际要求的内容。

Research

HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET Which talks about being able to read, and write, in chunks. But the code is unreadable being cut off like that i can't stand it. i'll look at it later.

HOW TO:在ADO.NET和Visual Basic .NET中使用Chunking从BLOB列读取和写入文件这涉及能够以块的形式读取和写入。但代码是不可读的被切断,就像我无法忍受它。我稍后会看。

Also this guy mentioned a new SQL Server 2005 [column.Write()]3 T-SQL syntax to write data - could be used to write data in small chunks (to avoid consuming all your server's memory). Maybe there's a [column].Read() pseudo-method

此外,这家伙提到了一个新的SQL Server 2005 [column.Write()] 3 T-SQL语法来写数据 - 可用于以小块的形式写入数据(以避免消耗所有服务器的内存)。也许有一个[column] .Read()伪方法

Microsoft has an article: Conserving Resources When Writing BLOB Values to SQL Server

Microsoft有一篇文章:在将BLOB值写入SQL Server时节省资源

3 个解决方案

#1

With newer versions of SQL Server you can just use plain SQL with the SUBSTRING() function on binary data types as well as text. See http://msdn.microsoft.com/en-us/library/ms187748.aspx

对于较新版本的SQL Server,您可以在纯二进制数据类型和文本上使用带有SUBSTRING()函数的纯SQL。请参阅http://msdn.microsoft.com/en-us/library/ms187748.aspx

To get the size of the image:

要获得图像的大小:

select datalength(blobcolumn) from myimages where imgid = 12345;

To read the first 8 bytes:

要读取前8个字节:

select substring(blobcolumn, 1, 8) from myimages where imgid = 12345;

To read 877 bytes, offset 115000 through 115876:

要读取877个字节,偏移115000到115876:

select substring(blobcolumn, 115001, 877) from myimages where imgid = 12345;

Remember that the substring function is based on a 1-offset, not 0.

请记住,子字符串函数基于1偏移量而不是0。

If you care about the column potentially changing between reading parts of it, you can put all the different selects within a transaction.

如果您关心的列可能会在读取部分之间发生变化,那么您可以在事务中放置所有不同的选择。

This is untested by me, but older versions of MS SQL Server apparently require the use of the (now deprecated) READTEXT verb and TEXTPTR() function. Such as:

这是我未经测试的,但旧版本的MS SQL Server显然需要使用(现已弃用)READTEXT动词和TEXTPTR()函数。如:

select textptr(blobcolumn) from myimages where imgid = 12345;

grab the returned pointer value, say PTR, and use it in the subsequent queries:

获取返回的指针值,比如PTR,并在后续查询中使用它:

readtext blobcolumn @PTR 115001 887

#2

You can consider using new Sql Server 2008 FileStream feature for this: http://msdn.microsoft.com/en-us/library/cc645940.aspx

您可以考虑使用新的Sql Server 2008 FileStream功能:http://msdn.microsoft.com/en-us/library/cc645940.aspx

#3

You will want to use ADO.NET's SqlDataReader object with the SequentialAccess CommandBehavior. This will allow you to define a buffer size and read the data in chunks.

您将希望将ADO.NET的SqlDataReader对象与SequentialAccess CommandBehavior一起使用。这将允许您定义缓冲区大小并以块的形式读取数据。

See this article: http://msdn.microsoft.com/en-us/library/87z0hy49(VS.71).aspx

请参阅此文章:http://msdn.microsoft.com/en-us/library/87z0hy49(VS.71).aspx

byte[] outbyte;
int bufferSize = 8;
SqlDataReader myReader = myCmd.ExecuteReader(CommandBehavior.SequentialAccess);
...
long returnBytes = myReader.GetBytes(1, 0, outbyte, 0, bufferSize);

#1