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
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 881
Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Topic: Script for inserting binary file data into a db
    Posted: 04 Aug 2008 at 2:37pm
The following script modifies the "Sample" script for "Backup To MS Access & Save Attachments" and saves the data directly to the table.
 
This is the layout of the attachments table:
 
 
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 'Attachments' in the database.
    If MSG_AttachmentCount=0 Then Exit Sub ' if no attachments do nothing
    Dim DBConnection As New ADODB.Connection
    Dim Attachments As New ADODB.Recordset
    Dim myStream As ADODB.Stream
    Dim A As Integer
    On Error GoTo erl
    DBConnection.ConnectionString=SQL_Connection ' use the connection string specified on the Database tab
    DBConnection.Open
    ' open the Attachments table
    Attachments.Open "Attachments",DBConnection,adOpenDynamic,adLockOptimistic
    For A=1 To MSG_AttachmentCount
        If len(msg_attachment(A)) > 0
          AddToLog("Processing: " & MSG_Attachment(A))
          Attachments.AddNew
          Attachments.Fields("UID")=MSG_UID
          Attachments.Fields("AttachmentNumber")=A
          Attachments.Fields("AttachmentName")=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))
          Attachments.Fields("AttachmentData")=myStream.Read
          Attachments.Fields("AttachmentSize")=myStream.Size
          myStream.Close
          Attachments.Fields("AttachmentType")=MSG_AttachmentType(A)
          Attachments.Update
      End If
    Next A
    Attachments.Close
    DBConnection.Close
    Call AddToLog("Attachment table updated successfully")
    Exit Sub
erl:
    Call AddToLog("Error in script: " & Err.Description)
End Sub


Edited by Daniel - 28 Jan 2009 at 11:00am
Daniel Tallentire
Support
Parker Software
Back to Top
Eds View Drop Down
New User
New User


Joined: 12 Nov 2008
Location: United States
Posts: 3
Post Options Post Options   Quote Eds Quote  Post ReplyReply Direct Link To This Post Posted: 16 Nov 2008 at 6:26pm
When writing to MySQL 5.1 you will need the following or else the binary field will be null.
 
 DBConnection.CursorLocation = adUseClient
 DBConnection.Open
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 881
Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 19 Nov 2008 at 11:50am
Hi Eds,

Thanks for that update!
Daniel Tallentire
Support
Parker Software
Back to Top
matrixIII View Drop Down
Professional
Professional


Joined: 21 Jul 2008
Posts: 68
Post Options Post Options   Quote matrixIII Quote  Post ReplyReply Direct Link To This Post Posted: 17 May 2009 at 4:45pm
For MS SQL 2005 the following line as giving an error - something about stored procedure not found.
 Attachments.Open "Attachments",DBConnection,adOpenDynamic,adLockOptimistic
 
I changed it to the following and it started working
Attachments.Open "Select * from Attachments where 1=2",DBConnection,adOpenDynamic,adLockOptimistic
 
Thanks.. this article was a great help!


Edited by matrixIII - 17 May 2009 at 4:46pm
Back to Top
Stephen View Drop Down
Admin Group
Admin Group
Avatar

Joined: 21 Oct 2005
Location: Stoke on Trent
Posts: 1415
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
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
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 881
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
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: 881
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
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
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down



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