How to Save Email Attachments as Binary to the Database
What the Rule Does
The rule extracts the email attachment,saves it to a temp folder on the disk, then inserts the attachment into the database and delete the attachment from the disk. Also, the rule includes the Script that restores the deleted attachment to the folder. You can activate/de-activate this component depending on your needs.
If the message has more than one attachment, the rule will process all attachments.
How to Use It
Extract the files from the .zip file.
The zip file contains the rule Save_Attachment_to_DataBase.txt file, a debug message in the .eml format and a sample MS Access database to which the attachment will be saved.
If you do not have G-Lock Email Processor yet, click here to download it now.
Install G-Lock Email Processor, open it and create the account from which you will process messages.
Extract the files from the zip file and do this.
Click on the “Open Rules Folder” on G-Lock Email Processor toolbar.
Save the rule Save_Attachment_to_DataBase.txt file to the Rules folder.
Open the Debug Messages folder in the Rules folder and save the Test with Attachment.eml file here.
Save the Test_File_Save.accdb file to root folder on the D drive (D:\Test_File_Save.accdb).
Go to G-Lock Email Processor and click on the “Reload Rules Folder” button.
Look for “Save Attachment to DataBase” rule in the list of rules. Click on the rule name to see what components it includes.
The rule contains the following components:
1. Block Extractor that extracts all attachments from the message just in case the message has more than one attachment.
The following components are inside the block to make the rule work for each attachment.
2. Field Extractor that extracts each attachment name.
3. Save Attachment component that saves the extracted attachments to the disk (D:Temp folder).
4. Script Processor that inserts the attachment from the disk into the database (D:\Test_File_Save.accdb). When you modify the rule to fit your needs, open the Script Processor and check the script. Correct the connection string as necessary.
5. Fields Processor that deletes the processed attachment from the disk (it’s necessary if the next message has the attachment with the same name as the processed attachment).
6. Script Processor (Test Saved Data) that restores the deleted attachment to the folder on the disk. You can disable this component if you don’t want to restore the deleted attachment.
Nechama
| #
Hi!
We’re trying to save email attachments to SQL database.
We made a rule that saves every email to a table (parent table)
and then we used your rule to save the attachments to a child table.
How can we get the Id from the parent table to save in the child table
for every row?
Reply
Julia
| #
Hi,
If you click on the Advanced tab in the “Insert into database” component, you can see the information about the connection to the database you will insert the fields into.
“Get identity after insert SQL is executed” – check this option and write your query if you want to get the Identity value after the fields are inserted into the database.
If you have worked with MS SQL Server, MS Access, you are probably familiar with Identity columns. The main purpose of these columns is to provide a primary key to the table, when a primary key can not be defined using other fields in the table. The Identity columns are like any other column except that their value is not inserted by the user, but by the system itself.
Example:
Select RecID as :PRECID from Mail_list where email=:Email
Reply
Nechama
| #
Hi !
Thank you for your answer.
I want to know please how & where can I keep the primary key of the row from parent table to insert later in the rows of the child table ,by the script of this example.
Reply
Julia
| #
Hi,
You can add a new Field Extractor component to the rule with the name “Primary_key” and source “Blank value” and write the result of the “Get identity after insert SQL is executed” procedure to the “Primary_key” field.
Reply