none
Using SQLBytes with a non-seekable stream RRS feed

  • Question

  • I have a table with a varbinary(MAX) field. I am trying to populate it with data that is received by an HttpListener. The HTTP POST request to the listener contains the data and I have an HttpListenerRequest.InputSteam. So far, so good.

     

    using SqlCommand, I create an INSERT statement with the column value as a parameter and execute:

     

    using (SqlCommand cmd = new SqlCommand("INSERT INTO MyTable (MyField) VALUES (@MyField)", conn)) {

    SqlParameter p = new SqlParameter("MyField", System.Data.SqlDbType.VarBinary, int.MaxValue);

    p.Value = new System.Data.SqlTypes.SqlBytes(rq.InputStream);

    cmd.Parameters.Add(p);

    cmd.ExecuteNonQuery();

    }

     

    When the statement is executed I get this exception:

     

    System.NoSupportedException : This steam does not support seek operations.

     

    Seems like 'somebody' called getter on the Stream.Length property with the predictable result.

     

    I just want to stream it. I don't want to have to create some intermediate byte array. Is there any way to make this work?

     

    Thanks,

    Paul.

     

    Friday, August 24, 2007 7:43 PM

All replies

  • I assume that the rq variable is of HTTP stream which is in common by definition non-seekable. You will have to transfer the stream data into a seekable type like MemoryStream which is able to get definte information about the stored data.


    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Saturday, August 25, 2007 10:31 PM
  • Thanks for your response.

     

    The 'rq' variable is of type HttpListenerRequest. rq.InputStream is the HTTP steam and as I said it is non-seekable. I don't understand why the infrastructure needs the length which implies that the stream has been read entirely into memory. One of the biggest advantages of using a stream is that you can interact with it a byte or buffer at a time and therefore it doesn't matter if the data is 1 Kb or 100 Gb. It matters a whole lot, however, if you have to read the entire thing into memory just to send it off to some other data store.

     

    Seems to me the varbinary(MAX) and SQLBytes() pattern is a broken design if it requires the length...

     

    Just my $0.02.

    Paul.

    Monday, August 27, 2007 11:55 AM
  • Thinking pragmatically, there are really only two ways to reresent arbitrarily long data blocks.  Either you allocate a subset of the possible byte patterns in the block out specifically to act as a terminator of the data (e.g. the 'null' terminator in C strings) or you know the length in advance.  For arbitrary binary data, it is impossible to specify a terminator, as you cannot guarantee that any preselected byte sequence will be unique to the tail of an arbitrary block of bytes.  That being said, the length is most useful at the beginning of the block so that it can be interpreted before the data is read.  If the length was sent after the block, it would be useless since it would be impossible to tell where the block ends to read the length.  Most likely, streamable types send their size first, then the bytes so you don't necessarily have to store the whole thing.  I suspect that HTTP streams use the terminator strategy, since they do not truly consist of an arbitrary binary blob, and so they are not seekable because the length cannot be determined a-priori.

     

    Hope that makes sense,

     

    John

    Monday, August 27, 2007 5:54 PM
  •  

    John,

     

    A stream defines its end. When Read() returns 0 bytes, you've read all the data. I suppose analagous to your C string terminator scenario.

     

    I used Reflector to quickly look at SqlBytes() and it seems to me that although it accepts a Stream as one of overloaded constructors, it internally wants to have all the data in a byte[] array.

     

    My point here is that although it is all very well and nice that a SQL varbinary(max) column can contain arbitrarily sized data, because of the implementation of SqlBytes(), it is impossible to insert more data than the amount of memory you can possibly allocate for the byte[] array in the client. My opinion is that SqlBytes() (or some other implementation detail in the SqlClient stack) is fundamentally broken by the fact that it cannot stream the data even though it will accept Stream as one of its constructors. Certainly nothing in the documentation indicates the Stream needs to be seekable and the constructor doesn't throw an ArgumentException for a non-seekable stream. Yet, no seek on the streams means .Length fails and the statement execution blows up.

     

    Perhaps there is some fundamental reason for this, but the result is that it effectively restricts the usefulness of varbinary(MAX) to the memory footprint of the client.

     

    Paul.

     

    Monday, August 27, 2007 6:05 PM
  • Fyi, it is not impossible to specify terminators in arbitrary data.  There are all sorts of networking protocols that do this.  As long as both the writer and reader (sender and receiver) agree on the terminator, the sender need only prepend any occurances of the selected character with the character again to act as an escape sequence.
    Sunday, August 15, 2010 9:31 PM