Saving Files to Database

Use the AppendChunk method of the field object to drop binary data into the db and the GetChunk method to retrieve.  Here is an example I wrote in DAO, which can be translated pretty easily to ADO (use actualsize instead of fieldsize property):

Public Sub sPutFileToField(strFileName As String, fld As Field)
   Dim hFile As Integer
   Dim strText As String
   Const ChunkSize = 32768
   Dim lngNumChunks As Long, i As Long
   Dim lngRemainder As Long
   
   hFile = FreeFile
   Open strFileName For Binary As #hFile
   
   lngNumChunks = LOF(hFile) \ ChunkSize
   lngRemainder = LOF(hFile) Mod ChunkSize
   
   strText = Space(ChunkSize)
   For i = 1 To lngNumChunks
       Get #hFile, , strText
       fld.AppendChunk strText
   Next
   
   strText = Space(lngRemainder)
   Get #hFile, , strText
   fld.AppendChunk strText
   
   Close #hFile


End Sub


Public Sub sGetFileFromField(strFileName As String, fld As Field)
   Dim hFile As Integer
   Dim strChunk As String
   Const ChunkSize = 32768
   Dim lngFieldSize As Long, i As Long
   Dim lngOffset As Long
   
   hFile = FreeFile
   Open strFileName For Binary As #hFile
   
   
   lngFieldSize = fld.FieldSize
   
   Do While lngOffset < lngFieldSize
       strChunk = fld.GetChunk(lngOffset, ChunkSize)
       Put #hFile, , strChunk
       lngOffset = lngOffset + ChunkSize
   Loop

   Close #hFile
   
   
   


End Sub

mdougan - Tried yours I got an error saying it could not save the file

PaulHews - I've seen this example before and have tried it.  My problem is that i see the record going in, and you get the <binary> in the field.  Calling this from an ASP page, I need to let this user download this file.  I was wondering how I call this record to allow them to download it.

ie...

<%
  set foo = Server.createobjet("myobject.VBSucks")
  filename = foo.sGetFileFromField(whatever,whenever)
%>
<a href = "download.asp?download=<%=filename%>">Download this!!!</a>

This is where I'm confused.  We have a third party component and when I transfer an existing recordset that was uploaded using their component it works, but we can't have a 3rd party component in a clustered environment.

Thanks guys

Something like this?

dim stream
set stream = server.createObject("adodb.stream")
stream.type = adTypeBinary
stream.open
stream.write rs.fields("blobcolumn").value
'push to the browser
Response.ContentType = "application/pdf"
Response.BinaryWrite objStream.Read

What should I be saving this as?  Currently I'm saving it as an image....

I am not a big fan of saving binary data to a database. I prefer to have a folder containing them and I store the path into the database. Database are normally easier to backup this way.

I agree, and I'm open for suggestions:

Here's the reason why I'm doing it this way:

2 Web servers ~~~~~~ DMZ ~~~~~~ 2 COM Servers

the file is created on the COM server, and is emailed out to someone via CDONTS.  It is Created and saved on the COM Server, but if the user wants to download the file, that's my problem.  I can't transfer files through the DMZ, so I figured this would work.

If you can figure out how to get this to work, I'll personally ship you a case of beer.  You pay for the shipping.

learning...

mdougan,

I like it <g>

Anthony

Was that your code I stole Anthony? ;)

It looks a little like it.  I am flattered!

Anthony

mdougan,

If you recall you were promoting the GetChunk/AppendChunk method and I was suggesting the Stream object.  I am not sure who got awarded the points if anybody <g>

Anthony

ha ha ha... no, there was no question, your Stream object code is much more concise than my old getchunk code.  I just never bothered to update my getchunk sample.  So, I think last time the question came up, I saved off the sample you gave for future reference.

Someone sample me some code!

>> Someone sample me some code! <<
mdougan did.  You responded:
Tried yours I got an error saying it could not save the file.

As always, it would be helpful to know on what line and exact error you received.

Anthony

tc, you should be able to get it to work with the stream object as in mdougan's example.  Once the data is in the db, you can also use the stream object to push the data to the browser as in the sample I gave above.

HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object (Q258038)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258038

OK, let me get an exact error for you....

thanks

God I'm stupid.....

The reason why this didn't work is because it would be in the process of creating this file...

When I try to do this, there would be access denied errors because it wasn't done writing to it.  I added a simple

Set objFSO = CreateObject("Scripting.FileSystemObject")
    'We need to wait until the pdf is done being created before we add it to the database
   
    While Not objFSO.FileExists(strFilePath)
        Sleep 500
    Wend
   
    Set objFile = objFSO.GetFile(strFilePath)
   
    While objFile.Size < 30000
        Sleep 500
    Wend

Now it works fine, via the microsoft link....

mdougan, can have the 200 pts and I'll post 100 for PaulHews for the microsoft link

any objections?

None whatsoever, I am glad you got it to work.

Anthony

Sounds good to me.  :)

Thanks!  I'll have to rewrite Anthony's example enough so he can't recognize it next time ;)

mdougan,

No chance! <vbg>

Anthony