Solved

Send BLOB field as Email Attachment

Posted on 2003-12-08
9
728 Views
Last Modified: 2009-12-16
I am trying to send a BLOB field from an Oracle Database connected through ODBC as an email attachment.  I have been unsucessful thus far.  I can display the BLOB field to the screen (in another file using different code) where it will prompt me to download the file.  But I cannot get it to email the file using this code.

here is my code at this point.

<% @Language="vbscript" %><%
Response.Buffer = TRUE
%>

<%
if session("Project_Number") = "" then
 response.redirect("login.asp")
end if

if session("Email") = "" then
 response.redirect("login.asp")
end if

if session("Password") = "" then
 response.redirect("login.asp")
end if

dim Project_Number
dim Email
dim Password

Project_Number = session("Project_Number")
Email = session("Email")
Password = session("Password")

%>

<%
mySQL = "select P.PDF_FILE "
mySQL = mySQL & " from UPCI_PROJECT_PDFS P, "
mySQL = mySQL & " UPCI_LOGIN_VIEW L "
mySQL = mySQL & " where P.PROJECT_ID = L.SEARCHNUM-1102 "
mySQL = mySQL & " and P.PROJECT_ID=" &  Project_Number
mySQL = mySQL & " and upper(L.email) = upper('" & Email
mySQL = mySQL & "') "
mySQL = mySQL & " and upper(L.password) = upper('" & Password
mySQL = mySQL & "') " 

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=OraOLEDB.Oracle;Data Source=PMPRD;User Id=gui;Password=gbts;DistribTx=0;"

Set oRS = Conn.Execute(mySQL)

PicSize = oRs("PDF_FILE").ActualSize
Pic = oRs("PDF_FILE").GetChunk(PicSize)

Dim objMessage

Set objMessage = Server.CreateObject("CDONTS.NewMail")

With objMessage
      ' Set message attributes
      .Body = PicSize
      .Subject = "PDF Project Attachement"
      .From = "kbraswell@upci.org"
      .To = Email
      '.Cc = strCC                        
      .MailFormat = 0                  
      .AttachFile Pic,"pdf.pdf","application/pdf"
      '      .AttachFile oRs("PDF_FILE").GetChunk(PicSize),"PDF.PDF","application/pdf"
      .Send
End With

Set objMessage = Nothing

Response.Write "<h2>File Sent!</h2>"

%>


0
Comment
Question by:kbraswell
[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
9 Comments
 

Author Comment

by:kbraswell
ID: 9898587
I am open to doing this as an Oracle Function also if anyone has that experience.
0
 
LVL 4

Expert Comment

by:astankovic
ID: 9898668
I believe that AttachFile method needs the full path and file name of the file you want attached.
So you might need to save the file first, then attach it, and delete it afterwards. But with that approach you must be careful to address concurrency issues.

I don’t know Oracle, so can’t tell you if there is a function that can do that.  

alex
0
 

Author Comment

by:kbraswell
ID: 9899292
I could save it as the project Number of the user. This would be unique to that user.  But, even to save it to disk and then read that particular file.  Do you have an example of how to do that?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 21

Expert Comment

by:ap_sajith
ID: 9902857
Here's how you could go about doing it. I have added a function that generates unique filenames. what about the file extension? is that stored anywhere in the DB?. In my code, i have hardcoded it as '.jpg'. You could alter the script a bit to incorporate the file extension from the DB.


<%
Dim sPath,objRS,objCDO

' ** retrieve the file name & path **
sPath=Server.Mappath("Uploads\" & genUID &".jpg")
Response.write sPath
response.end


set objRS = conn.execute("SELECT blobcolumn FROM blobtable WHERE id = 7")

'Write it to the browser
response.binaryWrite rs.fields("blobcolumn").value

'Write it to disk
dim stream
set stream = server.createObject("adodb.stream")
stream.type = adTypeBinary
stream.open
stream.write(objRS.fields("blobcolumn").value)
stream.saveToFile sPath, 2 ' ** Path, adSaveCreateOverWrite **
stream.close

Set objRS=nothing

' Mailing Script
Set objCDO=Server.CreateObject("CDONTS.objCDO")

' Add Attachment
objCDO.AttachFile sPath, "Sample Attachment"
' Send plain text email
objCDO.BodyFormat = 0
' Senders email address
objCDO.From = "sales@gimmick-prodo.co.uk"
' Recipients email address
objCDO.To = "someone@domain.co.uk"
' Email Subject
objCDO.Subject = "Test Email with attachment"
' Email Body
objCDO.Body = "Test Email with attachment"
' Send Email
objCDO.Send()
' Destroy object to release it from memory
Set objCDO = Nothing



' ** FUNCTION TO RETRIEVE UNIQUE 32 BIT STRINGS **
Function genuid    
     Dim Guid
    guid = server.createobject("scriptlet.typelib").guid
    guid=Left(guid,instr(guid,"}"))
    guid=replace(guid,"{","")
    guid=replace(guid,"}","")
    guid=replace(guid,"-","")
    genuid=guid
    set guid=nothing
end function

%>

Cheers!!
0
 
LVL 21

Accepted Solution

by:
ap_sajith earned 250 total points
ID: 9902895
Sorry.. some typo's in the previous code...


<%
Dim sPath,objRS,objCDO

' ** retrieve the file name & path **
sPath=Server.Mappath("Uploads\" & genUID &".jpg")

set objRS = conn.execute("SELECT blobcolumn FROM blobtable WHERE id = 7") ' ** Modify the query to suite your Table

'Write it to disk
dim stream
set stream = server.createObject("adodb.stream")
stream.type = adTypeBinary
stream.open
stream.write(objRS.fields("blobcolumn").value)
stream.saveToFile sPath, 2 ' ** Path, adSaveCreateOverWrite **
stream.close

Set objRS=nothing

' Mailing Script
Set objCDO=Server.CreateObject("CDONTS.objCDO")

' Add Attachment
objCDO.AttachFile sPath, "Sample Attachment"
' Send plain text email
objCDO.BodyFormat = 0
' Senders email address
objCDO.From = "sales@gimmick-prodo.co.uk"
' Recipients email address
objCDO.To = "someone@domain.co.uk"
' Email Subject
objCDO.Subject = "Test Email with attachment"
' Email Body
objCDO.Body = "Test Email with attachment"
' Send Email
objCDO.Send()
' Destroy object to release it from memory
Set objCDO = Nothing

' ** FUNCTION TO RETRIEVE UNIQUE 32 BIT STRINGS **
Function genuid    
     Dim Guid
    guid = server.createobject("scriptlet.typelib").guid
    guid=Left(guid,instr(guid,"}"))
    guid=replace(guid,"{","")
    guid=replace(guid,"}","")
    guid=replace(guid,"-","")
    genuid=guid
    set guid=nothing
end function

%>

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9902995
Add this code at the bottom to delete of the newly created file..

Dim objFSO
Set objFSO=Server.CreateObject("Scripting.FilesystemObject")
objFSO.DeleteFile (sPath)
Set objFSO=nothing

Do let me know if you need any clarifications with the script.

Cheers!!
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 10243989
Hi,
Any Updates?. Do you need any further assistance with this?. Please close this question if no further assistance is needed.
If you need help closing this question, please refer to http://oldlook.experts-exchange.com/help/closing.jsp on how to close a question.

Cheers!!
0
 
LVL 8

Expert Comment

by:fozylet
ID: 12062040
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: ap_sajith {http:#9902895}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

fozylet
EE Cleanup Volunteer
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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