Solved

Send BLOB field as Email Attachment

Posted on 2003-12-08
9
718 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

23 Experts available now in Live!

Get 1:1 Help Now