SQL Server Performance Forum – Threads Archive

How i can store some file contents in SQL Server ?

Dear Sir, I am Debasis Mishra working as a Software Developer in TATA STEEL,INDIA.Sir i face a problem in SQL Server database.My prblem is that i have created three feilds in my Customer table.The feilds names are, Cust_id,Cust_Name and Cust_Details.In the Cust_Details feild i want to store some files contents having the extension name as .doc,.pdf,.jpg and .txt etc etc then i display the File details in my ASP page.Sir how i can slove this problem and what should be the best way to solve the problem as i am using SQL Server 2000.
Sir i will be waiting for your positive response. Debasis Mishra
Software Developer

Are you looking to store images and document files or just name of those files? _________
Satya SKJ

Debasis, If I understand you right, what you want to do is store the binary image of the file in a image field, and the name of the document in a varchar field. For example: —————– CODE SNIP ————————– CREATE TABLE [FileStore] ( [FileID] [int] IDENTITY (1, 1) NOT NULL , [FileName] [varchar] (100) NOT NULL , [FileImage] [image] NULL , CONSTRAINT [PK_FileStore] PRIMARY KEY CLUSTERED ( [FileID] ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO —————– END CODE SNIP ————————– That’s the easy part. The part that I think your questioning is how to store and retrieve the document. Part of that problem comes from writing the right MIME type to the client when they view the document. A way you could do that is by looking at the file extension on the FileName field stored in the database. I’m not sure how your posting the data from the file to the web page, but we use SaFileUp here, so I’ll use that in my example. Most other components work the same way. (you can read the SaFileUp docs for more information on this at<a target="_blank" href=http://support.softartisans.com/docs/FileUpV4/>http://support.softartisans.com/docs/FileUpV4/</a>). —————– CODE SNIP ————————– sFileName = "myFile.doc" Set RSc = Server.CreateObject("ADODB.Recordset") SQLt = "FileStore" RSc.Open SQLt, oConn, adOpenDynamic, adLockOptimistic, adCmdTable RSc.AddNew RSc("FileName") = sFileName oUploadedFile.SaveAsBlob RSc.Fields("FileImage") RSc.Update RSc.Close —————– END CODE SNIP ————————– Please note that "oUploadedFile.SaveAsBlob" is a function of SaFileUp. It saves the binary contents of your submitted file to the SQL field "FileImage" in chunks. Next, you want to be able to retrieve that image from the database. You do that by writing the right content type to the browser requesting the file. Here’s a code snip that will do that. —————– CODE SNIP ————————– set RS = Server.CreateObject("ADODB.Recordset") SQtext = "SELECT FileName, FileImage From FileStore where FileID = "&Int(Request("id"))&"" Set RS = gConn.Execute (SQtext) aName = RS("FileName") i = Len(aName)-InStrRev(aName,".") SELECT CASE LCASE(RIGHT(aName,i)) CASE "doc" Response.ContentType = "application/msword" CASE "xls" Response.ContentType = "application/excel" CASE "xlt" Response.ContentType = "application/excel" CASE "pdf" Response.ContentType = "application/pdf" CASE "zip" Response.ContentType = "application/x-zip-compressed" CASE "txt" Response.ContentType = "text/html" Response.Write "&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />re>" CASE "jpeg","gif","jpg" Response.ContentType = "text/html" CASE ELSE Response.ContentType = "text/html" Response.Write "&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />re>" END SELECT Response.BinaryWrite RS("FileImage").GetChunk(RS("FileImage").ActualSize) RS.Close —————– END CODE SNIP ————————– Hope that helps you get an understanding of how files can be saved and retrieved. The key is parsing the extension to spit out the right ContentType. Once you have that your golden. Steve

HI, I have found myself with the same problem. need to upload and retrieve binary data from sql server, using asp. if anyone knows of a web site with examples please let me know, I will try this code above, but It looks like it is missing something.
Thanks,
Wolffy

You should take a look at the ADO Stream Object. Comes in very handy This is a code snippet I use to save the stream to file
Private Sub cmdSave_Click() Dim i As Integer Dim sql As String
Dim msg As String
Dim sFileName As String Dim rs As ADODB.Recordset
Dim strStream As ADODB.Stream CommonDialog1.Flags = cdlOFNHideReadOnly
CommonDialog1.InitDir = "C:"
CommonDialog1.ShowSave sql = "SELECT * FROM mails_attachments WHERE id=" & CLng(DataList3.BoundText) Set rs = New ADODB.Recordset
cnnArchive.Open SNIPPET_STRING
rs.Open sql, cnnArchive, adOpenDynamic, adLockOptimistic, adCmdText Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.Write rs.Fields("MsgAttachment").Value
strStream.SaveToFile CommonDialog1.FileName, adSaveCreateOverWrite cnnArchive.Close End Sub
This is something I use to store the Stream into an image column
Dim strStream As ADODB.Stream With rs
.AddNew
sFileName = File1.Path & "" & File1.FileName
Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.LoadFromFile sFileName
DoEvents
.Fields("filename") = File1.FileName
.Fields("img").Value = strStream.Read
.Update
End With Frank
http://www.insidesql.de
http://www.familienzirkus.de

This long URL is another starting point. Just modify the code to accomodate the other file types (extensions). http://support.microsoft.com/defaul…ort/kb/articles/Q173/3/08.asp&NoWebContent=1] Nathan H.O.
Moderator
SQL-Server-Performance.com

]]>