Solved

Send BLOB field as Email Attachment

Posted on 2003-12-08
9
716 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
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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

12 Experts available now in Live!

Get 1:1 Help Now