accessing binary (blob) data from SQL server

Hi,

I'm struggling a bit reading binary data from a database.

Basically, I have a column in our table that has the binary type, and
all I want to do is display that binary data in HEX. basically the table
looks like this:

binary_data binary(1024)
binary_length int

binary_length stores the number of bytes there are in the binary_data field.

However it seems difficult to say the least to accomplish this with
VBScript.

First I tried to read the data by using the a for loop like:

for a = 1 to binary_length
response.write Hex(Asc(Mid(DBRS.Fields("binary_data").value, a, 1)))
next

but that gives me wrong results for some data, and it doesn't seem right
to use the string function MID with binary data anyways.

Then, I played around with the stream object. I had some success with
that, but as it turns out I always had to save the stream to a file in
order to "activate" it (I know this sounds odd).

For example, I would successfully write the database field into the
stream with

stream.write DBRS.Fields("binary_data").value

and then read the data into a variable like

binaryData = stream.read

This however would not work, unless I use the .SaveToFile option before
the stream.read. I'm not sure why that is, but I can't always do that
and it seems odd anyway. I wasn't even using the file, but for some
reason I had to call that function.

Can somebody give me a hint on how to do something as simple as getting
binary data out of a table and display it? I don't want to save it as a
file or anything like that, just display it.


Thanks!

Re: accessing binary (blob) data from SQL server

Florian wrote:
> Hi,
>
> I'm struggling a bit reading binary data from a database.
>
> Basically, I have a column in our table that has the binary type, and
> all I want to do is display that binary data in HEX. basically the
> table looks like this:
>
> binary_data binary(1024)
> binary_length int
>
> binary_length stores the number of bytes there are in the binary_data
> field.
> However it seems difficult to say the least to accomplish this with
> VBScript.
>
> First I tried to read the data by using the a for loop like:
>
> for a = 1 to binary_length
> response.write Hex(Asc(Mid(DBRS.Fields("binary_data").value, a, 1)))
> next
>
> but that gives me wrong results for some data, and it doesn't seem
> right to use the string function MID with binary data anyways.
>
> Then, I played around with the stream object. I had some success with
> that, but as it turns out I always had to save the stream to a file in
> order to "activate" it (I know this sounds odd).
>
> For example, I would successfully write the database field into the
> stream with
>
> stream.write DBRS.Fields("binary_data").value
>
> and then read the data into a variable like
>
> binaryData = stream.read

The read method brings you to the end of the stream. You have to move back
to the beginning in order to utilize the stream. Use the Position property
to set the position to 0.

>
> This however would not work, unless I use the .SaveToFile option
> before the stream.read. I'm not sure why that is, but I can't always do
> that
> and it seems odd anyway. I wasn't even using the file, but for some
> reason I had to call that function.
>
> Can somebody give me a hint on how to do something as simple as
> getting binary data out of a table and display it?

Not sure why you think this is simple ... :-)


Check out the chunking methods in the ADO documentation at
msdn.microsoft.com/library. GetChunk is one of them, and I think you may
need to utilize it (I'm not sure 0 using the stream should work, but I've
never done this)

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: accessing binary (blob) data from SQL server

Hi Bob,

Thanks so much for the suggestion! This actually worked like a charm! No
need for the GetChunk etc. functions.

One thing I had utilize though was the MidB (binary version of Mid)
function! Now I'm doing

for i = 1 to LenOfBinary
Response.Write Hex(AscB(MidB(BinaryObject, i, 1)))
next

Asc & Mid didn't really work well with the binary object.


Thanks again ... this was quite a pain and I'm glad I finally got this
working ok!

Bob Barrows [MVP] wrote:
> Florian wrote:
>
>>Hi,
>>
>>I'm struggling a bit reading binary data from a database.
>>
>>Basically, I have a column in our table that has the binary type, and
>>all I want to do is display that binary data in HEX. basically the
>>table looks like this:
>>
>>binary_data binary(1024)
>>binary_length int
>>
>>binary_length stores the number of bytes there are in the binary_data
>>field.
>>However it seems difficult to say the least to accomplish this with
>>VBScript.
>>
>>First I tried to read the data by using the a for loop like:
>>
>>for a = 1 to binary_length
>>response.write Hex(Asc(Mid(DBRS.Fields("binary_data").value, a, 1)))
>>next
>>
>>but that gives me wrong results for some data, and it doesn't seem
>>right to use the string function MID with binary data anyways.
>>
>>Then, I played around with the stream object. I had some success with
>>that, but as it turns out I always had to save the stream to a file in
>>order to "activate" it (I know this sounds odd).
>>
>>For example, I would successfully write the database field into the
>>stream with
>>
>>stream.write DBRS.Fields("binary_data").value
>>
>>and then read the data into a variable like
>>
>>binaryData = stream.read
>
>
> The read method brings you to the end of the stream. You have to move back
> to the beginning in order to utilize the stream. Use the Position property
> to set the position to 0.
>
>
>>This however would not work, unless I use the .SaveToFile option
>>before the stream.read. I'm not sure why that is, but I can't always do
>>that
>>and it seems odd anyway. I wasn't even using the file, but for some
>>reason I had to call that function.
>>
>>Can somebody give me a hint on how to do something as simple as
>>getting binary data out of a table and display it?
>
>
> Not sure why you think this is simple ... :-)
>
>
> Check out the chunking methods in the ADO documentation at
> msdn.microsoft.com/library. GetChunk is one of them, and I think you may
> need to utilize it (I'm not sure 0 using the stream should work, but I've
> never done this)
>
> Bob Barrows
>