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 Reverse Sort Order
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
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 Topic: Script for inserting binary file data into a db
    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
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
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
James Horton View Drop Down
Admin Group
Admin Group
Avatar
Technical Support

Joined: 01 Jun 2009
Location: Stoke-on-Trent
Posts: 277 		Post Options Post Options   Quote James Horton Quote  Post ReplyReply Direct Link To This Post 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 10:43am
Hi,

Can someone help with similar script for Oracle, please?
And where should I put this script into?

Thanks,
Ashish


Edited by ash0602 - 15 Jun 2011 at 10:45am
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 875 		Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 10 May 2010 at 9:36am
That normally means you haven't added the reference to ADO with the references button at the top of the window;  have you done this?
Daniel Tallentire
Support
Parker Software
Back to Top
iareanet View Drop Down
New User
New User


Joined: 28 Apr 2010
Posts: 3 		Post Options Post Options   Quote iareanet Quote  Post ReplyReply Direct Link To This Post Posted: 29 Apr 2010 at 7:45pm
Dim DBConnection As New ADODB.Connection
    Dim Attachments As New ADODB.Recordset
    Dim myStream As ADODB.Stream
 are giving me an error
expecting a specific object type
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 875 		Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 18 Feb 2010 at 8:50pm
Hi Jen (sorry for the delayed response, I didn't spot this)
 
As far as I am aware there aren't any issues with file size - I've definitely done 1MB files before.  It could be a setting in the database options that controls this, or a time out?  What error code does it give?
Daniel Tallentire
Support
Parker Software
Back to Top
jenik View Drop Down
New User
New User


Joined: 15 Jun 2009
Location: San Jose, CA
Posts: 2 		Post Options Post Options   Quote jenik Quote  Post ReplyReply Direct Link To This Post Posted: 14 Jan 2010 at 7:28pm
i'm using a variation on this script to insert an attachment into CRM.  but it seems to fail when the attachment is over 200 KB.  is there a setting either in Email2DB or on the server that i can do to increase the file size limit?  i've added a buffer of 500KB to my code, but it doesn't seem to make a difference:

myStream.Read(512000)

thanks,
jen
Back to Top
Stephen View Drop Down
Admin Group
Admin Group
Avatar

Joined: 21 Oct 2005
Location: Stoke on Trent
Posts: 1392 		Post Options Post Options   Quote Stephen Quote  Post ReplyReply Direct Link To This Post Posted: 19 May 2009 at 1:50pm
For SQL Server you could also use:
 
 Attachments.Open "Attachments",DBConnection,adOpenDynamic,adLockOptimistic,adCmdTableDirect
 
The 'adCmdTableDirect' opens the specified table directly.
 
Steve
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.