Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Saving Files to Database

Posted on 2002-04-02
22
Medium Priority
?
167 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 800 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

610 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