Solved

Send BLOB field as Email Attachment

Posted on 2003-12-08
9
723 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
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 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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error viewing ASP page 12 176
Sudden decrease in performance when updating mysql using classic asp 6 34
Multiflying 2 Input Text On a Table 7 31
window close link 7 32
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
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/…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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