Possible Encoding Issue with BinaryStream

General
1

I have create the code below to read a jpeg file and store it in a SQL Server table via a stored procedure. The the code works if the associated file does not contain any Null characters, specially x0000. It appears that the binarystream (or ADO) is interpreting the stream as null terminated string. I’m using the SQLOLEDB provider to connect to the database.

I’ve tried using the binarystream with no encodings and other encodings, but UTF-16 appears to provide the best results except for the null issue.
Has anyone run across this before? Any possible solutions?

– Rob

  Dim f as FolderItem = GetOpenFolderItem(FileTypes1.All)
  Dim b As BinaryStream = BinaryStream.Open(f, False)
  b.littleEndian = true
  
  Dim mCmd as New OLEObject("ADODB.Command")
  mCmd.ActiveConnection = app.cnDatabase
  mCmd.CommandText = "dbo.myStoreProc"
  mCmd.CommandType = 4          
  mCmd.parameters.refresh
  
  mCmd.Parameters("@officecode").Value =txtOfficeCode.Text
  mCmd.Parameters("@rackid").Value =txtRackID.Text
  mCmd.Parameters("@pictdesc").Value =txtPictDesc.Text
  mCmd.Parameters("@pictimage").Value = b.read(b.length,Encodings.UTF16)
2

A JPEG file is binary data, it doesn’t use a text encoding. Try omitting the encoding parameter entirely.

3

So if I take the encoding out, the SQL Varbinary(max) column contains 8 bytes (0xFF00D800FF00E000) when it should have about 849K of info. The start of the original file looks like this: 0xFFD8FFE000104A4649460001010100B400B4. I’m not sure way the data is being represented as two bytes and why the data stops when x0000 is encountered.

– Rob

4

probably because in UTF-16 data 0x0000 is a NUL (end of string)
you may need to hex encode (or base64) the data rather than try & place it into a column using binary data

5

Norman, generally speaking, how would this be accomplished? I’m having trouble visualizing how to go from a file to base64 variable I can pass to the stored procedure.

6

something like

 dim s as string = b.read(b.length)
 s = Base64Encode(s,0)
 mCmd.Parameters("@pictimage").Value = s

“string” in this usage is “bucket of bytes” not “textual data”

7

I gave the Base64Encoding a try. Unfortunately, the Varbinary(max) field on the server is 2GB. It should be about 1.1MB.

8

Can you store it somehow as binary instead of base64. Base64 makes your data larger.