Parker Software Ltd Homepage
Forum Home Forum Home > ThinkAutomation Business Process Automation > General
  New Posts New Posts RSS Feed - Best Practice - Storing emails
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Best Practice - Storing emails

 Post Reply Post Reply Page  12>
Author
Message
mgeerling View Drop Down
New User
New User


Joined: 11 Aug 2014
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote mgeerling Quote  Post ReplyReply Direct Link To This Post Topic: Best Practice - Storing emails
    Posted: 11 Aug 2014 at 10:12pm
Hello,

We are using Email2DB to parse incoming emails.  We also wish to store the original email contents in a CLOB field in our Oracle database.  Is there a best practice for storing the text of an email in a database?

Thanks.
Back to Top
mgeerling View Drop Down
New User
New User


Joined: 11 Aug 2014
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote mgeerling Quote  Post ReplyReply Direct Link To This Post Posted: 11 Aug 2014 at 11:02pm
The problem we have with trying to use an Insert via the Update A Database - Custom or Execute a Database Command is that when the email text is large, we get the following error:

ORA-01704: string literal too long FROM ExecuteADOCommand (Snippet Insert Email Message)


Back to Top
Liam View Drop Down
Admin Group
Admin Group
Avatar

Joined: 29 Jun 2011
Location: Stoke-on-Trent
Points: 262
Post Options Post Options   Thanks (0) Thanks(0)   Quote Liam Quote  Post ReplyReply Direct Link To This Post Posted: 11 Aug 2014 at 11:04pm
Hello,

Are you looking to store just the message text or a full copy of the message (which could potentially be rebuilt into a full email message file)?

If you are just looking to save the text there isn't really anything to note here it would just be a case of inserting the msg_bdy content into the database field, this could be done with a String type field, it shouldn't require a BLOB.

If you are looking to save the entire message, then you would need to save the following fields exactly as mentioned below:
%msg_headers%<CRLF><CRLF>%msg_mimetext%
the <CRLF> should be replaced with whatever Oracle recognises as a line feed character.
Again, this could probably be saved in a String type field as it will be a stream of text, although a great deal of it may well be nonsensical as it will be in code but when placed into an eml file this will be openable by most Email clients and will be presented as full email message, including attachments.
Back to Top
mgeerling View Drop Down
New User
New User


Joined: 11 Aug 2014
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote mgeerling Quote  Post ReplyReply Direct Link To This Post Posted: 12 Aug 2014 at 12:58am
Hi Liam,

