BLOBs and SQL Server 2000

[Kobaia27]

Hi everyone,

Does anyone know how to store files directly in a database?

I need to develop an upload feature on a Website and I would like to store the uploaded files in SQL Server. I tried to use the text data type for this and insert the file in it:

insert into Files (fileName, mimeType, blobFile) values ('myFileWithExtension', 'mimeTypeOfTheFile', 'contentOfTheFile')

It seems to be working but in fact the file is not completely stored (I only have part of the file). And when I try to insert and retrieve an RTF file, it only works with a 64Kb file.

I also tried several other solutions I found in SQL Server documentation but nothing worked :-(

Thanks for your help.

 

[TomasDill]

Hi!

First of all, you should use Image field type instead of text. Text field corrupts binary data even when you manage to store it there completely.

Second, to store large data like large text or binary data, you require to use the SQL Server driver's/provider's feature to pass large data as parameters for SQL Server command. In ASP pages you probably use the ADO, so in your case you will require to prepare the INSERT command in following syntax:

insert into Files (fileName, mimeType, blobFile) values ('myFileWithExtension', 'mimeTypeOfTheFile', ?Content)

Then use Command object to execute this SQL command. In the Command object there are Parameters collection, where you can define all parameters that appear in the SQL Command (all ?{name} items).

Third, to save binary data using ADO, you cannot use direct assignment of the data to the Value property of field or parameter. There are methods GetChunk and AppendChunk that are used for binary data.

For details, read more help and samples for ADO related to what described above.

Hope this helps.
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[Kobaia27]

Thanks for the answer but I am using ColdFusion now with an ODBC connection :-(

I now know where my problem was: the inserted file is truncated when a NULL character is found in the file. I will try to Base64 it before storing it into the DB. Is there a function to do it with SQL Server?