Solved

Saving Files to Database

Posted on 2002-04-02
22
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
  • +3
22 Comments
 
LVL 18

Accepted Solution

by:
mdougan earned 200 total points
ID: 6913105
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
ID: 6913108
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
ID: 6913182
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 38

Expert Comment

by:PaulHews
ID: 6913314
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
ID: 6913344
What should I be saving this as?  Currently I'm saving it as an image....
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6913436
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
ID: 6913445
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
ID: 6913498
learning...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6913583
mdougan,

I like it <g>

Anthony
0
 
LVL 18

Expert Comment

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

Expert Comment

by:Anthony Perkins
ID: 6914747
It looks a little like it.  I am flattered!

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6914751
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
ID: 6914876
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
ID: 6915345
Someone sample me some code!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6915365
>> 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
ID: 6915466
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
ID: 6915477
OK, let me get an exact error for you....

thanks
0
 
LVL 11

Author Comment

by:thunderchicken
ID: 6915584
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
ID: 6915597
None whatsoever, I am glad you got it to work.

Anthony
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 6916023
Sounds good to me.  :)
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6916772
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
ID: 6916805
mdougan,

No chance! <vbg>

Anthony
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

729 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