We just want to insert the text/html of the message (the database CLOB column previously existed, so we can't really change that column).  However, when we try to push over say a 13K email, we get the following error:

8/11/2014 4:37:34 PM Email2DBMSGServer ORA-01704: string literal too long FROM ExecuteADOCommand (Snippet Insert Email Message)

The Oracle folks I've talked to have suggested using a bind variable in the INSERT, but I don't see how to do that in Email2DB.

Here are the entries from the Message Processor Server Log for the INSERT (EMAIL_BODY is the CLOB column):
8/11/2014 4:37:34 PM Preparing Command INSERT INTO CRM_EMAIL_MESSAGE(
8/11/2014 4:37:34 PM   EMAIL_SUBJECT,
8/11/2014 4:37:34 PM   EMAIL_FORMAT,
8/11/2014 4:37:34 PM   SENT_DATE,
8/11/2014 4:37:34 PM   SITE_CUSTOMER_ID,
8/11/2014 4:37:34 PM   AD_SOURCE_ID,
8/11/2014 4:37:34 PM   CAMPAIGN_ID,
8/11/2014 4:37:34 PM   INITIATOR_ID,
8/11/2014 4:37:34 PM   CREATED_BY,
8/11/2014 4:37:34 PM   CREATED_ON,
8/11/2014 4:37:34 PM   IS_ACTIVE,
8/11/2014 4:37:34 PM   IS_ARCHIVED,
8/11/2014 4:37:34 PM   EMAIL_BODY
8/11/2014 4:37:34 PM ) VALUES (
8/11/2014 4:37:34 PM   'Christina Lakes- Lease Hawk Test Property',
8/11/2014 4:37:34 PM   'HTML',
8/11/2014 4:37:34 PM   SYSTIMESTAMP,
8/11/2014 4:37:34 PM   307,
8/11/2014 4:37:34 PM   0,
8/11/2014 4:37:34 PM   0,
8/11/2014 4:37:34 PM   1,
8/11/2014 4:37:34 PM   'test.com',
8/11/2014 4:37:34 PM   SYSTIMESTAMP,
8/11/2014 4:37:34 PM   DEFAULT,
8/11/2014 4:37:34 PM   DEFAULT,
8/11/2014 4:37:34 PM   '<html><head><META http-equiv="Content-Type" content="t
8/11/2014 4:37:34 PM Command Parameter EMAILMSGID Set To  (Snippet Insert Email Message)

Back to Top
yalekhin View Drop Down
New User
New User
Avatar

Joined: 02 Sep 2014
Location: Philadelphia
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote yalekhin Quote  Post ReplyReply Direct Link To This Post Posted: 02 Sep 2014 at 5:46pm
Please provide sample information how to have Email2DB connecting to Oracle. I have Oracle client installed (ODBC and OLE DB), but having issues using/connecting from Email2DB.

Thank you.
Back to Top
Liam View Drop Down
Admin Group
Admin Group
Avatar

Joined: 29 Jun 2011
Location: Stoke-on-Trent
Points: 262
Post Options Post Options   Thanks (0) Thanks(0)   Quote Liam Quote  Post ReplyReply Direct Link To This Post Posted: 08 Sep 2014 at 10:39pm
Hello,
We don't have any official help for connecting to Oracle specifically.
But you should be able to connect to almost any database by using the following general guidance:
Access Windows's ODBC Data sources manager. This should be available in Control Panel -> Administrative Tools -> ODBC Data Sources.
As Email2Db is a 32bit application you will need to make sure that you are using the 32bit version of the ODBC Data Sources application -
For 64-bit Operating Systems:
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
for 32-bit Operating Systems:
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

In here you will then need to create a "System DSN" for your Oracle database, it needs to be a system DSN otherwise Email2DB will be unable to access it.
Once you have built the system DSN and that is working as expected you can now use it within Email2DB. When you come to build the connection string select the "Microsoft OLE DB Provider for ODBC Drivers" you should then see your recently connected DSN in the "Use Data source name" drop down list.
Fill in any other details that are required and click "test Connection". Please note that this just tests the TCP/IP connectivity not if the username or password is correct.
If this is successful click OK and then double check the connection string to ensure it looks okay and if you have set this up in a "Update a Database" action click the Test button. This will attempt to log into the database, if this returns okay you should be good to go and continue to build your Email2DB configuration.
Back to Top
mpm View Drop Down
New User
New User


Joined: 13 Dec 2018
Location: USA
Points: 2
Post Options Post Options   Thanks (0) Thanks(0)   Quote mpm Quote  Post ReplyReply Direct Link To This Post Posted: 22 Feb 2019 at 12:18pm
Hi,
Did you get solution to your issue?
I am also facing the same error(String literal too long) while trying to insert email body into a CLOB column in oracle db .
Appreciate the solution to this error.
Thanks,
Back to Top
Daniel Horton View Drop Down
Admin Group
Admin Group
Avatar
Technical Support

Joined: 01 Jun 2009
Location: Stoke-on-Trent
Points: 351
Post Options Post Options   Thanks (0) Thanks(0)   Quote Daniel Horton Quote  Post ReplyReply Direct Link To This Post Posted: 25 Feb 2019 at 4:55am
Hi there,

We would suggest you use a custom SQL statement action type and structure it like so:


DECLARE
  vString myTable.myColumn%type;
BEGIN
  vString := '%fieldvalue%'
  update myTable set myColumn=vString where somekey=somecriteria;
END;
Best Regards,



Daniel James Horton

Technical Manager

Parker Software
Back to Top
mpm View Drop Down
New User
New User


Joined: 13 Dec 2018
Location: USA
Points: 2
Post Options Post Options   Thanks (0) Thanks(0)   Quote mpm Quote  Post ReplyReply Direct Link To This Post Posted: 25 Feb 2019 at 6:20pm
Hi,
I tried the below but when getting executed by Think Automation it gives the below error:
ORA-06550:PLS-00103: Encountered the symbol " end of file" when expecting one of the following :*&=


This is my script which i used

DECLARE
  vString temp.emll_body%type := NULL
BEGIN

vString := '%msg_body%';
  INSERT ALL INTO temp (
        id,
        eml_body
    ) VALUES (
        id1,
        eml1
    ) SELECT
          '1234'   id,
          vString        eml1
      FROM
          dual;
commit;

END;
Back to Top
Hurlburt View Drop Down
New User
New User
Avatar

Joined: 23 Apr 2019
Location: Raleigh, NC
Points: 1
Post Options Post Options   Thanks (0) Thanks(0)   Quote Hurlburt Quote  Post ReplyReply Direct Link To This Post Posted: 24 Apr 2019 at 5:31am
Could it be that Think Automation itself is causing this issue?
Back to Top
 Post Reply Post Reply Page  12>
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.05
Copyright ©2001-2016 Web Wiz Ltd.

This page was generated in 0.063 seconds.