Send BLOB field as Email Attachment

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>"

%>


kbraswellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kbraswellAuthor Commented:
I am open to doing this as an Oracle Function also if anyone has that experience.
astankovicCommented:
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
kbraswellAuthor Commented:
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?
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

ap_sajithCommented:
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!!
ap_sajithCommented:
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!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ap_sajithCommented:
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!!
ap_sajithCommented:
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!!
fozyletCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.