Solved

Saving Files to Database

Posted on 2002-04-02
22
154 Views
Last Modified: 2010-05-02
I've been struggling with this for the past few days.

I wrote a component that creates a PDF document.  I want to save this into a database.  I'm using MS-SQL 2000.
0
Comment
Question by:thunderchicken
  • 6
  • 6
  • 4
  • +3
22 Comments
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
Comment Utility
Here is one way utilizing the ADODB Stream Object. I got this from someone else's example for storing images in the database (sorry, I don't remember who). This code adds a new record and sets the value of the field, but you could position yourself on an existing record, and use similar code to update the field.

Public Sub AddPDF(rs As ADODB.Recordset, ByVal FileName As String)
Dim stm As ADODB.Stream

Set stm = New ADODB.Stream
With stm
 .Type = adTypeBinary
 .Open
 .LoadFromFile FileName
 
 'Insert the binary object into the table.
 rs.AddNew
 rs.Fields("PDFColumn").Value = .Read
 rs.Update
 .Close
End With
Set stm = Nothing

End Sub

You could call this function as follows:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
  .Source = "Select * From MyTable"
  .ActiveConnection = "Provider=SQLOLEDB.1;User ID=userid;Initial Catalog=database;Data Source=sqlserver"
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
  .Open
  AddPDF rs, "c:\docs\mydoc.pdf"
  .Close
End With
Set rs = Nothing

End Sub

0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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
0
 
LVL 11

Author Comment

by:thunderchicken
Comment Utility
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
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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
0
 
LVL 11

Author Comment

by:thunderchicken
Comment Utility
What should I be saving this as?  Currently I'm saving it as an image....
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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.
0
 
LVL 11

Author Comment

by:thunderchicken
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
learning...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
mdougan,

I like it <g>

Anthony
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Was that your code I stole Anthony? ;)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
It looks a little like it.  I am flattered!

Anthony
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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.
0
 
LVL 11

Author Comment

by:thunderchicken
Comment Utility
Someone sample me some code!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>> 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
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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
0
 
LVL 11

Author Comment

by:thunderchicken
Comment Utility
OK, let me get an exact error for you....

thanks
0
 
LVL 11

Author Comment

by:thunderchicken
Comment Utility
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
None whatsoever, I am glad you got it to work.

Anthony
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
Sounds good to me.  :)
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Thanks!  I'll have to rewrite Anthony's example enough so he can't recognize it next time ;)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
mdougan,

No chance! <vbg>

Anthony
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now