How to transfer binary data from Server side to the client side through ASP Page ?

Hi,
I am designing a 4-tier application. It is like this.
Client - Business Logic Client Side (UI Validation) - ASP Page - Business
Logic Server Side (Database related) - SQL server database.
In this scenario, As of now I am passing a XML string from Server side
(Using ADO 2.6) to the client side through ASP. But I believe, compared to
transfer of huge XML strings from the server side to the client side,
transfer of binary data would be fast.
Can any one tell me, how to transfer binary data from Server side to the
client side through ASP Page ?
Thanks and Regards,
Peri
"Peri" <lperi@.CSPL.com> wrote in message
news:u4SyKPxPEHA.3220@.TK2MSFTNGP09.phx.gbl...
[snip]
> Can any one tell me, how to transfer binary data from Server side to the
> client side through ASP Page ?
You would need to figure out what the binary data would look like and how to
transfer it. That is the beauty of XML, you don't have to do either (but you
do pay the package tax).
Bryant

How to tell if there is something in a binary field

I tried to upload a pdf file into a binary field using asp upload. I
cannot tell if there is anything in the field using query analyzer, and I wa
s
wondering what shows in the grid when something exists there. It is just
showing as <binary>Don't use a grid for this kind of thing.
SELECT DATALENGTH(col) FROM Table
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Linda Lalewicz" <LindaLalewicz@.discussions.microsoft.com> wrote in message
news:3208A836-96ED-406C-BF8A-305946E6BFE7@.microsoft.com...
>I tried to upload a pdf file into a binary field using asp upload. I
> cannot tell if there is anything in the field using query analyzer, and I
> was
> wondering what shows in the grid when something exists there. It is just
> showing as <binary>|||I also found that a select for that field would return something if there wa
s
something in there, if not it came back null.
Now I need to figure out why the clues that you gave me yesterday don't seem
to show me my pdf file. I just see a blank page with no errors, no pdf. :
(
wahhhh
"Aaron [SQL Server MVP]" wrote:

> Don't use a grid for this kind of thing.
> SELECT DATALENGTH(col) FROM Table
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Linda Lalewicz" <LindaLalewicz@.discussions.microsoft.com> wrote in messag
e
> news:3208A836-96ED-406C-BF8A-305946E6BFE7@.microsoft.com...
>
>sql

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store file attachments (text and binary).

I need to store several different types of documents (text, MS Word, PDF, etc.) in SQL Server 2005. What is the best way to store file attachments that have different MIME types?

Currently I'm using a table with a varbinary(max) column which works for binary files but not for text files. Do I need to have multiple columns in the table for different file types? Thanks in advance for your help.

CREATE TABLE dbo.[CM_Attachment] (
[Id] int IDENTITY (1, 1) NOT NULL ,
[Change_Request_Id] int NOT NULL ,
[Attachment] varbinary(max) not null,
[Created_Date] datetime NOT NULL DEFAULT GetDate() ,
[Created_By] varchar(30) NOT NULL ,
[Modified_Date] datetime NOT NULL ,
[Modified_By] varchar(30) NOT NULL
)
GO

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB) -- Does not work
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob) -- Works
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob) -- Works
as ChangeRequestAttachment

I believe the datatype to use is "image" and it will store any file type.

Check out the link below:
http://www.aspnettutorials.com/tutorials/database/Save-Img-ToDB-Csharp.aspx

|||

2 quotes from "Microsoft SQL SERVER 2005 Implementation and maintenance Training Kit":

[

Binary Data Types

The most popular data type within this group is the image data type. This data type has an unfortunate name; it is not used exclusively to store images, such as a library of pictures from a recent vacation. Although you can store pictures in an image data type, you can also use this data type to store Word, Excel, PDF, and Visio documents. You can store any file that is 2 GB or less in size in an image data type. One of the most famous implementations of this data type is the TerraServer project, which is a multiterabyte database of terrestrial images that you can access at www.terraserver.com.

The varbinary(max) data type is new to SQL Server 2005. It can store the same amount of data as an image data type, and you can use it with all the operations and functions that you can use with binary/varbinary data types.

]

... and ...

[

Microsoft intends to drop support for the text, ntext, and image data types in upcoming SQL Server versions. For this reason, Microsoft recommends that you stop using these data types.

]

|||Can you please explain what you mean when you say it doesn't work for text? You would simply read it as SINGLE_BLOG and insert into varbinary(max) column. For text files, the data will be a binary stream so you should be able to simply cast it to varchar(max) or nvarchar(max) to read or see the text data. SQL Server doesn't care what you put inside varbinary(max) column or the nature of the content. The server simply sees the data as a stream of bytes.|||

Thank you everyone for your help thus far. Unfortunately I'm not quite there yet.

When trying to store a text file using SINGLE_BLOB my result is
(0 row(s) affected)

When trying to store a text file using SINGLE_CLOB my result is
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

SQL

INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
--Results in the following:
--(0 row(s) affected)

-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
-- Results in this error:
-- Msg 257, Level 16, State 3, Line 1
-- Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

-- These two work.
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #2.xls', Single_Blob)
-- FROM Openrowset( Bulk 'C:\Testing\Test Doc #3.jpg', Single_Blob)
as ChangeRequestAttachment

|||

I found my mistake! The text file I was testing with was a zero byte file (I forgot to put some text in the test file). Once I added some text to the test Text file the following two insert statements worked correctly.

-- This works using SINGLE_CLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, Convert(varbinary(max), BulkColumn) , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_CLOB)
as ChangeRequestAttachment

-- This works using SINGLE_BLOB
INSERT INTO CM_Attachment(Change_Request_Id, Attachment, Created_By, Modified_Date, Modified_By)
SELECT 6, BulkColumn , '', GetDate(), ''
FROM Openrowset( Bulk 'C:\Testing\Test Doc #1.txt', SINGLE_BLOB)
as ChangeRequestAttachment

Thank you again everyone for your help; what a great resource this form is.