Storing Document (xls) in tables
-
I am trying to research how to store word/excel/powerpoint/text documents in the database itself.
Does anyone have key words/phrases or a good source of documentation for this type of thing? I am looking at storing 10,000+ documents at 20K to 4Meg each to begin with.
Thanks,
Joseph
-
I know this sounds like a stupid question, but why not just let the file system do what it's designed to do and just store the path in the DB?
-
The system is being designed to share documents accross the corporation and have a sync process. The hardware setup and design is out of my hands. I have to be able to send the apropriate "records" to as many as 5 other databases in a nightly batch process. Some documents are tagged "all", so from the orignating server, it will be sent to the other servers. Some will be tagged for specific server(s).
Joseph
-
Blobs arent always a bad idea. Definitely is nice to have it all secure and backed up in one place.
ADO stream usually offers the easiest way to get the data in/out of a blob. We've got quite a few threads here on the site, probably some small code snippets. I know MSDN has a code example showing how to use it.
Andy
-
Hi Joseph,
agree with Andy. Blob's aren't always bad, though sometimes it seems to me like a philosophical question whether to store binary data in a db or on the file system.
Case you're doing this with some VB dialect, you can use the ADO.Stream object like this
Dim sFileName As String
Dim rs As ADODB.Recordset
Dim cnnAttach As ADODB.Connection
Dim strStream As ADODB.Stream
...
Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.LoadFromFile sFileName
.Fields("MsgAttachment").Value = strStream.Read
The above stores any kind of binary data into a image field
To retrieve the data try this one
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
If you want to show the document you have to add this to the above code
I use an asp page for access. It looks loke this one.
Set oConn = ConnectAttachment()
Set oRecSet = Server.CreateObject("ADODB.Recordset")
lpszTableName = Request.QueryString("section")
lpszMsgID = Request.QueryString("id")
SQL="SELECT img FROM " & lpszTableName & " WHERE id=" & lpszMsgID
oRecSet.Open SQL, oConn
If oRecSet.BOF And oRecSet.EOF Then
Response.End
End If
Response.ContentType = "application/vnd.ms-excel"
Response.BinaryWrite oRecSet.Fields("img")
The contenttype can be set to virtually whatever you like.
Hope that leads you in the right direction.
Suggestion to Sit-owners:
Although I haven't check this out yet, it might be a good idea to place some example code on storing an retrieving binary data somewhere on the site for there seems to be frequently questions on this. If there is already some code, forget my suggestion
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you for both of your comments. I will be doing this with VB. Going with .NET.
Thanks again,
Joseph
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply