Parker Software Ltd Homepage
Forum Home Forum Home > Email2DB Email Parser > Scripting > Scripting Samples
  New Posts New Posts RSS Feed: Script for inserting binary file data into a db
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Script for inserting binary file data into a db
 Post Reply Post Reply Page  <12
Author
Message
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
James Horton View Drop Down
Admin Group
Admin Group
Avatar
Technical Support

Joined: 01 Jun 2009
Location: Stoke-on-Trent
Posts: 275 		Post Options Post Options   Quote James Horton Quote  Post ReplyReply Direct Link To This Post Topic: Script for inserting binary file data into a db
    Posted: 15 Jun 2011 at 1:38pm
The script would be the same, just make sure that the Update Database section of the trigger is referencing your database via the Connection String or builder.
Best Regards,

James Horton
Technical Analyst
Parker Software
Back to Top
ash0602 View Drop Down
New User
New User


Joined: 14 Jun 2011
Location: United Kingdom
Posts: 2 		Post Options Post Options   Quote ash0602 Quote  Post ReplyReply Direct Link To This Post Posted: 15 Jun 2011 at 1:45pm
Thanks James,

This is my script, as mentioned earlier, %msg_uid% is not getting updated in the parent table (but getting updated in the child table). I was doing field extraction, and now even after adding in the script below, the cec_contacts.cnt_msg_uid (my parent table's column for %msg_uid%) is not getting updated. Without which I'll not be able to reference attachments from the parent table. Need help on this:

Sub Main()
   ' this script saves the saved attachment filenames in the Attachment table
    ' we have added a reference to the Microsoft ActiveX Data Objects (ADO).
    ' it assumes you already have a table called 'cec_contact_attachments' in the database.

    If MSG_AttachmentCount=0 Then Exit Sub ' if no attachments do nothing

    Dim DBConnection As New ADODB.Connection
    ' child table
    Dim cec_contact_attachments As New ADODB.Recordset
    ' parent table
    Dim cec_contacts As New ADODB.Recordset
    Dim myStream As ADODB.Stream

    Dim A As Integer

    On Error GoTo erl
    DBConnection.ConnectionString="Provider=OraOLEDB.Oracle.1;Password=custemails;Persist Security Info=True;User ID=custemails;Data Source=apexdev;" ' use the connection string specified on the Database tab
    DBConnection.Open

    ' open the cec_contacts table
    cec_contacts.Open "cec_contacts",DBConnection,adOpenDynamic,adLockOptimistic

    ' update relevant columns
    cec_contacts.Fields("cnt_msg_uid")=MSG_UID
    cec_contacts.Fields("cnt_file_count")=MSG_AttachmentCount
    cec_contacts.Update

    cec_contacts.Close

    ' open the cec_contact_attachments table
    cec_contact_attachments.Open "cec_contact_attachments",DBConnection,adOpenDynamic,adLockOptimistic

    For A=1 To MSG_AttachmentCount

        If len(msg_attachment(A)) > 0 
        Then
            AddToLog("Processing: " & MSG_Attachment(A))
            cec_contact_attachments.AddNew
            'cec_contact_attachments.Fields("cat_cnt_id")=CNT_ID
            cec_contact_attachments.Fields("cat_cnt_msg_uid")=MSG_UID
            cec_contact_attachments.Fields("cat_file_sequence")=A
            cec_contact_attachments.Fields("cat_file_name")=MSG_Attachment(A)
            Set myStream = New ADODB.Stream
            AddToLog("File is type: " & MSG_AttachmentType(A))
            myStream.Type = adTypeBinary
            AddToLog("Opening temp file: " & MSG_AttachmentTempLocation(A))
            myStream.Open
            myStream.LoadFromFile(MSG_AttachmentTempLocation(A))
            cec_contact_attachments.Fields("cat_file_content")=myStream.Read
            cec_contact_attachments.Fields("cat_file_size")=myStream.Size
            myStream.Close
            cec_contact_attachments.Fields("cat_file_mime_type")=MSG_AttachmentType(A)
            cec_contact_attachments.Update
        End If
    Next A

    cec_contact_attachments.Close

    DBConnection.Close

    Call AddToLog("cec_contact_attachments table updated successfully")

    Exit Sub

erl:
    Call AddToLog("Error in script: " & Err.Description)
End Sub
Back to Top
andyj1970 View Drop Down
New User
New User


Joined: 23 Jun 2011
Posts: 27 		Post Options Post Options   Quote andyj1970 Quote  Post ReplyReply Direct Link To This Post Posted: 27 Jun 2011 at 1:36pm
We had a problem with the script running againt Oracle 11g database to load attachments as above. The first attachment loads fine but subsequent ones were not being loaded. The error message shown in the service log was:
 

2011-06-22:12:20:07  ## file closed successfully ##

2011-06-22:12:20:07 Error in script: ROW-00014: Cannot update row as the data in the database has changedzfPu

2011-06-22:12:20:07 New record added to database

 

I checked some info online and the script appeared to be fine. I'm not a VB expert but one suggestion was to change the connection from adOpenDynamic to adOpenKeyset as below.

 

    cec_contacts.Open "cec_contacts",DBConnection,adOpenKeyset,adLockOptimistic

 

Once we did this the script now processes multiple attachments successfully.
Back to Top
jjaroska View Drop Down
New User
New User


Joined: 20 Apr 2012
Posts: 1 		Post Options Post Options   Quote jjaroska Quote  Post ReplyReply Direct Link To This Post Posted: 23 Apr 2012 at 3:43pm
I am having a problem when i read the file inserted into the database.  i get the error the magic number is wrong.  it seems that this is a common asp.net error message when the whole file is not present.  i can write and read other files when i use the file component from devexpress but not when i use the script from this thread.  can someone help?

Edited by jjaroska - 23 Apr 2012 at 3:44pm
Back to Top
 Post Reply Post Reply Page  <12

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.156 seconds.
These are the forums for Parker Software, developers of Live Chat Software: WhosOn and Email Automation Software: Email2DB.